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億回ループするプログラムとなります。

```sql
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
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トレースも取得してみました。

```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
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
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. ネイティブ・コンパイルとコンパイル後の実行結果

下記コマンドでサンプルのストアドをネイティブ・コンパイルしてみます。

```sql
ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE;
ALTER PROCEDURE PRC_TEST_LOOP COMPILE;
```

```sql
SQL> ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE;

Session altered.

SQL> ALTER PROCEDURE PRC_TEST_LOOP COMPILE;

Procedure altered.
```

ネイティブ・コンパイル後のプロシージャーを実行してみます。

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