Oracle11gのPL/SQLのネイティブコンパイルを試す。
環境
やったこと
検証用プログラム
どういう速度計測プログラムを書くことが検証に都合が良いかイマイチわからなかった。なので、とりあえずUTL_FILEを使用してテキストファイルに100万行書き出すだけのPL/SQLを書いた。各種設定後、このPL/SQLの実行時間に違いが出るかをを調べる。
CREATE OR REPLACE PROCEDURE FILE_WRITE_WITH_UTL_FILE AS fileHandle UTL_FILE.FILE_TYPE; BEGIN fileHandle := UTL_FILE.FOPEN('CSV_OUTPUT_DIR','test.txt','w',100); FOR I IN 1..1000000 LOOP UTL_FILE.PUT_LINE(fileHandle, 'hoge' || I); END LOOP; UTL_FILE.FCLOSE(fileHandle); END FILE_WRITE_WITH_UTL_FILE;
PL/SQLのネイティブ・コンパイルの初期化パラメータ設定
Oracle Database PL/SQL言語リファレンス11gリリース2(11.2) 12 PL/SQLの最適化とチューニング 12 PL/SQLの最適化とチューニング の手順に従って、設定などを変更していく。
まず、PL/SQLのコンパイル・モードをデフォルトのINTERPRETEDというインタプリタからNATIVEのネイティブへと変更する。初期化パラメータPLSQL_CODE_TYPEの詳細はこちら→Oracle Databaseリファレンス11gリリース2 (11.2) PLSQL_CODE_TYPE
ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE; --または ALTER SYSTEM SET PLSQL_CODE_TYPE = NATIVE;
まぁフツーはシステムレベルで設定するんでないかと思う。
また、下記のようなコマンドでネイティブコンパイルが可能かどうかチェックが出来る。
ALTER PROCEDURE FILE_WRITE_WITH_UTL_FILE COMPILE PLSQL_CODE_TYPE=NATIVE REUSE SETTINGS;
PL/SQL全体をネイティブコンパイルする。
上記の設定は、あくまでもこれからコンパイルされるPL/SQLに対してである。つまり、Oracleが既に持っているモジュールはそのまま＝ネイティブコンパイルされていない、である。なので、PL/SQL全体をネイティブコンパイルしてやらないと、片手落ちである。
その手順は、Oracle Database PL/SQL言語リファレンス11gリリース2(11.2) 12 PL/SQLの最適化とチューニング 12 PL/SQLの最適化とチューニングにあるので、これに沿って作業をしていく。
PL/SQLがネイティブコンパイルできるかどうかをチェックする。
ALTER PROCEDURE FILE_WRITE_WITH_UTL_FILE COMPILE PLSQL_CODE_TYPE=NATIVE REUSE SETTINGS;
ALTER SYSTEM SET PLSQL_CODE_TYPE = NATIVE;
$ sqlplus / as sysdba
SQL> SHUTDOWN NORMAL;
データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。
参考：-データベースの起動（SQL*Plus版） − ＠IT
# lsnrctl LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 20-2月 -2014 16:54:56 Copyright (c) 1991, 2011, Oracle. All rights reserved. Welcome to LSNRCTL, type "help" for information. LSNRCTL> stop (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))に接続中 コマンドは正常に終了しました。
アップグレード・モードでOracleを起動する。
$ sqlplus /nolog SQL*Plus: Release 11.2.0.2.0 Production on 木 2月 20 17:04:12 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. SQL> CONNECT / AS SYSDBA アイドル・インスタンスに接続しました。 SQL> STARTUP UPGRADE ORACLEインスタンスが起動しました。 Total System Global Area 405020672 bytes Fixed Size 2226960 bytes Variable Size 352322800 bytes Database Buffers 46137344 bytes Redo Buffers 4333568 bytes データベースがマウントされました。 データベースがオープンされました。
無効なPL/SQLユニットを一覧表示する。
SELECT o.OWNER, o.OBJECT_NAME, o.OBJECT_TYPE FROM DBA_OBJECTS o, DBA_PLSQL_OBJECT_SETTINGS s WHERE o.OBJECT_NAME = s.NAME AND o.STATUS='INVALID'; OWNER ------------------------------ OBJECT_NAME --------------------------------------------------------------------------------OBJECT_TYPE ------------------- KAGAMIHOGE ADD_DATA_TO_EMP_DEPT_CLUSTER PROCEDURE PUBLIC STATSPACK SYNONYM OWNER ------------------------------ OBJECT_NAME --------------------------------------------------------------------------------OBJECT_TYPE ------------------- PUBLIC STATSPACK SYNONYM
俺が作成したADD_DATA_TO_EMP_DEPT_CLUSTERてのは、たぶんコンパイルエラーのまま放置してるからだと思われる。もう一つは謎。大勢には影響ないと思われるんで、ひとまず放置する。なお、個別にコンパイルしなおす方法は先のドキュメントに書いてある。
NATIVEおよびINTERPRETEDでコンパイルされるオブジェクトの数を確認する。
SELECT TYPE, PLSQL_CODE_TYPE, COUNT(*) FROM DBA_PLSQL_OBJECT_SETTINGS WHERE PLSQL_CODE_TYPE IS NOT NULL GROUP BY TYPE, PLSQL_CODE_TYPE ORDER BY TYPE, PLSQL_CODE_TYPE; TYPE ------------ PLSQL_CODE_TYPE -------------------------------------------------------------------------------- COUNT(*) ---------- FUNCTION INTERPRETED 218 LIBRARY INTERPRETED 171 TYPE ------------ PLSQL_CODE_TYPE -------------------------------------------------------------------------------- COUNT(*) ---------- PACKAGE INTERPRETED 953 PACKAGE BODY INTERPRETED TYPE ------------ PLSQL_CODE_TYPE -------------------------------------------------------------------------------- COUNT(*) ---------- 907 PROCEDURE INTERPRETED 125 PROCEDURE TYPE ------------ PLSQL_CODE_TYPE -------------------------------------------------------------------------------- COUNT(*) ---------- NATIVE 1 TRIGGER INTERPRETED 511 TYPE ------------ PLSQL_CODE_TYPE -------------------------------------------------------------------------------- COUNT(*) ---------- TYPE INTERPRETED 1529 TYPE BODY INTERPRETED 152 TYPE ------------ PLSQL_CODE_TYPE -------------------------------------------------------------------------------- COUNT(*) ---------- 9行が選択されました。
ユーザーSYSとして$ORACLE_HOME/rdbms/admin/dbmsupgnv.sqlスクリプトを実行する。実行途中で値入力を求められるけど、とりあえず空のままエンターする。パラメータの意味は例のドキュメントに書いてある。
$ sqlplus / as sysdba SQL> @$ORACLE_HOME/rdbms/admin/dbmsupgnv.sql （中略） SQL> begin 2 dbmsncdb.setup_for_native_compile(&1); 3 end; 4 / 1に値を入力してください:
コミットする。
SQL> commit;
DBを停止する。
SQL> SHUTDOWN NORMAL;
データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。
DBを起動する。
SQL> STARTUP ORACLEインスタンスが起動しました。 Total System Global Area 405020672 bytes Fixed Size 2226960 bytes Variable Size 352322800 bytes Database Buffers 46137344 bytes Redo Buffers 4333568 bytes データベースがマウントされました。 データベースがオープンされました。
他のセッションが入ってこないように、接続制限モードにする。
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
システムが変更されました。
参考：インスタンスの接続制限 - オラクル・Oracleをマスターするための基本と仕組み
SYSで$ORACLE_HOME/rdbms/admin/utlrp.sqlを実行し、すべてのPL/SQLモジュールを再コンパイルする。全モジュールが対象なのでさすがに時間がかかる。ドキュメントによると並列度の設定が可能とあるので、コンパイルのスピードアップを図る場合にはソレを設定するのだと思われる。
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql （中略） ...Database user "SYS", database schema "APEX_040000", user# "47" 17:44:01 ...Compiled 0 out of 2720 objects considered, 0 failed compilation 17:44:02 ...234 packages ...227 package bodies ...426 tables ...12 functions ...19 procedures ...3 sequences ...439 triggers ...1177 indexes ...175 views ...0 libraries ...4 types ...0 type bodies ...0 operators ...0 index types ...Begin key object existence check 17:44:02 ...Completed key object existence check 17:44:02 ...Setting DBMS Registry 17:44:02 ...Setting DBMS Registry Complete 17:44:02 ...Exiting validate 17:44:02 PL/SQLプロシージャが正常に完了しました。
無効なPL/SQLユニットを一覧表示する。
SELECT o.OWNER, o.OBJECT_NAME, o.OBJECT_TYPE FROM DBA_OBJECTS o, DBA_PLSQL_OBJECT_SETTINGS s WHERE o.OBJECT_NAME = s.NAME AND o.STATUS='INVALID'; OWNER ------------------------------ OBJECT_NAME --------------------------------------------------------------------------------OBJECT_TYPE ------------------- KAGAMIHOGE ADD_DATA_TO_EMP_DEPT_CLUSTER PROCEDURE 1行が選択されました。
コンパイルエラーのまま放置しているヤツだけになったのが確認できる。
NATIVEおよびINTERPRETEDでコンパイルされるオブジェクトの数を確認する。
SQL> SELECT TYPE, PLSQL_CODE_TYPE, COUNT(*) FROM DBA_PLSQL_OBJECT_SETTINGS WHERE PLSQL_CODE_TYPE IS NOT NULL GROUP BY TYPE, PLSQL_CODE_TYPE ORDER BY TYPE, PLSQL_CODE_TYPE; TYPE ------------ PLSQL_CODE_TYPE -------------------------------------------------------------------------------- COUNT(*) ---------- FUNCTION NATIVE 218 LIBRARY INTERPRETED 171 PACKAGE INTERPRETED 953 PACKAGE NATIVE 1 PACKAGE BODY NATIVE 908 PROCEDURE NATIVE 126 TRIGGER NATIVE 511 TYPE INTERPRETED 1529 TYPE BODY NATIVE 152 9行が選択されました。
詳しいことはよーわからんけど、NATIVEになってるのが増えてるんでタブン成功してると思われる。
接続制限モードを解除する。
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
システムが変更されました。
リスナーを起動する。
# lsnrctl LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 20-2月 -2014 17:53:03 Copyright (c) 1991, 2011, Oracle. All rights reserved. Welcome to LSNRCTL, type "help" for information. LSNRCTL> start /u01/app/oracle/product/11.2.0/xe/bin/tnslsnrを起動しています。お待ちください... （中略）
速度計測
というわけで、下記3条件で速度計測した。下記のうち「初期化パラメータ設定のみ」はALTER SYSTEM SET PLSQL_CODE_TYPE = NATIVEしただけの状態を指す。「全体のネイティブコンパイル」はこのエントリで書いてあるすべての手順を行った状態を指す。
| 種類 | 1 | 2 | 3 |
|---|---|---|---|
| なし | 34.687 | 34.562 | 34.610 |
| 初期化パラメータ設定のみ | 34.687 | 34.750 | 34.735 |
| 全体のネイティブコンパイル | 33.109 | 33.094 | 33.234 |
たかだか1秒ちょいくらいではあるものの、差が出ることは確認できた。
感想とか
CPUとかもっとOSリソースを消費するコードでないと大きな差は出てこないんだろうけども。こんだけカンタンなプログラムでも差が出るんで、PL/SQLのチューニングを積極的に進めたい場合には検討されるオプションなんだろうな、と思いました。
