不為人知的技術--Oracle並行非同步執行儲存過程

背景：

最近遇到一個case，Client端程式呼叫儲存過程SP1，過程SP1執行完成後返回結果到Client。

因為過程SP1執行時間要5秒鐘，時間太長Client使用者無法接受。

分析主過程SP1效能，發現主要是其中呼叫的子過程SP2執行需要4秒，且子過程SP2中一條SQL因為資料量巨大邏輯複雜已無最佳化可能。另外子過程SP2的主要是計算審計功能並記錄日誌作用的。

設想：

能否讓使用者執行主過程時不等子過程完成就 直接返回結果 ，子過程非同步方式在後臺慢慢的執行?

直接透過Oracle的技術能否實現？

答案是可以的，透過DBMS_JOB.SUBMIT 下面的方法可以實現。

實現：

建立log表：

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

建立子過程：

CREATE OR REPLACE PROCEDURE SFIS1.JOB_SUBMIT (DATA3  IN  VARCHAR2)
IS
BEGIN
    IF DATA3='1' THEN
        dbms_lock.sleep(10); --模擬子過程執行10秒需要
        insert into sfis1.jobsubmit_log values(SYSDATE,DATA3);
    ELSE
        insert into sfis1.jobsubmit_log values(SYSDATE,DATA3);
    END IF;
    commit;
EXCEPTION
   WHEN OTHERS
   THEN
    DBMS_OUTPUT.PUT_LINE('FAIL2!');       
END;

建立主過程：

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)); --計算主過程執行總時間
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('FAIL!');
END;

執行主過程：

exec SFIS1.JOB_SP('1','2');

結果：

Start Time:2019-06-04-10:11:12
Elapsed Time:0 --執行時間0秒，說明子過程已經非同步在後臺執行了，主過程並沒有等待子過程執行完成

查詢log表：

select * from sfis1.jobsubmit_log;

2019/6/4 10:11:13     2
2019/6/4 10:11:23     1   --進一步說明子過程非同步在後臺執行

以上，主要透過 DBMS_JOB.SUBMIT 並行非同步後臺執行儲存過程。

本文使用技術主要參考Tom大師：