catch-img

Oracle SQLチューニング（第14回）「スカラーサブクエリの理解と効率的なSQLの作成」(4/5)

Oracle技術情報

Oracle SQLチューニング

今回は、スカラーサブクエリと結合に関しての１回目です。

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^^

​​​​​​​

前の記事

prev-article-image

Oracle SQLチューニング（第13回）「スカラーサブクエリの理解と効率的なSQLの作成」(3/5)

次の記事

next-article-image

Oracle SQLチューニング（第15回）「スカラーサブクエリの理解と効率的なSQLの作成」(5/5)

CONTACT

他社に頼らず自社でデータベースを監視・運用をしませんか？
MaxGaugeがサポートします

お役立ち資料は
こちらから

資料ダウンロード

不明点がある方は、
こちらからお問い合わせください

お問い合わせ

お電話でのお問い合わせはこちら

03-3518-0771

平日 10時～18時

人気記事ランキング

タグ一覧

logo_img
ページトップへ戻る

©EXEM JAPAN Corp. All Rights Reserved.