読者です 読者をやめる 読者になる 読者になる

kagamihogeの日記

kagamihogeの日記です。

Oracle11gのPL/SQLのネイティブコンパイル

Oracle DB

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;

参考:プロシージャのコンパイル - オラクル・Oracle PL/SQL 入門

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;

PL/SQLコンパイル・モードをネイティブに変更する。

ALTER SYSTEM SET PLSQL_CODE_TYPE = NATIVE;

Oracleインスタンスとリスナーを停止する。

$ 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 木 220 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のチューニングを積極的に進めたい場合には検討されるオプションなんだろうな、と思いました。