Oracle Database の PL/SQL には ネイティブ・コンパイル という機能があり、計算集中型のプロシージャーを高速化できます。
計算集中型とは DML(SELECT, INSERT, UPDATE, DELETE等)以外のロジック部分、例えばループ処理等で時間が掛かるプログラムの事を指します。
この記事では PL/SQL の ネイティブ・コンパイル でループ処理を高速化してみますやで。
彡(ﾟ)(ﾟ)
1. 参考マニュアル
マニュアルの記述は以下の通りです。
12.10 システム固有の実行のためのPL/SQLユニットのコンパイル
https://docs.oracle.com/cd/F19136_01/lnpls/plsql-optimization-and-tuning.html#GUID-7C4557E7-4C35-4CAB-A95D-CB96BDC6D487
：
PL/SQLのネイティブ・コンパイルによって、計算集中型のプロシージャ操作のパフォーマンスは大幅に改善されます
：
関連のパラメータは下記(PLSQL_CODE_TYPE)で INTERPRETED と NATIVE を選択します。デフォルトは INTERPRETED です。
1.277 PLSQL_CODE_TYPE
https://docs.oracle.com/cd/F19136_01/refrn/PLSQL_CODE_TYPE.html#GUID-19CE54C7-6B2A-4F3E-92C5-D30AFCD01E3F
：
PLSQL_CODE_TYPE = { INTERPRETED | NATIVE }
：
2. サンプルプログラムと実行結果(ネイティブ・コンパイル前)
下記のストアド・プロシージャーでテストしてみます。10億回ループするプログラムとなります。
ALTER SESSION SET PLSQL_CODE_TYPE = INTERPRETED;
CREATE OR REPLACE PROCEDURE PRC_TEST_LOOP
IS
i PLS_INTEGER;
j PLS_INTEGER;
BEGIN
j := 0;
FOR i IN 1..1000000000
LOOP
j := i;
END LOOP;
DBMS_OUTPUT.PUT_LINE('j => ' || TO_CHAR(j));
END;
/
SET SERVEROUTPUT ON SIZE 1000000;
SET TIMING ON;
EXEC PRC_TEST_LOOP;
実行結果は以下の通りです。
SQL> ALTER SESSION SET PLSQL_CODE_TYPE = INTERPRETED;
Session altered.
Elapsed: 00:00:00.01
SQL> CREATE OR REPLACE PROCEDURE PRC_TEST_LOOP
2 IS
3 i PLS_INTEGER;
4 j PLS_INTEGER;
5 BEGIN
6 j := 0;
7 FOR i IN 1..1000000000
8 LOOP
9 j := i;
10 END LOOP;
11 DBMS_OUTPUT.PUT_LINE('j => ' || TO_CHAR(j));
12 END;
13 /
Procedure created.
Elapsed: 00:00:00.02
SQL> SQL> SET SERVEROUTPUT ON SIZE 1000000;
SQL> SET TIMING ON;
SQL> EXEC PRC_TEST_LOOP;
j => 1000000000
PL/SQL procedure successfully completed.
Elapsed: 00:00:09.54
SQL>
10秒弱程度の時間が掛かっています。
SQLトレースも取得してみました。
ALTER SESSION SET TRACEFILE_IDENTIFIER = "AYSHIBAT";
EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE, plan_stat => 'ALL_EXECUTIONS');
EXEC PRC_TEST_LOOP;
EXEC DBMS_SESSION.SESSION_TRACE_DISABLE;
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = "AYSHIBAT";
Session altered.
Elapsed: 00:00:00.00
SQL> EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE, plan_stat => 'ALL_EXECUTIONS');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
SQL> EXEC PRC_TEST_LOOP;
j => 1000000000
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.91
SQL> EXEC DBMS_SESSION.SESSION_TRACE_DISABLE;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQLトレースの結果(tkprof後の抜粋)は以下の通り。CPU がブン廻ってますやね。
彡(^)(^)
SQL ID: 9azc0vuqbanzf Plan Hash: 0
BEGIN PRC_TEST_LOOP; END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 11.89 11.90 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 11.89 11.91 0 0 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 155
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
3. ネイティブ・コンパイルとコンパイル後の実行結果
下記コマンドでサンプルのストアドをネイティブ・コンパイルしてみます。
ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE;
ALTER PROCEDURE PRC_TEST_LOOP COMPILE;
SQL> ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE;
Session altered.
SQL> ALTER PROCEDURE PRC_TEST_LOOP COMPILE;
Procedure altered.
ネイティブ・コンパイル後のプロシージャーを実行してみます。
SET SERVEROUTPUT ON SIZE 1000000;
SET TIMING ON;
ALTER SESSION SET TRACEFILE_IDENTIFIER = "AYSHIBAT";
EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE, plan_stat => 'ALL_EXECUTIONS');
EXEC PRC_TEST_LOOP;
EXEC DBMS_SESSION.SESSION_TRACE_DISABLE;
SQL> SET SERVEROUTPUT ON SIZE 1000000;
SQL> SET TIMING ON;
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = "AYSHIBAT";
Session altered.
Elapsed: 00:00:00.00
SQL> EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE, plan_stat => 'ALL_EXECUTIONS');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> EXEC PRC_TEST_LOOP;
j => 1000000000
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.65
SQL> EXEC DBMS_SESSION.SESSION_TRACE_DISABLE;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL>
3秒弱程度で完了しました。SQLトレース(tkprof後)の抜粋は以下の通り
SQL ID: 9azc0vuqbanzf Plan Hash: 0
BEGIN PRC_TEST_LOOP; END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 2.51 2.63 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 2.51 2.63 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 155
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
CPU時間が短くなっているのが SQLトレース でも確認できました。
彡(^)(^)
4. まとめ
ネイティブ・コンパイルでPL/SQLのロジック部分が高速化するのを確認できました。
ストアド・プロシージャ(or ファンクション/パッケージ)のロジック部分で CPU がブン回っているようなら、試してみて下さいね。
彡(^)(^)