SQL計画ベースライン

19c新機能検証の第4回になります。

今回はSQL計画ベースラインについて検証します。

この機能はSE2(Standard Edition 2)、EE(Enterprise Edition)いずれでも利用可能で、勿論Exadataでも使えます。

検証に使用した環境は以下の通りです。

Oracle RAC（2ノード） EE 19.5.0.0.0

Oracle Linux Server release 7.6

 EXADATA DATABASE MACHINE X6-2

【オラクル認定資格試験対策書】ORACLE MASTER Bronze［Bronze DBA 12c］（試験番号：1Z0-065）完全詳解＋精選問題集 オラクルマスタースタディガイド [ エディフィストラーニング株式会社 ]

概略は以下の通りです。

・機能について
SQL計画管理は、データベースで既知の計画または確認済の計画のみが使用されるようにオプティマイザで実行計画を自動的に管理する予防的なメカニズムとなる。

・処理概要
SQL計画のパフォーマンス低下の自動解決を実施する。
SQL計画管理は、自動ワークロード・リポジトリ(AWR)内のSQL文を検索し、最大負荷によって優先順位を付けることで、SPM展開アドバイザは使用可能なすべてのソースにある代替計画を検索し、パフォーマンスが優れた計画を自動的にSQL計画ベースラインに追加する。

・参考資料

Oracle Database SQLチューニング・ガイド
28.2.2.3 SPM展開アドバイザの自動タスクの構成
DBMS_SPM.SET_EVOLVE_TASK_PARAMETERプロシージャを使用してタスク・パラメータを指定することにより、計画の自動展開を構成します。

・設定方法について
DBMS_SPM.SET_EVOLVE_TASK_PARAMETERプロシージャを使用してタスク・パラメータを指定することにより、計画の自動展開を構成する。デフォルトでは自動的に有効となる。

12cと異なり、optimizer_capture_sql_plan_baselines= FALSEであっても、SQL計画ベースラインが自動的に作成され、適用される点に注意が必要です。

実際に見てみます。

SQL> sho parameter baseline

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE

SQL> select SQL_HANDLE,PLAN_NAME,ACCEPTED,CREATED from dba_sql_plan_baselines where CREATED >= sysdate -1 order by
CREATED;

SQL_HANDLE PLAN_NAME ACC CREATED
------------------------------ ---------------------------------------- --- ------------------------------
SQL_126d628a473492a9 SQL_PLAN_14vb2j93m94p967b20bb8 NO 28-NOV-19 11.14.10.580063 AM
SQL_85b30c3dc9ecf9fa SQL_PLAN_8bcsc7r4ytygu852a6613 YES 28-NOV-19 11.14.12.038596 AM
SQL_83b6e2bbec91a326 SQL_PLAN_87dr2rgq938t66d316c9a NO 28-NOV-19 11.14.13.267076 AM
SQL_7cad78c4bd3be22d SQL_PLAN_7tbbsskymrsjdcb5156c7 NO 28-NOV-19 11.52.29.038060 AM
SQL_f55ce250f87cf0fa SQL_PLAN_gar72a3w7tw7u2f9b5067 NO 28-NOV-19 12.38.47.474582 PM
SQL_7cad78c4bd3be22d SQL_PLAN_7tbbsskymrsjdd080421d NO 28-NOV-19 01.40.34.527291 PM
SQL_f55ce250f87cf0fa SQL_PLAN_gar72a3w7tw7u3017bdc5 NO 28-NOV-19 02.11.57.146237 PM
SQL_e80854b743b0e69c SQL_PLAN_fh22nqx1v1tnwd9e44527 YES 28-NOV-19 10.00.12.837280 PM

幾つかのSQL計画ベースラインが作成され、その内の2つは適用されています。

制御するパラメータは以下の通りで、12cの時は無かったものです。

SQL> select distinct PARAMETER_NAME,PARAMETER_value from DBA_ADVISOR_PARAMETERS where PARAMETER_NAME like 'ALTER
NATE%';

PARAMETER_NAME PARAMETER_VALUE
---------------------------------------- --------------------
ALTERNATE_PLAN_BASELINE AUTO
ALTERNATE_PLAN_SOURCE AUTO
ALTERNATE_PLAN_LIMIT UNLIMITED

無効にする方法は以下の通りです。

BEGIN
　 DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
　 task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
　 parameter => 'ALTERNATE_PLAN_BASELINE',
　 value => '');
END;
/
BEGIN
　 DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
　 task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
　 parameter => 'ALTERNATE_PLAN_SOURCE',
　 value => '');
END;
/

作成はするが使用しないのみならば、初期化パラメータの設定のみでOKです。

optimizer_use_sql_plan_baselines=false

では、実際にSQL実行時の挙動を確認します。

※複雑で長いSQLなので省略しています。
1回目の実行では13.77秒かかりました。SQLベースラインが作成され、適用されているのが判ります。

SQL> SELECT * FROM （中略） WHERE ROWNUM <= 102;
no rows selected
Elapsed: 00:00:13.77

SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
SQL_ID 55rhc6dwpc7jg, child number 0
-------------------------------------
（略）
Plan hash value: 2589991747
--------------------------------------------------------------------------------------------------------------------------------
（略）
Note
- SQL plan baseline SQL_PLAN_f945bmnx83w1q9a602343 used for this statement

1回目を実行後、統計情報を取得しなおしたところ、リテラルが異なるだけの他のSQLは1秒未満で実行されるようになりましたが、前記SQLはSQL計画ベースラインがあるので、その実行計画が使用され、約14秒かかったままでした。

これはSQL計画ベースラインの残念な点ですね。

数日後に再度実行してみました。

新しいSQLベースラインが作成・使用され、0.43秒で実行されるようになったことが確認できました。

SQL> SELECT * FROM （中略） WHERE ROWNUM <= 102;
no rows selected
Elapsed: 00:00:00.43

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
SQL_ID 55rhc6dwpc7jg, child number 0
（略）
Note
-----
- SQL plan baseline SQL_PLAN_f945bmnx83w1qad979ddc used for this statement

既存のSQL計画ベースラインがあっても、より良いSQL計画ベースラインを作成し、適用できるようになった点が19cの新機能です。

検証のまとめは以下の通りです。

・検証結果の総括
12cの時は、よりよい実行計画があっても計画ベースラインにロードされるのみで、自動でACCEPTされるのは最初にロードされた実行計画のみだった。
19cの新機能により、AWRにレポートされる遅いSQLについては、よりよい実行計画がチェックされ、自動的にACCEPTされる。
19cからはoptimizer_capture_sql_plan_baselines=falseであってもSQLベースラインが自動作成される（SQLチューニングアドバイザJOBが有効で、DBMS_SPM.SET_EVOLVE_TASK_PARAMETERの設定がデフォルトの場合）
12cと初期化パラメータ、自動実行JOBの設定が同じでも動作が異なるので注意が必要。
（SPM展開アドバイザは、自動SQLチューニング・アドバイザが有効の場合、自動的に有効化される）

・考察
よりよい実行計画があっても計画ベースラインにロードされるのみで使用されないというこれまでの欠点を補う新機能となっているが、それによって実行計画が変わってしまうので、これまでのように実行計画の安定性を保証する為には対象のSQLベースラインの属性を手動でFIXに指定する必要がある。