今日はOracleでヒント句を使う際の危険性について書いてみたいと思います。
Oracleにはヒント句というものがあり、
SQLを解析・実行方法を決めている"オプティマイザ"の動作を
指定することが出来ます。
?
そのヒント句はオプティマイザの動作を理解して
チューニングして使うべきなのですが、
失敗例を見せてみようかなぁ～と思い、載せてみます。
?
今回使用するヒント句はORDEREDヒント句です。
これはFROM句に指定されている順番でテーブルを読むことを強制したヒント句です。
?
例題のSQLは以下のものになります。
SELECT /*+ ORDERED */ * FROM T1, T2, T3 WHERE T2.ID = T3.ID AND T3.ID = T1.ID;
このSQLでORDEREDヒント句を使用したので、
読むテーブルの順番は T1→T2→T3になります。
?
ここでオプティマイザ君はこんな感じに考えてくれます。
ORDEREDヒントがあるなぁ～～
→じゃぁ、T1を読む
?? →T2を読む
→T1とT2をつなぐ条件を探す
→WHERE句を見る
→・・・・・条件ないじゃん！ じゃぁ直積だね！(>_<;
?
WHERE句を見てみると・・・・T3.IDとT1.ID、T2.IDとT3.IDをつなぐ条件がありますが
T2.IDとT1.IDをつなぐ条件がありませんね！
人が考えると T2.ID = T3.ID かつ T3.ID = T1.ID ってことは
・・・ T2.ID = T3.ID = T1.ID ぢゃん！
だからT2.ID = T1.IDだろう！！！
っていえるんですけど、オプティマイザ君は人間ではありませんから・・・わからなかったんです。
?
実行計画もこんなになっちゃいました。
| 操作 |
オプション |
オブジェクト |
行 |
コスト |
時間 |
バイト |
フィルタ
条件 * |
アクセス
条件 |
|
SELECT STATEMENT |
? |
? |
? |
209,799 |
? |
? |
? |
? |
|
HASH JOIN |
? |
? |
98,095 |
209,799 |
2,518 |
5,885,700 |
? |
"T2"."ID" = "T3"."ID" AND "T3"."ID" = "T1"."ID" |
|
TABLE ACCESS |
FULL |
T3 |
10,593 |
8 |
1 |
211,860 |
? |
? |
|
MERGE JOIN |
CARTESIAN |
? |
115,620,914 |
207,866 |
2,495 |
4,624,836,560 |
? |
? |
|
TABLE ACCESS |
FULL |
T1 |
10,593 |
8 |
1 |
211,860 |
? |
? |
|
BUFFER |
SORT |
? |
10,915 |
207,858 |
2,495 |
218,300 |
? |
? |
|
TABLE ACCESS |
FULL |
T2 |
10,915 |
20 |
1 |
218,300 |
? |
? |
?
なので、今度はSQLをオプティマイザ君にもわかるように直しましょう。
| SELECT /*+ ORDERED */ * FROM T1, T2, T3 WHERE T1.ID = T2.ID AND T2.ID = T3.ID |
この状態で実行計画を取ると・・・・
| 操作 |
オプション |
オブジェクト |
行 |
コスト |
時間 |
バイト |
フィルタ
条件 * |
アクセス
条件 |
|
SELECT STATEMENT |
? |
? |
? |
42 |
? |
? |
? |
? |
|
HASH JOIN |
? |
? |
97,956 |
42 |
1 |
5,877,360 |
? |
"T2"."ID" = "T3"."ID" |
|
TABLE ACCESS |
FULL |
T3 |
10,593 |
8 |
1 |
211,860 |
? |
? |
|
HASH JOIN |
? |
? |
32,698 |
31 |
1 |
1,307,920 |
? |
"T1"."ID" = "T2"."ID" |
|
TABLE ACCESS |
FULL |
T1 |
10,593 |
8 |
1 |
211,860 |
? |
? |
|
TABLE ACCESS |
FULL |
T2 |
10,915 |
21 |
1 |
218,300 |
? |
? |
?
先ほどのものとアクセス条件が変わりましたね！
?
この様にヒント句を使ってオプティマイザを操作するだけでは
SQLが早くなると言い切れないのです。
?
実はこのSQL、ヒント句をはずしたほうが快適に動いてくれますしね。
#データの件数などによってうまく結合方法を変えてくれたりして
#オプティマイザ任せにしたほうが考えてくれます。
?
ヒント句は必要最低限で使用するのが良いということでしょうね。
?
以上、Night Writerの夏椰でした。
#懇親会でも言われましたが、毎度夜中更新ですみません。
#だって、仕事がいそがしいんだもんっっっっ