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

前回のつづきです。

前回は改善案を２つ提示しました。

まず、最初は案２のヒントを使って、コストベースオプティマイザに、「考えるな！　感じろ！」作戦w

元のクエリに次のようにヒントを書き加えちゃいます。
どの索引使えとか、結合方法はハッシュとか、とか、よく見かけるヒントだけです。

SELECT   
    /*+
        LEADING(T1 T2 T3)
        USE_HASH(T1 T2)
        USE_HASH(T1 T3)
        INDEX(T1 IX01_TEST) 
        INDEX(T3 IX01_TEST) 
    */
    T1.surro_id,
    T1.name,
    T1.modified
FROM
    test T1 JOIN (
        SELECT 
            /*+ 
                MERGE 
                INDEX(test IX02_TEST) 
            */ 
            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
    T1.surro_id IN (リテラル,....限界まで)
    OR T1.surro_id IN (リテラル,....限界まで）

    .....以下....すきなだけ繰り返しw

/

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

経過: 00:00:01.32

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

----------------------------------------------------------------------------------------------
| Id  | Operation                        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |           |  5999 |   456K|  5796   (1)| 00:01:10 |
|   1 |  VIEW                            | VM_NWVW_1 |  5999 |   456K|  5796   (1)| 00:01:10 |
|   2 |   HASH UNIQUE                    |           |  5999 |   451K|  5796   (1)| 00:01:10 |
|*  3 |    HASH JOIN                     |           |  5999 |   451K|  5795   (1)| 00:01:10 |
|   4 |     INLIST ITERATOR              |           |       |       |            |          |
|*  5 |      INDEX RANGE SCAN            | IX01_TEST |  5999 |   128K|  1935   (1)| 00:00:24 |
|*  6 |     HASH JOIN                    |           |  5999 |   322K|  3859   (1)| 00:00:47 |
|   7 |      INLIST ITERATOR             |           |       |       |            |          |
|   8 |       TABLE ACCESS BY INDEX ROWID| TEST      |  5999 |   269K|  2001   (1)| 00:00:25 |
|*  9 |        INDEX RANGE SCAN          | IX01_TEST |  5999 |       |  1935   (1)| 00:00:24 |
|  10 |      INLIST ITERATOR             |           |       |       |            |          |
|* 11 |       INDEX RANGE SCAN           | IX02_TEST |  5999 | 53991 |  1857   (1)| 00:00:23 |
----------------------------------------------------------------------------------------------

やたーーーーーーーーーーーーっ！　:) 目標の３秒を下回りましたよー :)

コストベースオプティマイザの悩みを解消してあげることができました…が、例のSHARABLE_MEMが3MB超えちゃってる問題はまだ抱えたままです。＞＜

SQL_ID        SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM SQL_TEXT
------------- ------------ -------------- ----------- ------------------------------
fbjw4wjdma4f7      3318142         396808      395664 SELECT  /*+   LEADING(T1 T2 T3
                                                      )   USE_HASH(T1 T2)   USE_HASH
                                                      (T1 T3)   INDEX(T1 IX01_TEST)
                                                        INDEX(T3 IX01_TEST)  */  T1.
                                                      surro_id,  T1.name,  T1.modifi
                                                      ed FROM  test T1 join (   SELE
                                                      CT    /*+     MERGE     INDEX(
                                                      test IX02_TEST)    */    DISTI
                                                      NCT    surro_id,    surro_bcd

今回はここまで、次回へつづく。

---

ここまでのあらずじ

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