1 2 Previous Next 16 Replies Latest reply: Mar 18, 2013 8:06 PM by asahide RSS

    範囲選択で結合したときのパフォーマンス

    996774
      お世話になります。

      2つのテーブルを結合したSQLを作成しています。
      実行すると40分ぐらいかかっているものを改善したいので
      お知恵を拝借させてください。

      バージョンは11gです。

      ・マスタテーブル<tt>
      KEY1_FROM
      KEY1_TO
      KEY2_FROM
      KEY2_TO
      FLG1
      FLG2

      (PRIMARY KEY KEY1_FROM,KEY1_TO,KEY2_FROM,KEY2_TO)
      (INDEX NON UNIQUE INDEX I1 KEY1_FROM,KEY1_TO,KEY2_FROM,KEY2_TO)
      798件</tt>

      ・トランザクションテーブル<tt>
      XXXCODE
      XXXDATE
      XXXCHANNEL
      XXXMONTH
      XXXKINGAKU

      (PRIMARY KEY XXXCODE, XXXDATE)
      (NONUNIQUE INDEX XXXCODE,XXXDATE,XXXMONTH,XXXKINGAKU)
      約14000000件</tt>

      改善したいSQLは↓です
      SELECT 
       W.XXXDATE
      ,W.XXXCHANNEL
      ,COUNT(W.XXXCODE) AS "件数"
      FROM トランザクションテーブル W
      LEFT JOIN マスタテーブル B
       ON W.XXXMONTH   BETWEEN B.KEY1_FROM AND B.KEY1_TO
      AND W.XXXKINGAKU BETWEEN B.KEY2_FROM AND B.KEY2_TO
      GROUP BY W.XXXDATE, W.XXXCHANNEL
      実行計画はこれです
      -------------------------------------------------------------------------------------------------------------
      | Id  | Operation                | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
      -------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT         |                          |    34 |   952 |       |   622K (84)| 02:04:29 |
      |   1 |  HASH GROUP BY           |                          |    34 |   952 |       |   622K (84)| 02:04:29 |
      |   2 |   MERGE JOIN OUTER       |                          |  6489M|   169G|       |   154K (36)| 00:30:52 |
      |   3 |    SORT JOIN             |                          |    13M|   225M|   854M|   101K  (1)| 00:20:14 |
      |   4 |     TABLE ACCESS FULL    | トランザクションテーブル |    13M|   225M|       | 24005   (1)| 00:04:49 |
      |   5 |    FILTER                |                          |       |       |       |            |          |
      |   6 |     SORT JOIN            |                          |   798 |  8778 |       |     4  (25)| 00:00:01 |
      |   7 |      INDEX FAST FULL SCAN| I1                       |   798 |  8778 |       |     3   (0)| 00:00:01 |
      -------------------------------------------------------------------------------------------------------------
      よろしくお願いいたします。

      Edited by: 993771 on 2013/03/13 21:57
        • 1. Re: 範囲選択で結合したときのパフォーマンス
          asahide
          まず、SQLとか実行計画は
          タグを使って頂けると見易いです。。
          -ご参考
          <<OTN掲示板の簡易マニュアル(タグの使い方とか)
          
          
          
          テーブルと索引については適切に統計情報は取得されてますでしょうか。
          両テーブルをSORT MERGEでFULL JOINしているので時間がかかっているように見えますが、NESTED LOOPとかに変えても同じような状況でしょうか。
          索引を結合列にそれぞれつけたら何か変わったりしますでしょうか。                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          • 2. Re: 範囲選択で結合したときのパフォーマンス
            996774
            &#62; まず、SQLとか実行計画は
            タグを使って頂けると見易いです。。
            
            ありがとうございます。修正しました。
            
            &#62; テーブルと索引については適切に統計情報は取得されてますでしょうか。
            
            取れていました。
            
            &#62; 両テーブルをSORT MERGEでFULL JOINしているので時間がかかっているように見えますが、NESTED LOOPとかに変えても同じような状況でしょうか。
            
            USE_NLを付けて実行してみましたが、
            結果は37分で時間は変わりませんでした。
            
            &#62; 索引を結合列にそれぞれつけたら何か変わったりしますでしょうか。
            
            インデックスを付けて実行計画を取り直しました。
            IDX_MON_W1_1 というのがそうです。
             
            [code]
            -------------------------------------------------------------------------------------------------
            | Id  | Operation                | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
            -------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT         |              |    34 |  1054 |       |   573K (84)| 01:54:39 |
            |   1 |  HASH GROUP BY           |              |    34 |  1054 |       |   573K (84)| 01:54:39 |
            |   2 |   MERGE JOIN OUTER       |              |  5956M|   171G|       |   145K (36)| 00:29:03 |
            |   3 |    SORT JOIN             |              |    13M|   212M|   854M| 94828   (1)| 00:18:58 |
            |   4 |     INDEX FAST FULL SCAN | IDX_MON_W1_1 |    13M|   212M|       | 20366   (1)| 00:04:05 |
            |*  5 |    FILTER                |              |       |       |       |            |          |
            |*  6 |     SORT JOIN            |              |   798 | 11970 |       |     4  (25)| 00:00:01 |
            |   7 |      INDEX FAST FULL SCAN| I1           |   798 | 11970 |       |     3   (0)| 00:00:01 |
            -------------------------------------------------------------------------------------------------
            [/code]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
            • 3. Re: 範囲選択で結合したときのパフォーマンス
              996774
              &#62; 両テーブルをSORT MERGEでFULL JOINしているので時間がかかっているように見えますが、NESTED LOOPとかに変えても同じような状況でしょうか。

              これについて追記です。
              先ほどはヒント句「USE_NL」を付けて時間が変わらなかったと書きましたが、
              「USE_NL(<b>W</b>)」とトランザクションテーブルのみを指定していました。

              その後に「USE_NL(<b>W B</b>)」と両方のテーブルを指定して、
              先ずは実行計画を取りました。
              ---------------------------------------------------------------------------------------
              | Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
              ---------------------------------------------------------------------------------------
              |   0 | SELECT STATEMENT       |              |    34 |  1054 |    15M  (4)| 52:06:52 |
              |   1 |  HASH GROUP BY         |              |    34 |  1054 |    15M  (4)| 52:06:52 |
              |   2 |   NESTED LOOPS OUTER   |              |  5956M|   171G|    15M  (1)| 50:41:16 |
              |   3 |    INDEX FAST FULL SCAN| IDX_MON_W1_1 |    13M|   212M| 20366   (1)| 00:04:05 |
              |*  4 |    INDEX FAST FULL SCAN| I1           |   427 |  6405 |     1   (0)| 00:00:01 |
              ---------------------------------------------------------------------------------------
              実行計画の結果では予想時間が延びてしまいました。

              ただこのヒント句を付けたSQLを実行して時間を計ったところ、
              19分で終わりました。
              一体どういうことなのでしょうか。

              早くなってきたのは嬉しいのですが、
              もう少し早くしたいと思っています。

              引き続きご支援頂けますよう、よろしくお願いいたします。
              • 4. Re: 範囲選択で結合したときのパフォーマンス
                asahide
                一体どういうことなのでしょうか。
                単純にコスト見積もりとあっていないのだと思います。
                索引作成してから再度統計情報取得されてますでしょうか?
                早くなってきたのは嬉しいのですが、
                もう少し早くしたいと思っています。
                SQLとしてはそんなに複雑ではないので、索引つける、ヒントつける、辺りでトライ&エラーを繰り返すのが早いのではないかと。。。

                マスタは小さいのでこちらを絞ってからNLで早くなりそうな気はするんですけど。
                • 5. Re: 範囲選択で結合したときのパフォーマンス
                  996774
                  &#62; 単純にコスト見積もりとあっていないのだと思います。
                  &#62; 索引作成してから再度統計情報取得されてますでしょうか?

                  統計情報を手動で取得して実行計画を取り直しましたが、
                  結果は変わりませんでした。

                  なかなか難しいです。。
                  • 6. Re: 範囲選択で結合したときのパフォーマンス
                    asahide
                    なかなか全部を正確に~、というのは難しいのかもしれないですね。

                    索引の順序とか、結合の順序とかを色々と変えて試されてみてはと思います。
                    色々と計画をアップしてればエキスパートがアドバイスくれるかもしれません。。
                    • 7. Re: 範囲選択で結合したときのパフォーマンス
                      996774
                      SQLを実際に流して時間を計っているので捗らないのですが、
                      ヒント句に「ORDERD USE_NL(W B) INDEX(B プライマリキー名 )」
                      としたところ、8分まで短縮できました。

                      プライマリキーを参照させることでROWIDでアクセスするから早くなったのでしょうか。

                      こうなるともっと早くしたいと思いますが、
                      当初の40分から8分になったので良しと思ってもいます。

                      色々とアドバイスを頂き、ありがとうございました。

                      # これを読んでいる皆々様へ
                      # まだアドバイスがあれば受付続けますので、何か御座いましたらお願いいたします!
                      • 8. Re: 範囲選択で結合したときのパフォーマンス
                        asahide
                        是非実行計画等をシェア頂けると、アドバイス付く可能性はあがると思います!
                        • 9. Re: 範囲選択で結合したときのパフォーマンス
                          996774
                          今のところ、早いパターンがこれです。
                          SELECT /*+ ORDERD USE_NL(W B) INDEX(B +プライマリキー名+ ) */
                           W.XXXDATE
                          ,W.XXXCHANNEL
                          ,COUNT(W.XXXCODE) AS "件数"
                          FROM トランザクションテーブル W
                          LEFT JOIN マスタテーブル B
                           ON W.XXXMONTH   BETWEEN B.KEY1_FROM AND B.KEY1_TO
                          AND W.XXXKINGAKU BETWEEN B.KEY2_FROM AND B.KEY2_TO
                          GROUP BY W.XXXDATE, W.XXXCHANNEL
                          その実行計画です。SQLPlusで実行すると8分で終わります。
                          ----------------------------------------------------------------------------------------------------------
                          | Id  | Operation                     | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
                          ----------------------------------------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT              |                          |    34 |  1496 |    98M  (1)|327:49:54 |
                          |   1 |  HASH GROUP BY                |                          |    34 |  1496 |    98M  (1)|327:49:54 |
                          |   2 |   NESTED LOOPS OUTER          |                          |  6808M|   278G|    97M  (1)|326:11:28 |
                          |   3 |    TABLE ACCESS FULL          | トランザクションテーブル     |    13M|   279M| 24018   (1)| 00:04:49 |
                          |   4 |    TABLE ACCESS BY INDEX ROWID| マスタテーブル               |   488 | 11224 |     7   (0)| 00:00:01 |
                          |*  5 |     INDEX RANGE SCAN          | プライマリキー名             |   488 |       |     3   (0)| 00:00:01 |
                          ----------------------------------------------------------------------------------------------------------
                           
                          • 10. Re: 範囲選択で結合したときのパフォーマンス
                            asahide
                            マスタテーブルはきちんと絞れてますので、トランザクションテーブルで索引スキャンになればよいのですよね。
                            トランザクションテーブル側のJOIN条件のところに索引付けても変わりませんでしょうか。
                            • 11. Re: 範囲選択で結合したときのパフォーマンス
                              996774
                              ありがとうございます。

                              JOIN条件の列に索引を付けて実行しました。
                              SQLPlusでの実行時間は15分でした。
                              単純にTABLE ACCESS BY INDEX ROWIDとなれば早くなるわけじゃないんですね。むぅ

                              NON UNIQUE INDEX IDX_2 (XXXMONTH, XXXKINGAKU, XXXDATE, XXXCHANNEL)
                              SELECT 
                              /*+ ORDERD USE_NL(W B) INDEX(W IDX_2) INDEX(B +プライマリキー名+ ) */
                               W.XXXDATE
                              ,W.XXXCHANNEL
                              ,COUNT(W.XXXCODE) AS "件数"
                              FROM トランザクションテーブル W
                              LEFT JOIN マスタテーブル B
                               ON W.XXXMONTH   BETWEEN B.KEY1_FROM AND B.KEY1_TO
                              AND W.XXXKINGAKU BETWEEN B.KEY2_FROM AND B.KEY2_TO
                              GROUP BY W.XXXDATE, W.XXXCHANNEL
                              実行結果
                              ----------------------------------------------------------------------------------------------------------
                              | Id  | Operation                     | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
                              ----------------------------------------------------------------------------------------------------------
                              |   0 | SELECT STATEMENT              |                          |    34 |  1496 |    83M  (1)|278:17:03 |
                              |   1 |  HASH GROUP BY                |                          |    34 |  1496 |    83M  (1)|278:17:03 |
                              |   2 |   NESTED LOOPS OUTER          |                          |  5956M|   244G|    83M  (1)|276:51:27 |
                              |   3 |    TABLE ACCESS BY INDEX ROWID| トランザクションテーブル |    13M|   279M|    13M  (1)| 43:52:20 |
                              |   4 |     INDEX FULL SCAN           | IDX_2                    |    13M|       | 53627   (1)| 00:10:44 |
                              |   5 |    TABLE ACCESS BY INDEX ROWID| マスタテーブル           |   427 |  9821 |     5   (0)| 00:00:01 |
                              |*  6 |     INDEX RANGE SCAN          | プライマリキー名         |   427 |       |     2   (0)| 00:00:01 |
                              ----------------------------------------------------------------------------------------------------------
                              • 12. Re: 範囲選択で結合したときのパフォーマンス
                                996774
                                もう一つ試しました。

                                INDEX_FFSを試しました。
                                が、インデックスは先ほど新たに付けたIDX_2ではなく、
                                このスレッドの最初の投稿の中に記載したインデックスを参照する実行計画が出ましたが。

                                &#62; NONUNIQUE INDEX XXXCODE,XXXDATE,XXXMONTH,XXXKINGAKU
                                ⇒ これをIDX_1とします

                                なお、SQLPlusでの処理時間は7:57で、
                                僅かながらこれまでの記録を更新しました :-)

                                SQL
                                SELECT 
                                /*+ ORDERD USE_NL(W B) INDEX_FFS(W IDX_2) INDEX(B +プライマリキー名+ ) */
                                 W.XXXDATE
                                ,W.XXXCHANNEL
                                ,COUNT(W.XXXCODE) AS "件数"
                                FROM トランザクションテーブル W
                                LEFT JOIN マスタテーブル B
                                 ON W.XXXMONTH   BETWEEN B.KEY1_FROM AND B.KEY1_TO
                                AND W.XXXKINGAKU BETWEEN B.KEY2_FROM AND B.KEY2_TO
                                GROUP BY W.XXXDATE, W.XXXCHANNEL
                                実行計画
                                ---------------------------------------------------------------------------------------------------
                                | Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
                                ---------------------------------------------------------------------------------------------------
                                |   0 | SELECT STATEMENT              |                   |    34 |  1496 |    70M  (1)|234:28:51 |
                                |   1 |  HASH GROUP BY                |                   |    34 |  1496 |    70M  (1)|234:28:51 |
                                |   2 |   NESTED LOOPS OUTER          |                   |  5956M|   244G|    69M  (1)|233:03:16 |
                                |   3 |    INDEX FAST FULL SCAN       | IDX_1             |    13M|   279M| 20689   (1)| 00:04:09 |
                                |   4 |    TABLE ACCESS BY INDEX ROWID| マスタテーブル    |   427 |  9821 |     5   (0)| 00:00:01 |
                                |*  5 |     INDEX RANGE SCAN          | プライマリキー名  |   427 |       |     2   (0)| 00:00:01 |
                                ---------------------------------------------------------------------------------------------------
                                • 13. Re: 範囲選択で結合したときのパフォーマンス
                                  asahide
                                  INDEX_FFSは力技ですよね。。
                                  一応こちらをパラレルでやったりするともう少し早くなるかもしれません。


                                  (XXXMONTH, XXXKINGAKU)だけで索引つけてもやはりFULL SCAN(もしくはINDEX FULL SCAN)となってしまうのでしょうか。
                                  集計の箇所とかがうまくいかないのですかね。。
                                  • 14. Re: 範囲選択で結合したときのパフォーマンス
                                    996774
                                    パラレルを試しました。
                                    CPUが8なので16を指定しました。
                                    この指定の仕方が間違っているのか、結果はあまり変わりませんでした。
                                    難しいものですね。。。
                                    SELECT 
                                    /*+ ORDERD USE_NL(W B) PARALLEL(W 16) INDEX_FFS(W IDX_2) INDEX(B +プライマリキー名+ ) */
                                     W.XXXDATE
                                    ,W.XXXCHANNEL
                                    ,COUNT(W.XXXCODE) AS "件数"
                                    FROM トランザクションテーブル W
                                    LEFT JOIN マスタテーブル B
                                     ON W.XXXMONTH   BETWEEN B.KEY1_FROM AND B.KEY1_TO
                                    AND W.XXXKINGAKU BETWEEN B.KEY2_FROM AND B.KEY2_TO
                                    GROUP BY W.XXXDATE, W.XXXCHANNEL
                                    ---------------------------------------------------------------------------------------------------
                                    | Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
                                    ---------------------------------------------------------------------------------------------------
                                    |   0 | SELECT STATEMENT              |                   |    34 |  1496 |    70M  (1)|234:28:51 |
                                    |   1 |  HASH GROUP BY                |                   |    34 |  1496 |    70M  (1)|234:28:51 |
                                    |   2 |   NESTED LOOPS OUTER          |                   |  5956M|   244G|    69M  (1)|233:03:16 |
                                    |   3 |    INDEX FAST FULL SCAN       | IDX_1             |    13M|   279M| 20689   (1)| 00:04:09 |
                                    |   4 |    TABLE ACCESS BY INDEX ROWID| マスタテーブル    |   427 |  9821 |     5   (0)| 00:00:01 |
                                    |*  5 |     INDEX RANGE SCAN          | プライマリキー名  |   427 |       |     2   (0)| 00:00:01 |
                                    ---------------------------------------------------------------------------------------------------
                                    SQLPlusでの実行結果 ⇒ 9分55秒


                                    /*+ ORDERD USE_NL(W B) PARALLEL(W 16) INDEX(W IDX_2) INDEX(B +プライマリキー名+ ) */
                                    --SQLは同じなので割愛
                                    ----------------------------------------------------------------------------------------------------------
                                    | Id  | Operation                     | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
                                    ----------------------------------------------------------------------------------------------------------
                                    |   0 | SELECT STATEMENT              |                          |    34 |  1496 |    83M  (1)|278:17:03 |
                                    |   1 |  HASH GROUP BY                |                          |    34 |  1496 |    83M  (1)|278:17:03 |
                                    |   2 |   NESTED LOOPS OUTER          |                          |  5956M|   244G|    83M  (1)|276:51:27 |
                                    |   3 |    TABLE ACCESS BY INDEX ROWID| トランザクションテーブル |    13M|   279M|    13M  (1)| 43:52:20 |
                                    |   4 |     INDEX FULL SCAN           | IDX_2                    |    13M|       | 53627   (1)| 00:10:44 |
                                    |   5 |    TABLE ACCESS BY INDEX ROWID| マスタテーブル           |   427 |  9821 |     5   (0)| 00:00:01 |
                                    |*  6 |     INDEX RANGE SCAN          | プライマリキー名         |   427 |       |     2   (0)| 00:00:01 |
                                    ----------------------------------------------------------------------------------------------------------
                                    SQLPlusでの実行結果 ⇒ 18分24秒
                                    1 2 Previous Next