Oracleの実行計画（SPM） - 忘れかけのIT備忘録

今回はOracleの実行計画のSPMについて調査、検証しました。

SPM（SQL Plan Management：SQL計画管理）とは
Oracle 11gから登場した機能で、SQLごとに実行計画を固定化したり、統計情報の変化によって新たに生成された最適な実行計画を候補として登録したりなど実行計画を管理します。
オプティマイザが最適と判断した実行計画が登録されており、SQLのパフォーマンスに応じて使用する実行計画を制御できるメリットがあります。
なお、類似の機能としてプラン・スタビリティという機能がありますが、こちらは実行計画を固定化する機能で、11gから非推奨となりました。

【補足】
SQL管理ベース（SQL Management Base）
SQLごとのSQL計画履歴を保管する領域
SYSAUX表領域に存在する

SQL計画ベースライン
SQL計画履歴の（1つ以上の）複数の実行計画のうち、承認され使用できる実行計画
SQLはSQL計画ベースラインに登録されている実行計画のみ使用できる（DBAが承認した実行計画のみ使用できる）
SQLごとに存在する

SQL計画履歴
SQLに対して生成されたすべての実行計画
SQL計画ベースラインは承認された実行計画のみ格納されているが、SQL計画履歴には承認済み/未承認の実行計画が格納されている
自動収集された実行計画もSQL計画履歴に格納されるが、承認されるまで使用できない
SQLごとに存在する

プラン・スタビリティ
実行計画を固定化する機能
SQLの実行計画をストアド・アウトライン（実行計画を再現するためのオプティマイザヒントのセット）としてディクショナリ（OUTLNスキーマ）に格納される
プラン・スタビリティが有効にされたセッションでストアド・アウトラインが作成されたSQLを実行した場合、格納されたオプティマイザヒントが取り出され、SQLに適用される
実行計画を誘導したいがパッケージ製品のSQLのためSQLが修正できない（ヒント句が付与できない）状況で有効
※プラン・スタビリティを検証するため、書籍やWebサイトの手順を参考にしましたが実行計画の固定化はできませんでした
　私の検証環境が12cR1 Enterprise Editionだったのが原因かもしれません（Enterprise Editionの場合、SPMが使用できるためあえてプラン・スタビリティは使えないようになっている？）
　Standard Editionが手に入ったら別途検証してみようと思います
　プラン・スタビリティについて参考資料だけ載せておきます
（参考）
新・門外不出のOracle現場ワザエキスパートが明かす運用・管理の極意（151～154ページ）
【Oracle Database】実行計画の固定化方法まとめ | アシスト

SPMを使用した実行計画の登録方法
実行登録の方法は2種類（自動登録、手動登録）あります

SPM展開アドバイザ
12cから自動メンテナンスタスクに追加された新機能（SYS_AUTO_SPM_EVOLVE_TASK）で、SQL計画履歴に追加された実行計画を展開（未承認の実行計画の優位性の検証と承認）します。
11gまでは新たに生成された実行計画は1つ目はSQL計画ベースラインとして登録されますが、2つ目以降は未承認の実行計画としてSQL計画履歴に登録されます（管理者に承認される前に使用されるとパフォーマンス低下に繋がる可能性があるため、あえて未承認の状態にしています）
実行計画を承認するためには管理者が手動（DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE()など）でSQL計画ベースラインに登録する必要がありました。
よって、新たに追加された実行計画が最適な実行計画でも管理者が承認しない限り、すぐに使用できませんでした。
SPM展開アドバイザは、新たに追加された実行計画が現行の実行計画（SQL計画ベースライン）より優位性が高いと判断された場合、自動的に実行計画が承認されます
なお、19c以降はAWRにレポートされる遅いSQLについてより良い実行計画が見つかった場合、優位性を検証し、有効と判断した場合、自動的に承認（SQL計画ベースラインに登録）されます
（参考）SQL計画ベースラインの管理

私の経験上、実行計画を固定化するためにSPMを使用する現場が多かったです。（DBバージョンアップ後にSQLのパフォーマンスが悪くなり、バージョンアップ前の実行計画に固定するという要件が多かった）
今回は実行計画を固定化する方法のうち、今までの現場で最も多く使用していた方法「手動で共有SQL領域（カーソル）から実行計画をロードする方法」を検証しました。

■前提
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES BOOLEAN初期化パラメータはFALSE

■検証環境
OS：Oracle Linux 6.5
DB/GI：Oracle Database 12c Release 1 (12.1.0.2.0) Enterprise Edition
※2ノードRAC(管理者管理型DB)

■検証パターン
①実行計画の固定化（共有SQL領域（カーソル）から実行計画をロード）
②固定化した実行計画を削除

■検証
①実行計画の固定化（共有SQL領域（カーソル）から実行計画をロード）
性能の悪いSQLの実行計画を性能の良いSQLの実行計画で固定化した場合、性能の悪いSQLを実行しても固定化した実行計画で実行されるかどうか検証します

【検証手順】
1. [セッション1 / アプリケーションユーザ] 性能の悪いSQLを実行
2. [セッション2 / 管理者ユーザ] 性能の悪いSQLのSQL識別子、シグネチャを取得
3. [セッション2 / 管理者ユーザ] 性能の悪いSQLの実行計画を取得
4. [セッション1 / アプリケーションユーザ] 性能の良いSQLを実行
5. [セッション2 / 管理者ユーザ] 性能の良いSQLのSQL識別子、シグネチャを取得
6. [セッション2 / 管理者ユーザ] 性能の良いSQLの実行計画を取得
7. [セッション2 / 管理者ユーザ] SQL計画ベースラインの取得（性能の悪いSQLの実行計画ロード前）
8. [セッション2 / 管理者ユーザ] 性能の悪いSQLの実行計画をSQL計画ベースラインとして登録
9. [セッション2 / 管理者ユーザ] SQL計画ベースラインの取得（性能の悪いSQLの実行計画ロード後）
10. [セッション2 / 管理者ユーザ] 性能の悪いSQLのSQL計画ベースラインの属性変更（現在の実行計画の無効化）
11. [セッション2 / 管理者ユーザ] 属性変更したSQL計画ベースラインの確認（属性変更後）
12. [セッション2 / 管理者ユーザ] 性能の悪いSQLのSQL計画ベースラインのSQLハンドルと性能の良いSQLのSQLID、実行計画のセットを使用して新しい実行計画を登録
13. [セッション2 / 管理者ユーザ] SQL計画ベースラインの取得（属性変更前）
14. [セッション2 / 管理者ユーザ] 新たに登録したSQLのSQL計画ベースラインの属性変更（自動パージ無効化）
15. [セッション2 / 管理者ユーザ] SQL計画ベースラインの取得（属性変更後）
16. [セッション1 / アプリケーションユーザ] 性能の悪いSQLの実行計画を取得

【作業ログ】

1. [セッション1 / アプリケーションユーザ] 性能の悪いSQLを実行
SQL> SELECT /* BAD_XPLAN */ E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME FROM DEPT D, EMP E WHERE D.DEPTNO = E.DEPTNO;

EMPNO ENAME DEPTNO DNAME
---------- --------------- ---------- ---------------
7782 CLARK 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
（略）
7900 JAMES 30 SALES
7698 BLAKE 30 SALES
7654 MARTIN 30 SALES

14 rows selected.

2. [セッション2 / 管理者ユーザ] 性能の悪いSQLのSQL識別子、シグネチャを取得
SQL> SELECT SQL_ID, PLAN_HASH_VALUE, SQL_TEXT, EXACT_MATCHING_SIGNATURE, FIRST_LOAD_TIME FROM V$SQL WHERE SQL_TEXT like '%BAD_XPLAN%' order by FIRST_LOAD_TIME desc;

SQL_ID PLAN_HASH_VALUE SQL_TEXT EXACT_MATCHING_SIGNATURE FIRST_LOAD_TIME
------------- --------------- -------------------------------------------------------------------------------- ---------------------------- --------------------
8phh3bbfzfb9p 2836784050 SELECT SQL_ID, PLAN_HASH_VALUE, SQL_TEXT, EXACT_MATCHING_SIGNATURE, FIRST_LOAD_T 1,096,901,067,240,622,581 2023-03-24/10:06:33
IME FROM V$SQL WHERE SQL_TEXT like '%BAD_XPLAN%' order by FIRST_LOAD_TIME desc

chyjw3v56383h 844388907 SELECT /* BAD_XPLAN */ E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME FROM DEPT D, EMP E WH 7,132,992,821,986,115,812 2023-03-24/10:06:06
ERE D.DEPTNO = E.DEPTNO

※SQL_ID：SQL識別子（ライブラリ・キャッシュ）
※PLAN_HASH_VALUE：実行計画ID（数値）
※SQL_TEXT：SQL文（最初の1000文字）
※EXACT_MATCHING_SIGNATURE：正規化（SQL文の空白や改行の削除、非リテラル文字列の大文字化）されたSQLの識別子（ハッシュ値）
※FIRST_LOAD_TIME：SQL（親カーソル）が共有プール（ライブラリ・キャッシュ）にロードされた時刻

3. [セッション2 / 管理者ユーザ] 性能の悪いSQLの実行計画を取得
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('chyjw3v56383h'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID chyjw3v56383h, child number 0
-------------------------------------
SELECT /* BAD_XPLAN */ E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME FROM DEPT D,
EMP E WHERE D.DEPTNO = E.DEPTNO

Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 38 (100)| |
| 1 | MERGE JOIN | | 14 | 364 | 38 (3)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 182 | 36 (3)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 35 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
★ソートマージ結合になっている→ハッシュ結合に変更する

4. [セッション1 / アプリケーションユーザ] 性能の良いSQLを実行
SQL> SELECT /* GOOD_XPLAN */ /*+ USE_HASH(E) LEADING(D) */ E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME FROM DEPT D, EMP E WHERE D.DEPTNO = E.DEPTNO;

14 rows selected.

5. [セッション2 / 管理者ユーザ] 性能の良いSQLのSQL識別子、シグネチャを取得
SQL> SELECT SQL_ID, PLAN_HASH_VALUE, SQL_TEXT, EXACT_MATCHING_SIGNATURE, FIRST_LOAD_TIME FROM V$SQL WHERE SQL_TEXT like '%GOOD_XPLAN%' order by FIRST_LOAD_TIME desc;

SQL_ID PLAN_HASH_VALUE SQL_TEXT EXACT_MATCHING_SIGNATURE FIRST_LOAD_TIME
------------- --------------- -------------------------------------------------------------------------------- ---------------------------- --------------------
70kmzfpgs4sqr 2836784050 SELECT SQL_ID, PLAN_HASH_VALUE, SQL_TEXT, EXACT_MATCHING_SIGNATURE, FIRST_LOAD_T 2,437,342,059,016,891,273 2023-03-24/10:10:58
IME FROM V$SQL WHERE SQL_TEXT like '%GOOD_XPLAN%' order by FIRST_LOAD_TIME desc

gxc9mvqxknma1 615168685 SELECT /* GOOD_XPLAN */ /*+ USE_HASH(E) LEADING(D) */ E.EMPNO, E.ENAME, D.DEPTNO 956,405,267,997,147,471 2023-03-24/10:10:42
, D.DNAME FROM DEPT D, EMP E WHERE D.DEPTNO = E.DEPTNO

6. [セッション2 / 管理者ユーザ] 性能の良いSQLの実行計画を取得
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('gxc9mvqxknma1'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gxc9mvqxknma1, child number 0
-------------------------------------
SELECT /* GOOD_XPLAN */ /*+ USE_HASH(E) LEADING(D) */ E.EMPNO, E.ENAME,
D.DEPTNO, D.DNAME FROM DEPT D, EMP E WHERE D.DEPTNO = E.DEPTNO

Plan hash value: 615168685

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 70 (100)| |
|* 1 | HASH JOIN | | 14 | 364 | 70 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 52 | 35 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 35 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("D"."DEPTNO"="E"."DEPTNO")

7. [セッション2 / 管理者ユーザ] SQL計画ベースラインの取得（性能の悪いSQLの実行計画ロード前）
SQL> SELECT SQL_HANDLE, PLAN_NAME, to_char(CREATED, 'YYYY-MM-DD HH24:MI:SS') as CREATED, to_char(LAST_EXECUTED, 'YYYY-MM-DD HH24:MI:SS') as LAST_EXECUTED, ENABLED, ACCEPTED, FIXED, AUTOPURGE FROM DBA_SQL_PLAN_BASELINES WHERE SIGNATURE IN (SELECT EXACT_MATCHING_SIGNATURE FROM V$SQL WHERE SQL_ID = 'chyjw3v56383h') ORDER BY CREATED DESC;

no rows selected

※SQL_HANDLE：SPMで使用するSQL識別子。V$SQLのSQL_IDと基本的に意味は同じだが、SQL_HANDLEは正規化されたSQLの識別子となる
※PLAN_NAME：SPMで使用する実行計画の識別子
※CREATED：SQL計画ベースライン/SQL計画履歴に実行計画が作成された時刻
※LAST_EXECUTED：SQL計画ベースラインの実行計画が最後に実行された時刻
※ENABLED：SQL計画ベースラインの実行計画が使用できるか否か（YES：使用可能 / NO：使用不可）
※ACCEPTED：SQL計画ベースラインの実行計画が承認されているか否か（YES：承認済み。SQL計画ベースラインに移動 / NO：未承認。SQL計画管理に残る）
※FIXED：SQL計画ベースラインの実行計画が固定されているか否か（YES：固定。ハード解析後も新たな実行計画はSQL計画管理に登録されない / NO：固定解除。ハード解析後は新たな実行計画はSQL計画管理に登録される）
※AUTOPURGE：SQL計画ベースラインの実行計画が自動削除されるか否か（YES：一定期間(デフォルト53週)経過後、自動削除される / NO：自動削除されない）
※SIGNATURE：正規化されたSQLの識別子（ハッシュ値）。V$SQLのSIGNATUREと同じ値

8. [セッション2 / 管理者ユーザ] 性能の悪いSQLの実行計画をSQL計画ベースラインとして登録
SQL> set serveroutput on
SQL> DECLARE
2 ret number;
3 BEGIN
4 ret := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
5 sql_id => 'chyjw3v56383h',
6 plan_hash_value => '844388907');
7 DBMS_OUTPUT.PUT_LINE('LOAD PLANS:' || ret);
8 END;
9 /
LOAD PLANS:1

PL/SQL procedure successfully completed.
★「LOAD PLANS:」はSQL計画ベースラインにロードした実行計画の個数

9. [セッション2 / 管理者ユーザ] SQL計画ベースラインの取得（性能の悪いSQLの実行計画ロード後）
SQL> SELECT SQL_HANDLE, PLAN_NAME, to_char(CREATED, 'YYYY-MM-DD HH24:MI:SS') as CREATED, to_char(LAST_EXECUTED, 'YYYY-MM-DD HH24:MI:SS') as LAST_EXECUTED, ENABLED, ACCEPTED, FIXED, AUTOPURGE FROM DBA_SQL_PLAN_BASELINES WHERE SIGNATURE IN (SELECT EXACT_MATCHING_SIGNATURE FROM V$SQL WHERE SQL_ID = 'chyjw3v56383h') ORDER BY CREATED DESC;

10. [セッション2 / 管理者ユーザ] 性能の悪いSQLのSQL計画ベースラインの属性変更（現在の実行計画の無効化）
SQL> DECLARE
2 ret number;
3 ret1 number;
4 ret2 number;
5 ret3 number;
6 BEGIN
7 ret1 := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
8 sql_handle => 'SQL_62fd7b2debe4c0e4',
9 plan_name => 'SQL_PLAN_65zbv5rpy9h745ac47e2d',
10 attribute_name => 'ENABLED',
11 attribute_value => 'NO');
12 ret2 := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
13 sql_handle => 'SQL_62fd7b2debe4c0e4',
14 plan_name => 'SQL_PLAN_65zbv5rpy9h745ac47e2d',
15 attribute_name => 'FIXED',
16 attribute_value => 'NO');
17 ret3 := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
18 sql_handle => 'SQL_62fd7b2debe4c0e4',
19 plan_name => 'SQL_PLAN_65zbv5rpy9h745ac47e2d',
20 attribute_name => 'AUTOPURGE',
21 attribute_value => 'NO');
22 ret := ret1 + ret2 + ret3;
23 DBMS_OUTPUT.PUT_LINE('ALTER PLANS:' || ret);
24 END;
25 /
ALTER PLANS:3

PL/SQL procedure successfully completed.
★「ALTER PLANS:」はSQL計画ベースラインの属性変更した実行計画の個数

11. [セッション2 / 管理者ユーザ] 属性変更したSQL計画ベースラインの確認（属性変更後）
SQL> SELECT SQL_HANDLE, PLAN_NAME, to_char(CREATED, 'YYYY-MM-DD HH24:MI:SS') as CREATED, to_char(LAST_EXECUTED, 'YYYY-MM-DD HH24:MI:SS') as LAST_EXECUTED, ENABLED, ACCEPTED, FIXED, AUTOPURGE FROM DBA_SQL_PLAN_BASELINES WHERE SQL_HANDLE = 'SQL_62fd7b2debe4c0e4' ORDER BY CREATED DESC;

12. [セッション2 / 管理者ユーザ] 性能の悪いSQLのSQL計画ベースラインのSQLハンドルと性能の良いSQLのSQLID、実行計画のセットを使用して新しい実行計画を登録
SQL> DECLARE
2 ret number;
3 BEGIN
4 ret := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
5 sql_id => 'gxc9mvqxknma1', --性能の良いSQLのSQLID
6 plan_hash_value => '615168685', --性能の良いSQLの実行計画
7 sql_handle => 'SQL_62fd7b2debe4c0e4', --性能の悪いSQLのSQLハンドル
8 enabled => 'YES', --実行計画を有効化
9 fixed => 'YES'); --実行計画を固定化
10 DBMS_OUTPUT.PUT_LINE('LOAD PLANS:' || ret);
11 END;
12 /
LOAD PLANS:1

PL/SQL procedure successfully completed.

13. [セッション2 / 管理者ユーザ] SQL計画ベースラインの取得（属性変更前）
SQL> SELECT SQL_HANDLE, PLAN_NAME, to_char(CREATED, 'YYYY-MM-DD HH24:MI:SS') as CREATED, to_char(LAST_EXECUTED, 'YYYY-MM-DD HH24:MI:SS') as LAST_EXECUTED, ENABLED, ACCEPTED, FIXED, AUTOPURGE FROM DBA_SQL_PLAN_BASELINES WHERE SQL_HANDLE = 'SQL_62fd7b2debe4c0e4' ORDER BY CREATED DESC;

SQL_HANDLE PLAN_NAME CREATED LAST_EXECUTED ENABLED ACCEPTED FIXED AUTOPURGE
------------------------------ ------------------------------ -------------------- -------------------- ---------- ------------ ---------- ----------
SQL_62fd7b2debe4c0e4 SQL_PLAN_65zbv5rpy9h748447c07a 2023-03-24 10:47:00 YES YES YES YES
SQL_62fd7b2debe4c0e4 SQL_PLAN_65zbv5rpy9h745ac47e2d 2023-03-24 10:44:14 NO YES NO NO
★1行目が新たにロードした実行計画（実行計画は性能の良いSQLの実行計画）

14. [セッション2 / 管理者ユーザ] 新たに登録したSQLのSQL計画ベースラインの属性変更（自動パージ無効化）
SQL> DECLARE
2 ret number;
3 BEGIN
4 RET := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
5 sql_handle => 'SQL_62fd7b2debe4c0e4', --性能の悪いSQLのSQLハンドル
6 plan_name => 'SQL_PLAN_65zbv5rpy9h748447c07a', --新たにロードした実行計画
7 attribute_name => 'AUTOPURGE', --属性名（自動パージ）
8 attribute_value => 'NO'); --無効化
9 DBMS_OUTPUT.PUT_LINE('ALTER PLANS:' || ret);
10 END;
11 /
ALTER PLANS:1

PL/SQL procedure successfully completed.

15. [セッション2 / 管理者ユーザ] SQL計画ベースラインの取得（属性変更後）
SQL> SELECT SQL_HANDLE, PLAN_NAME, to_char(CREATED, 'YYYY-MM-DD HH24:MI:SS') as CREATED, to_char(LAST_EXECUTED, 'YYYY-MM-DD HH24:MI:SS') as LAST_EXECUTED, ENABLED, ACCEPTED, FIXED, AUTOPURGE FROM DBA_SQL_PLAN_BASELINES WHERE SQL_HANDLE = 'SQL_62fd7b2debe4c0e4' ORDER BY CREATED DESC;

SQL_HANDLE PLAN_NAME CREATED LAST_EXECUTED ENABLED ACCEPTED FIXED AUTOPURGE
------------------------------ ------------------------------ -------------------- -------------------- ---------- ------------ ---------- ----------
SQL_62fd7b2debe4c0e4 SQL_PLAN_65zbv5rpy9h748447c07a 2023-03-24 10:47:00 YES YES YES NO
SQL_62fd7b2debe4c0e4 SQL_PLAN_65zbv5rpy9h745ac47e2d 2023-03-24 10:44:14 NO YES NO NO

16. [セッション1 / アプリケーションユーザ] 性能の悪いSQLの実行計画を取得
SQL> EXPLAIN PLAN FOR SELECT /* BAD_XPLAN */ E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME FROM DEPT D, EMP E WHERE D.DEPTNO = E.DEPTNO;

Explained.

SQL> @?/rdbms/admin/utlxpls.sql

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 364 | 70 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 364 | 70 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 52 | 35 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 35 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("D"."DEPTNO"="E"."DEPTNO")

Note
-----
- SQL plan baseline "SQL_PLAN_65zbv5rpy9h748447c07a" used for this statement
★新たに登録した実行計画「SQL_PLAN_65zbv5rpy9h748447c07a（ハッシュ結合）」を使用して実行された
★EXPLAIN PLAN文は実行計画の出力のみで実行はされない

②固定化した実行計画を削除
固定化した実行計画をSQL計画ベースラインから削除した場合、性能の悪いSQLを実行して固定化する前の実行計画でSQLが実行されるか検証します

【検証手順】
1. [セッション1 / アプリケーションユーザ] 実行計画を固定化したSQLの実行計画を取得
2. [セッション2 / 管理者ユーザ] SQL計画ベースラインの取得（新たにロードした実行計画の削除前）
3. [セッション2 / 管理者ユーザ] 固定化した実行計画をSQL計画ベースラインから削除
4. [セッション2 / 管理者ユーザ] SQL計画ベースラインの取得（新たにロードした実行計画の削除後）
5. [セッション1 / アプリケーションユーザ] 実行計画を固定化解除したSQLの実行計画を取得

【作業ログ】

1. [セッション1 / アプリケーションユーザ] 実行計画を固定化したSQLの実行計画を取得
SQL> EXPLAIN PLAN FOR SELECT /* BAD_XPLAN */ E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME FROM DEPT D, EMP E WHERE D.DEPTNO = E.DEPTNO;

Explained.

SQL> @?/rdbms/admin/utlxpls.sql

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 364 | 70 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 364 | 70 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 52 | 35 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 35 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("D"."DEPTNO"="E"."DEPTNO")

Note
-----
- SQL plan baseline "SQL_PLAN_65zbv5rpy9h748447c07a" used for this statement

2. [セッション2 / 管理者ユーザ] SQL計画ベースラインの取得（新たにロードした実行計画の削除前）
SQL> SELECT SQL_HANDLE, PLAN_NAME, to_char(CREATED, 'YYYY-MM-DD HH24:MI:SS') as CREATED, to_char(LAST_EXECUTED, 'YYYY-MM-DD HH24:MI:SS') as LAST_EXECUTED, ENABLED, ACCEPTED, FIXED, AUTOPURGE FROM DBA_SQL_PLAN_BASELINES WHERE SQL_HANDLE = 'SQL_62fd7b2debe4c0e4' ORDER BY CREATED DESC;

SQL_HANDLE PLAN_NAME CREATED LAST_EXECUTED ENABLED ACCEPTED FIXED AUTOPURGE
------------------------------ ------------------------------ -------------------- -------------------- ---------- ------------ ---------- ----------
SQL_62fd7b2debe4c0e4 SQL_PLAN_65zbv5rpy9h748447c07a 2023-03-24 10:47:00 YES YES YES NO
SQL_62fd7b2debe4c0e4 SQL_PLAN_65zbv5rpy9h745ac47e2d 2023-03-24 10:44:14 NO YES NO NO

3. [セッション2 / 管理者ユーザ] 固定化した実行計画をSQL計画ベースラインから削除
SQL> DECLARE
2 ret number;
3 BEGIN
4 ret := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
5 sql_handle => 'SQL_62fd7b2debe4c0e4',
6 plan_name => 'SQL_PLAN_65zbv5rpy9h748447c07a');
7 DBMS_OUTPUT.PUT_LINE('DROP PLANS:' || ret);
8 END;
9 /
DROP PLANS:1

PL/SQL procedure successfully completed.
★「DROP PLANS:」はSQL計画ベースラインから削除した実行計画の個数

4. [セッション2 / 管理者ユーザ] SQL計画ベースラインの取得（新たにロードした実行計画の削除後）
SQL> SELECT SQL_HANDLE, PLAN_NAME, to_char(CREATED, 'YYYY-MM-DD HH24:MI:SS') as CREATED, to_char(LAST_EXECUTED, 'YYYY-MM-DD HH24:MI:SS') as LAST_EXECUTED, ENABLED, ACCEPTED, FIXED, AUTOPURGE FROM DBA_SQL_PLAN_BASELINES WHERE SQL_HANDLE = 'SQL_62fd7b2debe4c0e4' ORDER BY CREATED DESC;

5. [セッション1 / アプリケーションユーザ] 実行計画を固定化解除したSQLの実行計画を取得
SQL> EXPLAIN PLAN FOR SELECT /* BAD_XPLAN */ E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME FROM DEPT D, EMP E WHERE D.DEPTNO = E.DEPTNO;

Explained.

SQL> @?/rdbms/admin/utlxpls.sql

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 364 | 38 (3)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 364 | 38 (3)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 182 | 36 (3)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 35 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
★実行計画の固定化前（ソートマージ結合）に戻った

■参考資料
自動データベース・メンテナンス・タスクの管理
SQL計画ベースラインの管理
SQL計画ベースラインの管理
https://blogs.oracle.com/otnjp/post/tsushima-hakushi-75
オラクルマスター教科書　ORACLE MASTER Expert パフォーマンス・チューニング編
Oracleの現場を効率化する100の技

■おわりに
下記のような状況はSPMが有効です。
・実行計画の変動が多くパフォーマンスが安定しない
・アプリケーション（SQL）にヒント句を追加したいが修正できない
　→実行計画を固定化
・適切な実行計画が選択されずある日突然パフォーマンスが低下してしまった
・過去の性能の良い実行計画に戻したい
　→過去の実行計画に戻す
・SQL計画ベースラインを進化させたい（より良い実行計画が選択されるようにしたい）
　→SQL計画ベースラインのFIXED属性をNOにする
なお、SPMで実行計画を固定化した場合でも、下記のような場合は実行計画が再現できないため注意が必要です。
・索引を削除したなどオブジェクトに変更があった場合
　→索引を利用した実行計画をSPMに登録した状態で索引を削除すると索引を使用した実行計画が再現できない
・トリガーを無効にした場合
　→トリガーを有効にした場合と無効にした場合で実行計画が一致しない場合がある
・外部キー制約を無効化した場合
　→外部キー制約はハード解析時に最適化されるが、制約を無効化すると最適化されない場合がある