SQLチーニング

本日、技術ネタなのでわからない人はごめんなさい。

きょうは、摩訶不思議な状態に陥った。
SQLにて、外部結合の記述方法に「より、恐ろしいほど検索時間が違う結果が出た。

(外部結合の方法)
SELECT
  *
FROM
  TABLE1,
  TABLE2
WHERE
  TABLE1.KOOUMOKU = TABLE2.KOOUMOKU(+)

(JOINT文の記述)
SELECT
  *
FROM
  TABLE1 LEFT OUTER JOIN TABLE2
   ON TABLE1.KOOUMOKU = TABLE2.KOOUMOKU

私は、上記の分両方とも同じ動きをするものだと思っている。
本当は、上記のような簡単な結合ではなく、5テーブルほど結合されているが、基本は上の通り。

なぜか、(外部結合の方法)では一瞬で検索結果が返ってくるのに、(JOINT文の記述)では、恐ろしく時間がかかる。
テーブルには、約60万件のデータが入っているので、ちょっとした記述ミスで遅くなることはあると思うが、何が違うのか良くわからない。
もちろん、結合する項目には、すべてINDEXが張ってある。

実は、(JOINT文の記述)の方で検索条件を変えたら、検索結果がすぐ戻ってきた。
違いは、検索条件を「Like」で検索するか「=」で検索するかの差だった。
ちなみに、(外部結合の方法)で「Like」検索しても、スピードが遅くなることはないんです。

これだけの情報で回答するのは難しいでしょうが、何か心当たりのある方は、返信お願いします。
私が知りたいのは、(外部結合の方法)と(JOINT文の記述)で論理的に同じ記述をした場合、データベース側(今回の場合はOracle)が内部でどのような処理を行っているか(たぶん、外部結合の場合Where句に結合条件を記述するので、Oracle側で条件絞り込んでから結合しているのではと読んでいますが...)が知りたいと思っています。

まあ、自分で実行計画を問い合わせして調べろとといわれればそれまでなのですが...
遅い時間に発生したもので、ろくに調べずに帰ってきてしまいました。

ちなみに、現在SQLで時間がかかるものについては、なぜかわからないが、(JOINT文の記述)を(外部結合の方法)に変更すると、スピードが上がるので、とりあえずそのような対処をしている始末です。
2005/02/24(Thu) 23:25:33 | お仕事

落としどころ

今は、年度末リリースできつい状態なので、合間に考えるだけでそれほど急いでいません。
少し落ち着いてから、また本格的に調べてみたいと思っています。
その間、時間があるときにチョコチョコ調べてみますよ。

それと、昨日みたDBはテスト用であったため、実機でのテーブルを確認したところ、Analyzeされていませんでした。
連絡が遅くなってすみません。

なぜテスト環境だけAnalyzeされていたのか不思議ですが、現在現象が出ているのが実機ですので、現在はAnalyzeに関しては度外視して調査しています。
otata(2005/03/01(Tue) 18:04:13)

ほっほっほっ

最後に1つ

チューニングはやり出すときりがないです。

落としどころが重要です。
最初にどこまでやるか決めてからの方が良いです。

はまらないように・・・
た○し(2005/03/01(Tue) 14:19:58)

Re:Re:Re2:なるほどね...

一部の表に Analyze ですか・・・

SELECT --+ RULE
column1, column2
FROM TABLE1;

とするとルールベースを明示的に指定できますので
 付けた時と
 付けない時と
で実行計画が変わるかどうか確認してみてください。

また、表の Analyze ならこの確認方法で良いと思います。

ちなみにどう効くかはやっぱり実行計画をみないと分かりません。

実のところ Analyze は昔ちょこっと試しただけなのであまり詳しく
ありません。

それに途中でコストベースよりルールベースで要所にヒント文を
入れた方が確実だと判断したためつっこんだ調査はしていません。

Oracle のバージョンによってもコストベースの実行計画は違うと
思うので選択の方法としては

まず

ルールベース(ヒント文とかはなし)
コストベース(ヒント文とかはなし)

で性能を比較

スポット的にヒント文で対応が可能な部分を洗い出す

チューニングの人的コストと性能を総合的に判断する

となるのではないかと思います。

#この過程でインデックスの見直し等もあり得ます

検索の効率はデータの状態にもよるのでコストベースで問題なけれ
ばその方が良いのかもしれませんが・・・

でも、ルールベースに1票です・・・
た○し(2005/03/01(Tue) 12:59:28)

Re:Re2:なるほどね...

う〜ん...
「コストベース」「ルールベース」が混在しているようだ。
更新が多発するテーブルが「コストベース」になっているようです。
どのような意図があって、このような構成になったか、再度確認してみます。

なお、残念ながら9iR2で、10gではないので、いけてないかもしれない。
調査結果が出ましたら、また書き込みます。

(確認のために実行したSQL)
select
 table_name テーブル,
 num_rows レコード数,
 avg_row_len 平均レコード長,
 blocks 使用ブロック数
from
 user_tables
※この検索結果、「レコード長」「平均レコード長」「使用ブロック数」の情報が存在するものが、統計情報を確保されているテーブルで、コストベースで実行計画を立てているということで間違いないと思いますが...もし間違いなら、ご指摘を...
otata(2005/02/28(Mon) 18:19:49)

Re:Re2:なるほどね...

Analyze を
 していると「コストベース」
 していないと「ルールベース」
となります。

Analyze とはデータの統計情報を収集して保持するもので
これを元に Oracle が最適な実行計画を導き出すというものです。

しか〜し!

個人的には良い思いをしたことがないです。
逆に検索が遅くなることもあります。

もっとも Analyze を試したのは昔のことなので 10g ならイケてる
かも?

ちなみにた○しは次のプロジェクトで初めて 10g 使います。

10g もこなれてきたそうで・・・
た○し(2005/02/28(Mon) 15:31:06)

Re2:なるほどね...

ひとつ質問。
「コストベースのオプティマイザーを使用している」というのは、具体的にどのようなことでしょうか?
横文字は苦手なもので、ご教授お願いします。

なお、恥ずかしい話ですが、今回の回答で「ヒント文」や「ビットマップインデックス」は初めて知ったことでしたので、勉強になります。
今後も、たまに調査に時間がかかっても大丈夫なものであれば、こちらに書き込みますので、ご教授お願いします。

>
>た○し = す○○
>果たして本当にそうなのでしょうか?
>たにし
>たわし
>たかし
>たけし
>・・・
>私にも分かりません。
>
物理的には、本人でしょう。
(間違いない!![もう死語])
多重人格のもう一人か?
otata(2005/02/27(Sun) 07:24:56)

Re:なるほどね...

1つ重要なことを書いていませんでした。

前回のコメントは全てルールベースのオプティマイザーを想定したものです。
コストベースのオプティマイザーを使用している場合はこの限りではありません
ので確認をしてください。

た○し = す○○

果たして本当にそうなのでしょうか?

たにし
たわし
たかし
たけし
・・・

私にも分かりません。
た○し(2005/02/26(Sat) 11:07:56)

なるほどね...

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

一応、チューニングとしての対応方法は、すでにプロジェクト内で打ち出しており、対応終了していますが、下記の件に関して、どうしてスピードの差が発生するのかは、まだ原因がつかめておりません。出向先の上司が納得していないようで、納得できる材料を探しています。
(どうも、こっちの気分が悪い...)

現在、調査する時間がとれないため、後回しの作業になっていますが、下記の回答を参考にして調査をして見ます。

私も考え的には「2.」のことが関係していると思っています。
これまではFrom句の結合を先に行い、その結果からWhere句の条件で絞り込むという順序で処理を行っていると思っていたのですが、今回の件でどうも、Where句の条件の順序で絞込み作業の順序が違っているという感じがしましたので、何とか裏を取りたいと思って意見を集いました。

今後、現在のインデックス設定から見直し、その上でパフォーマンスがあがらない場合は、ヒント文を用いて検討してみます。

本日、実行計画も見てみましたが、始めての事でよくわからなかったので、今後勉強しながら調べます。

その辺の結果が出たら、また書き込みます。
参考になりました。ありがとうございました。
(と、言うのは早いと思いますので、結果報告はちゃんとします。)

それと、これまで「た○し」が誰なのか、いまいち確信を持てませんでしたが、今回の回答ではっきりしました。
ありがとう、「す○○」くん。
otata(2005/02/26(Sat) 00:39:37)

Re:SQLチーニング

実行計画を確認してみないと何とも言えませんが

結合方法(記述)によって

1.駆動表が変わっている
2.インデックスの評価順序が変わっている
  もしくは効いていない
3.インデックスの Marge が発生している

等が考えられます。

1.複数表を JOIN する場合その駆動表がパフォーマンスに影響します。
  表毎のレコード数の差が大きいと特に顕著に現れます。
  よりレコード数の少ない表が駆動表になるようにします。
  でもこの場合書き順変えても TABLE1 が駆動表になるかな?
2.この場合 Where 句に指定した条件(A.xxxx like 'abc%' 等)よりも JOIN が
  優先されている可能性があります。
  これはヒント文を指定することにより回避できると思います。
  単一表に対する Select でももっとも効率が良いインデックスを明示的に指定
  することでパフォーマンス向上が望めます。
3.これは一概にパフォーマンスダウンに繋がるとは言えませんが複数のインデッ
  クスを Marge するより効率の良いインデックスを1つ使用する方が安定した
  パフォーマンスを確保することが容易になります。
  こちらもヒント文を指定することで明示的にインデックスを指定します。

でもって今回は多分2が原因ではないかと・・・
実際には5つの表とのことなので1も疑ってみてください。

ちなみにインデックスはあれば良いというものでもありません。
効率が悪く利用頻度の低いインデックスは削除した方が安定したパフォーマンスを
確保できる場合もあります。
JOIN に使用しているカラム以外でこのようなインデックスが存在するかチェックし
てみるのも良いかもしれません。

また、値の分布が偏っている場合(性別等)などは B*Tree ではなくビットマップ
インデックスを使用することで検索のパフォーマンス向上が望めます。
しかし、ビットマップインデックスは更新の負荷が高いようなので頻繁に更新が発
する表には向いていないと思います。

とりあえず思いつくことを書いてみました。

ご参考まで・・・
た○し(2005/02/25(Fri) 14:56:19)
Tittle: Name:

Profile

otata
otata
バレーボール、エアロビクス、マラソンが趣味の親父です。
良かったら、一緒にエアロビましょう!!

Categories

New Entries

Comment

Search

Link

Blogパーツ

OtataShopping
ブログ内の紹介商品購入はこちらから!
Music Stream
最近見ないけど「Perfume」いいよね〜
選曲するか「一時停止→再生」で音が出ます。
ミサイル発射!!

Archives(490)