Unknown Technology-- Oracle parallel Asynchronous execution of stored procedures

Background:

Recently I encountered a case,Client program calling the stored procedure SP1, which returns the result to Client after the execution of the procedure SP1 is completed.

Because the process SP1 execution time takes 5 seconds, the time is too long for Client users to accept.

Based on the analysis of the performance of the main procedure SP1, it is found that it takes 4 seconds for the SP2 execution of the subprocedure called in it, and it is impossible to optimize a SQL in the subprocedure SP2 because of the huge amount of data and complex logic. In addition, the main function of the subprocess SP2 is to calculate the audit function and record the log function.

Imagine:

Can the user return the result directly without waiting for the completion of the subprocess when the user executes the main process, and the subprocess runs slowly in the background asynchronously?

Can it be achieved directly through Oracle technology?

The answer is yes, which can be achieved through the following methods of DBMS_JOB.SUBMIT.

Achieve:

Create the log table:

CREATE TABLE SFIS1.JOBSUBMIT_LOG (EXE_TIME DATE, DATAX VARCHAR2 (5 BYTE))

Create a subprocess:

CREATE OR REPLACE PROCEDURE SFIS1.JOB_SUBMIT (DATA3 IN VARCHAR2) ISBEGIN IF DATA3='1' THEN dbms_lock.sleep (10);-- it takes insert into sfis1.jobsubmit_log values (SYSDATE,DATA3) to simulate the execution of a subprocess for 10 seconds; ELSE insert into sfis1.jobsubmit_log values (SYSDATE,DATA3); END IF; commit;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('FAIL2procedures'); END

Create the main process:

CREATE OR REPLACE PROCEDURE SFIS1.JOB_SP (DATA1 IN VARCHAR2,DATA2 IN VARCHAR2) IS START_TIME DATE; l_job NUMBER;BEGIN START_TIME: = SYSDATE; DBMS_OUTPUT.PUT_LINE ('Start Time:' | | TO_CHAR (START_TIME,' YYYY-MM-DD-HH24:MI:SS')); DBMS_JOB.submit (l_job, 'SFIS1.JOB_SUBMIT (' | | data1 | |');) DBMS_JOB.submit (l_job, 'SFIS1.JOB_SUBMIT (' | data2 | |'); COMMIT; DBMS_OUTPUT.PUT_LINE ('Elapsed Time:' | | CEIL ((SYSDATE-START_TIME) * 24 * 60 * 60));-- calculate the total running time of the main process EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (' faulty'); END

Execute the main process:

Exec SFIS1.JOB_SP ('1m and 2')

Results:

Start Time:2019-06-04-10:11:12

Elapsed Time:0-the execution time is 0 seconds, indicating that the sub-procedure has been executed asynchronously in the background, and the main process does not wait for the execution of the sub-procedure to complete.

Query log table:

Select * from sfis1.jobsubmit_log

2019-6-4 10:11:13 2

2019-6-4 10:11:23 1-further explanation that subprocedures are executed asynchronously in the background

Above, the stored procedure is executed mainly through the DBMS_JOB.SUBMIT parallel asynchronous background.

The technology used in this paper mainly refers to Master Tom:

Https://asktom.oracle.com/pls/asktom/asktom.search?tag=execute-procedures-concurently-in-a-procedure