Oracle's a stored procedure in the job for automatic execution efficiency is slow, a few seconds to deal with a record, if put in PLSQL or up sqlplus to perform fast, 10000 records in about 20 seconds.
When the database is oracle12c, rac environment, the two nodes, has 32 g server memory, CPU core, server and configuration should be no problem, no deadlock, open_cursor set to 1000, the configuration is as follows:




I need to execute the stored procedure once per minute, but each time all need a long time now, to deal with the amount of data also is not very big,
System under the oracle10g/11 g is very normal, this is 12 c rac environment, is really don't know what the reason,
Which tall person myself, really appreciate,

CodePudding user response:

The 32 gb memory is a little less oh, generally in more than 128 g ~

CodePudding user response:

Under your stored procedure, look at the code?

CodePudding user response:

PROCEDURE autoprocuploadconsumerec
AS

Out_msg VARCHAR2 (1000);
Out_result NUMBER;
Vplanstoptime VARCHAR2 (128);
Vcount NUMBER;
Visbalance NUMBER;
N_opcount NUMBER;
N_oddfare NUMBER;
N_subopcount NUMBER;
N_suboddfare NUMBER;

CURSOR c1
IS
SELECT *
The FROM (SELECT *
The FROM t_xfjla
The ORDER BY Anderson, d)
WHERE ROWNUM & lt; 800;
The BEGIN
Out_msg:=';

Dbms_output. Put_line (' START PROCESS _CONSUME '| | to_char (SYSTIMESTAMP,' yy - mm - dd hh24: mi: ss: ff3 '));


FOR c1_rec IN c1
LOOP
The SELECT NVL (COUNT (*), 0)
INTO vcount
The FROM base_customers a
WHERE a.c ustomerid=c1_rec. Customerid
AND a. d. pid=c1_rec. Dpid;


- if for formal account, the lock base_customers table rows in the
IF vcount & gt; 0
THEN
The SELECT a.o pcount,
A.o ddfare,
A.s ubopcount,
A.s uboddfare
INTO n_opcount,
N_oddfare,
N_subopcount,
N_suboddfare
The FROM base_customers a
WHERE a.c ustomerid=c1_rec. Customerid
AND a. d. pid=c1_rec. Dpid
FOR UPDATE OF
A.o pcount, a.o ddfare, a.s ubopcount, a.s uboddfare skip locked;
END the IF;

The SELECT NVL (COUNT (*), 0)
INTO vcount
The FROM rec_writeoff a
WHERE a.c ustomerid=c1_rec. Customerid
AND a. d. pid=c1_rec. Dpid;



Pkg_rec_cust_acc. Processconsumerec (c1_rec customerid,
Out_msg,
Out_result);

- record deal with success, delete uploaded
The DELETE rec_upload_consume
WHERE id=c1_rec. Id;

Dbms_output. Put_line (' END PROCESS REC_UPLOAD_CONSUME '| | to_char (SYSTIMESTAMP,' yy - mm - dd hh24: mi: ss: ff3 '));
COMMIT;
END LOOP;


The EXCEPTION
The WHEN OTHERS
THEN
ROLLBACK;
Out_msg:=out_result | | '_' | | out_msg | | '_' | | SQLERRM;
Out_result:=1;
Dbms_output. Put_line (' proceupdateoldflag '| | OUT_MSG | | to_char (SYSTIMESTAMP,' yy - mm - dd hh24: mi: ss: ff3 '));

The END;

Do I use the exec dbms_scheduler job automatically performs a 1 minute, the current total of more than 1 minute at a time, PLSQL perform fast

CodePudding user response:

Memory is like 96 g, with enough

CodePudding user response:

Job execution, see if there are any locks, see what's waiting for v $session_wait events

CodePudding user response:

A minute why strips performs a record, a batch of execution is not directly? And you this didn't add monitoring time, you don't know you the SQL execution the step is slow

CodePudding user response:

Id have index no, add a index to see see! Base_customers queries the walk, and pkg_rec_cust_acc processconsumerec, what is the process FOR UPDATE OF no can not, add a log record temporary table section running record time in milliseconds, see which code is slow, run to analyze!