2021.10.27
SQLチューニング（第1７回）「WITH節理解と効率的なSQLの作成」(２/3)
今回は、WITH節の活用に関して考察します。さあ、張り切って参りましょう。スタートです。
4.2 SQL性能改善のためにWITH節を活用する
4.1.2.1 データ重複アクセスを除去する
たびたびSQLで同じデータを反復処理して性能問題を発生させる場合がある。 このような場合、改善方法でたくさん活用される構文がWith節である。
ところがWith節を活用して性能を改善しようとする時、最も効果的なSQLのパターンはデータ件数は少ないが、データ抽出時I/O処理量が多い時である。 SQLで色々な部分に使われるデータを1回抽出して、データをGlobal Temporary Tableに保存して、同じデータに対する要求時保存されたデータだけ読んで処理すれば良いので、SQLの性能を改善することができる。 そしてデータを抽出すること自体が1回であるからI/O処理量も大幅に削減されることになる。
反面、同じデータを反復処理しても、With節で抽出されたデータ件数が非常に多いならば、Global Temporary Tableに保存するコストと保存されたデータを読む時のコストも侮れないので、必ずWith節を使わなければならないのか悩まなければならない。
4.1.2.2 VIEW PREDICATING 性能問題を除去すること
（注） OptimizerはSQLの性能を改善するためにビュー外部の条件をビュー内部で浸透させようと試みるが、この時、ビュー内部で条件を成功裏に浸透させることになれば、<strong>View Predicating</strong>が発生されたという。SQLの性能問題のうちビュー外部条件がビュー内部で浸透することができなくて、ビューのデータを全部処理した後、結合連結条件をFilter条件で使って深刻な非効率が発生する場合がある。
このような性能問題が発生する場合、解決方法が難しく見えるが、ビュー外部条件を使用者がビュー内部に強制的に追加させることができるならば性能を改善することができる。 理解を助けるため以下の[性能問題SQL]を持ってテストを通じて調べてみる。
[性能問題SQL]
前提条件
1. Outer JoinであるからWITH_T1テーブルを先に実行する。
2. T1と結合接続カラムのT2とT3テーブルのC1列の値はUniqueである。
SELECT t1.c1,
t1.c2,
t2.c1,
t2.c2,
t3.c3
FROM WITH_T1 T1,
WITH_T2 T2,
(
SELECT /*+ NO_MERGE NO_PUSH_PRED */
c1, c2, sum(c3) c3
FROM WITH_T3
GROUP BY c1, c2
) T3
WHERE t1.c1 = t2.c1(+)
AND t1.c1 = t3.c1(+)
AND t1.c2 = 'A'
AND t1.c3 <= 11000;
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39 | 1716 |
|* 1 | HASH JOIN OUTER | | 39 | 1716 |
| 2 | NESTED LOOPS OUTER | | 39 | 702 |
| 3 | TABLE ACCESS BY INDEX ROWID| WITH_T1 | 39 | 429 |
|* 4 | INDEX RANGE SCAN | WITH_T1_IDX_02 | 39 | |
| 5 | TABLE ACCESS BY INDEX ROWID| WITH_T2 | 1 | 7 |
|* 6 | INDEX RANGE SCAN | WITH_T2_IDX_01 | 1 | |
| 7 | VIEW | | 500K| 12M|
| 8 | HASH GROUP BY | | 500K| 5371K|
| 9 | TABLE ACCESS FULL | WITH_T3 | 500K| 5371K|
------------------------------------------------------------------------
上記[性能問題SQL]の実行計画の中でID7、8、9の部分を見ると、WITH_T1から抽出されたデータに満足しているデータ（T1.C1= T3.C1（+））のみを抽出することなく、WITH_T3テーブルのフルデータを処理した。（T3インラインビューに追加されているNO_MERGEヒントは、パフォーマンスの問題が発生している事例を作成するために任意に追加したものであることをあらかじめ述べておく。）
WITH_T3テーブルの全体データを処理した決定的な理由は、インライン ビューT3外部の結合条件がT3インライン ビュー内部で記述(PREDICATING)できなかったためである。
もし、インラインビューの結合条件であるT1.C1= T3.C1（+）でT1.C1列の値がT3ビュー内部に記述することが可能ならば、Nested Loops Joinに実行してWITH_T3でT1のC1の値を満足しているデータだけを処理することができるだろう。しかし、インラインビューT3外部の結合条件は、内部に記述（Predicating）できなかったし、これにより、WITH_T3全体のデータを処理するしかないのである。
前提条件で説明したように、WITH_T1テーブルと結合されるカラムであるWITH_T2とWITH _T3テーブルのC1カラムはインデックスが存在して、データもUniqueである。 したがってWITH_T1で抽出されるデータが少ないならば、WITH_T3でデータを抽出する時WITH_T1で抽出されたデータを効率的に提供してSQLの性能を改善することができるはずである。
上記SQL性能を改善するための鍵はWITH_T1テーブルの抽出データ件数であるから、抽出されるデータ件数から確認してみる。
[テーブルWITH_T1の抽出件数確認]
SELECT count(*)
FROM WITH_T1
WHERE c2 = 'A'
AND c3 <= 11000 ;
COUNT(*)
--------
38 ---> 38件抽出
確認してみた結果、WITH_T1で抽出されるデータはC2 = ‘A’ AND C3 <= 11000条件によって38件で大変少ない。 したがってWITH_T2とWITH_T3のデータ処理はWITH_T1で抽出された38件に該当するデータだけ抽出できるならば性能が改善されるだろう。
すなわち、関連した性能問題を除去するための方法として二つ改善案を導き出すことができる。
一つ目は、インライン ビュー外部の条件がビュー内部で記述されないことが問題であるから、WITH_T1で抽出した値をインライン ビューで作った後、インライン ビューT3 (WITH_T3)の中に強制的に追加する方法である。
二つ目は、With節を宣言して必要なデータをあらかじめ抽出した後、必要な時ごとに再使用するようにSQLを作成する方法である。
二つの改善案のうち先に最初の改善案を反映してみることにしよう。 最初の改善案はT1で抽出されたデータをインライン ビューT3中に追加した後、結合条件を提供して非効率を除去する方法である。
改善方法[1]. WITH_T1テーブル照会の部分をT3インライン ビュー内に追加して性能改善
SELECT t1.c1,
t1.c2,
t2.c1,
t2.c2,
t3.c3
FROM WITH_T1 t1
,WITH_T2 t2
,( SELECT /*+ LEADING(T1) USE_NL(T1 T3) */
t3.c1, t3.c2, SUM(t3.c3) c3
FROM WITH_3 t3,
(
SELECT c1, c2
FROM WITH_T1
WHERE c2 = 'A' AND c3 <= 11000
) t1
WHERE t1.c1 = t3.c1
GROUP BY t3.c1, t3.c2 ) t3
WHERE t1.c1 = t2.c1(+)
AND t1.c1 = t3.c1(+)
AND t1.c2 = 'A' AND t1.c3 <= 11000 ;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39 | 1716 |
| 1 | NESTED LOOPS OUTER | | 39 | 1716 |
|* 2 | HASH JOIN OUTER | | 39 | 1443 |
| 3 | TABLE ACCESS BY INDEX ROWID | WITH_T1 | 39 | 429 |
|* 4 | INDEX RANGE SCAN | WITH_T1_IDX_02 | 39 | |
| 5 | VIEW | | 38 | 988 |
| 6 | HASH GROUP BY | | 38 | 836 |
|* 7 | TABLE ACCESS BY INDEX ROWID | WITH_T3 | 1 | 11 |
| 8 | NESTED LOOPS | | 38 | 836 |
| 9 | TABLE ACCESS BY INDEX ROWID| WITH_T1 | 39 | 429 |
|* 10 | INDEX RANGE SCAN | WITH_T1_IDX_02 | 39 | |
|* 11 | INDEX RANGE SCAN | WITH_T3_IDX_01 | 1 | |
| 12 | TABLE ACCESS BY INDEX ROWID | WITH_T2 | 1 | 7 |
|* 13 | INDEX RANGE SCAN | WITH_T2_IDX_01 | 1 | |
----------------------------------------------------------------------------
改善方法[1]を適用して、インライン ビューT3を処理する時性能が大幅改善された。 しかしこの改善方法はまた他の非効率が存在する可能性がある。 すぐに、WITH_T1の照会を同じ条件で2回実行するということだ。 テストではWITH_T1でデータを抽出するのにコストが少なく、同じSQLを2回実行しても全体性能に大きい影響を与えなかった。 ところで実際の運営環境でWITH_T1でデータを抽出するのに多くのコストが発生するならば、同じデータを2回実行する時発生するコストを簡単に無視できないだろう。 したがって同じデータを2回読む非効率に対する問題点も必ず考慮しなければならない。
もう一つWith節を使った性能改善方法に対して調べてみよう。
改善方法[2]. With節を宣言してWITH_T1テーブルの抽出データをGLOBAL TEMPORARY TABLEに保存後T3インライン ビューに追加して性能改善
WITH t1 AS (
SELECT /*+ materialize */
<strong>← 「WITH節実行の部分
→ SQLでT1で使用」
</strong>
c1, c2
FROM with_t1
WHERE c2 = 'A' AND c3 <= 11000 )
SELECT t1.c1,
t1.c2,
t2.c1,
t2.c2,
t3.c3
FROM t1
<strong> ← 「SQLで実行の部分」</strong>
,with_t2 t2
,( SELECT /*+ leading(t1) use_nl(t1 t3) */
t3.c1, t3.c2, sum(t3.c3) c3
FROM with_t3 t3,
t1
<strong>← 「SQLで実行の部分」</strong>
WHERE t1.c1 = t3.c1
GROUP BY t3.c1, t3.c2 ) t3
WHERE t1.c1 = t2.c1(+)
AND t1.c2 = t2.c2(+)
AND t1.c1 = t3.c1(+)
AND t1.c2 = t3.c2(+) ;
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39 |
〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜<strong><em>『WITH節実行の部分』</em></strong>〜〜〜〜〜〜
| 1 | TEMP TABLE TRANSFORMATION | | |
| 2 | LOAD AS SELECT | | |
| 3 | TABLE ACCESS BY INDEX ROWID | WITH_T1 | 39 |
|* 4 | INDEX RANGE SCAN | WITH_T1_IDX_02 | 39
〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜~〜〜〜〜〜~〜〜〜〜〜〜
| 5 | NESTED LOOPS OUTER | | | | 39
|* 6 | HASH JOIN OUTER | | | | 39
| 7 | VIEW | V | | 39
| 8 | TABLE ACCESS FULL | <strong>SYS_TEMP_0FD9D6602_16CD8855</strong> | 39 |
|
| 9 | VIEW | | | 39 |
|
| 10 | HASH GROUP BY | | | 39 |
|
|* 11 | TABLE ACCESS BY INDEX ROWID| WITH_T3 <strong><em>「SQL実行の部分」</em></strong> | | 1 | |
| 12 | NESTED LOOPS | | | 39 |
|
| 13 | VIEW | | | 39 |
V
| 14 | TABLE ACCESS FULL | <strong>SYS_TEMP_0FD9D6602_16CD8855 </strong>| 39 |
|* 15 | INDEX RANGE SCAN | WITH_T3_IDX_01 | 1 |
|* 16 | TABLE ACCESS BY INDEX ROWID | WITH_T2 | 1 |
|* 17 | INDEX RANGE SCAN | WITH_T2_IDX_01 | 1 |
-------------------------------------------------------------------------------SQLで反復実行されるデータをWith節を利用してGlobal Temporary Tableに格納しておけば、同じデータ要求時時保存された結果データだけ問い合わせて処理するので、同じデータを2回処理する非効率が改善された。
4.1.2.3 階層クエリーのデータ処理を最小化すること
次は実際に顧客からチューニング依頼を受けたSQLである。 まず、SQLを見てみることとする。
SELECT apt_id
,COUNT(subsno) apt_cnt_subsno_1
,SUBSTR(MAX(SYS_CONNECT_BY_PATH(subsno, ',')), 2) subsno_lst
-----------------------<strong><em>『階層構造分析対象データ抽出の部分』</em></strong>--------------------------
FROM ( SELECT a.*
,ROW_NUMBER() OVER(PARTITION BY tmp_key ORDER BY subsno) rnum
FROM ( SELECT …
FROM tb_logdata a
WHERE …省略…
GROUP BY a.apt_id, a.subsno
HAVING COUNT(a.subsno) >= 2 ) a ) b
-------------------------------------------------------------------------------
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1
AND PRIOR tmp_key = tmp_key
GROUP BY apt_id ;
階層構造を処理するSQLは普通START WITH節→ CONNECT BY節→ WHERE節の順序で実行されて、階層構造を処理する各部分に適切なインデックスのみ存在すれば性能問題はあまり発生しない。 しかし、時々Optimizerが階層クエリーを解釈する時犯すことがある致命的なエラーによって性能問題が発生する場合がある。上記SQLがすぐにOptimizerの誤った解釈で発生する性能問題に対する良い例である。
上記SQLは階層構造分析対象になるデータ抽出の部分に対して先にグルーピングを実行するようにすることによって、データ件数を減らした後階層構造を分析するように作成した。 すなわち、SQL作成者は階層クエリーを実行する時、階層構造を分析する対象データを最小化して最適な性能で実行しようと努力したと考えられる。 もし、SQLが作成者の意図のとおり実行されたとすれば性能問題は発生しなかっただろう。
しかしSQLの実行計画を見れば、作成者が意図したこととは全く違うように実行され性能問題が発生した。 SQLの内容のうちデータの階層構造を分析するSTART WITH,CONNECT BY,WHERE実行の部分がグルーピングされないまま実行され、データの階層構造分析以後に抽出データに対してグルーピングを処理して性能問題が発生したのである。
本来の意図通り実行するようにするためには、先にデータをグルーピングした後、階層構造を処理するようにSQLを再作成しなければならない。この時With節を活用する。 階層構造分析対象データに対してグルーピングを処理したデータをWith節で宣言しあらかじめ抽出しておいて、With節で抽出されたデータで階層構造を分析する。
ここで注意する点は、With節の実行が必ずMaterialize動作方式で実行されなければならないということである。 なぜなら、With節であらかじめ抽出して保存されたデータを使って階層構造を分析することができるように誘導しなければならないためである。
前のSQLはWith節で宣言した部分がSQLで1回だけ実行されるのでInline View動作方式で実行することになって性能が改善されない。 したがってWith節にMaterializeヒントを必ず追加しなければならない。
[改善後SQL ]
---------------------<strong><em>『WITH節を利用して対象データをあらかじめ抽出』</em></strong>--------------------
WITH temp_t1 AS (
SELECT/*+ materialize*/ ---> 必ずヒントゥチュが
a.*,
row_number() over(partition by tmp_key order by subsno) rnum
-------------------------------------------------------------------------------
FROM (
SELECT /*+ full(a) */
...
FROM tb_logdata a
WHERE... 省略...
GROUP BY a.apt_id, a.subsno
HAVING count(a.subsno) >= 2
) a
)
SELECT apt_id ,
count(subsno) apt_cnt_subsno_1 ,
substr(max(sys_connect_by_path(subsno, ',')), 2) subsno_lst
FROM temp_t1 ---> WITH節で宣言した名称
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1
AND PRIOR tmp_key = tmp_key
GROUP BY apt_id ;
いかがでしたでしょうか？次回は、「WITH節を使う時、注意しなければならない点は?」に関してのお話になります。ご期待ください。では、See you ^^
