概要
DBMS_XPLAN.COMPARE_PLANS APIを使用して実行プランを比較する19cの新機能を試したのでメモします。
DBMS_XPLAN.COMPARE_PLANSは、実行計画の比較機能を提供する19cの新機能です。
参照計画とテスト計画の任意のリストを取得し、それらの違いを強調表示します。
用途
- 実行計画の良し悪しを比較して、論理的な差異を特定
- 差異の原因を特定することにより、SQL計画ベースラインやSQLプロファイルで計画の再現性の問題を優先順位付け
- パフォーマンスが低下しているクエリの現在の実行計画をAWRでキャプチャされた古い実行計画と比較
- ヒントの追加、パラメーターの変更、インデックスの作成/削除などの際のSQL実行の影響を評価する
- SQL ProfileまたはSQL Performance Analyzerによって生成された計画が元の計画とどのように異なるかを判別する
DBMS_XPLAN.COMPARE_PLANSプロシージャ
DBMS_XPLAN.COMPARE_PLANS(
reference_plan IN generic_plan_object,
compare_plan_list IN plan_object_list,
type IN VARCHAR2 := 'TEXT',
level IN VARCHAR2 := 'TYPICAL',
section IN VARCHAR2 := 'ALL')
RETURN CLOB;
| Options | Description |
|---|---|
| REFERENCE_PLAN | 比較される実行計画 – ひとつだけ指定 |
| COMPARE_PLAN_LIST | 比較する実行計画 – 複数指定 |
| TYPE | TEXT, HTML, XMLのどれか |
| LEVEL | BASIC, TYPICAL, ALLのどれか |
| SECTION | SUMMARY, FINDINGS, PLANS, INFORMATION, ERRORS, ALLのどれか |
REFERENCE_PLAN, COMPARE_PLAN_LIST引数の指定対象
| 計画ソース | 引数指定方法 |
|---|---|
| PLAN TABLE | plan_table_object(owner, plan_table_name, statement_id, plan_id) |
| カーソル・キャッシュ | cursor_cache_object(sql_id, child_number) |
| AWR | awr_object(sql_id, dbid, con_dbid, plan_hash_value) |
| SQLチューニング・セット | sqlset_object(sqlset_owner, sqlset_name, sql_id, plan_hash_value) |
| SQL計画管理 | spm_object(sql_handle, plan_name) |
| SQLプロファイル | sql_profile_object(profile_name) |
| アドバイザ | advisor_object(task_name, execution_name, sql_id, plan_id) |
実行例
- Nested Loopsの外部表はSALES
- Nested Loopsの外部表はPRODUCTS
SQL> SELECT
COUNT(*)
FROM
PRODUCTS P,
SALES S
WHERE
P.PROD_ID = S.PROD_ID
AND P.PROD_MIN_PRICE > 200
;
2 3 4 5 6 7 8 9
COUNT(*)
----------
63763
1行が選択されました。
SQL_ID: gpuwkvycagvbt
SQL> SELECT
COUNT(*)
FROM
PRODUCTS P,
SALES S
WHERE
P.PROD_ID = S.PROD_ID
AND S.QUANTITY_SOLD = 43
;
2 3 4 5 6 7 8 9
COUNT(*)
----------
0
1行が選択されました。
SQL_ID: bk1328vh6m6y9
SQL> VARIABLE v_rep CLOB
SQL_ID: 304dsva0bvz67
SQL> BEGIN
:v_rep := DBMS_XPLAN.COMPARE_PLANS(
reference_plan => cursor_cache_object('gpuwkvycagvbt', null),
compare_plan_list =>
plan_object_list(cursor_cache_object('bk1328vh6m6y9', null)),
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL');
END;
/
2 3 4 5 6 7 8 9 10
PL/SQLプロシージャが正常に完了しました。
SQL_ID: 9txapcbw16vhb
SQL> SET PAGESIZE 50000
SQL> SET LONG 100000 LINESIZE 210
SQL> COLUMN report FORMAT a200
SQL> SELECT :v_rep REPORT FROM DUAL;
REPORT
---------------------------------------------------------------------------------------------
COMPARE PLANS REPORT
---------------------------------------------------------------------------------------------
Current user : SH
Total number of plans : 2
Number of findings : 1
---------------------------------------------------------------------------------------------
COMPARISON DETAILS
---------------------------------------------------------------------------------------------
Plan Number : 1 (Reference Plan)
Plan Found : Yes
Plan Source : Cursor Cache
SQL ID : gpuwkvycagvbt
Child Number : 0
Plan Database Version : 19.0.0.0
Parsing Schema : "SH"
SQL Text : SELECT COUNT(*) FROM PRODUCTS P, SALES S WHERE
P.PROD_ID = S.PROD_ID AND P.PROD_MIN_PRICE > 200
Plan
-----------------------------
Plan Hash Value : 3037679890
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 36 | |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| * 2 | HASH JOIN | | 781685 | 10161905 | 36 | 00:00:01 |
| * 3 | TABLE ACCESS FULL | PRODUCTS | 61 | 549 | 3 | 00:00:01 |
| 4 | PARTITION RANGE ALL | | 918843 | 3675372 | 29 | 00:00:01 |
| 5 | BITMAP CONVERSION TO ROWIDS | | 918843 | 3675372 | 29 | 00:00:01 |
| 6 | BITMAP INDEX FAST FULL SCAN | SALES_PROD_BIX | | | | |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("P"."PROD_ID"="S"."PROD_ID")
* 3 - filter("P"."PROD_MIN_PRICE">200)
Notes
-----
- This is an adaptive plan
---------------------------------------------------------------------------------------------
Plan Number : 2
Plan Found : Yes
Plan Source : Cursor Cache
SQL ID : bk1328vh6m6y9
Child Number : 0
Plan Database Version : 19.0.0.0
Parsing Schema : "SH"
SQL Text : SELECT COUNT(*) FROM PRODUCTS P, SALES S WHERE
P.PROD_ID = S.PROD_ID AND S.QUANTITY_SOLD = 43
Plan
-----------------------------
Plan Hash Value : 4261227730
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 521 | |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | NESTED LOOPS | | 1 | 11 | 521 | 00:00:01 |
| 3 | PARTITION RANGE ALL | | 1 | 7 | 521 | 00:00:01 |
| * 4 | TABLE ACCESS FULL | SALES | 1 | 7 | 521 | 00:00:01 |
| * 5 | INDEX UNIQUE SCAN | PRODUCTS_PK | 1 | 4 | 0 | |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 4 - filter("S"."QUANTITY_SOLD"=43)
* 5 - access("P"."PROD_ID"="S"."PROD_ID")
Comparison Results (1):
-----------------------------
1. 問合せブロックSEL$1: 結合順序が位置1で異なります(参照プラン: "P"@"SEL$1"、現在のプラン: "S"@"SEL$1")。
---------------------------------------------------------------------------------------------
1行が選択されました。
結合順序が異なると報告されている
参考資料
Oracle Database SQLチューニング・ガイド 19c 6.4 実行計画の比較 https://docs.oracle.com/cd/F19136_01/tgsql/generating-and-displaying-execution-plans.html#GUID-0023D232-5695-4BA8-89C5-88672B7647E2
