2021.09.22
SQLチューニング（第14回）「スカラーサブクエリの理解と効率的なSQLの作成」(4/5)
今回は、スカラーサブクエリと結合に関しての１回目です。
3.1.2 スカラーサブクエリと結合の理解および活用
実務でよく用いるスカラーサブクエリの性能問題は大きく二種類に分類できる。 一番目はスカラーサブクエリの実行位置にともなう性能問題であり、二番目はスカラーサブクエリと結合の関係で発生する性能問題である。 それでは今からこの二種類のパターン対して詳しく調べる。
3.1.2.1 スカラーサブクエリは最終結果だけに実行しよう
まず以下のの”SQL説明”部分を見よう。
SQL説明 : SCALAR_T1テーブル全体データ対象にC1,C2カラムで昇順整列後10件だけ持ってくるSQLを作成しようと思う。 この時、抽出するデータはSCALAR_T1のC1,C2,C3とSCALAR_T2のC3カラム値である。 ただ、SCALAR_T2のC3カラム価格はSCALAR_T1で抽出したC1課SCALAR_T2 C1値が同じ場合にだけ抽出して、同じデータがないならばNULLを抽出しなければならない。
SQL説明の部分に対するSQL作成方法は色々なものがあるだろう。 そのうちのスカラーサブクエリを利用してSQLを作成するべきだとすれば、下のSQL[1],SQL[2]とともに作成することができる。
スカラーサブクエリ作成SQL[1].
SELECT ROWNUM rnum,
x.*
FROM (
SELECT c1,
c2,
c3,
(SELECT t2.C3
FROM SCALAR_T2 T2
WHERE t2.c1 = t1.c1) AS t2_c3
FROM SCALAR_T1 T1
ORDER BY c1, c2
) x
WHERE ROWNUM <= 10 ;
スカラーサブクエリ作成SQL[2].
SELECT ROWNUM rnum, x.*,
(SELECT t2.c3
FROM SCALAR_T2 T2
WHERE t2.c1 = x.c1) AS t2_c3
FROM (
SELECT c1,
c2,
c3
FROM SCALAR_T1 T1
ORDER BY c1, c2
) x
WHERE ROWNUM <= 10 ;
SQL[1]とSQL[2]は作成方法は違うが、同じデータを抽出するSQLである。 しかし、二つのSQLの実行方式には大きな差がある。 それではSQL[1],SQL[2]のトレース結果を通じてどんな差異点があるのか調べてみる。
SQL[1]のトレース結果
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 2 2.02 2.05 0 1502292 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 2.02 2.05 0 1502292 0 10
Rows Row Source Operation
------- ---------------------------------------------------
500000 TABLE ACCESS BY INDEX ROWID SCALAR_T2 (cr=1501114 pr=0 …)
500000 INDEX RANGE SCAN SCALAR_T2_IDX_01 (cr=1001114 pr=0 pw=0 time=1084517 us)
10 COUNT STOPKEY (cr=1502292 pr=0 pw=0 time=2051304 us)
10 VIEW (cr=1502292 pr=0 pw=0 time=2051288 us)
10 SORT ORDER BY STOPKEY (cr=1502292 pr=0 pw=0 time=2051285 us)
500000 TABLE ACCESS FULL SCALAR_T1 (cr=1178 pr=0 pw=0 time=52 us)
SQL[2]のトレース結果
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 2 0.14 0.13 0 1210 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.14 0.13 0 1210 0 10
Rows Row Source Operation
------- ---------------------------------------------------
10 TABLE ACCESS BY INDEX ROWID SCALAR_T2 (cr=32 pr=0 pw=0 time=120 us)
10 INDEX RANGE SCAN SCALAR_T2_IDX_01 (cr=22 pr=0 pw=0 time=72 us)
10 COUNT STOPKEY (cr=1178 pr=0 pw=0 time=132413 us)
10 VIEW (cr=1178 pr=0 pw=0 time=132397 us)
10 SORT ORDER BY STOPKEY (cr=1178 pr=0 pw=0 time=132385 us)
500000 TABLE ACCESS FULL SCALAR_T1 (cr=1178 pr=0 pw=0 time=47 us)
SQL[1]とSQL[2]のトレースを直接実行したとすれば、実行時間は大差なかっただろう。 なぜなら、テストのために作られた小さいテーブル(テーブル サイズ1178ブロック)であり、実行時Physical Reads (pr=0)もなかったためである。
しかしSQL[1]とSQL[2]のトレース結果内容のうちSQLのI/O処理量(ブロック数)を現わすQUERY項目を見れば、処理ブロック数の差が非常に大きいということが分かる。
それでは二つのSQLのI/O処理量の差はなぜ発生するのだろうか?
SCALAR_T2でC3カラム値を抽出するためにスカラーサブクエリを使った。 ところが二つのSQLに使われたスカラーサブクエリの実行位置が違って実行回数に大きい差が発生したためである。
SQL[1]はSCALAR_T1テーブルのすべてのデータ件数である500,000件にスカラーサブクエリ(SCALAR_T2)が反復実行されて合計1,501,114ブロックを読んだ。
1,501,114 = 1,502,292 (全体処理Block)? 1,178 (スカラーサブクエリ実行以前の処理Block)
その一方、SQL[2]はSCALAR_T1テーブルを先にC1,C2カラムで整列した後、ROWNUM条件で10件だけ抽出してスカラーサブクエリ(SCALAR_T2)を実行した。
したがってSQL[2]はSQL[1]に比べてスカラーサブクエリの実行回数が減って、SQL[1]に比べて非常に少ないI/O処理量でSQLを実行した。
SQL[1]とSQL[2]は実際の業務で使うSQLに比べて簡単で、ROWNUM <= 10というの部分範囲処理（注）条件があって改善方法を簡単に導き出した。 しかし、SQLの性能問題のうち、SQL[1]と同じパターンがしばしば実行されて、DBサーバーに性能問題が発生する場合をたくさん見ることがある。 したがって前で調べてみた内容をよく理解して、このような性能問題が発生しないように願う。
（注） 部分範囲処理とはSQLの結果を全部処理しないで、満足する結果が抽出されれば実行を止める処理方式をいう。 例えば、ROWNUM <=5という条件があるならば、5件だけ抽出してこれ以上処理しないで結果を返す。 反面、ROWNUM条件が存在するが、すべてのデータを処理しなければならない場合を全体範囲処理という。以下の[改善前SQL]はB1インラインビューで抽出した11,658件に対して、ct_rsn_cd_01条件に該当する947件を抽出するSQLである。 抽出データが947件であるから、スカラーサブクエリは最大947回だけ実行することが最も効率的だ。 ところが[改善前SQL]の実行内訳を見ルト効率的に実行されなかった。
……以前省略
) B1 ---> 11658件抽出
) C1
WHERE ct_rsn_cd_01 = NVL('1',ct_rsn_cd_01) ---> 11658件中新規件だけ抽出
……以後省略 [改善前SQL]
SELECT c1.*
FROM (
SELECT b1.*
,CASE
WHEN( NVL(seq, '1') = '1' AND ct_rsn_cd = 1 )
THEN '1' -- 1:新規
WHEN( NVL(seq, '1') <> '1' AND ct_rsn_cd = 1 )
THEN '2' -- 2:再登録
WHEN( NVL(seq, '1') = seq_max AND ct_rsn_cd = 2 )
THEN '3' -- 3:有効
WHEN( NVL(seq, '1') <> seq_max AND ct_rsn_cd = 2 )
THEN '4' -- 4:終了
END ct_rsn_cd_01 --区分
FROM (SELECT ………省略………
( SELECT MAX(NVL(seq, 1))
FROM tsd_partner_i
WHERE corp_cd = a.corp_cd
AND aply_fta_cd = a.aply_fta_cd
AND vend_cd = a.vend_cd
AND part_no = a.part_no ) seq_max,
( SELECT DECODE(COUNT(corp_cd), 0, 'N', 'Y')
FROM tsd_partner_fl_i
WHERE corp_cd = a.corp_cd
AND crte_no = a.crte_no ) orig_intro_fg,
( SELECT CASE WHEN COUNT(*) > 0 THEN 'Y' ELSE 'N' END
FROM tmm_part_recp_i p
WHERE p.corp_cd = a.corp_cd
AND p.crte_no = a.crte_no ) use_yn,
( SELECT CASE WHEN COUNT(*) > 0 THEN 'Y' ELSE 'N' END
FROM tmm_sply_vd_sign p
WHERE p.corp_cd = a.corp_cd
AND p.vend_cd = a.vend_cd
AND p.use_stop_fg = 'Y' ) AS divs_yn
FROM ……省略……
WHERE ……省略……
) b1
) c1
WHERE ct_rsn_cd = 01 = NVL('1', ct_rsn_cd_01)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 65 4.17 8.67 670 94663 0 947
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 67 4.19 8.69 670 94663 0 947
Rows Row Source Operation
------- ---------------------------------------------------
11349 SORT AGGREGATE (cr=41271 pr=63 pw=0 time=709730 us)
23473 TABLE ACCESS BY INDEX ROWID TSD_PARTNER_I (cr=41271 pr=63 …)
23473 INDEX RANGE SCAN TSD_PARTNER_I_01 (cr=11485 pr=63 …)
11658 SORT AGGREGATE (cr=11660 pr=0 pw=0 time=110012 us)
845 INDEX UNIQUE SCAN PK_TSD_FILE_I (cr=11660 pr=0 …)
11658 SORT AGGREGATE (cr=23978 pr=576 pw=0 time=3722237 us)
155210 INDEX RANGE SCAN IDX_TMM_PART_RECP_I_01 (cr=23978 pr=576 …)
1023 SORT AGGREGATE (cr=2475 pr=13 pw=0 time=109492 us)
950 TABLE ACCESS BY INDEX ROWID TMM_SPLY_VD_SIGN (cr=2475 pr=13 …)
1603 INDEX RANGE SCAN TMM_SPLY_VD_SIGN_PK (cr=1026 pr=1 …)
947 SORT ORDER BY (cr=129844 pr=670 pw=0 time=9335061 us)
947 VIEW (cr=129844 pr=670 pw=0 time=30675494 us)
11658 NESTED LOOPS OUTER (cr=15279 pr=18 pw=0 time=359027 us)
11658 HASH JOIN (cr=2774 pr=4 pw=0 time=254090 us)
6892 VIEW (cr=1769 pr=4 pw=0 time=111928 us)
6892 HASH UNIQUE (cr=1769 pr=4 pw=0 time=105036 us)
12427 HASH JOIN (cr=1769 pr=4 pw=0 time=157653 us)
13 TABLE ACCESS FULL TSD_CUST_CART_I (cr=7 pr=4 pw=0 time=6318 us)
42883 TABLE ACCESS FULL TMM_CUST_CAR_TP_PART_M (cr=1762 pr=0 …)
16421 TABLE ACCESS FULL TSD_PARTNER_I (cr=1005 pr=0 …)
845 TABLE ACCESS BY INDEX ROWID TSD_PARTNER_FL_I (cr=12505 …)
845 INDEX UNIQUE SCAN PK_TSD_FILE_I (cr=11660 pr=1 …)
[改善前SQL]のトレース結果内容のうち、性能問題が発生する部分は下記のとおりである。
……以前省略……
(P3) 947 SORT ORDER BY (cr=129844 pr=670 pw=0 time=9335061 us)
(P2) 947 VIEW (cr=129844 pr=670 pw=0 time=30675494 us)
(P1) 11658 NESTED LOOPS OUTER (cr=15279 pr=18 pw=0 time=359027 us)
……以後省略……
(P1) ～ (P3)の実行順序は(P1) -> (P2) -> (P3)であり、各説明は下記のとおりである。
・(P1):抽出件数= 11,658件、総I/O処理量= 15,279 blocks. インライン ビューのB1抽出件数
・(P2):抽出件数= 947件、総I/O処理量= 114,565 blocks [ 129,844 (P2) -15,279 (P1)]. WHERE ct_rsn_cd_01 = NVL('1',ct_rsn_cd_01)条件を実行後最終データが抽出される。 I/O処理量が(P1)のI/O処理量で114,565 blocksが増加した部分はスカラーサブクエリ(Functionはなし)のI/O処理量である(性能問題の部分)。
・(P3):抽出件数= 947件. 947件に対するOrder By節実行
By節実行
(P2)でI/O処理量がたくさん発生した理由はインライン ビューB1で抽出された11,658件だけにスカラーサブクエリを反復実行したためである。
したがって[改善後SQL]はスカラーサブクエリの実行位置をSQLの最も外側に変更して、最終抽出データである947件に対してだけ実行されるように再作成した。 SQLの性能にどのような変化があるのかトレース結果を確認してみる。
[改善後SQL]
SELECT ………省略………
( SELECT DECODE(COUNT(corp_cd), 0, 'N', 'Y')
FROM tsd_fta_part_orig_partner_fl_i
WHERE corp_cd = c1.corp_cd
AND crte_no = c1.crte_no ) orig_intro_fg,
( SELECT CASE WHEN COUNT(*) > 0 THEN 'Y' ELSE 'N' END
FROM tmm_part_recp_fta_i p
WHERE p.corp_cd = c1.corp_cd
AND p.crte_no = c1.crte_no ) AS use_yn,
( SELECT CASE WHEN COUNT(*) > 0 THEN 'Y' ELSE 'N' END
FROM tmm_sply_vd_sign p
WHERE p.corp_cd = c1.corp_cd
AND p.vend_cd = c1.vend_cd
AND p.use_stop_fg = 'Y' ) AS divs_yn
FROM (
SELECT b1.*
,CASE
WHEN( NVL(seq, '1') = '1' AND ct_rsn_cd = 1 )
THEN '1' -- 1:新規
WHEN( NVL(seq, '1') <> '1' AND ct_rsn_cd = 1 )
THEN '2' -- 2:再登録
WHEN( NVL(seq, '1') = seq_max AND ct_rsn_cd = 2 )
THEN '3' -- 3:有効
WHEN( NVL(seq, '1') <> seq_max AND ct_rsn_cd = 2 )
THEN '4' -- 4:終了
END ct_rsn_cd_01 --区分
FROM (
SELECT ……省略……
--( SELECT MAX(NVL(seq, 1))
-- FROM tsd_partner_i
-- WHERE corp_cd = a.corp_cd
-- AND aply_fta_cd = a.aply_fta_cd
-- AND vend_cd = a.vend_cd
-- AND part_no = a.part_no ) seq_max,
--( SELECT DECODE(COUNT(corp_cd), 0, 'N', 'Y')
-- FROM tsd_partner_fl_i
-- WHERE corp_cd = a.corp_cd
-- AND crte_no = a.crte_no ) orig_intro_fg,
--( SELECT CASE WHEN COUNT(*) > 0 THEN 'Y' ELSE 'N' END
-- FROM tmm_part_recp_i p
-- WHERE p.corp_cd = a.corp_cd
-- AND p.crte_no = a.crte_no ) AS use_yn,
--( SELECT CASE WHEN COUNT(*) > 0 THEN 'Y' ELSE 'N' END
-- FROM tmm_sply_vd_sign p
-- WHERE p.corp_cd = a.corp_cd
-- AND p.vend_cd = a.vend_cd
-- AND p.use_stop_fg = 'Y' ) AS divs_yn
FROM ……省略……
WHERE ……省略……
) b1
) c1
WHERE ct_rsn_cd_01 = NVL('1', ct_rsn_cd_01)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 65 0.74 0.75 0 18548 0 947
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 67 0.76 0.77 0 18548 0 947
Rows Row Source Operation
------- ---------------------------------------------------
947 SORT AGGREGATE (cr=949 pr=0 pw=0 time=8197 us)
1 INDEX UNIQUE SCAN PK_TSD_FILE_I (cr=949 pr=0 …)
947 SORT AGGREGATE (cr=1904 pr=0 pw=0 time=12011 us)
0 INDEX RANGE SCAN IDX_TMM_PART_RECP_I_01 (cr=1904 pr=0 …)
214 SORT AGGREGATE (cr=417 pr=0 pw=0 time=4267 us)
146 TABLE ACCESS BY INDEX ROWID TMM_SPLY_VD_SIGN (cr=417 pr=0 pw=0 time=3567 us)
216 INDEX RANGE SCAN TMM_SPLY_VD_SIGN_PK (cr=216 pr=0 …)
947 SORT ORDER BY (cr=21539 pr=0 pw=0 time=796094 us)
947 VIEW (cr=15278 pr=0 pw=0 time=336864 us)
11658 WINDOW SORT (cr=15278 pr=0 pw=0 time=419939 us)
11658 NESTED LOOPS OUTER (cr=15278 pr=0 pw=0 time=263015 us)
11658 HASH JOIN (cr=2773 pr=0 pw=0 time=181394 us)
6892 VIEW (cr=1768 pr=0 pw=0 time=97590 us)
6892 HASH UNIQUE (cr=1768 pr=0 pw=0 time=90697 us)
12427 HASH JOIN (cr=1768 pr=0 pw=0 time=141563 us)
13 TABLE ACCESS FULL TSD_CUST_CART_I (cr=7 pr=0 pw=0 time=151 us)
42892 TABLE ACCESS FULL TMM_CUST_CAR_TP_PART_M (cr=1761 pr=0 pw=0 42932 us)
16421 TABLE ACCESS FULL TSD_PARTNER_I (cr=1005 pr=0 pw=0 time=32955 us)
845 TABLE ACCESS BY INDEX ROWID TSD_PARTNER_FL_I (cr=12505 pr=0 …)
845 INDEX UNIQUE SCAN PK_TSD_FILE_I (cr=11660 pr=0 pw=0 time=44557 us)
……以前の省略……
(P4) 947 SORT ORDER BY (cr=21539 pr=0 pw=0 time=796094 us)
(P3) 947 VIEW (cr=15278 pr=0 pw=0 time=336864 us)
(P2) 11658 WINDOW SORT (cr=15278 pr=0 pw=0 time=419939 us)
(P1) 11658 NESTED LOOPS OUTER (cr=15278 pr=0 pw=0 time=263015 us)
……以後省略……
(P1) ～ (P4)の実行順序は(P1) -> (P2) -> (P3) -> (P4)であり、各説明は下記のとおりである。
・(P1):抽出件数= 11,658件、総I/O処理量= 15,278ブロック(インライン ビューB1の抽出件数)
・(P2):抽出件数= 11,658件、総I/O処理量= 15,278ブロック(インライン ビューB1内のスカラーサブクエリをMAX() OVER処理に変更した部分)
・(P3):抽出件数= 947件、総I/O処理量= 15,278ブロック(WHERE ct_rsn_cd_01 = NVL('1',ct_rsn_cd_01)処理後抽出件数)
・(P4):抽出件数= 947件、総I/O処理量= 6,261ブロック((21,539? 15,278). 抽出データである947件だけにスカラーサブクエリを実行して、[改善前SQL]の性能問題が改善された)
今回は、ここまででです。以下でしたでしょうか？次回は、引き続き「スカラーサブクエリと結合に関して」のお話になります。See you^^
