ねら~ITエンジニア雑記

やきうのお兄ちゃんが綴るOracle Databaseメインのブログ

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 がブン回っているようなら、試してみて下さいね。
彡(^)(^)