はじめに
Db2で実行されたSQLのアクセス・プランの解析をすることが多いのですが、これまでは、SQLをファイルに記述して、アクセス・プランを取得して解析をしていました。
しかし、最近、モニター・ビューから負荷の高いSQLを検出して、パッケージ・キャッシュから アクセス・プランを取得するのが、場合によっては便利なので手順としてまとめておきたいと思います。
ご参考になれば幸いです。
アクセス・プランとは
リレーショナルデータベースは、アプリケーションがデータベースシステムからデータ抽出を 要求するとき、具体的な実行の手順ではなく、SQLと呼ばれるデータ抽出用の非手続き言語を経由します。
SQLでは、「？に一致する行を抽出する」、「ある列を取り出す」などの 何を(What)に関する内容を記述しますが、 「索引Aを使って行を抽出する」、「表を直接抽出する」などの、どのように(How)の部分は記述しません。
このため、リレーショナルデータベースでは、SQLから具体的な手順に変換するための機能があります。SQL・表・索引の情報から最も効率的な具体的な手順に変換するため、この機能をオプ ティマイザーと呼び、SQLの具体的な手順をアクセス・プラン(または実行計画)といいます。
通常はオプティマイザーにしたがって、SQLが効率的に実行されています。
しかし、「適切な索引が作成されていない」、「表・索引の情報が不十分」、「SQL書き方が適 切でない」等により、処理時間が長かったり、CPUの使用率の高いSQLが実行されたりする場合があります。
そのため、アクセス・プランの情報から、オプティマイザーがどのように判具体的な処理に変換したことを分析して、適切な対応を取る必要が出てきます。
アプセス・プラン取得の手順
Db2でアクセス・プランを取得するまでの手順は、以下のようになります。
- EXPLAIN表を準備する（事前準備）
- SQLのアクセス・プランをEXPLAIN表に書き出す
- EXPLAIN表からアクセス・プラン情報をテキスト形式で取り出す
2の作業でEXPLAIN表に一旦アクセス・プラン情報を書き込んで、3の作業で取り出すことになるので、シェルなどを利用して、 一度に実行できるようにするのが作業が効率化できると思います。
EXPLAIN表の準備
Db2のアクセス・プランを詳細に取得するためには、EXPLAIN表 と呼ばれる複数の表を事前に作 成をしておく必要があります。EXPLAIN表といっても表1個ではなく、複数表から構成されています。
EXPLAIN表は、以下の2種類のスキーマのいずれかに作成することができます。 Db2 11.5以降であれば、SYSTOOLSスキーマ上に作成すれば、全ての実行ユーザーで利用ができますので、 おすすめです。
- SQL実行する実行ユーザーのスキーマ
- SYSTOOLSスキーマ(Db2 11.5以降の利用可能)
db2 connect to [データベース名]
db2 "call SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', [モード], [作成表スペース], [作成スキーマ])"
モードは 'C'は作成、'D'は削除を意味する。
# 例: SAMPLEデータベース・デフォルト表スペース・SYSTOOLSスキーマにEXPLAIN表を作成する
db2 connect to SAMPLE
db2 "call SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', CAST(NULL AS VARCHAR(128)), CAST(NULL AS VARCHAR(128)))"
db2 terminate
モードの'D'を使うと、EXPLAINの複数表を一つ一つDROPするのではなく、一度に削除できます。
ちなみに、今までは「/home/インスタンスオーナー/sqllib/misc/EXPLAIN.ddl」にある。DDLを 実行して、EXPLAIN表を作成していました。
db2 connect to [データベース名]
db2 -tvf /home/[インスタンスオーナー]/sqllib/misc/EXPLAIN.ddl
db2 terminate
EXPLAIN表への書き出し
SQLのアクセス・プランをEXPLAIN表に書き出すには、「SQLが保存されているパッケージを指定 してアクセス・プランを取得」、「ファイルにテキスト形式で記述されたSQLのアクセス・プランを取得」等さまざまな方法があります。
ここでは、「Db2のパッケージ・キャッシュに保存されている、実行されたSQLからアクセス・プランを取得」の手順を利用してみます。Db2で実行されたSQLは、Db2のパッケージ・キャッシュに保存されており、実行時間やCPU使用時間などを条件にして、SQLを抽出することができます。
また、Db2では モニターレポートと呼ばれるプロシジャーの中に、MONREPORT.PKGCACHE という、 Db2のパッケージ・キャッシュから負荷の高いSQLを抽出する機能があります。
# サンプルデータベースに接続
db2 connect to sample
# SQLを実行します。
db2 "select count(*) from employee a, employee b, employee c, employee d, employee e"
# パッケージ・キャッシュから負荷の高いSQLを抽出します
db2 "call MONREPORT.PKGCACHE" > pkgcache.txt
"call MONREPORT.PKGCACHE" の出力を確認すると、CPU処理時間やロック待機時間等の負荷の高いSQLのトップ10が抽出されている。 また、それぞれのSQLのパッケージ・キャッシュ上のユニークな識別子 EXECUTABLE_ID が記述されています。
:
Top 10 statements by TOTAL_CPU_TIME <- CPU処理時間の長いSQLのトップ10
--------------------------------------------------------------------------------
# TOTAL_ STMT_TEXT
CPU_TIME
-- ----------- ---------------------------------------------------------------
1 1520008 select count(*) from employee a, employee b, employee c, employ
2
:
Part 2 - EXECUTABLE_IDs for statements in Part 1
# EXECUTABLE_ID
-- ----------------------------------------------------------------------------
1 x'0100000000000000770000000000000000000000020020210302074700472124'
2
負荷の高いSQLのアクセス・プランを取得するためには、以下のストアード・プロシジャーを実行します。
db2 "call EXPLAIN_FROM_SECTION([EXECUTABLE_ID] , 'M', null, 0, EXPLAIN表のスキーマ名, ?, ?, ?, ?, ?)"
前述のSQL文のアクセス・プランを取得するには、以下を実行します。
db2 "call EXPLAIN_FROM_SECTION(x'01000000000000001E0100000000000001000000010020210302034946872729' , 'M', null, 0, 'SYSTOOLS', ?, ?, ?, ?, ?)"
テキスト形式で取り出す
さて、前述のアクセス・プランのストアード・プロシジャーを実行すると、EXPLAIN表にアクセス・プランが書き込まれた状態になりますので、 それを、人間が読み取れるようなテキスト形式にフォーマットします。
db2exfmt -d [データベース名] -g tic -e [EXPLAINスキーマ名] -n % -s % -w -1 -# 0 -o -t >explain.txt
前述のSQL文のアクセス・プランをテキスト形式にフォーマット化するためには、以下のコマンドを実行します。
db2exfmt -d SAMPLE -g tic -e SYSTOOLS -n % -s % -w -1 -# 0 -o -t >explain.txt
これで、explain.txt ファイルでアクセス・プランを確認できるようになりました。
終わりに
Db2のアクセス・プランを取得するための手順を記述しましたが、何点が注意点があります。
- 対象SQLがパッケージ・キャッシュで参照できない
パッケージ・キャッシュのサイズが小さいと対象のSQLがキャッシュアウトされて、参照できないことがあります。
- コマンドをシェル化する
負荷の高いSQLのアクセス・プランを解析するためには、「モニターレポートで負荷の高いSQLを抽出する」、「見つけた負荷の高いSQLのアクセス・プランをテキスト形式で取得」を繰り返す必要があります。コマンドをシェル化することで、素早く実行できるようにしておくことが必要です。