2021.07.21
SQLチューニング(第10回)「サブクエリ動作方式を理解すること」(8/8)
今回は、「サブクエリ動作方式を理解すること」の最終回として、Where節のサブクエリについてのお話をお届けします。
2.1.3.3. WHERE節のサブクエリを結合に変更しよう
前回、結合をサブクエリに変更してSQLの性能問題を改善した事例を調べた。 今回は反対にサブクエリを結合に変更して性能問題を改善する方法に対して調べてみることにする。
サブクエリが一個だけ含まれたSQLならば、Optimizerは一つのサブクエリーに対するCostを計算すれば良いので、統計情報だけ正確ならば比較的効率的な実行計画を樹立できることになる。
しかし、Where句にサブクエリが多いと、Optimizerは、SQLの最適な実行計画を策定することは難しい。なぜなら、サブクエリが持つことができるすべての組み合わせのCostを計算する必要があるので、Optimizerが実行計画を最適化する過程がより負荷になることがあり、そのため精度を放棄し、すべてのサブクエリをUnnest実行したのと両方を実行していないこと二つのCostのみ計算するからである。
したがってSQLにサブクエリが多いならば、効率的な実行計画を樹立する確率が急激に落ちていくことになる。 これが前で言及したサブクエリを乱用してはいけない理由である。
サブクエリがいくつか存在するSQLの性能問題を改善する方法に対して調べてみる。
運用環境ではBind Variable Peeking機能はほとんどFalseで運営するため、そのパラメータを変更した後、テストを実行したことを事前にお伝えする。
ALTER SYSTEM SET "_optim_peek_user_binds" = FALSE ;
SELECT t1.*
FROM SUBQUERY_T1 t1
WHERE EXISTS ( SELECT 'x'
FROM SUBQUERY_T2 t2
WHERE t2.c2 like :b1
AND t2.c3 >= :b2
AND t2.c3 <= :b3
AND t2.c1 = t1.c4
AND t2.c2 = t1.c5 )
AND EXISTS ( SELECT 'x'
FROM SUBQUERY_T3 t3
WHERE t3.c2 LIKE :b4
AND t3.c3 >= :b5
AND t3.c3 <= :b6
AND t3.c1 = t1.c4 )
AND EXISTS ( SELECT 'x'
FROM SUBQUERY_T1 t11
WHERE t11.c5 LIKE :b7
AND t11.c4 >= :b8
AND t11.c4 <= :b9
AND t11.c4 = t1.c4 );
Bind Variable Peaking(以下BVP)について :Bind変数を持つSQLに対して最初解析する時、実際のBind値を先に確認して、ヒストグラムを参照して最適化することをいう。 最初ハード解析するSQLに対しては最適な実行計画を樹立することができるという長所がある。 だがSQLが最初解析される時点で配置プログラムが実行され、Optimizerがヒストグラムを参照してFull Table Scanをする実行計画を樹立したとすれば、以後オンラインで実行されるSQLが期間に対するBind値の期間が短くてIndex Scanが性能上有利にも関わらず、既に樹立した実行計画に対してソフト解析でFull Table Scanをする実行計画をそのまま使うことになって性能問題が発生する可能性がある。 このような状況は確率上低い方だが、発生する場合、性能に及ぼす影響が非常に深刻なので普通BVP機能はFalseで運営するのが一般的である。 BVPはヒースはパラメーターである_OPTIM_PEEK_ USER_BINDSにより変更可能である。
前のSQLを見ると、サブクエリーが合計3個存在し、各サブクエリ内のWhere句の条件はBind変数として処理されている。上記SQLはサブクエリ以外の別の定数条件が存在しないため、サブクエリを最初に実行した後、Main SQLが比較する値を受け取り、実行するとSQLのパフォーマンスが良好になる。そこで、最初のサブクエリが(SUBQUERY_T2)を最初に実行することで、パフォーマンスが有利になるようBind変数の値を設定した後、トレース結果を確認してみる。
var b1 varchar2(10)
var b2 number
var b3 number
var b4 varchar2(10)
var b5 number
var b6 number
var b7 varchar2(10)
var b8 number
var b9 number
exec :b1 := 'A'
exec :b2 := 200000
exec :b3 := 200100
exec :b4 := '%'
exec :b5 := 100000
exec :b6 := 300000
exec :b7 := '%'
exec :b8 := 100000
exec :b9 := 300000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 19 5.75 90.32 22044 478496 0 256
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 5.75 90.32 22044 478496 0 256
Rows Row Source OPERATION
------- ---------------------------------------------------
256 FILTER (cr=478496 pr=22044 pw=0 time=90748742 us)
256 NESTED LOOPS SEMI (cr=478496 pr=22044 pw=0 time=90748735 us)
246144 NESTED LOOPS SEMI (cr=466943 pr=22031 pw=0 time=997164558 us)
246144 NESTED LOOPS (cr=459246 pr=22031 pw=0 time=993718531 us)
100002 SORT UNIQUE (cr=974 pr=728 pw=0 time=510612 us)
100002 TABLE ACCESS BY INDEX ROWID SUBQUERY_T3 (cr=974 pr=728 …)
200001 INDEX RANGE SCAN SUBQUERY_T3_IDX_01 (cr=504 pr=376 …)
246144 TABLE ACCESS BY INDEX ROWID SUBQUERY_T1 (cr=458272 pr=21303 …)
246144 INDEX RANGE SCAN SUBQUERY_T1_IDX_01 (cr=212128 pr=13827 …)
3846 INDEX RANGE SCAN SUBQUERY_T1_IDX_01 (cr=7697 pr=0 …)
4 TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=11553 pr=13 …)
3846 INDEX RANGE SCAN SUBQUERY_T2_IDX_01 (cr=7707 pr=13 …)
SQL作成者はBind変数にどんな値を使うのかあらかじめ知っているので、SUBQUERY_T2を先に実行してこそ最も効率的な処理されるということを知っている。しかしOptimizerはBind変数にどんな値が入力されるのか知らない状態で実行計画を樹立しなければならない。 それでOptimizerは前のトレース結果とともにSUBQUERY_T2でないSUBQUERY_T3テーブルを先に読んで実行することが効率的だと間違って判断して非効率が発生することになった。したがってOptimizerが間違って樹立した実行計画をSQL作成者が意図したように、SUBQUER_T2を先に実行するように誘導しなければならない。 このような場合には、性能改善方法としてサブクエリを結合に変更することが有効である。 まずサブクエリで作成された部分をインライン ビューに変えてSQLを作成し、抽出データが重複値を持つ場合にはDISTINCTを付与して重複を除去する。 最後にインライン ビューに変わったサブクエリを結合順序を調節するLEADINGヒントを使って実行計画を制御すれば意図した通りのSQLを実行できる。
以下はSUBQUERY_T2を先に実行するようにSQLを再作成したものである。 SQL性能が改善されたのかトレース結果を確認してみよう。
SELECT /*+ LEADING(T2 T1) USE_NL(T2 T1) */
t1.*
FROM SUBQUERY_T1 t1,
( SELECT DISTINCT t2.c1, t2.c2
-------
FROM SUBQUERY_T2 t2
|---サブクエリを用いて
WHERE t2.c2 like :b1
| 結合に変換
AND t2.c3 >= :b2 AND t2.c3 <= :b3 ) t2
-------- (DISTICTを含む)
WHERE t2.c1 = t1.c4
AND t2.c2 = t1.c5
AND EXISTS ( SELECT 'x'
FROM SUBQUERY_T3 t3
WHERE t3.c2 LIKE :b4
AND t3.c3 >= :b5 AND t3.c3 <= :b6 AND t3.c1 = t1.c4 )
AND EXISTS ( SELECT 'x'
FROM SUBQUERY_T1 t11
WHERE t11.c5 LIKE :b7
AND t11.c4 >= :b8 AND t11.c4 <= :b9
AND t11.c4 = t1.c4 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.03 0 0 0 0
Execute 1 0.01 0.02 0 0 0 0
Fetch 19 0.00 0.02 29 827 0 256
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 0.01 0.08 29 827 0 256
Rows Row Source OPERATION
------- ---------------------------------------------------
256 NESTED LOOPS SEMI (cr=827 pr=29 pw=0 time=46064 us)
256 NESTED LOOPS SEMI (cr=811 pr=29 pw=0 time=37370 us)
256 NESTED LOOPS (cr=799 pr=29 pw=0 time=35047 us)
4 VIEW (cr=512 pr=29 pw=0 time=25797 us)
4 HASH UNIQUE (cr=512 pr=29 pw=0 time=25787 us)
4 FILTER (cr=512 pr=29 pw=0 time=60 us)
4 TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=512 pr=29 …)
7692 INDEX RANGE SCAN SUBQUERY_T2_IDX_01 (cr=42 pr=29 …)
256 TABLE ACCESS BY INDEX ROWID SUBQUERY_T1 (cr=287 pr=0 …)
256 INDEX RANGE SCAN SUBQUERY_T1_IDX_01 (cr=31 pr=0 …)
4 INDEX RANGE SCAN SUBQUERY_T1_IDX_01 (cr=12 pr=0 …)
4 TABLE ACCESS BY INDEX ROWID SUBQUERY_T3 (cr=16 pr=0 …)
4 INDEX RANGE SCAN SUBQUERY_T3_IDX_01 (cr=12 pr=0 …)
SQLを再作成してヒントを付与して、意図したようにSQLが実行された。 既に90.32秒必要とされたSQLが0.08秒で実行される満足な結果を得ることができた。
SQL作成時Where節に多くのサブクエリが含まれる場合、性能が良くない実行計画を樹立する確率が非常に高い。 このような場合、ヒントを使って実行計画を制御しにくいので、サブクエリを結合に変更するのを検討しなければならない。
いかがでしたでしょうか?今回で「サブクエリ動作方式を理解すること」に関してのお話は終了です。
次回からは、「スカラーサブクエリの理解と効率的なSQLの作成」に関するお話になります。乞うご期待!See you ^-^