Oracle SQLチューニング Season2（第13回）　第3章　SQL実行情報の分析及び活用方法（2/3）

2025-06-11 23:52Oracle技術情報

　さて、今回のSQLチューニングブログ（第13回）では、前回の続きを解説していきます。

　全3回シリーズの2回目、「 SQL実行情報の分析及び活用方法」を説明します。

　それでは早速スタートしましょう！！

3.2 DBMS_XPLAN 情報を活用する

　Oracle 9iから導入されたDBMS_XPLAN Package（以下、DBMS_XPLAN）は、10046 Traceと一緒に最も多く使われているSQLチューニングツールです。10046 Traceでは提供しない一部の情報を確認することが可能です。

　当該Packageについての実行権限があれば、OSへアクセスする必要が無く、セキュリティが徹底された環境におけるSQLチューニングツールとして多く活用されています。

　DBMS_XPLANを使うためには、SQLの実行履歴を照会できるDictionary Viewの照会（SELECT）権限が必要です。

■　該当ビューは下記の通りとなります。

V$SESSION
V$SQL_PLAN
V$SQL
V$SQL_PLAN_STATISTICS_ALL

　DBMS_XPLANの主な機能は、PLAN Statistics情報を照会することで、そのデータを収集（実際の実行計画）するために、以下の3つの条件から1つ以上を設定する必要があります。

STATISTICS LEVEL = ALL （パラメータ）　
ROWSOURCE_EXECUTION_STATISTICS = true （隠しパラメータ）
GATHER_PLAN_STATISITCS （SQLヒント）

　前述した照会権限とデータ収集のために必要な設定が完了すると、DBMS_XPLANを使ってSQLが実行された時に発生した情報を照会することが可能になります。

　DBMS_XPLANは、Packageで使えるFunctionが存在しています。
　その中でも一番多く使われるものが、DISPLAY と DISPLAY_CURSOR Function です。
　DISPLAYは、予想実行計画を確認する時、DISPLAY_CURSOR は､実際の実行履歴データを確認する時に使います。

　ここでは、実際の実行計画を抽出するために使用する DISPLAY_CURSOR についてのみ説明します。

■　DISPLAY_CUROSRは、3つの引数を持つFunctionです。構成は下記の通りです。

ARGUMENT	TYPE	IN/OUT	説明
SQL_ID	VARCHAR2	IN	実行SQL_ID:指定しない場合、そのセッションが最後に実行したSQLを指定。
CURSOL_CHILD_NO	NUMBER（38）	IN	実行 SQL_ID の CHILD_NUMBER.:指定しない場合、最初の CHILD_NUMBER 値を使用。
FORMAT	VARCHAR2	IN	DBMS_XPLAN.DISPALY.FORMAT：予測値 ALLSTATS：実際にアクセスしたROW数、実行時間、CR、PR、PW情報を含む（累積値）　　　　　　　　　 ALLSTATS LAST：ALLLSTATSと同じ。最後に実行した情報 ADVANCED ALLSTATS LAST：すべてのFORMATの情報を含む

3.2.1 DBMS_XPLAN データの理解

　下記の出力データは、実行SQLに/*+ gather_plan_statistics */ヒントを付与した上で、　DISPLAY_CURSOR出力データFORMATを’ADVANCED ALLSTATS LAST’に設定して、照会した結果です。

●　3.2.1.1 実行計画

　DBMS_XPLANを使用すると、最初に出力されるデータがSQLの実行計画です。
　SQLがどのように実行されたかについての解釈が可能です。
　テーブルのアクセス情報、インデックス活用の有無、結合方法、結合順序など、当該SQLの実行履歴を詳細に分析することができ、実行計画を解釈する方法と出力データに対する理解が先に行わなければなりません。

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | E-Bytes | Cost (%CPU) | E-Time | Pstart | Pstop | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 139 (100)| | 1 | 00:00:00:00.08 | 228
| 1 | SORT AGGREGATE | | 1 | 1 | 1 | 6 | | 1 | 00:00:00.08 | 228
|* 2 | HASH JOIN | | 1 | 82112 | 481K| 139 (2) | 00:00:02 | | 82112 | 00:00:00.07 | 228 | (2) 
| 3 | INDEX FULL SCAN | CHANNELS_PK | 1 | 5 | 15 | 1 (0) | 00:00:01 | 5 | 00:00:00.01 | 1
| 4 | PARTITION RANGE ALL | 1 | 82112 | 240K| 137 (1) | 00:00:02 | 1 | 28 | 82112 | 00:00:00.03 | 227
| 5 | TABLE ACCESS FULL | COSTS | 28 | 82112 | 240K| 137 (1) | 00:00:02 | 1 | 28 | 82112 |00:00:00.02 | 227 | ...
-----------------------------------------------------------------------------------------------------------------------------------------------

　実行計画の各ID別詳細分析のためには、DBMS_XPLANで出力される各カラムの意味をよく知っておく必要があります。
　下記の表の内容を参考にしましょう。

Plan　情報領域
Id	各OperationのID Number
Operation	Row単位Access情報
Name	アクセスするObject情報
Starts	該当Operationの実行回数（Nestead Loopジョインの場合はLooping回数）
E-Starts	当該Operationで抽出されるRow数(予想
E-Byte	当該Operationで抽出されるRow数(実測)
A-Time	当該Operation段階で行われた時間。Second. (実測)
Buffers	当該Operation段階で実際のBuffer Cacheを通じて読み込んだBlock量(Logical Reads)
Read	当該Operation段階で実際のDiskから読み込んだBlock量(Physical Reads)
0Mem	Optimal 演算に必要な Memory Size (予想)
1Mem	One Pass演算に必要な Memory Size (予想)
Used-Mem	実際に使用された Memory Size. (0:Optimal / 1:One Pass / 2以上:Multi Pass)

●　3.2.1.2 Query Block

　Query Blockとは、SQL内に別のSQL形態であるインラインビュー、サブクエリなどを区別することができる固有の識別子をBlock名に付与したものです。SQL文が意図しない方法で実行される時、これを改善することを目的として、Query Block NameとObject Aliasを活用する場合があります。

●　3.2.1.3 Outline Data

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "C"@"SEL$1" ("CHANNELS"."CHANNEL_ID"))
      FULL(@"SEL$1" "S"@"SEL$1")
      LEADING(@"SEL$1" "C"@"SEL$1" "S"@"SEL$1")
      USE_HASH(@"SEL$1" "S"@"SEL$1")
      END_OUTLINE_DATA
  */

　Outline dataは、Query Block NameをベースにするOracle Optimizerが参考するHidden Hintのリストだと考えてください。

●　3.2.1.4 Predicate Information

　SQLチューニング時に必ず分析しなければならないデータです。
　Predicate InformationはExecution Planの内容を分析する際に必ず確認しなければならないデータで、SQLの結合処理、Where節の条件がどのように処理されたかを確認することができます。
また、インデックススキャン時にどのカラムにAccessしたか、どのカラムがFilterで実行したのかも確認することができるため、チューニング時にはとても便利に活用することができます。

●　3.2.1.5 Column Projection Information

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=0) COUNT(*)[22]
   2 - (#keys=1)
   3 - "C"."CHANNEL_ID"[NUMBER,22]
   4 - "S"."CHANNEL_ID"[NUMBER,22]
   5 - "S"."CHANNEL_ID"[NUMBER,22]

3.2.2 DBMS_XPLAN使用時の注意事項

DBMS_XPLANを使用する場合、通常下記のように出力されるデータを照会します。

　最初のNULLはSQL_IDで、2番目のNULLはSQLのChild Numberを意味します。
　ところで、このように実行するのは、同じセッションでSQLを実行した後、上記のように “NULL,NULL,’allstats last'” で実行すると、セッションで最後に実行したSQL情報を出力するため、SQL_IDを指定せずに実行することが多いからです。

　ところが、上記のように実行すると、正しい情報を出力できない場合があります。
　例えば、Parallel Queryの実行履歴をDBMS_XPLANで抽出する時です。
　Parallel Queryに対する正確な実行履歴情報を出力するためには、必ず最初の引数にSQL_IDを記入する必要があります。

簡単なテストを通して実際のSQL実行履歴がどう違うか見てみましょう。

SQLの例 ：
SELECT  /*+ parallel(2) */
        c.cust_first_name|| ' ' || cust_last_name ,
        cust_gender ,
        cust_main_phone_number ,
        cust_street_address ,
        cust_city ,
        cust_state_province ,
        PROD_NAME,
        get_max_price(s.prod_id) price
FROM sales s
       ,customers c
       ,products p
WHERE s.cust_id = c.cust_id
AND s.prod_id = p.prod_id
AND c.cust_last_name = :lastname
AND c.cust_marital_status = 'single';

　上のSQLの例を実行した後、DBMS_XPLANを実行する時、SQL_IDを記入しない場合とSQL_IDを追加した場合、どのようにデータが出力されるかを見てみましょう。

［テスト1］ SQL_IDを削除してLastオプションを使う場合

　［テスト1］の結果を見ると、CoordinatorがSlave Processから受け取ったBlock数、所要時間、処理件数のみを確認できるものの、実行段階別の情報は出力されませんでした。

　ところが、SQLの性能分析のためにはExecution Planの各ID別情報が必要となるため、上記の抽出されたデータでは性能分析自体が難しくなってしまいます。

［テスト2］ SQL_IDを使用してLastオプションを削除する場合

select * from table (dbms_xplan.display_cursor('03du78rh3n3tp',null,'basic +iostats -rows '));

child number 1
Plan hash value: 3984793935

---------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |      1 |    569 |00:00:00.43 |     124 |
|   1 |  PX COORDINATOR          |           |      1 |    569 |00:00:00.43 |     124 |
|   2 |   PX SEND QC (RANDOM)    | :TQ10002  |      0 |      0 |00:00:00.01 |       0 |
|*  3 |    HASH JOIN             |           |      0 |      0 |00:00:00.01 |       0 |
|   4 |     PX RECEIVE           |           |      0 |      0 |00:00:00.01 |       0 |
|   5 |      PX SEND BROADCAST   | :TQ10000  |      0 |      0 |00:00:00.01 |       0 |
|   6 |       PX BLOCK ITERATOR  |           |      0 |      0 |00:00:00.01 |       0 |
|*  7 |        TABLE ACCESS FULL | PRODUCTS  |      0 |      0 |00:00:00.01 |       0 |
|*  8 |     HASH JOIN            |           |      0 |      0 |00:00:00.01 |       0 |
|   9 |      PX RECEIVE          |           |      0 |      0 |00:00:00.01 |       0 |
|  10 |       PX SEND BROADCAST  | :TQ10001  |      0 |      0 |00:00:00.01 |       0 |
|  11 |        PX BLOCK ITERATOR |           |      0 |      0 |00:00:00.01 |       0 |
|* 12 |         TABLE ACCESS FULL| CUSTOMERS |      0 |      0 |00:00:00.01 |       0 |
|  13 |      PX BLOCK ITERATOR   |           |      0 |      0 |00:00:00.01 |       0 |
|* 14 |       TABLE ACCESS FULL  | SALES     |      0 |      0 |00:00:00.01 |       0 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("S"."PROD_ID"="P"."PROD_ID")
   7 - access(:Z>=:Z AND :Z<=:Z)
   8 - access("S"."CUST_ID"="C"."CUST_ID")
  12 - access(:Z>=:Z AND :Z<=:Z)
       filter(("C"."CUST_LAST_NAME"=:LASTNAME AND
              "C"."CUST_MARITAL_STATUS"='single'))
  14 - access(:Z>=:Z AND :Z<=:Z)
       filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."CUST_ID"))

child number 1	   
Plan hash value: 3984793935

---------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |      0 |      0 |00:00:00.01 |       0 |
|   1 |  PX COORDINATOR          |           |      0 |      0 |00:00:00.01 |       0 |
|   2 |   PX SEND QC (RANDOM)    | :TQ10002  |      0 |      0 |00:00:00.01 |       0 |
|*  3 |    HASH JOIN             |           |      2 |    286 |00:00:03.50 |    1686 |
|   4 |     PX RECEIVE           |           |      1 |    144 |00:00:00.01 |       0 |
|   5 |      PX SEND BROADCAST   | :TQ10000  |      0 |      0 |00:00:00.01 |       0 |
|   6 |       PX BLOCK ITERATOR  |           |      2 |     72 |00:00:00.01 |       6 |
|*  7 |        TABLE ACCESS FULL | PRODUCTS  |      2 |     72 |00:00:00.01 |       6 |
|*  8 |     HASH JOIN            |           |      2 |    569 |00:00:03.49 |    1686 |
|   9 |      PX RECEIVE          |           |      2 |     72 |00:00:00.01 |       0 |
|  10 |       PX SEND BROADCAST  | :TQ10001  |      0 |      0 |00:00:00.01 |       0 |
|  11 |        PX BLOCK ITERATOR |           |      2 |     36 |00:00:00.01 |    1584 |
|* 12 |         TABLE ACCESS FULL| CUSTOMERS |     26 |     36 |00:00:00.01 |    1584 |
|  13 |      PX BLOCK ITERATOR   |           |      2 |    953 |00:00:03.48 |    1686 |
|* 14 |       TABLE ACCESS FULL  | SALES     |     17 |    953 |00:00:00.21 |    1686 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("S"."PROD_ID"="P"."PROD_ID")
   7 - access(:Z>=:Z AND :Z<=:Z)
   8 - access("S"."CUST_ID"="C"."CUST_ID")
  12 - access(:Z>=:Z AND :Z<=:Z)
       filter(("C"."CUST_LAST_NAME"=:LASTNAME AND
              "C"."CUST_MARITAL_STATUS"='single'))
  14 - access(:Z>=:Z AND :Z<=:Z)
       filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."CUST_ID"))

　［テスト2］は、DBMS_XPLANを実行する時に最初の引数であるSQL_IDを記入して、FORMATオプションからLASTを削除して実行した結果です。
　SQL_IDなしで実行した[テスト1]で抽出されたデータ以外にもう一つのSQL実行計画が抽出されたことが確認できます。
　Child numberに抽出された実行計画は［テスト1］では、抽出された実行計画のデータを除いて、追加のSlave Processが実行した情報が抽出されたことが分かります。

　つまり［テスト2］の場合、DBMS_XPLANを実行したユーザーが希望するデータとなります。
　2つのテスト例を通じて、Parallel Queryの実行情報をDBMS_XPLANに出力するためには、SQL_IDを最初の引数に記入し、Lastオプションを削除した後、実行しなければ性能分析のためのデータを照会することができないことがわかります。

3.2.3 10046 Trace vs DBMS_XPLAN の比較

　先に説明した2つの実行計画ツールは、SQLチューニング時に最も多く使用されます。
　しかし、性能分析が必要なSQLによってきちんと選別する必要があります。
　DBMS_XPLANは、10046 Traceと比較して性能改善時に必要な様々な追加データを抽出することができます。
　その中でも最も代表的なものが、Predicate Information情報です。
　この情報は、どのカラムがAccessで実行されたかFilterで実行されたかを把握することができるため、カラム型変換、インデックスの効率性など性能に密接な部分を判断する時、非常に重要な根拠として使用できます。

　また、Remote SQLの場合、実際のSQLがRemote DBでどのように変換されて実行されるかを知ることができます。
　しかし、DBMS_XPLANがすべての情報を含んでいる訳ではありません。
　つまり、SQLに含まれるFunctionの実行情報やWaitイベント情報を抽出するためには、10046 Traceに依存するしかないのです。

　したがって、両方の方法を熟知してSQLの特性に適合するように活用することが望ましいと言えるでしょう。

次回ブログテーマ

SQLチューニングブログ 2nd Season（第14回）
「SQL実行情報の分析及び活用方法（3/3）」について

Oracle SQLチューニング Season2（第13回）　第3章　SQL実行情報の分析及び活用方法（2/3）

3.2 DBMS_XPLAN 情報を活用する

3.2.1 DBMS_XPLAN データの理解

●　3.2.1.1 実行計画

●　3.2.1.2 Query Block

●　3.2.1.3 Outline Data

●　3.2.1.4 Predicate Information

●　3.2.1.5 Column Projection Information

3.2.2 DBMS_XPLAN使用時の注意事項

3.2.3 10046 Trace vs DBMS_XPLAN の比較

前の記事

次の記事

他社に頼らず自社でデータベースを監視・運用をしませんか？
MaxGaugeがサポートします

人気記事ランキング

タグ一覧

Oracle SQLチューニング Season2（第13回） 第3章 SQL実行情報の分析及び活用方法（2/3）

3.2 DBMS_XPLAN 情報を活用する

3.2.1 DBMS_XPLAN データの理解

● 3.2.1.1 実行計画

● 3.2.1.2 Query Block

● 3.2.1.3 Outline Data

● 3.2.1.4 Predicate Information

● 3.2.1.5 Column Projection Information

3.2.2 DBMS_XPLAN使用時の注意事項

3.2.3 10046 Trace vs DBMS_XPLAN の比較

前の記事

次の記事

他社に頼らず自社でデータベースを監視・運用をしませんか？MaxGaugeがサポートします

人気記事ランキング

タグ一覧

Oracle SQLチューニング Season2（第13回）　第3章　SQL実行情報の分析及び活用方法（2/3）

●　3.2.1.1 実行計画

●　3.2.1.2 Query Block

●　3.2.1.3 Outline Data

●　3.2.1.4 Predicate Information

●　3.2.1.5 Column Projection Information

他社に頼らず自社でデータベースを監視・運用をしませんか？
MaxGaugeがサポートします