スクラッチでのSQL文におけるヒント句の有効化方法について

質問

• スクラッチでのSQL文にヒント句を使用した場合、オブジェクトブラウザでの動作が遅くなることが確認されました。
• しかし、ヒント句が無効になっているわけではなく、スクラッチではヒント句がコメントとみなされるため、意味をなさない可能性があります。
• スクラッチでヒント句を有効にする方法について詳しい方がいらっしゃいましたら、教えていただきたいです。

回答No.1

uresiiwa · ベストアンサー

「スクラッチにおけるSQL文」の意味がはっきり分からなかったのですが、「手組みのアプリケーションの処理から発行されたSQL」と解釈しました。それが、「同様なSQLをオブジェクトブラウザから実行」した場合で速度に差があるが何故か、ということですよね。

サーバ側の環境が同じで、かつ、SQLが一言一句同じであれば、一方だけ実行計画が異なるということは決してなく、特に速度に差も出ません。
オブジェクトブラウザとヒント句に特別な相関はないです。

【考えられること】
１．実はSQLが違う
２つのSQLは、どこかに違いがあるのでは？
改行や空白の違いは無視してよいですが、ありがちなのは、オブジェクトブラウザからはWHERE句をリテラルで（WHERE STATUS='A'のように）指定し、アプリでは変数・バインド値で指定されている（WHERE STATUS=:1のように）場合です。ヒント句は全体の実行計画を後押しする「ヒント」に過ぎないため、ほかの部分が変わってしまうと実行計画が変わることがあります。特に、バインド値とリテラル値の違いは、オプティマイザにとってかなり大きな違いがあります。
２．バインドされている値が違う
万一SQLが同じの場合は、バインドされている値が違う（WHERE STATUS=:1の：1にバインドされるのが一方がA、一方がBなど）ということになりますが、さすがにそれはないですよね。レスポンスが違ってあたりまえですので。

【行うべきこと】
１．アプリが実際に実行しているSQLの抽出
A案．
データベースにトレースをかけて、それぞれのSQLを実行したときにトレースに現れるSQLおよび実行計画を抽出し、比較してください。どこかに違いがあると思います。なお、実行計画も見るためにはレベル１２のトレースにする必要がありますが、DB負荷が高くなる（約1.5倍ぐらい遅くなる）のでお気をつけください。

B案．
OEMが使えるのであれば、「トップアクティビティ」を監視しながらアプリを実行すれば、お手軽にアプリが実行しているSQLと実行計画が抽出できます。

C案．
v$sqlというビューを使って、実行されているSQLを抽出してみてください。
例） select sql_text, executions from v$sql
アプリを実行した時にexecutionsがアップするのを探せば当該SQLが見つかると思います。sql_text like '%思い当たる文字列%'も良いです。

２．アプリ側が実際に実行していたSQLをオブジェクトブラウザから実行する
もし、アプリ側SQLが分かったら、オブジェクトブラウザに貼り付けてSQLを実行してみてください。アプリ動作時と同じレスポンスになります。

補足

uresiiwa様

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

uresiiwa様のご指示通り、アプリが実際に実行しているSQLの抽出を行いました。

方法としては、ローカル環境でデバッグを用い、SQLを実行しているところにデバッグポイントを設け、中身を確認しました。

そこで表示されているSQLをオブジェクトブラウザに貼り付け、実行したところ、10秒近くかかっていました。

ただし、表示されているSQLをバインド値からリテラル値に変更するとアプリと同じ程度のレスポンスがえられます。(アプリではバインド値として表示されています。)

また、システムエラーログでは、問題となっているSQLの''(クォーテーションマーク)や、<>(不等号記号)が変換されて表示されています。
(どのような文字に変更されているかは手元に資料が無いので詳しくはお答えできません。)

ひょっとすると、そういった違いが影響しているのかもしれないと思っているのですが、その可能性はありえますか?
(ちなみに、オブジェクトブラウザ上では、記号が変換された後のSQLを実行しようとすると実行できません。)

お忙しいところ、無知のため、たびたびお手を煩わしてしまい、申し訳ありませんが、お教えいただけますようよろしくお願いします。