ORACLEデータベースの統計情報に注意！

今回は、ORACLEデータベースのパフォーマンスについての話です。
ちょっと専門用語も出てきます。

■データベースにも反抗期がある

　人の一生と、データベースの一生が似ている気がします。
　
　人は、生まれた日が誕生日です。
　データベースの誕生日は、本番運用開始日がそれにあたります。
　それぞれ、世に出た日が誕生日なのです。
　人は、赤ん坊のころは、泣き叫ぶことで、自分の要求を伝えます。
　データベースも同様。
　運用開始したころは、想定以上の負荷に対して、泣き叫ぶようにエラーを吐き続けます。
　時には、ハングして、親であるデータベースエンジニアをも泣かせます。
　運用開始後1年経つと、障害発生数は少なくなり、あまり手がかからなくなります。
　人だったら、小学生から中学生くらいでしょうか。
　もう色んなことが一人でできるようになり、自立への道を進んでいる時期です。
　自分の意見や主張も出てくる頃です。
　やがて、親に反発して、何もしゃべってくれなかったり、自分で決めたことを勝手にやるようになります。
　大人が嫌いになり、突然不機嫌になるということもあります。
　データベースも、同じように、データベースエンジニアを困らすような、分けのわからない不機嫌さを見せるようになります。

■わがままデータベース

　エラーは出さないのですが、突然パフォーマンスが落ちたり不安定になったり。
　運用開始直後の、分かりやすい障害ではなく、徐々に何かが悪くなっていくような、、、、分かり辛い障害。
　はっきりした兆候がログに記録されていればいいのですが、それも無くて、原因がつかみづらい、、、

　　親：データベースエンジニア
　　　「何か、言いたいことあるのか！？」
　　子：データベース
　　　「親父に言うことなんかねえよ！」

　ここで、人の子ならほっといて様子を見ることもありなのでしょうが、データベースの場合、機嫌よく動いてもらわないと
　いけないので、親父であるデータベースエンジニアが、意地でも何とかします。

■息子よ、おまえは何か隠しているだろう！？

　昨日まで、パフォーマンスが良かった機能が、今日は、パフォーマンスが悪くなり、何もしなくても次の日良くなった
　ということがありました。

　1)突然、不機嫌に
　　お客様「何か、この機能、いつもより遅いけど調べてくれない？」
　　という依頼を受けて、以下のようなことを調査しました。

　　　・アプリケーションが変わってないか
　　　・データベースのインデックスや、テーブル構成が変わっていないか
　　　・テーブルの統計情報は毎日取得されているか
　　　・テーブルのデータ件数は大きく変化していないか

　　上記については、特に問題ありませんでした。
　　データベースに対しては何も手を加えていないし、データも大きく変わっていないので、何で急にパフォーマンスが
　　落ちたのか原因を特定することが出来ませんでした。
　　特定の機能だけが遅いだけで、システム全体としては動作するので、とりあえず様子を見ることにしました。
　　
　2)翌日、上機嫌に
　　お客様「昨日、遅かった機能、パフォーマンス戻ったよ。」
　　との連絡を受けました。
　　こちらとしては、何もしていないのにパフォーマンスが戻って、良かったと言いたいところですが、腑に落ちない部分が
　　ありました。
　　とりあえず、再現待ちとして、また何か起これば、そのときはサポートにも問い合わせて調査しましょうということで
　　落ち着きました。
　　
　3)翌日、また不機嫌に
　　お客様「何で、また遅くなってるんだ？ちゃんと調査してくれ！」
　　今度は、データベースの内部情報を取得して調査することにしました。
　　・パフォーマンスが良かった昨日の状態
　　・パフォーマンスが悪い今日の状態
　　2つの状態を比較するため、昨日と今日(問題となった機能が使用された時間帯に絞って)のAWRレポート(※1)を取得、
　　比較しました。
　　
　　比較の結果、昨日は同じテーブルに対してインデックススキャンをしていたのに、今日はフルスキャンをしていました。
　　統計情報を毎日同じ時間に取得しているのに、何で、毎日実行計画が変わるのか不思議でなりません。
　　
　　(※1)AWRレポートとは、特定の時間帯に絞って、データベースにどういった待機イベントが発生していたか、
　　　　どんなSQLの負荷が高かったかを確認できるHTMLまたは、txt形式のファイル。

■SQLはどう解釈されるのか？

　統計情報とか、アクセスパスとか、色々と専門用語が出てきたので、ちょっと解説します。
　以下の「ORACLEが受け取ったSQLを解釈する」図を見てください。

　①端末から投げられたSQLは、ORACLEに受け渡されます。

　②ORACLEは、受け取ったSQLをオプティマイザという機能で解析します。
　　オプティマイザとは、ORACLEの脳みたいなものです。
　　投げられたSQLを解析して、テーブルの統計情報(※2)から、最適な実行計画(※3)を決定します。

　③実行計画を元にディスクからデータを取得して、端末に結果を返します。

　　(※2)統計情報とは、テーブルデータのばらつきや、件数などの情報のことです。
　　　　　テーブルごとに存在します。
　　(※3)実行計画とは、投げられたSQLと統計情報を元に、どういう風にテーブルからデータを取得するかといったORACLEの
　　　　動作についての計画書です。

■データベースが思っていること

　統計情報が、実際のデータと一致していない状態は、パフォーマンス劣化を引き起こす原因になります。
　オプティマイザが、統計情報を参考に実行計画を作成するからです。
　例として、インデックスを作成しているテーブルに対して統計情報を取得したとします。
　このテーブルが0件の時、統計情報を取得したら、ORACLEのオプティマイザはこのテーブルが100万件になっても、
　「0件のまんまだな」
　と、ずっと思っています。
　このテーブルに対して、インデックスを使用したSQLを実行しても、応答が遅いかもしれません。
　なぜなら、ORACLEのオプティマイザは
　「0件のまんまだな」
　と、今も思っているからです。
　0件だったら、インデックス使う必要無いと思い、フルスキャンでアクセスする可能性が高いです。

■統計情報取得時のデータ件数が問題だった

　問題となった機能で使用されているテーブルAは、ワークのような使い方をするテーブルでした。
　多いときは100万件、少ないときは0件という状態が不定期に発生します。
　パフォーマンスが悪かった日、テーブルAが統計状態を取得される時のデータ件数は0件でした。
　ちなみに、統計情報は毎日夜中2時に取得をします。
　0件の時に統計が取得されたテーブルAは、その日1日、日中に100万件のデータが入った状態でも0件の統計情報を使用して、
　実行計画を作成します。
　この場合、インデックスの項目をwhere句に指定したSQLは、有無を言わさず、100万件のデータに対してフルスキャンを行う
　ような動きをしてしまいます。

　そして、次の日、テーブルAは100万件のデータの状態で、統計情報の取得が行われました。
　その日一日パフォーマンスが回復しました。
　この場合、インデックスの項目をwhere句に指定したSQLは、ちゃんとインデックスが使用されたからです。
　ただ、その日は良くても次の日どうなるか分かりません。
　0件の状態で統計情報を取得される可能性があるからです。

■統計情報を取らないことにしました

　テーブルAのようなテーブル件数の増減が激しいワークのようなテーブルは、統計情報を取得しないことになりました。
　取得しないというよりは、一度、100万件の状態で取得して、その後は取得しないようにする設定を入れました。
　こうすることで、統計情報はずっと同じものを使い続けるので、毎日パフォーマンスが変化することがありません。
　毎日インデックスが使用されるはずです。
　本当なら、設計の段階でワークのようなテーブルを洗い出し、本番運用前にこういった統計情報の固定化対策を入れておくべき
　でした。