2021.05.26
SQLチューニング（第6回）「サブクエリ動作方式を理解すること」(4/8)
今回は、「サブクエリ動作方式理解」の２回目として、「結合動作方式」についてです。早速、内容に入っていきましょう。
２.１.２.２ 結合動作方式
結合動作方式をFilter動作方式と比較した際、最も大きい差異点は可変性だと考える。 前で調べたFilter動作方式は実行順序や実行方法が固定されて、多様な状況に柔軟な対処が難しい反面、結合動作方式はNested Loops Join,Hash Join,Sort Merge Join,Semi Join,Anti Joinなどの多様な結合方法のうち有利なのを選択することができ、また、Semi / Anti Joinを除いては、実行順序まで選択できることで、より柔軟な処理が可能である。（※ 注３） しかし、結合動作方式のうちにNested Loops Join Semiを除いた残り結合方法はFilter動作方式が持っているFILTERオペレーション効果による利益を得流ことができない。 したがってInput値の種類が少ない場合は、Filter動作方式が性能上有利なこともある。
（※ 注３）基本的にはSemi/ Anti Joinは実行順序が変更されず、Main Query Blockのテーブルが最初に実行される。このように結合順序が固定され、Main SQLの抽出件数が多い場合は、Hash Joinを実行すると、Build Inputセットが大きくなり非効率が多く発生することになるが、Oracle10gからHash Joinの場合に限り、ドライビングテーブルの順序の変更が可能となり、Build Inputのセットが大きくなると発生する非効率を効果的に向上させることができるようになった。これからSQLのトレース結果を見て、結合動作方式原理およびFilter動作方式との性能差がどこで発生するのか詳しく見ることにする。
まず、以下の同じSQLをそれぞれFilter動作方式と結合動作方式で実行してみた後、性能差を比較してみることによって性能差が発生する理由を考察することにする。
以下のテストSQLはFilter動作方式で実行され、性能問題が発生している。以下のSQLを通じてFilter動作方式が持っている問題点を調べ、結合動作方式に誘導した時の性能変化がどうなるかを調べてみる。
テスト[1]. Filter方式で実行されて性能問題が発生するSQL
var b1 number
var b2 number
exec :b1 := 249990
exec :b2 := 250210
SELECT c1, c2, c3
FROM SUBQUERY_T2 t2
WHERE c1 >= :b1 AND c1 <= :b2
AND EXISTS ( SELECT /*+ NO_UNNEST */
'x'
FROM SUBQUERY_T1 t1
WHERE t1.c6 = t2.c3 AND t1.c6 >= :b1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 156.11 157.03 0 7863857 0 11
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 156.12 157.03 0 7863857 0 11
Rows Row Source OPERATION
------- ---------------------------------------------------
11 FILTER (cr=7863857 pr=0 pw=0 time=157033184 us)
221 FILTER (cr=7 pr=0 pw=0 time=2467 us)
221 TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=7 pr=0 pw=0 time=2125 us)
221 INDEX RANGE SCAN PK_SUQUERY_2 (cr=4 pr=0 pw=0 time=690 us)
11 FILTER (cr=7863850 pr=0 pw=0 time=157028384 us)
11 TABLE ACCESS FULL SUBQUERY_T1 (cr=7863850 pr=0 pw=0 time=157028031 us)
Filter動作方式で実行したテスト[1]の場合、テーブルT1のカラムC6にインデックスがなくMain SQLの抽出件数回、SUBQUERY_T1テーブルを繰り返しFull Table Scanしている。
上記SQLはSUBQUERY_T1のカラムC6にインデックスを生成すれば性能が改善されるだろう。 しかしながら、実運用環境ではインデックスを生成する改善方法を適用できないこともある。 上記SQLの性能改善のためには当然生成させるべきであるが、インデックス生成作業は多くのリスクとチェック事項などを伴うコストが大きい作業であり、さらに生成するインデックスの使用頻度が低いならばインデックス生成効果が大変少ないはずであるためである。
インデックスを生成するにはまずSUBQUERY_T1テーブルをアクセスする他のSQLの実行計画に影響を及ぼすかどうか確認しなければならない。また、トランザクション プログラムの負荷も考慮しなければならない。 そしてインデックス追加にともなうディスクの余裕および今後の増加量もチェックしなければならない。 時々実行されるSQL一つを改善するために新規インデックスを生成するのはもしかしたら、「労多くして功少なし」となる可能性もあるので、必ず追加の良し悪しを十分考慮する必要がある。
DBサーバーの性能改善において、インデックスを生成することの利益が大きくないならば、インデックスを生成しないで性能を改善する方法を探さなければならない。
テスト[1]の性能を改善するために最も重要なポイントは反復的なFull Table Scanを減らすことである。 したがってサブクエリを結合動作方式に変更して、Hash Join Semiで実行するように/*+ UNNEST HASH_SJ*/ ヒントを付与した。
var b1 number
var b2 number
exec :b1 := 249990
exec :b2 := 250210
SELECT c1, c2, c3
FROM SUBQUERY_T2 t2
WHERE c1 >= :b1 AND c1 <= :b2
AND EXISTS ( SELECT /*+ UNNEST HASH_SJ */
'x'
FROM SUBQUERY_T1 t1
WHERE t1.c6 = t2.c3
AND t1.c6 >= :b1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.90 1.91 0 37422 0 11
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.90 1.92 0 37422 0 11
Rows Row Source OPERATION
------- ---------------------------------------------------
11 FILTER (cr=37422 pr=0 pw=0 time=1910470 us)
11 HASH JOIN SEMI (cr=37422 pr=0 pw=0 time=1910466 us)
221 TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=5 pr=0 pw=0 time=42 us)
221 INDEX RANGE SCAN PK_SUQUERY_2 (cr=3 pr=0 pw=0 time=31 us)
6400640 TABLE ACCESS FULL SUBQUERY_T1 (cr=37417 pr=0 pw=0 time=6261 us)
ヒントを付与するとすぐに実行計画でサブクエリは既存のFilter動作方式でない結合動作方式に変更され、結合はHash Join Semiで実行された。 このような実行計画の変更はSQLがFilter動作方式で実行されて221回繰り返しFull Table Scanをしなければならなかった非効率を除去し、一回のみFull Table Scanで実行するように変更されたのである。 改善前のSQLの場合、インデックスが存在しないことが非効率の根本的な原因だが、インデックスを生成するのが難しい場合にサブクエリの動作方式を理解してヒントを使って動作方式を変更することだけでも満足するほどの改善効果があるということが分かる。
頻繁に発生するパフォーマンスの事例をもう一つ見てみましょう。Main SQLの抽出件数が非常に多く、サブクエリの定数条件が非常に効率的で、サブクエリを最初に実行するのが効率的なSQLがあると仮定しよう。このような場合、Filterと結合動作のどの動作が有利なのか調べてみよう。
var b1 number
var b2 number
var b3 number
var b4 number
exec :b1 := 1
exec :b2 := 450210
exec :b3 := 100000
exec :b4 := 100004
SELECT c4, c5, c6
FROM SUBQUERY_T1 t1
WHERE c6 >= :b1 AND c6 <= :b2
AND EXISTS ( SELECT /*+ NO_UNNEST */
'x'
FROM SUBQUERY_T2 t2
WHERE t2.c1 = t1.c4
AND t2.c3 >= :b3 AND t2.c3 <= :b4 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 23 76.78 80.41 0 63908416 0 320
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 25 76.79 80.42 0 63908416 0 320
Rows Row Source OPERATION
-------- ---------------------------------------------------
320 FILTER (cr=63908416 pr=0 pw=0 time=80418233 us)
16000000 FILTER (cr=37440 pr=0 pw=0 time=87 us)
16000000 TABLE ACCESS FULL SUBQUERY_T1 (cr=37440 pr=0 pw=0 time=1192396 us)
5 FILTER (cr=63870976 pr=0 pw=0 time=57868833 us)
5 TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=63870976 pr=0 …)
15967744 INDEX UNIQUE SCAN PK_SUQUERY_2 (cr=47903232 pr=0 …)
Main SQLの抽出データが何と1,600万件である。 そしてサブクエリはPrimary KeyインデックスをUnique Scanでアクセスする。 だがI/Oが合計6,300万ブロックで、実行時間も何と80秒もかかった。 トレース内容のうち重要な点はMain SQLで抽出された1,600万件に対してサブクエリを実行した後、最終抽出されるデータはせいぜい320件に過ぎないということである。 すなわち、320件を抽出するSQLに対して、処理したI/Oブロック数と実行時間を大量に消費されたということである。 もし、上記SQLがDBサーバーで秒当たり10回以上実行されるほど頻繁なSQLならば、DBサーバーの性能に深刻な問題を起こすだろう。
それでは上記SQLをFilter動作方式でない結合動作方式(Hash Join Semi)に誘導する場合、SQLの性能にどのような変化が現れるのか確認してみよう。
var b1 number
var b2 number
var b3 number
var b4 number
exec :b1 := 1
exec :b2 := 450210
exec :b3 := 100000
exec :b4 := 100004
SELECT c4, c5, c6
FROM SUBQUERY_T1 t1
WHERE c6 >= :b1 AND c6 <= :b2
AND EXISTS ( SELECT /*+ UNNEST HASH_SJ */
'x'
FROM SUBQUERY_T2 t2
WHERE t2.c1 = t1.c4
AND t2.c3 >= :b3 AMD t2.c3 <= :b4 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 23 4.30 4.35 0 38756 0 320
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 25 4.30 4.39 0 38756 0 320
Rows Row Source OPERATION
------- ---------------------------------------------------
320 FILTER (cr=38756 pr=0 pw=0 time=4351744 us)
320 HASH JOIN RIGHT SEMI (cr=38756 pr=0 pw=0 time=4351731 us)
5 TABLE ACCESS FULL SUBQUERY_T2 (cr=1316 pr=0 pw=0 time=31425 us)
16000000 TABLE ACCESS FULL SUBQUERY_T1 (cr=37440 pr=0 pw=0 time=92 us)
既存SQLでサブクエリが1,600万回反復実行された部分を除去してSQL性能が大きく改善された。 しかし改善後抽出される件数が320件に過ぎないが、SUBQUERY_T1テーブルに対するFull Table Scanにより処理時間が4秒も必要とされた。
Note. Nested Loops Joinの負荷を減らすためにHash Joinで処理する場合が存在する。 この時、Semi/Anti/Outer Joinのように結合順序が固定された場合、先に読むテーブルの抽出結果が多い場合、先行テーブルに対するHash Mapを生成する時負荷が発生する可能性がある。 このような問題を解決するためにOracle 10gからはHash Joinである場合に限って結合順序を変更できるHash Join Right (Semi/Anti/Outer)機能を提供する。 これを制御するヒントはSWAP_JOIN _INPUTSとNO_SWAP_JOIN_INPUTSがある。これをより一層改善してみよう。 性能をより一層改善させることができる余地はSUBQUERY_T2の抽出データ5件でSUBQUERY_T1の結合処理後、最終320件が抽出されることにある。 したがってサブクエリ(SUBQUERY_T2)を先に処理し、Main SQLのSUBQUERY_T1テーブルとNested Loops Join (結合連結カラムにインデックスが存在)を実行するならば性能が改善されるだろう。 したがってサブクエリを先に実行して、Hash Semi Joinを誘導するHASH_SJ代わりにNested Loops Semi Joinを誘導するNL_SJヒントを使ってSQLを実行すれば性能が改善されることで期待される。
したがって前のSQLを改善する方法で下の二種類の方法を使うことができる。
- SQLの変更（O）：サブクエリを結合に変更して、効率的な実行計画に誘導
- SQLの変更（X）：サブクエリとMain SQLに結合順序（LEADING）、結合方法（NL_SJ）、QUERY BLOCK（QB_NAME）ヒントを追加して、効率的な実行計画に誘導
以下はSQL変更なしでサブクエリを先に読んでNested Loops Joinで実行するようにヒントを使って改善した内容である。 実行結果を確認してみよう。
var b2 number
var b3 number
var b4 number
exec :b1 := 1
exec :b2 := 450210
exec :b3 := 100000
exec :b4 := 100004
SELECT /*+ QB_NAME(MAIN) LEADING(T2@SUB) USE_NL(T1@MAIN) */
c4, c5, c6
FROM SUBQUERY_T1 T1
WHERE c6 >= :b1 AND c6 <= :b2
AND EXISTS ( SELECT /*+ UNNEST QB_NAME(SUB) */
'x'
FROM SUBQUERY_T2 T2
WHERE t2.c1 = t1.c4
AND t2.c3 >= :b3 AND t2.c3 <= :b4 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 23 0.01 0.01 0 1673 0 320
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 25 0.01 0.04 0 1673 0 320
Rows Row Source OPERATION
------- ---------------------------------------------------
320 FILTER (cr=1673 pr=0 pw=0 time=16328 us)
320 TABLE ACCESS BY INDEX ROWID SUBQUERY_T1 (cr=1673 pr=0 …)
326 NESTED LOOPS (cr=1353 pr=0 pw=0 time=15354 us)
5 SORT UNIQUE (cr=1316 pr=0 pw=0 time=14996 us)
5 TABLE ACCESS FULL SUBQUERY_T2 (cr=1316 pr=0 pw=0 time=14960 us)
320 INDEX RANGE SCAN SUBQUERY_T1_IDX_01 (cr=37 pr=0 …)
上のトレース結果で必ず着目しなければならないことはSUBQUERY_T2データを抽出した後SORT UNIQUEオペレーションが発生した部分である。 なぜなら、サブクエリを結合に変更するために抽出結果の件数が違うのを防止するために必ず重複値を除去しなければならないためである。（注３）
（注３）：以前で説明した“テスト[1]. サブクエリの基本的な特性を調べてみること”で重複した値が除去された結果が抽出されたことを思い出してみよう。前の例題はサブクエリの抽出データが5件に過ぎなくて追加的な整列作業の負荷が殆どないが、サブクエリで抽出データが多い場合には整列作業による負荷を無視できないことである。 したがって必ずサブクエリの抽出件数を考慮した後効率的な処理方法を選択しなければならない。
ここまでFilter動作方式と結合動作方式に対して調べてみた。 そしてデータ抽出結果とInput値の種類によってある方式が性能上、さらに有利かも確認してみた。 サブクエリがどんな動作方式を選択するかによりSQL性能は大きい差が発生する可能性があるので、サブクエリが含まれたSQLを作成する時は必ず作成後実行計画を検討して、効率的な動作方式で実行されるようにしなければならない。
今回は、ここまでです。次回は、「サブクエリ動作方式を制御するヒント」についてです。ご期待ください。 See You Next ! ^-^
