2024.11.12
SQLチューニング 2nd Season（第22回） 第4章 SQL TuningとHINTの関係 （5/8）
今回のSQLチューニングブログ2nd Season（第22回）は、 「SQL Tunig と HINTの関係」 の第5回目です。
～ WITH節の制御ヒント・PARALLELコントロールヒント ～ について解説していきます。
それでは早速、はじめていきましょう。
4.2.7 WITH節の制御ヒント
With節を制御するヒントを適切に使うためには、With節の動作方法を理解しておく必要があります。
With節は、同じデータを繰り返し読み込んで処理するSQLの性能を改善するための方法としてよく使われます。
一般的にWith節は常にGlobal Temporary Tableを生成した後、抽出された結果セットを保存します。
保存されたデータをSQLで使用することは、このブログを読んでいらっしゃる皆さんならご存知ではないか？・・・と思います。
しかしその一方で、With節ではGlobal Temporary Table (Materialize)を使用する方式とInline方式の二つの方式で実行されます。
11g以前のバージョンでは、With節の実行方式の判断をオプティマイザー任せとなることから、使用回数によっては、InlineまたはMaterialize方式のどちらかが選択されることになります。
選択基準としては、With節がSQL内で2回以上実行される場合はMaterialize方式、1回だけの場合はInline方式で実行されます。
このようなWith節の実行方式は、SQLの性能と関連する場合が多いため、状況によってはユーザーが強制的に調整する必要があります。
この様なケースにおいて使用可能となるヒントは2種類が存在します。 ” INLINE ”と” MATERIALIZE ” です。
11gでは、隠しパラメータ”_WITH_SUBQUERY”によっても制御することも可能です。
・マテリアライズ
使用バージョン：9.0.0
使用方法: /*+ MATERIALIZE */ (Inverse: INLINE)
ヒント意味：WITH節から抽出されたデータをGlobal Temporary Table保存後使用
下記の使用例は、With節を制御するヒントの中でMATERIALIZEヒントを使った例です。
With節を宣言した後、Main SQLのUnion Allセットでそれぞれ1回ずつ呼び出し、合計2回呼び出すSQLです。
With節のセットを2回呼び出しますが、MATERIALIZEヒントのおかげで1回だけ実行されることが確認できます。
使用例：
WITH TMP AS (
SELECT /*+ MATERIALIZE */ -- 該当QUERY BLOCKのデータをGlobal Temp Tableに保存します。
CUST_NO
FROM HINT_T3
WHERE ORDDATE BETWEEN TO_DATE( '2013-01-01' , 'YYYY-MM-DD' )
AND TO_DATE( '2013-01-31' , 'YYYY-MM-DD' )
)
SELECT T1.CUST_NO
FROM TMP ,
HINT_T1 T1
WHERE TMP.CUST_NO = T1.CUST_NO
AND T1.CUST_NAME = 'CUST_A'
UNION ALL
SELECT TO_NUMBER( T2.CUST_NO )
FROM TMP ,
HINT_T2 T2
WHERE TMP.CUST_NO = T2.CUST_NO
AND T2.ADDR_DIV = 0 ;
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |00:00:00.01 | 226 | 5 |
| 1 | TEMP TABLE TRANSFORMATION | |00:00:00.01 | 226 | 5 |
| 2 | LOAD AS SELECT | |00:00:00.01 | 8 | 0 |
| 3 | TABLE ACCESS BY INDEX ROWID | HINT_T3 |00:00:00.01 | 4 | 0 |
|* 4 | INDEX RANGE SCAN | IDX03_HINT_T3 |00:00:00.01 | 3 | 0 |
| 5 | UNION-ALL | |00:00:00.01 | 215 | 5 |
| 6 | NESTED LOOPS | |00:00:00.01 | 61 | 5 |
| 7 | NESTED LOOPS | |00:00:00.01 | 33 | 4 |
| 8 | VIEW | |00:00:00.01 | 6 | 1 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662E_428F34 |00:00:00.01 | 6 | 1 |
|* 10 | INDEX UNIQUE SCAN | IDX01_HINT_T1 |00:00:00.01 | 27 | 3 |
|* 11 | TABLE ACCESS BY INDEX ROWID| HINT_T1 |00:00:00.01 | 28 | 1 |
| 12 | NESTED LOOPS | |00:00:00.01 | 154 | 0 |
| 13 | NESTED LOOPS | |00:00:00.01 | 70 | 0 |
| 14 | VIEW | |00:00:00.01 | 4 | 0 |
| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662E_428F34 |00:00:00.01 | 4 | 0 |
|* 16 | INDEX RANGE SCAN | IDX02_HINT_T2 |00:00:00.01 | 66 | 0 |
|* 17 | TABLE ACCESS BY INDEX ROWID| HINT_T2 |00:00:00.01 | 84 | 0 |
----------------------------------------------------------------------------------------------------
・INLINE
使用バージョン：9.0.0
使用方法: /*+ INLINE */ (Inverse: MATERIALIZE)
ヒント意味：WITH節をINLINE VIEWで実行するように誘導するヒント
下記の使用例は、MATERIALIZEヒントの例と同じです。適用したヒントが、ただINLINEに変更されただけです。
INLINEヒントを適用したWith節セットは、下記のMain SQLのUnion Allオペレーションで合計2回呼び出されます。
呼び出される度に、With節のQuery Blockが実行されることを確認することができます。
使用例：
WITH TMP AS (
SELECT /*+ INLINE */
CUST_NO
FROM HINT_T3
WHERE ORDDATE BETWEEN TO_DATE( '2013-01-01' , 'YYYY-MM-DD' )
AND TO_DATE( '2013-01-31' , 'YYYY-MM-DD' )
)
SELECT T1.CUST_NO
FROM TMP ,
HINT_T1 T1
WHERE TMP.CUST_NO = T1.CUST_NO
AND T1.CUST_NAME = 'CUST_A'
UNION ALL
SELECT TO_NUMBER( T2.CUST_NO )
FROM TMP ,
HINT_T2 T2
WHERE TMP.CUST_NO = T2.CUST_NO
AND T2.ADDR_DIV = 0 ;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 37 |00:00:00.01 | 215 |
| 1 | UNION-ALL | | | 37 |00:00:00.01 | 215 |
| 2 | NESTED LOOPS | | | 1 |00:00:00.01 | 59 |
| 3 | NESTED LOOPS | | 31 | 28 |00:00:00.01 | 31 |
| 4 | TABLE ACCESS BY INDEX ROWID| HINT_T3 | 31 | 31 |00:00:00.01 | 4 |
|* 5 | INDEX RANGE SCAN | IDX03_HINT_T3 | 31 | 31 |00:00:00.01 | 3 |
|* 6 | INDEX UNIQUE SCAN | IDX01_HINT_T1 | 1 | 28 |00:00:00.01 | 27 |
|* 7 | TABLE ACCESS BY INDEX ROWID | HINT_T1 | 1 | 1 |00:00:00.01 | 28 |
| 8 | NESTED LOOPS | | | 36 |00:00:00.01 | 156 |
| 9 | NESTED LOOPS | | 53 | 84 |00:00:00.01 | 72 |
| 10 | TABLE ACCESS BY INDEX ROWID| HINT_T3 | 31 | 31 |00:00:00.01 | 6 |
|* 11 | INDEX RANGE SCAN | IDX03_HINT_T3 | 31 | 31 |00:00:00.01 | 4 |
|* 12 | INDEX RANGE SCAN | IDX02_HINT_T2 | 3 | 84 |00:00:00.01 | 66 |
|* 13 | TABLE ACCESS BY INDEX ROWID | HINT_T2 | 2 | 36 |00:00:00.01 | 84 |
4.2.8 PARALLELコントロールヒント
並列処理(Parallel Processin)でデータを処理する際に使用するヒントです。
並列処理とは、ヒントで記述したDegree数だけテーブル、インデックスのデータを照会/演算/ソートなどの作業をSlave Processが同時に実行し、各Slave Processの実行結果をCoordinator Processに伝達する一連の作業を意味します。
並列処理は、対象セグメント(テーブル＆インデックス)をMulti Block I/O (Table Full ScanまたはIndex Fast Full Scan)を行う場合にのみ使用することが可能で、DML作業も並列処理が可能です。
・PARALLEL
使用バージョン：8.1.0〜
使用方法: /*+ FULL(T3) PARALLEL(T3 4) */ 使用方法
ヒント意味：T3テーブルをDegree 4でParallel処理されるように誘導するヒント
T3テーブルを4つの並列プロセスで処理するように誘導するヒントです。
DMLの場合、Parallelアクティブモードでのみ実行が可能です。
下記のコマンドでParallel DML（セッションに、先に設定する必要があります）を設定してから使用します。
ALTER SESSION ENABLE PARALLEL DML ；使用例：
SELECT /*+ FULL(T3) PARALLEL(T3 4) */
COUNT( * )
FROM HINT_T3 T3
WHERE GOODS_NO IS NOT NULL ;
------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 |00:00:12.98 | 5 |
| 1 | SORT AGGREGATE | | 1 | 1 |00:00:12.98 | 5 |
| 2 | PX COORDINATOR | | | 4 |00:00:12.98 | 5 |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 0 |00:00:00.01 | 0 |
| 4 | SORT AGGREGATE | | 1 | 0 |00:00:00.01 | 0 |
| 5 | PX BLOCK ITERATOR | | 2500K| 0 |00:00:00.01 | 0 |
|* 6 | TABLE ACCESS FULL| HINT_T3 | 2500K| 0 |00:00:00.01 | 0 |
------------------------------------------------------------------------------------
・PARALLEL_INDEX
使用バージョン：8.1.0〜
使用方法: /*+ PARALLEL_INDEX(T3 IDX01_HINT_T3 4) */ (Inverse: NO_PARALLEL_INDEX)
ヒント意味: INDEXを並列処理するように制御するヒント。
インデックスを並列処理するように誘導するヒントです。
SQL文で使う全てのカラムがインデックス構成カラムの場合のみ使用可能です。
つまり、インデックスをIndex Fast Full Scanで実行することができなければ、並列処理が可能です。
使用例：
SELECT /*+ PARALLEL_INDEX(T3 IDX01_HINT_T3 4) */
COUNT( * )
FROM HINT_T3 T3
WHERE GOODS_NO IS NOT NULL ;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 |00:00:12.07 | 5 |
| 1 | SORT AGGREGATE | | 1 | 1 |00:00:12.07 | 5 |
| 2 | PX COORDINATOR | | | 4 |00:00:12.07 | 5 |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 0 |00:00:00.01 | 0 |
| 4 | SORT AGGREGATE | | 1 | 0 |00:00:00.01 | 0 |
| 5 | PX BLOCK ITERATOR | | 2500K| 0 |00:00:00.01 | 0 |
|* 6 | INDEX FAST FULL SCAN| IDX01_HINT_T3 | 2500K| 0 |00:00:00.01 | 0 |
--------------------------------------------------------------------------------------------
SQLチューニング2nd Season（第22回）終
次回のSQLチューニングブログは
SQLチューニングブログ2nd Season（第4章）
「SQL Tuning と HINTの関係」（6/8）
～ INSERT制御のヒント ～
日本エクセムのデータベースソリューション
データベース可観測性ソリューション
プロアクティブで高品質なリモートDBA
最新情報は公式SNSでも配信中
私たちはITインフラにおける
プロジェクト運営 ～ システム運用 の安定化と効率化を推進します。
SQLチューニングブログについてのお問い合わせは
