Oracle Database 12c (12.1) でSPM機能を試してみた
はじめに
Oracle DBでSQLの実行計画をベースラインとして管理できるSPM(SQL Plan Management)という機能がある。
今回、これを使ってSQLの実行計画の登録と固定化を試してみた。
通常、実行計画は統計情報を元にオプティマイザが動的に最適なものを作成してくれるため、
統計情報やデータ量の変化によって実行計画は変化しうるものである。
多くの場合は、このオプティマイザにSQL実行のたび(正確に言うとハードパースのたび)に判断に委ねて実行計画を作ってもらうのだが、
場合によってはこれをよしとせず、一度作った実行計画で固定して安定運用したい場合がある。例えば、Oracle DBのバージョンアップ前後で実行計画が変化しうるため、アプリケーションの急激な性能劣化となるリスク低減のためこれまで運用した実行計画で固定化する場合などである。
SPMはそのような場合に、実行計画のベースラインを管理し、固定化や無効化といった運用が可能となる機能である。
今回の検証環境は以下のOracle DBのバージョンは12c (12.1)。
select banner from v$version;
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production PL/SQL Release 12.1.0.2.0 - Production "CORE 12.1.0.2.0 Production" TNS for Linux: Version 12.1.0.2.0 - Production NLSRTL Version 12.1.0.2.0 - Production
SPM機能を有効化する
デフォルトの状態は、optimizer_use_sql_plan_baselinesがTRUEのためSPM機能は有効な状態ではある一方で、optimizer_capture_sql_plan_baselinesがFALSEのためベースラインが蓄積されずdba_sql_plan_baselinesビューが0件の状態になっている。
まずは、これらのオプションをTRUEに変更し、実行されたSQLの実行計画を自動的にキャプチャしベースラインに蓄積するようにする。
alter system set optimizer_capture_sql_plan_baselines=TRUE;
以下のように両方のパラメータがTRUEになっていればOK。DB再起動は特に不要。
select name, value, description from v$parameter where name like '%sql_plan_baselines'
| NAME | VALUE | DESCRIPTION |
|---|---|---|
| optimizer_capture_sql_plan_baselines | FALSE | automatic capture of SQL plan baselines for repeatable statements |
| optimizer_use_sql_plan_baselines | TRUE | use of SQL plan baselines for captured sql statements |
これで適当にSQLを実行した結果、以下のクエリでベースラインの数が増えていることが確認できる。
select count(1) cnt from dba_sql_plan_baselines
| CNT |
|---|
| 10 |
ベースラインを固定化する
実行計画が固定化されることを確認するため、以下のシナリオを考えてみる。
# ある少量データのテーブルに対してフルスキャンで検索を行う実行計画を固定化する
# テーブルにデータを大量に投入し検索列にインデックスを付与する
# インデックススキャンとならずに、依然フルスキャンで検索されていることを確認する
まずテスト用のテーブルを作成する。idとname列の2列のみを持つ単純な構造で、id列は一意とする想定だが現時点ではインデックスは作成しない。
create table emp(id number, name varchar2(100))
適当にデータを投入する。今回は以下のようなダミーデータを用意した。
select * from emp
| ID | NAME |
|---|---|
| 1 | 石川 千代乃 |
| 2 | 岡本 君子 |
| 3 | 小柴 大地 |
| 4 | 長浜 陽菜子 |
| 5 | 小山 栄次 |
| 6 | 古谷 里香 |
| 7 | 岩渕 春男 |
| 8 | 落合 栄治 |
| 9 | 三角 辰夫 |
| 10 | 須山 春男 |
さて、このテーブルを検索する以下のようなクエリを実行する。
ベースラインに実行計画をキャプチャするため、2回実行することを忘れないように。また、トレースを見たいので、sqlplusでautotraceを有効化して実行する。
SQL> set autotrace traceonly SQL> select * from emp where id = 10; 実行計画 ---------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 65 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 65 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=10) Note ----- - dynamic statistics used: dynamic sampling (level=2) - SQL plan baseline "SQL_PLAN_44mp77vsv99h5d8a279cc" used for this statement 統計 ---------------------------------------------------------- 4 recursive calls 0 db block gets 15 consistent gets 0 physical reads 0 redo size 639 bytes sent via SQL*Net to client 551 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
まず実行計画からテーブルフルスキャンが選択されていることが分かる。
また、SQL plan baseline "SQL_PLAN_44mp77vsv99h5d8a279cc"という名前のベースラインが作成され、使われていることが分かる。
さて、固定化する前にベースラインの状態を確認しておく。ベースラインを操作するためにはSQL_HANDLEとPLAN_NAMEの値が必要となるため、ここで確認しておく。
select sql_handle , plan_name , last_executed , enabled , accepted , fixed from dba_sql_plan_baselines where plan_name = 'SQL_PLAN_44mp77vsv99h5d8a279cc'
| SQL_HANDLE | PLAN_NAME | LAST_EXECUTED | ENABLED | ACCEPTED | FIXED |
|---|---|---|---|---|---|
| SQL_424ea73ef1b4a605 | SQL_PLAN_44mp77vsv99h5d8a279cc | 2018-03-30 14:44:39.000000 | YES | YES | NO |
※PCだと表がサイドバーとかぶってて見づらいかもしれませんがご了承ください
enabledとacceptedがYESなので、このベースラインは有効かつ承認された状態、つまり使われる実行計画となっていることが分かる。
一方、固定化はまだされていないため、fixedはNOとなっている。
ベースラインの操作は基本的に、dbms_spmパッケージの機能を使う。対象ベースラインの識別にはSQL_HANDLEとPLAN_NAMEを指定する。
固定化する場合は以下のPL/SQLブロックを実行する。
declare rc integer; begin rc := dbms_spm.alter_sql_plan_baseline( sql_handle => 'SQL_424ea73ef1b4a605', plan_name => 'SQL_PLAN_44mp77vsv99h5d8a279cc', attribute_name => 'FIXED', attribute_value => 'YES' ); end;
上記を実行した後、再びdba_sql_plan_baselinesビューを確認すると、FIXED値がYESに更新されたことが確認できる。
| SQL_HANDLE | PLAN_NAME | LAST_EXECUTED | ENABLED | ACCEPTED | FIXED |
|---|---|---|---|---|---|
| SQL_424ea73ef1b4a605 | SQL_PLAN_44mp77vsv99h5d8a279cc | 2018-03-30 14:44:39.000000 | YES | YES | YES |
固定化されていることの確認のため、現在10件のデータを10,000件に増幅した上でインデックスを付与してみる。
本来であればインデックススキャンとなるはずだが、固定化されていればフルスキャンのままとなるはずである。
まずダミーデータを増幅する。
select count(1) cnt from emp
| CNT |
|---|
| 10000 |
id列は1から10000までの連番となっており、一意である。
select * from emp
| ID | NAME |
|---|---|
| 1 | 石川 千代乃 |
| 2 | 岡本 君子 |
| 3 | 小柴 大地 |
| … | … |
| 9998 | 露木 正行 |
| 9999 | 大上 素子 |
| 10000 | 三好 広治 |
続いてid列にユニークインデックスを付与する。
create unique index empid on emp(id)
統計も取り直しておく。
execute dbms_stats.gather_table_stats(ownname => 'scott', tabname => 'emp');
ユニークインデックスに対して一意となる検索を行う。通常であればインデックスユニークスキャンになるはず。
SQL> set autotrace traceonly SQL> select * from emp where id = 10; 実行計画 ---------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 18 | 11 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 18 | 11 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=10) Note ----- - SQL plan baseline "SQL_PLAN_44mp77vsv99h5d8a279cc" used for this statement 統計 ---------------------------------------------------------- 15 recursive calls 0 db block gets 46 consistent gets 0 physical reads 132 redo size 639 bytes sent via SQL*Net to client 551 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
インデックスは使われず、先ほどと同様にテーブルフルスキャンとなっている。また、NoteからSQL_PLAN_44mp77vsv99h5d8a279ccが使われていることも先ほどと同様であることが分かる。
念のため、固定化の影響であることを確かめるため、固定化を外して再実行する。
declare rc integer; begin rc := dbms_spm.alter_sql_plan_baseline( sql_handle => 'SQL_424ea73ef1b4a605', plan_name => 'SQL_PLAN_44mp77vsv99h5d8a279cc', attribute_name => 'FIXED', attribute_value => 'NO' ); end;
ベースラインを確認する。enabledがNOになっており、無効化されたため、このベースラインは使われないはず。
| SQL_HANDLE | PLAN_NAME | LAST_EXECUTED | ENABLED | ACCEPTED | FIXED |
|---|---|---|---|---|---|
| SQL_424ea73ef1b4a605 | SQL_PLAN_44mp77vsv99h5d8a279cc | 2018-03-30 14:44:39.000000 | NO | YES | YES |
再度同じ検索クエリを実行する。
SQL> select * from emp where id = 10; 実行計画 ---------------------------------------------------------- Plan hash value: 248647376 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 18 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 18 | 2 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | EMPID | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=10) 統計 ---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 499 bytes sent via SQL*Net to client 540 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
インデックスが使われた！やはり固定化の影響であることが分かった。
この状態でSQL_HANDLEをキーにベースラインを確認すると、以下のように二つのベースラインが登録されている状態となった。
select sql_handle , plan_name , last_executed , enabled , accepted , fixed from dba_sql_plan_baselines where sql_handle = 'SQL_424ea73ef1b4a605'
| SQL_HANDLE | PLAN_NAME | LAST_EXECUTED | ENABLED | ACCEPTED | FIXED |
|---|---|---|---|---|---|
| SQL_424ea73ef1b4a605 | SQL_PLAN_44mp77vsv99h5158272bd | YES | NO | NO | |
| SQL_424ea73ef1b4a605 | SQL_PLAN_44mp77vsv99h5d8a279cc | 2018-03-30 14:44:39.000000 | NO | YES | YES |
複数のベースラインを評価する
まず、先ほど無効化したベースラインを再び有効化し、ついでに固定化も解除しておく。
declare rc integer; begin rc := dbms_spm.alter_sql_plan_baseline( sql_handle => 'SQL_424ea73ef1b4a605', plan_name => 'SQL_PLAN_44mp77vsv99h5d8a279cc', attribute_name => 'ENABLED', attribute_value => 'YES' ); rc := dbms_spm.alter_sql_plan_baseline( sql_handle => 'SQL_424ea73ef1b4a605', plan_name => 'SQL_PLAN_44mp77vsv99h5d8a279cc', attribute_name => 'FIXED', attribute_value => 'NO' ); end;
ベースラインは以下のようになった。
| SQL_HANDLE | PLAN_NAME | LAST_EXECUTED | ENABLED | ACCEPTED | FIXED |
|---|---|---|---|---|---|
| SQL_424ea73ef1b4a605 | SQL_PLAN_44mp77vsv99h5158272bd | YES | NO | NO | |
| SQL_424ea73ef1b4a605 | SQL_PLAN_44mp77vsv99h5d8a279cc | 2018-03-30 14:44:39.000000 | YES | YES | NO |
フルスキャンのベースラインがaccepted=YESとなっているため承認済みとなっている一方、インデックススキャンの方がaccepted=NOとなっており、承認されていないことが分かる。
Oracleで複数のベースラインがあった場合にパフォーマンスを評価して承認する仕組みがあるので、それを使ってみる。
評価の実行方法は以下の通り。なお、これはユーザが明示的に評価する場合の方法だが、夜間バッチでOracle内部的にも自動で評価されているらしい。
declare report clob; begin report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( sql_handle => 'SQL_424ea73ef1b4a605', plan_name => NULL, time_limit => DBMS_SPM.NO_LIMIT, verify => 'YES', commit => 'YES' ); DBMS_OUTPUT.put_line(report); end;
これを実行すると以下のようなレポートが出力される。
GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------
Task Information:
---------------------------------------------
Task Name : タスク_6223
Task Owner : SYS
Execution Name : EXEC_6806
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 03/30/2018 16:59:33
Finished : 03/30/2018 16:59:34
Last Updated : 03/30/2018 16:59:34
Global Time Limit : 2147483647
Per-Plan Time Limit : UNUSED
Number of Errors : 0
---------------------------------------------------------------------------------------------
SUMMARY SECTION
---------------------------------------------------------------------------------------------
Number of plans processed : 1
Number of findings : 2
Number of recommendations : 1
Number of errors : 0
---------------------------------------------------------------------------------------------
DETAILS SECTION
---------------------------------------------------------------------------------------------
Object ID : 2
Test Plan Name : SQL_PLAN_44mp77vsv99h5158272bd
Base Plan Name : SQL_PLAN_44mp77vsv99h5d8a279cc
SQL Handle : SQL_424ea73ef1b4a605
Parsing Schema : SCOTT
Test Plan Creator : SCOTT
SQL Text : select * from emp where id = 10
Execution Statistics:
-----------------------------
Base Plan Test Plan
---------------------------- ----------------------------
Elapsed Time (s): .000013 .000001
CPU Time (s): .000013 0
Buffer Gets: 3 0
Optimizer Cost: 11 2
Disk Reads: 0 0
Direct Writes: 0 0
Rows Processed: 0 0
Executions: 10 10
FINDINGS SECTION
---------------------------------------------------------------------------------------------
Findings (2):
-----------------------------
1. 計画は0.11000秒で検証されました。検証されたパフォーマンスが、ベースライン計画のパフォーマンスを12.67083倍上回ったため、利点基準に達しま
した。
2. 計画は自動的に承認されました。
Recommendation:
-----------------------------
Consider accepting the plan.
EXPLAIN PLANS SECTION
---------------------------------------------------------------------------------------------
Baseline Plan
-----------------------------
Plan Id : 8527
Plan Hash Value : 3634526668
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 11 | 00:00:01 |
| * 1 | TABLE ACCESS FULL | EMP | 1 | 18 | 11 | 00:00:01 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("ID"=10)
Test Plan
-----------------------------
Plan Id : 8528
Plan Hash Value : 360870589
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 2 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 18 | 2 | 00:00:01 |
| * 2 | INDEX UNIQUE SCAN | EMPID | 1 | | 1 | 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("ID"=10)計画は自動的に承認されました。というメッセージが出力されており、内容としてはフルスキャンのベースラインに対してインデックススキャンの実行計画の方がパフォーマンスが改善したため、こちらが承認された、という結果となった。
承認されたため検索クエリを実行する。
SQL> select * from emp where id = 10; 実行計画 ---------------------------------------------------------- Plan hash value: 248647376 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 18 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 18 | 2 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | EMPID | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=10) Note ----- - SQL plan baseline "SQL_PLAN_44mp77vsv99h5158272bd" used for this statement 統計 ---------------------------------------------------------- 22 recursive calls 17 db block gets 13 consistent gets 0 physical reads 3352 redo size 499 bytes sent via SQL*Net to client 540 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
インデックススキャンのベースラインが採用されるようになった。
ベースラインを確認する。
select sql_handle , plan_name , last_executed , enabled , accepted , fixed from dba_sql_plan_baselines where sql_handle = 'SQL_424ea73ef1b4a605'
| SQL_HANDLE | PLAN_NAME | LAST_EXECUTED | ENABLED | ACCEPTED | FIXED |
|---|---|---|---|---|---|
| SQL_424ea73ef1b4a605 | SQL_PLAN_44mp77vsv99h5158272bd | 2018-03-30 17:03:33.000000 | YES | YES | NO |
| SQL_424ea73ef1b4a605 | SQL_PLAN_44mp77vsv99h5d8a279cc | 2018-03-30 14:44:39.000000 | YES | YES | NO |
SQL_PLAN_44mp77vsv99h5158272bdの実行計画もaccepted=YESとなり、さらにlast_exectuted列も更新され使用されたことが分かる。
最後にこちらのプランで固定化しておけば、常にインデックススキャンが選択されるようになる。
declare rc integer; begin rc := dbms_spm.alter_sql_plan_baseline( sql_handle => 'SQL_424ea73ef1b4a605', plan_name => 'SQL_PLAN_44mp77vsv99h5158272bd', attribute_name => 'FIXED', attribute_value => 'YES' ); end;
| SQL_HANDLE | PLAN_NAME | LAST_EXECUTED | ENABLED | ACCEPTED | FIXED |
|---|---|---|---|---|---|
| SQL_424ea73ef1b4a605 | SQL_PLAN_44mp77vsv99h5158272bd | 2018-03-30 17:03:33.000000 | YES | YES | YES |
| SQL_424ea73ef1b4a605 | SQL_PLAN_44mp77vsv99h5d8a279cc | 2018-03-30 14:44:39.000000 | YES | YES | NO |
まとめ
以上SPMの機能を使い、実行計画を固定化する方法を確認できた。
固定化は必ずしも必要な機能ではなく多くの場合はオプティマイザに都度実行計画を判断させることが多いと思うが、
場合によっては実行計画を安定的に運用したい場合もある。
そのような場合にSPMを使えば固定化できることが分かった。
以上
