

Db2 for z/OS環境でSQLパフォーマンスを安定化するノウハウ集
こんにちは、システム基盤サービス部の力久です。
Db2のバージョンアップや運用上のやむを得ないREBINDの実行によってSQLのパフォーマンスが悪化した、といった経験をお持ちではありませんか?
SQLステートメントを実行する際のデータへのアクセス経路(アクセス・パス)は、Db2のオプティマイザーが、稼働環境の状態(Db2カタログ表の統計情報、プロセッサーの能力、使用可能なバッファー・プールの大きさなど)に基づいて決定します。
Db2では、適切な統計情報のもと、オプティマイザーに最適なアクセス・パスを決定させるのが原則ですが、急激なパフォーマンスの変化を回避するために“人間”がある程度介入できる余地(機能)が提供されています。ここではSQLパフォーマンスを安定させるために使用できるDb2の機能の利用方法をいくつか紹介します。
オプティマイゼーション・ヒント機能
選択させたいアクセス・パス情報をPLAN_TABLEに用意して、オプティマイザーにアクセス・パスを指示する機能です。 ひと手間かける(アクセス・パス情報をPLAN_TABLEに用意する)必要がありますが、REBINDによるアクセス・パスの変化を回避するためには、比較的容易で有効な手段です。
オプティマイゼーション・ヒント機能の使用(静的SQL)
静的SQLにおいてオプティマイゼーション・ヒント機能を使用する手順は以下の通りです。デフォルトではオプティマイゼーション・ヒント機能は無効になっているため、DSNZPARMでOPTHINTS=YESを指定して有効にする必要があります。
- DSNZPARMのOPTHINTSパラメーターにYESを指定する
- 選択させたいアクセス・パス情報( EXPLAINレコード )をPLAN_TABLEに用意する(ヒントIDの設定)
- REBIND時に用意したアクセス・パスを指定する(ヒントIDの指定)
アクセス・パス情報のPLAN_TABLEへの登録
PLAN_TABLEには、ユーザーが任意のアクセス・パス情報(EXPLAINレコード)を登録(INSERT)できますが、有効な EXPLAINレコード を手動で設定するのは現実的ではありません。
過去に取得された、稼働実績のある(良好なパフォーマンスが得られる) EXPLAINレコード を使用するのが簡単、確実で、推奨される方法です。
オプティマイゼーション・ヒントを使用する場合は、PLAN_TABLEに有効な EXPLAINレコード を保管しておくことが、重要なポイントです。
EXPLAINレコードの例
QUERYNO | PROGNAME | METHOD | TNAME | ACCESSNAME | COLLID | OPTHINT | HINT_USED |
1234 | PROG01 | 0 | TB100 | TB100X02 | COLL1 | ||
1234 | PROG01 | 1 | TB200 | TB200X01 | COLL1 |
PLAN_TABLEのOPTHINT列へのヒントIDの登録
下記のようなSQL UPDATEステートメントを使用して、選択させたいEXPLAINレコードに任意のヒントIDを設定します。(この例ではヒントIDとして’GOODPATH’を設定しています)
UPDATE PLAN_TABLE
SET OPTHINT = 'GOODPATH'
WHERE
QUERYNO = 1234 AND
APPLNAME = ' ' AND
PROGNAME = 'PROG01' AND
VERSION = '' AND
COLLID = 'COLL1'
;
ヒントIDを設定したEXPLAINレコード
QUERYNO | PROGNAME | METHOD | TNAME | ACCESSNAME | COLLID | OPTHINT | HINT_USED |
1234 | PROG01 | 0 | TB100 | TB100X02 | COLL1 | GOODPATH | |
1234 | PROG01 | 1 | TB200 | TB200X01 | COLL1 | GOODPATH |
オプティマイゼーション・ヒントの適用
REBIND時にOPTHINTオプションを使用して、設定したEXPLAINレコードのヒントIDを指定します。
REBIND PACKAGE(COLL1.PROG01) EXPLAIN(YES) OPTHINT('GOODPATH')
REBIND時にヒントが使用された場合は、REBINDによって新しく選択されたアクセス・パス情報(EXPLAINレコード)のHINT_USED列には、使用されたヒントのヒントIDが設定されます。
ヒントが使用されたEXPLAINレコード
QUERYNO | PROGNAME | METHOD | TNAME | ACCESSNAME | COLLID | OPTHINT | HINT_USED |
1234 | PROG01 | 0 | TB100 | TB100X02 | COLL1 | GOODPATH | |
1234 | PROG01 | 1 | TB200 | TB200X01 | COLL1 | GOODPATH | |
1234 | PROG01 | 0 | TB100 | TB100X02 | COLL1 | GOODPATH | |
1234 | PROG01 | 1 | TB200 | TB200X01 | COLL1 | GOODPATH |
ヒント指定でバインドされたPACKAGEは、以降のREBINDでは、明示的にOPTHINTオプションを指定しない場合でも、前回使用されたヒントが適用されます(REBIND処理では、前回のバインドで指定されたオプションが忠実に再現されるため)。
ヒントを使用しているPACKAGEに(ヒントを使用せずに)新しいアクセス・パスを選択させたい場合は、REBIND時にOPTHINTオプションでブランクを指定します。
REBIND PACKAGE(COLL1.PROG01) EXPLAIN(YES) OPTHINT(' ')
オプティマイゼーション・ヒント機能の使用(動的SQL)
動的SQLについてもヒントを使用することが可能です。
PLAN_TABLEに選択させたいアクセス・パス情報(EXPLAINレコード)を用意するのは静的SQLの場合と同様ですが、動的SQLの場合は、BIND/REBIND時にアクセス・パスが選択されるわけではないため、ヒントを使用するためには、SQLステートメントを変更する必要があります(SET CURRENT OPTIMIZATION HINT、およびQUEYNO指定の追加)。
よって、動的SQLでヒントを使用するためには、アプリケーション・レベルでの変更が必要になり、静的SQLの場合と比較すると使いづらい面があります。
また、動的SQLでヒントを使用する場合、SQLステートメントの実行時にPLAN_TABLEがアクセスされることになるため、PLAN_TABLEには必ずINDEXを作成するようにしてください。prefix.SDSNSAMP(DSNTESC)にINDEX定義のサンプルが提供されています。
Plan Stability(プラン安定性)機能: REBIND PLANMGMT/SWITCH
REBIND時にパッケージのコピー(バックアップ)を作成し、アクセス・パスの変化によるパフォーマンス劣化などの問題が発生した場合に、保管されたパッケージのコピー(バックアップ)に戻すことができる機能です。この機能は、Db2 V9から提供されています。
パッケージのREBIND時にPLANMGMTオプション(BASIC or EXTENDED)を指定することによりパッケージのコピーが保管されます。
REBIND前のアクセス・パス(保管されたパッケージのコピー)に戻す場合は、SWITCHオプションを使用してREBINDを実施します。
PLANMGMTオプション: BASIC | EXTENDED | OFF
PLANMGMTオプションは下記の3つです。
BASIC | 2世代のパッケージ(現行と直前のコピー)が保管されます。 |
EXTENDED | 3世代のパッケージ(現行と直前のコピー、およびオリジナル・コピー)が保管されます。 |
OFF | 現行パッケージのみ。以前のパッケージのコピーは保管されません。 |
REBIND PACKAGE(collid.package) PLANMGMT(EXTENDED)
PLANMGMT(EXTENDED)オプションを使用してREBINDを実施したときの動きは以下の通りです。
1. BIND PACKAGE
新しいパッケージ(Copy 1)が作成され、現行(アクティブな)コピーになります。以降、このパッケージ (Copy 1) がオリジナル・コピーになります。


2. BIND後、最初のREBIND PACKAGE PLANMGMT(EXTENDED)
新しいパッケージ(Copy 2)が作成され、現行コピーになります。上記1のBIND PACKAGEによって作成されたパッケージ(Copy 1)は直前のコピーとして保管されます。また、オリジナル・コピーがまだ存在していないため、Copy 1のクローンが作成され、オリジナル・コピーとして保管されます。


3. BIND後、2回目以降のREBIND PACKAGE PLANMGMT(EXTENDED)
新しいパッケージ(Copy 3)が作成され、現行コピーになります。上記2のREBIND PACKAGEによって作成されたパッケージ(Copy 2)は直前のコピーとして保管され、前回の直前コピー(Copy 1)は破棄されます。 オリジナル・コピー(Copy 1)はそのまま保管されます。


SWITCHオプション: PREVIOUS | ORIGINAL
SWITCHオプションは下記の2つです。
PREVIOUS | 現行パッケージと直前のコピーを入れ替えます。 |
ORIGINAL | 現行パッケージとオリジナル・コピーを入れ替えます。現行パッケージは、直前のコピーになります。オリジナル・コピーは変更されません。 |
REBIND PACKAGE(collid.package) SWITCH(PREVIOUS)
SWITCH(PREVIOUS)オプションを使用したREBINDによって、パッケージを直前のコピーに戻す(アクセス・パスを直前の状態に戻す)ことができます。


Plan Stability機能の使用に関する注意事項、考慮事項
REBIND SWITCHによるアクセス・パスの戻しは、静的SQLについてのみ有効です。
REBIND SWITCHによるアクセス・パスの戻しを実施する場合、(アクセス・パスだけではなく)パッケージ自体が以前の状態に戻ります。例えば、PTFによる修正(不正な結果出力の解決など)を反映するためにREBINDを実施した後に、REBIND SWITCHによる戻しを行うと、PTFによる修正も破棄されてしまいます。
Plan Stability機能を使用すると、Db2カタログ表、ディレクトリーの関連するすべてのパッケージ情報が履歴保管されますので必要スペースが増加します。PLANMGMT(BASIC)の場合は2倍、PLANMGMT(EXTENDED)の場合は3倍のスペースが必要です。
パッケージの世代を手動で削除する場合は、FREE PACKAGEのPLANMGMTSCOPEオプションを使用します。
統計情報の戻し: ADMIN_INFO_SQLストアード・プロシージャーの使用
オプティマイザーに最適なアクセス・パスを選択させるためには、RUNSTATSによって収集される統計情報を最新の状態(現状と乖離していない状態)にしておくことが基本ですが、新たなRUNSTATSによる統計情報更新の結果(統計情報更新後に新たに選択されたアクセス・パスによって)、以前よりパフォーマンスが悪化することが、稀にあります。
ADMIN_INFO_SQLストアード・プロシージャーは、主にIBM社のソフトウエア・サポートが問題の再作成およびトラブルシューティングを行うために使用されるツールですが、このストアード・プロシージャーを使って統計情報を以前の状態に戻すことができます。ADMIN_INFO_SQLは、Db2 V9以降で使用可能です。
ADMIN_INFO_SQLストアード・プロシージャー
このストアード・プロシージャーを使用すると、Db2カタログ表のアクセス・パス選択に使用される統計情報を再現するSQLステートメント(Db2カタログ表の更新ステートメント)を生成できます。
RUNSTATS(統計情報の更新)を実施する前に、ADMIN_INFO_SQLを使用して、現状のDb2カタログ統計情報を再現するSQLステートメントを生成、保管しておけば、統計情報の更新の結果、アクセス・パスが変化し、パフォーマンス劣化が発生した場合などに、Db2カタログの統計情報を元に戻すことができます。
prefix.SDSNSAMP(DSNTEJ6I)に、ADMIN_INFO_SQLストアード・プロシージャーをCALLするプログラムDSNADMSBを実行するサンプルJCLが提供されています。
DSNADMSBの実行JCL例
//*--------------------------------------------------------------*
//* Execute DSNADMSB to call SYSPROC.ADMIN_INFO_SQL *
//*--------------------------------------------------------------*
//JOBLIB DD DSN=DSNC10.SDSNLOAD,DISP=SHR
// DD DSN=CEE.SCEERUN,DISP=SHR
//*
//DSNADMSB EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*,DCB=(RECFM=VB,LRECL=4096)
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN1)
RUN PROGRAM(DSNADMSB) PLAN(DSNADMSB)
//*
//*********************************************************************
//* THE DSNADSM CONTROL STATEMENT FOLLOWS. IT CONSISTS OF 13 POSITIONAL
//* PARAMETERS AS INDICATED. EACH PARAMETER APPEARS ON A SEPARATE
//* LINE. USE A PLUS SIGN(+) IN COLUMN 1 TO INDICATE THAT IT IS A
//* CONTINUATION OF A PARAMETER FROM THE PREVIOUS LINE.
//*********************************************************************
//* 1) TABLE_CREATOR VARCHAR(128)
//* 2) TABLE_NAME VARCHAR(128)
//* 3) CATALOG_CREATOR VARCHAR(128)
//* 4) PLAN_INFO VARCHAR(150)
//* 5) COLLECT_DDL CHAR(1)
//* 6) COLLECT_STATS CHAR(1)
//* 7) COLLECT_COLUMN_STATS CHAR(1)
//* 8) EDIT_DDL CHAR(1)
//* 9) EDIT_VERSION_MODE CHAR(4)
//* 10) PART_ROTATION CHAR(1)
//* 11) OUTPUT_METHOD CHAR(1)
//* 12) OUTPUT_INFO VARCHAR(1024)
//* 13) PMR_INFO VARCHAR(13)
//*********************************************************************
//INPUTP DD *
DSN81210
EMP
DEFAULT
NONE
N
Y
Y
N
NONE
N
R
NONE
11111.000.000
//*********************************************************************
//* End of DSNADMSB input parameters.
//*********************************************************************
ADMIN_INFO_SQLの出力例(抜粋)
***************************
** Start of SYSTABLES **
***************************
UPDATE SYSIBM.SYSTABLES SET
CARDF = 4.200000000000000e+01,
NPAGES = 2,
PCTROWCOMP = 76,
AVGROWLEN = 52,
NPAGESF = 2.000000000000000e+00,
SPACEF = 2.880000000000000e+03,
PCTPAGES = 14,
STATSTIME = '2016-12-19-14.29.30.946868'
WHERE CREATOR = 'DSN81210'
AND NAME = 'EMP';
最後に
Db2のオプティマイザーは人智をはるかに超えています。時折、予測が不可能な振る舞いをする可能性があることを念頭に置いて、ここでご紹介した内容を参考にしていただければ幸いです。