SQE (SQL Query Engine)

Information Center からコピーしてきた SQE のアーキテクチャー図です。

SQL の実行形態

SQL には「静的」「動的」「拡張動的」の三つの種類があります。

「静的」SQL というのは、いわゆる埋め込み SQL などとも言われるもので、プログラムのソースの中にもう書かれていて、CRTSQLRPG/CRTSQLRPGI コマンドなどで作成されるものです。こうした埋め込み SQL 用のコマンドで、あらかじめ SQL を実行させるためのコンパイルを行ってしまうわけです。アクセス・プランが生成され、プログラムの一部として保管されます。こちらで見た最初の方の例のものですね。

たとえば Java のプログラムの中に SQL が埋め込んで書いてあったとしても、これは CRTSQLxxx のようなコマンドで事前にコンパイルを行うわけではないので、こうしたものは「静的」SQLとはいいません。文字列として SQL が飛んできて、それをその時その時でコンパイルしていくようなかたちになるため、「動的」SQL と言います。

「拡張動的」というのは DB2/400 独特のもので、上記の「動的」SQL で一度作成されたアクセス・プランなどを "SQL パッケージ" というものに保管して再利用できるようにしたものを言います。

アクセス・プランの共有

SQL パッケージの歴史的位置付け

アクセス・プランの作成は、SQL の解釈/最適化/実行 というフェイズの中の「最適化」というフェイズの中で一番時間のかかる処理になります。(「解釈」「最適化」「実行」の中で何が時間がかかるってそりゃあ「実行」ですから、「最適化」にかかる時間がちょっと増えても全体としてはあまり大したことにはならないのかもしれませんけどね。「最適化」にかかる時間は Visual Explain 等から知ることが出来ます。通常数ms〜数十、数百ms、といったところです)
この「最適化」フェイズで再利用可能な"成果物"としては、このアクセス・プランくらいしかありません。そこで、まずはこのアクセス・プランの再利用のための方策がいくつか採られるようになりました。
歴史的にいうと、その第一弾として上述の「拡張動的」SQL = SQL パッケージが導入されたわけです。

「動的」の場合でも、同じ SQL が同じジョブの中で実行されれば再利用されるようになっています。(「ジョブ・キャッシュ」というものがあります) このキャッシュの概念を「拡張」して、SQL パッケージを共有するジョブの間でアクセス・プランを再利用できるようにする、というのがこの「拡張動的」の特徴です。「SQL パッケージ」というオブジェクトになりますので、保管/復元が可能になっています。つまりシステムをまたがって再利用できる、ということにもなるわけですね。

ただし、この SQL パッケージというものは、それを利用すると宣言したジョブでのみ使用できる仕様になっています。
また、オブジェクトであるため、ロックの問題やサイズの上限など、いろいろな実使用していく上での問題点がありました。

ジョブ・キャッシュ/プラン・キャッシュ/システムワイド・ステートメント・キャッシュ

そこで、どうせだったらデータベースエンジンの中で、しかも静的だとか動的だとかはたまた拡張動的だとかの SQL のインターフェイスにかかわらずに、こうしたアクセス・プランのようなものは共有できないものかということで、V5R2 以降の SQE と言われる新しいエンジンではプラン・キャッシュというものが導入されました。名前のとおり、アクセス・プランがキャッシュされます。

Information Center からコピーしてきた プラン・キャッシュの概念図です。

プラン・キャッシュは、SQL プログラム、SQL パッケージ、およびサービス・プログラムに加えて、システム全体のステートメント・キャッシュと密接に協働して機能します。プラン・キャッシュは全体サイズが 25600 万バイトで作成されており、ほぼ 250 メガバイト (MB) となります。プラン・キャッシュには、最適化された照会アクセス・プランとともに元の照会も入っています。 照会とともに格納されている他のオブジェクトは、生成された照会ランタイム・オブジェクトだけでなく、照会ランタイム情報 (アクセス・プランの使用情報も格納する) も含んでいます。

「システム全体のステートメント・キャッシュ」とはいわゆる SWSC のことですね。

上の図を見ると、元々 CQE から来たものは、例えば PGM の中、SWSC、SQL パッケージの中に SQL ステートメントとアクセス・プランがセットになって入っていたように見えますが、この SQE のプラン・キャッシュ・モデルではこれが分かれて、ステートメントについては元のとおりで、アクセス・プランのみがプラン・キャッシュに蓄積されるようになっています。

このパラグラフのタイトルに"ジョブ・キャッシュ"というものを含めましたが、ジョブ自身もキャッシュを持っており、それをジョブ・キャッシュといいます。アクセス・プランもキャッシュされますので、このジョブ・キャッシュにヒットすれば一番早いわけです。後述する ODP もそうですが、JDBC サーバージョブである QZDASOINIT のようなケースでは正直あまりあてにはできないような気はします。

アクセス・プランには SQL 文そのもの、実行される環境の情報、その SQL に対応した最適なアクセス方法、といったものが含まれます。プラン・キャッシュにはそうしたものが"最適化"された形で格納されている、といくつかの資料には書いてあります。

SWSC は「動的」SQL による PREPARE ステートメントのみがキャッシュされ、プラン・キャッシュは SQE によってのみ使用されるので、それぞれカバーする範囲が微妙に異なっていることに注意が必要です。
どっちも一杯になってしまえばそれなりに問題を起こします。あんまり一杯になってしまうというケースは多くないかもしれませんが ...... SWSC は 16MB 単位で作成され、1000個になったらそれ以上作成されない、という仕様らしいので、サイズのコントロールはできないということのようです。

また、SWSC は以下の QAQQINI の設定で、(動的 SQL にとって) 使用するかしないかをセットすることができますが、SQE にとってプラン・キャッシュを使用しない、というオプションはありません。

SYSTEM_SQL_STATEMENT_
CACHE

*DEFAULT デフォルト値は、*YES に設定されます。
*YES SQL 作成要求が処理されるとき、システム規模の SQL ステートメント・キャッシュを調べます。一致するステートメントがすでにキャッシュに存在する場合は、その作成の結果を使用します。これにより、アプリケーションの実行する作成のパフォーマンスが向上する可能性があります。
*NO SQL 作成要求が処理されるとき、システム規模の SQL ステートメント・キャッシュを調べません。

インフォメーション・センターの記述にもあるように、「システム全体のステートメント・キャッシュと密接に協働して機能」するわけですし、実際にアクセス・プランおよび関連されるオブジェクトが格納されるのはどちらのインターフェイスからでも同じ場所 (SQE の場合は、ですね。SWSC は CQE に対しても機能するので) であるように書いてある資料も見た記憶があります。

アクセス・プランのキャッシュ・ヒット

CQE の場合は (ジョブ・キャッシュ以外には) PGM に格納されているプラン (静的 SQL)、SQL パッケージ (拡張動的 SQL)、SWSC (動的 SQL) しかキャッシュさせる場所はないわけで、このいずれかでヒットしない場合にアクセス・プランの再作成 (再最適化) が行われるわけです。

SQE の場合、PGM に格納されているプランと SQL パッケージ、SWSC 以外にも、ジョブ・キャッシュとプラン・キャッシュを使用できる、ということになります。と言うか、前述のようにアクセス・プランを双方にダブって持っているわけではなく、アクセス・プランとそれ以外のものに分かれていて、"それ以外のもの"はそれぞれの場所で持っていて、アクセス・プランはプラン・キャッシュで一元管理しています。逆にいろんなところでダブって持たないだけ効率的で、それ故ひとつの SQL について環境の違うアクセス・プランを複数 (たしか 3つ) 持つことができるようになっている、といった余裕のあるかたちになっているのかもしれません。たしかにシステム全体としては効率的ですよね。
いずれにせよ SQE の場合、要するにジョブ・キャッシュかプラン・キャッシュにあればいいようになっています。つまり、プラン・キャッシュのサイズをそれなりに大きくすればプランの再最適化で使用されるシステム資源 (主に CPU とメモリ、でしょうね) は相当削減できる、ということも言えそうです。V5R4 からはこのサイズと内容をモニターできるようになっています。(V5R2/V5R3 にはプラン・キャッシュに対してまともな[ちゃんとマニュアルに載っている]インターフェイスは存在しません)

ODP の再利用

「実行」フェイズで再利用できるものでなおかつ重たい処理は「オープン・データ・パスの作成」です。これはジョブ構造の一部なので、ジョブの中でしか再利用できません。もちろん、ジョブの中で再利用するのとしないのでは CPU にかける負荷が大違いになりますので、これが再利用できるような工夫はやはり必要になります。

DB2/400 の解説記事などでは Optimize/Open/Run というフェイズの区分けになっていることが多く、チューニング・ポイントという観点からは確かにこちらの区分けの方が実践的です。

理論的に言う、解析/最適化/実行という区分けを考えてみましょう。

SQL 文の解釈・解析も、コスト・ベースのオプティマイザの場合は対象テーブルの行数やインデックスの有無を勘案して、結果としてはアクセス・プランを作成するフェイズ、とまとめられるわけです。

次に、実行というフェイズですが、これはプログラムの実行と同様の処理になります。
昔からデータベースファイルのオープンというのは資源も時間も必要な処理である、とされており、そのために ODP の共有などといった仕組みがあるわけです。順次処理を基調とする RPG の場合、うっかり共有すると読み始めの位置が違ってしまい、それを考慮していないコーディングだと問題が起きるわけですが、集合操作である SQL の場合はそんな問題は起こりません。ですから、はじめから ODP は共有されるようになっています。

JDBC アクセスの場合、QZDASOINIT というジョブの中でしか ODP は共有できないので、この場当たり的にアサインされるジョブの中で ODP の再利用をヒットさせるのはなんだかあんまり沢山ありそうもない気もしないでもないですね。

いずれにせよ、ODP の作成というのはシステムにとってコストのかかる処理であり、再利用によって減らすことができればそれにこしたことはないわけです。なので、ODP の再利用を阻害する要因はできるだけ取り除いておく、というのが設計/チューニングのひとつのポイントには確かになります。
実際のところ、パフォーマンスが問題になってくるような事後ではほぼ対応できないものがほとんどなので、DB/アプリケーション設計時にそういった考慮点についてはきちっと考えておくべきでしょう。

それぞれのフェイズでどのくらい時間がかかっているかは個々の SQL によって違いますが、個別に Visual Explain の出力を見ることによってわかります。

【こんなあたりが参考資料でしょうか】

http://www.e-bellnet.com/special/tec/tec_0308.html
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/ic2962/info/rzajq/rzajqmst.pdf
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/ic2924/info/rzajq/rzajqmst.pdf
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzajq/rzajq.pdf

[Top Pageに戻る]

Ads by TOK2