悩ませ過ぎは及ばざるがごとし #7 - おまけ Tweet

最近、おまけ付けるの好きなので:)

コストベースオプティマイザがどんだけ考えているか、チューニング前とチューニング後（案１と案２）でトレースファイルサイズで比較してみることに（本来それだけで比較できるものではないとは思いますが、あまりにも差があるのでw)

コストベースオプティマイザのトレースは次のようにして取得できますよね（詳細はググってね）

SCOTT> alter session set events='10053 trace name context forever, level 1';

セッションが変更されました。

経過: 00:00:00.00
SCOTT> SELECT　……　

・・・仲略・・・

5999行が選択されました。

経過: 00:00:53.48

SCOTT> alter session set events='10053 trace name context off';

セッションが変更されました。

経過: 00:00:00.00

SCOTT> 

オプティマイザトレースファイルのサイズを比較すると以下のようになります：）　
オプティマイザを悩ますクエリ、ヒントで考えるな！感じろ！にしたクエリ（考えてますけど〜〜〜w）、最後が、相関副問合せに書き換えたクエリの順となっています。

[oracle@lampeye trace]$ ll
合計 59088
-rw-r----- 1 oracle oinstall    54431  9月  8 22:14 alert_lampeye2.log
-rw-r----- 1 oracle oinstall 54294792  9月  8 22:16 lampeye2_ora_5913.trc
-rw-r----- 1 oracle oinstall  5154103  9月  8 22:17 lampeye2_ora_5969.trc
-rw-r----- 1 oracle oinstall   898699  9月  8 22:19 lampeye2_ora_5981.trc
[oracle@lampeye trace]$ 

最初のトレースファイル(.trc)，54294792バイトもありますよーw このオプティマイザトレースファイルがハードパースで４８秒も考え込んでた時のものです。
（対象クエリも合わせて載せてみました）

-rw-r----- 1 oracle oinstall 54294792  9月  8 22:16 lampeye2_ora_5913.trc

こんなにオプティマイザを考えさせちゃうクエリ↓

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
    T1.surro_id IN (リテラル,....限界まで)
    OR T1.surro_id IN (リテラル,....限界まで）

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

/

２つめのトレースファイル、5154103バイトで、１／１０程度になりましたーでも大きいですよね。これはヒントを付加して悩まないようにしてあげたものですが、それでもこんなサイズになってます！

-rw-r----- 1 oracle oinstall  5154103  9月  8 22:17 lampeye2_ora_5969.trc

案２、ヒントを付加したクエリでもこんなにでるのね。

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

/

最後のトレースファイルが（案１）、相関副問合せを利用して改善したクエリをハードパースした際に出力されたトレースファイルです。随分違うもんですねー

-rw-r----- 1 oracle oinstall   898699  9月  8 22:19 lampeye2_ora_5981.trc

案１、処理時間も短いけど、ほんと決断はやって感じですね。

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
    )
/

---

ここまでのあらずじ

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