いつもお世話になってます。

質問

いつもお世話になってます。
他プロジェクトのまた聞きなのですが。。。。

oracle9iからoracle10gにバージョンアップを行いました。

数日後、数千万件にバインド変数でアクセスするＳＱＬがインデックスを使用せず、フルアクセスしてしまい、トラブルとなりました。
この事象はＣＢＯのデメリットなので、納得なのですが。

１．トラブル発生前は該当ＳＱＬはINDEX RANGE SCANを使用していた。
２．トラブル対処として、ヒント句を使用すると、INDEX FULL SCANになってしまい、結果として遅くなってしまった。

前置きが長くなりましたが、ヒント句を使用してINDEX FULL SCANになってしまった場合、明示的にINDEX RANGE SCANを適用させる方法はあるのでしょうか？

ネット検索してもそのような方法を見つけることができませんでした。

回答No.3

dda167 · ベストアンサー

> おそらくバインドピークのデメリットが顕著にでてしまった
値の偏った列ということですね。

> 表名＋インデックス名を書いてました
/*+ index(table_name index_name) */ですね。
以下はマニュアルの抜粋ですが
別名の件はご存知でしたか？

--------------------------------------------------
アクセスする表は、文に指定する場合と同じように正確
に指定してください。文が表の別名を使用している場合、
表の名前ではなく、表の別名をヒントで使用する必要が
あります。スキーマ名が文中にある場合は、ヒント内の
表名にそのスキーマ名を入れないでください。
--------------------------------------------------

http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19207-02/hintsref.htm

SQL、ヒント、統計情報の取得に問題がないのであれば、
INDEX RANGE SCANよりINDEX FULL SCANがコストが低いと
オプティマイザが判断したということでしょうね。

# バインド変数をやめてリテラルで指定するとか……

> タイトルをつけないで投稿してしまい
タイトルとつけないとこうなるんですか。
（知らなかった……）
勉強になります（笑）。

# 現場にいれば、あれこれとできるんですけどね。
# もどかしいものですね（笑）。

回答No.2

entree

NO_INDEX_FFS ヒントも指定することで INDEX FULL SCAN は回避できるで
しょう。でもその前に、索引作成後に統計情報はちゃんと取得しましたか？

補足

回答ありがとうございます。

NO_INDEX_FFSだと、Fast Full Index scansだけが回避されるのかと思ってました。
INDEX FULL SCANも回避できるのですね。
他プロジェクトなので、簡単には試せませんが、自プロジェクトで環境を作って試してみたいと思います。

＞でもその前に、索引作成後に統計情報はちゃんと取得しましたか
ORACLEデフォルトと22時で自動取得しているそうです。
１０ｇ移行後数日間や、トラブル当日の午前中は、それなりに動いていた（性能がでていた）そうなので、バインドピークのハードバースで
ハズレをひいたのか？　と想像しています。

回答No.1

dda167

> トラブル発生前は該当ＳＱＬはINDEX RANGE SCANを使用していた
10gにバージョンアップした後、
数日間はINDEX RANGE SCANだったということですね？

> トラブル対処として、ヒント句を使用すると、INDEX FULL SCANになってしまい
ヒントの書き方は間違っていないのですね？

# 質問の内容がわかるようなタイトルをつけていただく
# わけにはいかないですか？

補足

回答ありがとうございます。

＞10gにバージョンアップした後、
＞数日間はINDEX RANGE SCANだったということですね？
はい。
INDEX RANGE SCANだったものが、ある瞬間からINDEX FULL SCANになったそうです。
おそらくバインドピークのデメリットが顕著にでてしまったと想像してます。

＞ヒントの書き方は間違っていないのですね？
はい。
表名＋インデックス名を書いてました。

# 質問の内容がわかるようなタイトルをつけていただく
# わけにはいかないですか？
タイトルをつけないで投稿してしまい、１行目がタイトルになってしまいました。
今後、気をつけたいとおもいます。