処理経路の変化(3)

じゃあ、どうすればいいのか？

前回までの話だとそういう風に思われる方々がほとんどじゃないでしょうか。

実際僕の経験上、この処理経路(実行計画)が変化し、パフォーマンスが劣化した場合にエンドユーザさんに激怒し、開発者や運用管理者の方もなぜこんなことになったのかと、われわれDBエンジニアに食いかかってくる場合があります。

そんな人を説得する一つの例が、

SELECT * FROM TEST

WHERE COL1 = 'A' AND COL2 = 100

/

上記SQLで”何件データが返されるか”わかりますか？

と聞いてみることです。

「わかりますよ、だいたいXX件ぐらいなはずです」と返答された場合は、次にこう聞き返します。

「じゃあ、COL1かCOL2、どちらの索引を使うのが効率的ですか？それとも表走査が最適ですか？」

ここまでで答えれる場合は、最後にこう説得します。

「経路がわかっているのなら、なぜその経路にいくようにヒント句(*)を埋め込まなかったのですか？」

(*)実行計画を特定の経路に誘導するためにSQLに記述する文言の事。これをSQL文に埋め込むことで、多くのRDBMSにおいて意図した実行計画へ誘導することができます

ちなみに上記のような回答を得られるケースは非常に少ないです。また得られたとしても、ウソかハッタリのケースが多いような気がします。

正直に言えば、上記SQLで何件が返されるかを正確に言える人は、よほどその業務に精通している方でないといないはずです。必然的にほとんどのケースは、「わからない」という回答になるわけです。

この場合、まずはデータ取得経路についてアツク説明をする必要があります。これは面倒な作業ではありますが、しかしここを理解しあわない限りは、永遠にオプティマイザとは分かり合えないので仕方がありません。

統計情報を収集し、その上で実行計画が悪くなった場合、

　・統計情報収集方法が悪い

　・ギリギリの判断で悪い方が選ばれた

のとぢらかであると思われます。

いずれにせよオプティマイザに全てを委ねる以上は、実行計画は良くなるかもしれないし、悪くなるかもしれないわけです。

個人的にはオプティマイザを99%信用していますが、残り1%は信用していません。経験上、必ずオプティマイザが最適ではない実行計画を選んでしまう事があることを知っているからです。

ですからオプティマイザに全てを委ねる場合は、最低でも以下の点だけは意識して運用をする必要があります。

　1.統計情報のバックアップを取得しておく

　2.実行計画を常に取得しておく

2はそういったツールを使わないと難しいですが、1はさほど難しくないでしょう。何かあれば、統計情報自体を昔の良かった頃に戻せば、元の実行計画に戻るからです。

この運用に満足できない方は、オプティマイザに全てを委ねるのではなく、全SQLにヒント句を入れるしかないのです。

少し厳しい意見もあったかと思いますが、これは現状のDBシステムの一つの真実なんじゃないかと思います。

処理経路の変化(2)

最適な経路（アクセス・パス、実行計画）って何でしょうか？

この問いに対する答えは非常に奥深いものがありますが、単純化すると

その経路でデータを取得した際にどの程度のI/Oが発生し得るかを予め計算し、最もI/O量の少ないと予測される経路

が一つの答えとして上げられます。

（違う考え方もあります。例えばバッチ処理等では単純に上記だけでは最適とは言えません）

例えばORACLEというソフトウェアのオプティマイザ機能は、コスト・ベース・オプティマイザ（略してCBO）と呼ばれますが、これなんかはその典型で、コスト（予想されるI/O量）が最も少ない経路でデータを取得しようとします。

そしてそのI/O見積もりで使用されるのが、前回お話した「統計情報」なのです。

ここで結論を先に言ってしまうと、DBシステムにおいて最適な実行計画を得るために考え得る運用方針は、

　・オプティマイザに完全に委ねる

　・自分（管理者、開発者）で決めた経路で常に処理をさせるように指示する

のどちらかしかありません。

もっと辛辣な言い方をすると、前者は最適な実行計画を判断出来ないおバカな管理者、開発者の方 (*) が、後者は最適な実行計画を判断出来る管理者、開発者の方が、採用することになります。

処理経路が変わる、つまり実行計画が変化することによって発生するパフォーマンス劣化は全て、前者の運用方針を採用している場合に発生します。

（ちなみに後者の場合にパフォーマンス劣化が起きた場合は、経路が変わったからでなく、経路そのものが当初と比べて最適ではなくなったから、です。このケースは突然に遅くなる、という事象には一般的になりにくく、徐々に遅くなっていきますので日々監視していれば問題を特定し、時間をもって対処することができます。）

(*) 超高度な判断としてオプティマイザに完全に委ねる選択肢もあります。この場合、コスト計算に対する係数を用いての調整が必須となります

さて、話を本題に戻します。

なぜオプティマイザに委ねてしまうと、実行計画が変わってしまうのか。

その答えは前回軽く触れた統計情報 、が原因です。

オプティマイザに全てを委ねる場合、オプティマイザに対してこれから走査しようとする表や索引の情報を正確に教える必要があります。この情報は常に最新のものである必要があります。

このため一般的には毎晩、週に1度といったタイミングで表や索引の統計情報を収集する運用をすることになります。

オプティマイザはこの統計情報をもとにして、実行計画を生成します。

ですから、

統計情報が変われば、実行計画が（良くも悪くも）変化する可能性がある

ということです。

この点を管理者、開発者の方はよく認識する必要があると思います。

処理経路が変化するのは、皆さんの知識がなかったからかもしれない、ということなのですから。

ちなみにORACLEを含め各RDBMSソフトウェアは、オプティマイザに全てを判断させる方向でソフトウェアを進化させるようになってきています。恐らく、実行計画を意識出来る管理者、開発者の方が減ってきているからでしょう。

と同時に、実行計画を完全に自分達の思い通りにしたい、という方々もその裏側で多くいらっしゃいます。

この論争はしばらく続くかと思いますが、現状ではどちらの言い分を満たすようなところでオチが着いて進化は止まるんじゃないかと予想しています。

続く

セッションが・・・

最近、セッションがハングしたり、プロセスが落ちたりと不可解な事象によく遭遇します。

何かに取り付かれてしまったんでしょうか？

この辺のトラブル対応についても、後日まとめたいなと思っています。

Windowsだと exeにまとめられてしまうのでなかなか調査が難しいですが、UnixやLinux系であればプロセスのトレースを取るコマンドが用意されているので、原因追求が何とかなったりするものですね。

まぁいずれにしても、定常的な監視が大事、ってことでしょうか。