GAGA LIFE.

インフラエンジニアブログ

スポンサーリンク

実行計画の比較(DBMS_XPLAN.COMPARE_PLANSファンクション)

概要

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)

実行例

  1. Nested Loopsの外部表はSALES
  2. 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

スポンサーリンク