索引の再構築をすべき？

質問

とあるプロジェクトに放り込まれた新人ＳＥです。
当該ＰＪでは、客先にＤＢを構築して納入しているのですが、最近動作が遅くなってきたというクレームを耳にしました。調べてみると、特定のテーブルのSelect処理に時間がかかっているようです。問題のテーブルは、毎日数十万件のデータがInsertされ、一定の保持期限より古いデータは毎日削除されます。
問題はここからで、索引は設定されていますが、それをメンテしているという話を聞かないのです。
ちなみに、日付に対応するパーティションがあり、表・索引ともにパーティションで管理されています。毎日深夜に一番古いパーティションを削除し、翌日分のパーティションを新規作成するデーモンが動きます。
OracleSilver挑戦中の頭で一生懸命考えたのですが、納入から時間が経っているので索引が凄いことになっているのでは・・・という考えと、毎日新しいパーティションが生成されてるから大丈夫なのでは・・・という根拠の無い考えが渦巻いています。
アドバイス、お願いできますでしょうか。

回答No.3

k_o_r_o_c_h_a_n · ベストアンサー

>CBOでは実行計画の最適化には統計情報が使われるのですか・・・！
>ということは、定期的にANALYZEしないとボケた実行計画を選択してしまう、ということですか。
>（ちなみにCBO or RBOは少し調べて理解したのですが、どこかで明示されているんでしょうか？）

CBOは、原則として、統計情報が必要です。
統計情報にあるテーブル／インデックスの特性と実態が異なるようなケースでは、
統計情報が悪さをして、まともな実行計画が立案できません。
なので、テーブルの登録内容が大きく変わる場合は、統計情報の更新を行う必要が
あります。
ただし、統計情報が一度も採取されていないテーブルやインデックスについては、
CBOは、類推して、実行計画を算定します。
これが偶々良い方向で実行計画に作用する場合も無いとは限りません。
少なくとも、大嘘付きの統計情報を使うくらいなら、ない方がましかもしれません。

現在どのオプティマイザを使っているかは、初期化パラメータに
指定があるはずです。（デフォルトはCHOOSE）
特定のSQLのみルールベースにしたり、コストベースオプティマイザの種類を変更したり
というのは、オプティマイザヒントで行えるので、統計情報が信頼できず、
統計情報の更新タイミングが持てないような運用の場合、ヒントで切り抜けるのが
ラクな解決法だと思います。
少なくとも、他への影響がないので。

お礼

ありがとうございます。非常に良く理解できました。
#4の方の仰るように、いきなりアナライザを叩くのは危ないと考え、まずは下地となる情報収集から始めてみたいと思います。
（SQLトレース程度なら大丈夫かな、とか・・・それも有識者に確認してみます）

回答No.5

MZ-80B

#4 です

調査する方法を書いていなかったので補足です。

まず、SQLPLUS の show parameter opt コマンドで初期化パラメータや
usert_tables , user_indexes, index_stats などのディクショナリの内容と意味あたりから調べていくと良いです
統計情報についても判断できます。

件数が非常に多いみたいですから、テスト機で実験するなど
しっかりと事前準備と作業をしたほうがよいです。

回答No.4

MZ-80B

データの規模を考えると事態を悪化させる危険性があるので
理解の乏しい場合には統計情報をとるのはやめたほうがよいです。(信用を無くします）

というよりもデータベースの設計担当者が不在、
さらにオラクルを良く理解していない状態なのに
最近動作が遅くなってきたというクレームがある状態で
オプティマイザを刺激する危険なことは絶対にしてはいけません。

オラクルを良く理解している人と現状環境を良く調べてください。

お礼

ありがとうございます。さらにアナライザ、オプティマイザについて調べてみました。
なるほど・・・これはデータベースの基幹を揺るがすことにつながるんですね。
下手に動かして、索引が無効になったりしたらシャレにならないので・・・まずは現状がどうであるかをまとめたうえで、技術担当窓口と相談して方針を決めたいと思います。
Gold持っている人が辞めたあと、SilverFellowしか居ないというのも問題なので、そこらへんも突っ込んでみます。

回答No.2

k_o_r_o_c_h_a_n

#1どの
＞グローバル索引であれば再作成も視野に入れる必要があるでしょうが、

グローバル索引付きのパーティションは、パーティション単位でのDROPを
行うと、索引が使用不能になるはずです。よって、グローバル索引を使っているなら
必然的に索引の再作成が行われているはず・・です。（索引が使用可であればですけど）

本題ですが・・

パーティションのCREATE/DROPを行いつつ運用するような考え方のテーブルであれば
索引は、それなりに維持されていると思いますよ。
むしろ、コストベースの運用の場合、統計情報の更新が問題になるかと思います。

とりあえず、現時点での実行計画を出して、正しく索引が使われているかを
確認してください。
コストベースであれば、統計情報を適宜更新するか、オプティマイザヒントを
使って、まともな実行計画を立案させることになると思います。

お礼

ありがとうございます。
CBOでは実行計画の最適化には統計情報が使われるのですか・・・！
ということは、定期的にANALYZEしないとボケた実行計画を選択してしまう、ということですか。
（ちなみにCBO or RBOは少し調べて理解したのですが、どこかで明示されているんでしょうか？）

索引に関しては、元々そういう設計思想だったのかもしれませんね。
実は設計者が退職してしまって、わかる人がいないのです・・・。
いずれにしろ、一度上に許可をもらって統計情報をとってみたいと思います。
どんなパスがあって、どの情報をもとにどれを選択しているのか、なんてSilver挑戦中の人間は見てもさっぱりわからないと思いますが、可能であれば挑戦してみたいです。

回答No.1

entree

設定されている索引がグローバル索引かローカル索引カニもよるでしょう。グローバル索引であれば再作成も視野に入れる必要があるでしょうが、ローカル索引の場合は毎日パーティションの入れ替えを行っているというのであれば、そのときに再作成されているはずなので特にメンテナンスの必要はないかもしれません。

索引を疑うのであれば、まずは問題となっている索引の統計情報を取得してみて、その索引のかさ高さをチェックされると良いでしょう。一般的に、かさ高さが４以上であれば「問題あり」と言われています。

Oracle 9i 以降であれば、若干（？）負荷はかかるでしょうが、

ALTER INDEX index_name REBUILD ONLINE;

でオンラインでの索引再作成ができると思います。(Enterprise Edition のみでのサポートですが、パーティショニング・オプションを使われているということは Enterprise Edition なんでしょう)

その他の要素をチェックしたいのであれば、statspack をインストールしてバッチ処理の中に組み込んでおくことで、ボトルネックとなっている処理や原因が分かるかもしれません (Oracle Master Gold のパフォーマンス・チューニングの範囲になりますが)。

お礼

索引にも種類があるのですね。サーバ内にCreate文が残っていますので、あとで参照してみます。
アナライズ文を発行する方法もあるようですので、結果の見方を覚えて必要そうであればやってみたいと思います。
（もし再構築中は索引が利用できないとかだと、運用的に問題があるかもしれませんが・・・）
ありがとうございました。週明けにでも試してみます。