こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

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

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

投稿日時 - 2011-05-22 05:16:41

QNo.6754508

困ってます

質問者が選んだベストアンサー

見やすいように、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の情報がないと困難です。

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

投稿日時 - 2011-05-23 10:25:02

お礼

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

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

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

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

投稿日時 - 2011-06-06 09:17:42

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.4

こんにちわ。

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

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

投稿日時 - 2011-05-22 10:17:54

お礼

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

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

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

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

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

投稿日時 - 2011-05-23 06:45:31

ANo.3

9iと10gの実行計画出せますか。(Explainなどでの)
ヒント句を用いて、実行計画を変更できる可能性はあります。

投稿日時 - 2011-05-22 10:11:10

お礼

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

>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:20

ANo.2

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

投稿日時 - 2011-05-22 09:03:19

お礼

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

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

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

投稿日時 - 2011-05-22 09:36:58

ANo.1

統計情報を確実に取得してますか?全件分の統計情報です。
9i以降標準はコストベースです。
実行計画は9iと10gでは違っても不思議ではないですけどね。

投稿日時 - 2011-05-22 08:32:18

お礼

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

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

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

投稿日時 - 2011-05-22 09:25:10

あなたにオススメの質問