2024.05.14
SQLチューニング 2nd Season（第13回）
第3章 SQL実行情報の分析及び活用方法（2/3）
さて、今回の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-Rows | 当該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
クエリブロック名 / オブジェクトエイリアス（操作IDで識別）：
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / C@SEL$1
5 - SEL$1 / S@SEL$1Query 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
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."CHANNEL_ID"="C"."CHANNEL_ID")
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]
Predicate Information領域では、WHERE節に使用されたカラムに関する情報を表示するのに対して、Column Projection Information情報では、Operationに使用されるすべてのカラムの情報を確認することができます。
実行計画の特定の段階でどのカラムを抽出するかを確認することができます。
3.2.2 DBMS_XPLAN使用時の注意事項
DBMS_XPLANを使用する場合、通常下記のように出力されるデータを照会します。
SQL> select * from table(dbms_xplan.display(NULL, NULL, ‘allstats last’)) ；
最初の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オプションを使う場合
select * from table (dbms_xplan.display_cursor(null, null, 'basic +iostats -rows last'));
child number 0
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"))
［テスト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（第13回） 終
次回ブログテーマ
SQLチューニングブログ 2nd Season（第14回）
「SQL実行情報の分析及び活用方法（3/3）」について
■ データベース運用のことなら日本エクセムにお任せください ■
当社の最新情報はSNSでも配信中（フォローをお願いいたします）
掲載内容についてのお問合せは
私たちは、日本のITインフラにおける
プロジェクト運営～システム運用 の安定化と効率化を推進します。
日本エクセム株式会社
営業推進部 まで
✉ sales@ex-em.co.jp
