ヒント句が無効になります

質問

こんばんは。

スクラッチで開発を行っているものです。

SQLのレスポンスが悪いので、テーブルにインデックスを追加し、
念のためにsqlにヒント句をつけよう
と上司から提案され、SQLのヒント句が正常に読み込まれるか確認を行いました。
しかし、オブジェクトブラウザで動作が遅くなることが確認できた(通常:1秒未満、ヒント句:10秒以上)ヒント句を使用したsql文をプロパティファイルに設定してもレスポンスはヒント句をつける前とつけた後で変わりませんでした。

オブジェクトブラウザで動作が遅くなっていることが確認済ですので、
ヒント句が正しくないためにヒント句が無効になっている
と言うわけではありません。

スクラッチでは、ヒント句がコメントとみなされるため、ヒント句自体が意味ないのか、使用しているヒント句がスクラッチで推奨されていない(使用しているヒント区は「FULL(テーブル名)」です。)または、それ以外の理由なのか皆目見当がつきません。

もし、スクラッチでもヒント句を有効にする方法があれば教えていただきたいです。

どなたか、スクラッチにおける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を実行しようとすると実行できません。)

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

回答No.3

uresiiwa

＞また、システムエラーログでは、問題となっているSQLの''(クォーテーションマーク)や、<>(不等号記号)が変換されて表示されています。
＞ひょっとすると、そういった違いが影響しているのかもしれないと思っているのですが、その可能性はありえますか?

これはないのではないかと思います。ログの表記はあくまで便宜上のものでしょう。

デバッグを行いながらSQLを抽出したとのことですが、まだ「本当にアプリで実行されたSQLが抽出できた」のかどうか疑問です。具体的な方法が書かれていないため分からないのですが、「アプリではバインドで実行されているようなのに、リテラルにしないと同じ速度にならない」というのはおかしいです。
私のA案～C案など試して、本当に実行されているものを抽出してみてください。特に、トレース取得などはOracleを使う開発者としてステップアップするのに必要不可欠な技術ですので、この機に調べてみてはいかがでしょうか？どの案を試しても、このSQLの解明をしながら技術力を身につけることができると思いますよ。

補足

uresiiwa様

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

返信遅れてしまい、申し訳ありません。

私のとった方法は、
デバッグで、SQL実行した場所で一旦動作をとめ、SQL文が格納されている変数の中身をチェックする
です。

現在、他の案件を優先的に行うように指示されたため、質問させていただいている件の対応ができない状態です。
対応でき次第、uresiiwa様の提案されている案を試し、その際に不明点があれば、また質問させていただき、解決できればご報告させていただこうと思っています。

ただ、一つ気になった点として、開発に使用しているツールが
JSPにおいて、プレビュー(正式名称は解りませんが、ツール上でJSPのフォーマットを確認できる機能です)では、ボタンの文字を、alignによって中央寄せできるのに、ローカル環境で確認すると文字が右寄せになってしまう。
という事象が発生しています。

この原因が
ローカル環境で確認しているから
なのか
ツールに問題があるから
なのか明確ではありません。

開発環境で確認したいのですが、現在、開発環境において移行作業中のため、むやみに開発環境をいじれず、確認できません。

JSPとプロパティファイルの違いのため、参考にならないとは思いますが、
通常使用できるはずの関数が使用できなくなっている
と言うことをご認識いただきたく思い、ご報告させていただきます。

回答No.2

3rd_001

>FULL(テーブル名)
って、「FULLヒント」なのでテーブルフルスキャンを指示してますよね。索引をつかうなってヒントをなぜ与えているのですか?

インデックスヒントは以下です。
SELECT /*+ INDEX (テーブル名 索引名)*/
FROM テーブル名

select ename
from scott.EMP
where ename='FORD'

ENAME
----------
FORD

実行計画
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 6 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ENAME"='FORD')

統計
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
421 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

select /*+ INDEX(EMP PK_EMP) */ ename
from scott.EMP
where ename='FORD';

ENAME
----------
FORD

経過: 00:00:00.00

実行計画
----------------------------------------------------------
Plan hash value: 4170700152

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 6 | 2 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='FORD')

統計
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
421 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

インデックスヒントを使うと索引アクセスになりますよ。
※上記はENAMEをWhere句にしているので意味がないですけど、
　索引アクセスになりました。

補足

3rd_001様

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

>って、「FULLヒント」なのでテーブルフルスキャンを指示してますよね。索引をつかうなってヒントをなぜ与えているのですか?
これに関しては、使用している環境では、他のインデックスをヒント句により参照させても、ヒント句を使用しない場合とレスポンスがあまり変わらないためです。
ヒント句を使用しない場合との差が歴然としているのが、唯一ヒント句に「FULLヒント」を使用しているものでした。

以上です。説明不足のために余計な混乱を招いてしまい申し訳ありませんでした。