悩ませ過ぎは及ばざるがごとし #6 Tweet

前回のつづきです。

ヒントを使ってコストベースオプティマイザを悩ませないという方法で、４０秒台っただった処理時間を１秒台にすることに成功しました。

これでも解決は解決なのですが、SQL文単体としては。ただ、インスタンスレベルて考えた場合無駄なメモリ使い過ぎだろという点が気がかり。コストベースオプティマイザが悩まなくなったことでCPUリソースの無駄遣いは回避できましたが、メモリリソースの無駄遣いが凄い。

ということで、どちらの無駄も減らす効果が期待できる案１を…

案１で書き換えるとヒントなしでも随分スッキリしちゃいます。
リテラル値が６０００個近くありましたが、その部分を相関副問合せにしてあります。リテラル値の箇所はバインド変数化しちゃえばいいっすね。

SELECT
     T1.surro_id
    ,T1.name
    ,T1.modified
FROM
    test T1 JOIN (
        SELECT DISTINCT
             surro_id
            ,surro_bcd
        FROM
            test
    ) T2
    ON
        T1.surro_id  = T2.surro_id AND
        T1.surro_bcd = T2.surro_bcd
    JOIN test T3
    ON
        T1.surro_id = T3.surro_id AND
        T1.surro_acd= T3.surro_acd
WHERE
    EXISTS (
        SELECT
            1
        FROM
            test_keys
        WHERE
            surro_id BETWEEN 1000000001 AND 1000005999
            AND surro_id = T1.surro_id
    )
/

実行してみると…　（以下、SQL*Plusで set autot trace exp statコマンド叩いて実行した結果です。なお事前に共有プールはクリアしてあります。）

5999行が選択されました。

経過: 00:00:00.07

実行計画
----------------------------------------------------------
Plan hash value: 3724917145

-------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |              |  5997 |   468K|   144   (3)| 00:00:02 |
|   1 |  VIEW                            | VM_NWVW_1    |  5997 |   468K|   144   (3)| 00:00:02 |
|   2 |   HASH UNIQUE                    |              |  5997 |   491K|   144   (3)| 00:00:02 |
|*  3 |    HASH JOIN RIGHT SEMI          |              |  5997 |   491K|   143   (2)| 00:00:02 |
|*  4 |     INDEX RANGE SCAN             | PK_TEST_KEYS |  5999 | 41993 |    17   (0)| 00:00:01 |
|*  5 |     HASH JOIN                    |              |  5997 |   450K|   125   (1)| 00:00:02 |
|*  6 |      INDEX RANGE SCAN            | IX01_TEST    |  5999 |   128K|    20   (0)| 00:00:01 |
|*  7 |      HASH JOIN                   |              |  5998 |   322K|   105   (1)| 00:00:02 |
|*  8 |       INDEX RANGE SCAN           | IX02_TEST    |  5999 | 53991 |    19   (0)| 00:00:01 |
|   9 |       TABLE ACCESS BY INDEX ROWID| TEST         |  5999 |   269K|    85   (0)| 00:00:02 |
|* 10 |        INDEX RANGE SCAN          | IX02_TEST    |  5999 |       |    19   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

SQL_ID        SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM SQL_TEXT
------------- ------------ -------------- ----------- ------------------------------
52gaz3tjd90dh        43446          15648       14504 SELECT  T1.surro_id,  T1.name,
                                                        T1.modified FROM  test T1 jo
                                                      in (   SELECT    DISTINCT    s
                                                      urro_id,    surro_bcd   FROM
                                                        test  ) T2  ON   T1.surro_id
                                                        = T2.surro_id AND   T1.surro
                                                      _bcd = T2.surro_bcd         JO
                                                      IN test T3         ON   T1.sur
                                                      ro_id = T3.surro_id AND   T1.s
                                                      urro_acd= T3.surro_acd WHERE
                                                      EXISTS (   SELECT    1   FROM
                                                         test_keys   WHERE    surro_
                                                      id BETWEEN 1000000001 AND 1000
                                                      005999    AND T1.surro_id = su
                                                      rro_id  )

きゃほーーーーーーっ。　最高〜〜〜〜っ！ :) 言うことナッシングw　　

処理時間が、48.83秒　から　0.07秒へ、SHARABLE_MEMが、3318142バイト　から　43446バイトに。　でバインド変数化しちゃえば〜〜〜、

同時に多数のセッションから集中して発行されても耐えられそうな〜

最後に、ドヤ顔（キリっ

ほんとうにヤバそうなところはキッチリ潰しておきましょ！

OLTP系で実行計画はよいけど、コストベースオプティマイザを悩ませ過ぎて遅延してたら本末転倒ですよー。と。

ーーーー完ーーーーー

---

ここまでのあらずじ

・悩ませ過ぎは及ばざるがごとし #1
・悩ませ過ぎは及ばざるがごとし #2
・悩ませ過ぎは及ばざるがごとし #3
・悩ませ過ぎは及ばざるがごとし #4
・悩ませ過ぎは及ばざるがごとし #5