PL/SQL の ネイティブ・コンパイル(PLSQL_CODE_TYPE=NATIVE) でループ処理を高速化してみる。(Oracle Database)
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 がブン回っているようなら、試してみて下さいね。
彡(^)(^)
