9iと10gでの実行計画の違いについて

SQLの実行計画について教えていただきたいです。

【現状】
9iと10gそれぞれの環境で /*+ ALL_ROWS */ を使用したあるSQLの
実行計画を出したところ、全く異なった実行計画になっていました。
コストは9iが2000、10ｇが150で、10gでは数秒で結果が返ってくるのに対し、
9iではいつまで経っても結果が返ってきません。
9i環境と10g環境とではテーブルの構造やINDEXは同じですが
データの中身は別で、件数は9iが100万件、10gが150万件です。

【教えていただきたいこと】
このように9iと10gとで実行計画や処理の時間が異なる原因は
データの中身が違うこと以外では何が考えられるでしょうか。
解決策があれば合わせてお教えいただきたいです。
/*+ ALL_ROWS */を使えば9iでもSQLをコストベースにできると
思っているのですが、それが間違いなのでしょうか。。。

そもそも実行計画がよく分かっていないので申し訳ないのですが、
何かお気づきのことがあればお教えいただきたいと思います。
よろしくお願いします！

No.5 ベストアンサー 回答者： nora1962 回答日時： 2011/05/23 10:25

見やすいように、1バイト空白2個を2バイト空白に変換しました。

【9i】
SELECT STATEMENT REMOTE　 Cost = 2933
　SORT UNIQUE　
　　FILTER　　
　　　NESTED LOOPS　　
　　　　NESTED LOOPS　　
　　　　　HASH JOIN　　
　　　　　　TABLE ACCESS FULL テーブルA　
　　　　　　INDEX FAST FULL SCAN PK_テーブルB UNIQUE
　　　　　TABLE ACCESS FULL テーブルA　←　問題はここ
　　　　INDEX UNIQUE SCAN PK_テーブルB UNIQUE
　　　INDEX UNIQUE SCAN PK_テーブルB UNIQUE

【10g】
SELECT STATEMENT REMOTE Cost = 180
　SORT UNIQUE
　　TABLE ACCESS BY INDEX ROWID テーブルA TABLE
　　　NESTED LOOPS
　　　　NESTED LOOPS
　　　　　NESTED LOOPS ANTI
　　　　　　HASH JOIN
　　　　　　　TABLE ACCESS FULL テーブルA TABLE
　　　　　　　INDEX FULL SCAN PK_テーブルB INDEX (UNIQUE)
　　　　　　INDEX UNIQUE SCAN PK_テーブルB INDEX (UNIQUE)
　　　　　INDEX RANGE SCAN PK_テーブルB INDEX (UNIQUE)
　　　　INDEX RANGE SCAN テーブルA_IDX01 INDEX

NESTED LOOP JOINの中にテーブルAのFULL SCANが入っています。
これではコストが高くなって当然です。(テーブルAがかなり小さいなら別ですが）
疑問なのは10gで該当する部分が「INDEX UNIQUE SCAN PK_テーブルB INDEX (UNIQUE)」になっていてテーブル名も違います。
失礼ですがコピーする際にミスはありませんでしたでしょうか。
解決の方向としては、実行計画の「TABLE ACCESS FULL テーブルA」を「INDEX SCAN」に変えることです。
これ以上は実際のSQLと作成されているINDEXの情報がないと困難です。

情報の補足をお願いします。

この回答へのお礼

ご回答ありがとうございます。
そして、お礼が遅くなり大変失礼いたしました。

結果から申し上げますと、ご回答を受けて色々と修正を
試みたのですが、結局コストの改善はできませんでした。。。
また、スクリプトについてなのですが、少々ロジックを変えて
PL/SQLで作成してみたところ9iでも結果が取得できました。
作成期日が迫っていたスクリプトだったため、今回については
PL/SQLで対応することにいたしました。

せっかくご回答いただいたのに活かしきれずすみません。
それと、このSQLは仕事に関わるものなためこれ以上情報を
提供することはできそうにありません。。。

中途半端な状態で終わらせてしまい本当に申し訳ないのですが、
本件についてはこれでひと段落とさせていただきたいと思います。
丁寧にご対応くださったnora1962さんをBAに選ばせていただきます。
ご対応いただきありがとうございました。

お礼日時：2011/06/06 09:17

No.4 回答者： muyoshid 回答日時： 2011/05/22 10:17

こんにちわ。

> 9i環境と10g環境とではテーブルの構造やINDEXは同じですが
> データの中身は別で、件数は9iが100万件、10gが150万件です。
例え実行計画が同じでもデータが異なるのであれば性能に大きな
違いがあるのは普通です。

9i 環境で統計情報の再取得を行うと、実行計画が変化して性能が
向上する可能性があります。

この回答へのお礼

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

>例え実行計画が同じでもデータが異なるのであれば性能に大きな
>違いがあるのは普通です。

やっぱりそうですよね。。。

>9i 環境で統計情報の再取得を行うと、実行計画が変化して性能が
>向上する可能性があります。

これについては、9iでは統計情報の再取得を行った結果
更にコストがかかるようになってしまいました。。。

お礼日時：2011/05/23 06:45

No.3 回答者： nora1962 回答日時： 2011/05/22 10:11

9iと10gの実行計画出せますか。

（Explainなどでの）
ヒント句を用いて、実行計画を変更できる可能性はあります。

この回答へのお礼

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

>9iと10gの実行計画出せますか。（Explainなどでの）

テーブル名の部分だけ変更させていただきましたが、
それぞれ以下のような実行計画になっています。
（統計情報を取得し直したらコストが変わっていました）

【9i】
SELECT STATEMENT REMOTE Cost = 2933
SORT UNIQUE
FILTER
NESTED LOOPS
NESTED LOOPS
HASH JOIN
TABLE ACCESS FULL テーブルA
INDEX FAST FULL SCAN PK_テーブルB UNIQUE
TABLE ACCESS FULL テーブルA
INDEX UNIQUE SCAN PK_テーブルB UNIQUE
INDEX UNIQUE SCAN PK_テーブルB UNIQUE

【10g】
SELECT STATEMENT REMOTE Cost = 180
SORT UNIQUE
TABLE ACCESS BY INDEX ROWID テーブルA TABLE
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS ANTI
HASH JOIN
TABLE ACCESS FULL テーブルA TABLE
INDEX FULL SCAN PK_テーブルB INDEX (UNIQUE)
INDEX UNIQUE SCAN PK_テーブルB INDEX (UNIQUE)
INDEX RANGE SCAN PK_テーブルB INDEX (UNIQUE)
INDEX RANGE SCAN テーブルA_IDX01 INDEX

自分なりに分析・改良をしようとはしているのですが、
まだ勉強不足なため上手くいっていません。
何かアドバイスなどいただけると嬉しいです。

お礼日時：2011/05/23 06:37

No.2 回答者： testman199 回答日時： 2011/05/22 09:03

当たり前の話ですが

バージョンアップに伴いオプティマイザの改良も行われています。
同じコストベースとは言え、その結果が全く違うことは多々あります。
(PSRでも変更される場合があります。。。)

この回答へのお礼

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

>同じコストベースとは言え、その結果が全く違うことは多々あります。

そうだったんですね。。。
質問してばかりで申し訳ないのですが、『この実行計画で』
と実行計画を指定して実行させる方法はないのでしょうか。
SQL自体の改良も試みてはいるのですが、このままだと
9iの環境では結果が取得できそうにないので。。。

お礼日時：2011/05/22 09:36

No.1 回答者： SaKaKashi 回答日時： 2011/05/22 08:32

統計情報を確実に取得してますか？全件分の統計情報です。

9i以降標準はコストベースです。
実行計画は9iと10gでは違っても不思議ではないですけどね。

この回答へのお礼

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

>統計情報を確実に取得してますか？全件分の統計情報です。

ANALYZE TABLE テーブル名 COMPUTE STATISTICSで
統計情報を全検分取得してから実行しているので
問題ないと思うのですが。。。

お礼日時：2011/05/22 09:25