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

さて、さて、前回のつづきです。

意識的に間を取ったんですが、ちょいと長過ぎたかw

前回、ハードパースに時間がかかってるねーってとこまで確認しましたよね〜

・１回目のSQLトレース

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1     45.18      45.15          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      401      0.04       0.05          0       1897          0        5999
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      403     45.23      45.21          0       1897          0        5999

・２回目のSQLトレース

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      401      0.04       0.04          0       1897          0        5999
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      403      0.04       0.04          0       1897          0        5999

ということでした。

じゃ、こんなにコストペースオプティマイザに考えさせちゃうSQL文でどんなのよー。と。　OLTPなのに分析系のすげー難しそーなSQL文なげてるとか？？？　想像しているより現物見た方が早いですw

こんな感じのクエリだんたんよー。

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

/

え、え〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜っ、リテラル値が〜〜〜〜すげ〜〜〜〜〜〜〜ぞ〜〜〜〜〜〜。
クエリ自体はたいしたことないw あの人達がすきそーな自己結合だー。(失礼w)

このクエリを見て、解決策を２つをすぐに思いついたあたなあなた！、　そう、あなた、凄いです！
INリストには上限あるからね：）- Oracle® Database SQL言語リファレンス11gリリース2（11.2）式のリスト

このクエリの解決策。

解決案１
一番効果のありそうなの、リテラルを排除して、その部分を副問合せにしちゃう！。

解決案２
コストベースオプティマイザを悩ませない方法って、ありますよね、そう、ヒントを使って、「こんな感じにうごいて！」作戦。

このクエリの一番の問題点は、リテラル値が山ほどあって、似たような索引が沢山あって、しかも、リテラル値だから毎回値が異なる。結果として毎回ハードパースになるので、毎回コストベースオプティマイザが考え過ぎちゃうとこと。

解決策は前述の２案のうちいずれか一つなんだけど、おすすめは案１のほう。

その訳は、でかいんです、なにが？

以下を見れば、なに？　が　でかいかよーくわかりますw

23:49:06 SYS> SELECT SQL_ID,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM,SQL_TEXT FROM v$sql WHERE SQL_TEXT LIKE '%/*TEST*/%';

SQL_ID        SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM SQL_TEXT
------------- ------------ -------------- ----------- ------------------------------
82q8wxz56y357      3317991         396816      395672 SELECT /*TEST*/  T1.surro_id,
                                                       T1.name,  T1.modified FROM  t
                                                      est T1 join (   SELECT  DISTIN
                                                      CT    surro_id,    surro_bcd
                                                       FROM    test  ) T2  ON   T1.s
                                                      urro_id  = T2.surro_id AND   T
                                                      1.surro_bcd = T2.surro_bcd
                                                           JOIN test T3         ON

ね、でかいでしょ。SHARABLE_MEMなんて３MBいっちゃってるし、この程度のことするにしてはデカ過ぎw
これって、直ちには影響はないのですが…（どっかで聞いた事ある言い回し＞＜　　やはり同じ事するセッション多いとさーボディーブローよ。全部ハードパースだしw

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

---

ここまでのあらずじ

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