Db2 for z/OS環境でSQLパフォーマンスを安定化するノウハウ集

2012年6月1日Db2,SQL,z/OS

こんにちは、システム基盤サービス部の力久です。

Db2のバージョンアップや運用上のやむを得ないREBINDの実行によってSQLのパフォーマンスが悪化した、といった経験をお持ちではありませんか?

SQLステートメントを実行する際のデータへのアクセス経路(アクセス・パス)は、Db2のオプティマイザーが、稼働環境の状態(Db2カタログ表の統計情報、プロセッサーの能力、使用可能なバッファー・プールの大きさなど)に基づいて決定します。

Db2では、適切な統計情報のもと、オプティマイザーに最適なアクセス・パスを決定させるのが原則ですが、急激なパフォーマンスの変化を回避するために“人間”がある程度介入できる余地(機能)が提供されています。ここではSQLパフォーマンスを安定させるために使用できるDb2の機能の利用方法をいくつか紹介します。

※ここで解説する機能を使用される場合は、IBM社のマニュアルもご確認のうえ、ご自身の責任においてご使用ください。

オプティマイゼーション・ヒント機能

選択させたいアクセス・パス情報をPLAN_TABLEに用意して、オプティマイザーにアクセス・パスを指示する機能です。 ひと手間かける(アクセス・パス情報をPLAN_TABLEに用意する)必要がありますが、REBINDによるアクセス・パスの変化を回避するためには、比較的容易で有効な手段です。

オプティマイゼーション・ヒント機能の使用(静的SQL)

静的SQLにおいてオプティマイゼーション・ヒント機能を使用する手順は以下の通りです。デフォルトではオプティマイゼーション・ヒント機能は無効になっているため、DSNZPARMでOPTHINTS=YESを指定して有効にする必要があります。

  1. DSNZPARMのOPTHINTSパラメーターにYESを指定する
  2. 選択させたいアクセス・パス情報( EXPLAINレコード )をPLAN_TABLEに用意する(ヒントIDの設定)
  3. REBIND時に用意したアクセス・パスを指定する(ヒントIDの指定)

アクセス・パス情報のPLAN_TABLEへの登録

PLAN_TABLEには、ユーザーが任意のアクセス・パス情報(EXPLAINレコード)を登録(INSERT)できますが、有効な EXPLAINレコード を手動で設定するのは現実的ではありません。

過去に取得された、稼働実績のある(良好なパフォーマンスが得られる) EXPLAINレコード を使用するのが簡単、確実で、推奨される方法です。

オプティマイゼーション・ヒントを使用する場合は、PLAN_TABLEに有効な EXPLAINレコード を保管しておくことが、重要なポイントです。

EXPLAINレコードの例

QUERYNOPROGNAMEMETHODTNAMEACCESSNAMECOLLIDOPTHINTHINT_USED
1234PROG010TB100TB100X02COLL1
1234PROG011TB200TB200X01COLL1
Db2 V10以降のパッケージでは、EXPLAINステートメントのPACKAGEキーワードを使用して、現在稼働中のパッケージのEXPLAINレコードをPLAN_TABLEに書き出すことができます。この機能を使用すれば、PLAN_TABLEにEXPLAINレコードが保管されていない場合でも、ヒントで使用するEXPLAINレコードを準備できます。

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レコード

QUERYNOPROGNAMEMETHODTNAMEACCESSNAMECOLLIDOPTHINTHINT_USED
1234PROG010TB100TB100X02COLL1GOODPATH
1234PROG011TB200TB200X01COLL1GOODPATH

オプティマイゼーション・ヒントの適用

REBIND時にOPTHINTオプションを使用して、設定したEXPLAINレコードのヒントIDを指定します。

REBIND PACKAGE(COLL1.PROG01) EXPLAIN(YES) OPTHINT('GOODPATH')

REBIND時にヒントが使用された場合は、REBINDによって新しく選択されたアクセス・パス情報(EXPLAINレコード)のHINT_USED列には、使用されたヒントのヒントIDが設定されます。

ヒントが使用されたEXPLAINレコード

QUERYNOPROGNAMEMETHODTNAMEACCESSNAMECOLLIDOPTHINTHINT_USED
1234PROG010TB100TB100X02COLL1GOODPATH
1234PROG011TB200TB200X01COLL1 GOODPATH
1234 PROG01 0 TB100 TB100X02 COLL1GOODPATH
1234 PROG01 1 TB200 TB200X01 COLL1GOODPATH

ヒント指定でバインドされた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つです。

BASIC2世代のパッケージ(現行と直前のコピー)が保管されます。
EXTENDED3世代のパッケージ(現行と直前のコピー、およびオリジナル・コピー)が保管されます。
OFF現行パッケージのみ。以前のパッケージのコピーは保管されません。

REBIND PACKAGE(collid.package) PLANMGMT(EXTENDED)

PLANMGMT(EXTENDED)オプションを使用してREBINDを実施したときの動きは以下の通りです。

1. BIND PACKAGE
新しいパッケージ(Copy 1)が作成され、現行(アクティブな)コピーになります。以降、このパッケージ (Copy 1) がオリジナル・コピーになります。

BIND PACKAGE

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

BIND後、最初のREBIND PACKAGE PLANMGMT(EXTENDED)

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

BIND後、2回目以降のREBIND PACKAGE PLANMGMT(EXTENDED)

SWITCHオプション: PREVIOUS | ORIGINAL

SWITCHオプションは下記の2つです。

PREVIOUS現行パッケージと直前のコピーを入れ替えます。
ORIGINAL現行パッケージとオリジナル・コピーを入れ替えます。現行パッケージは、直前のコピーになります。オリジナル・コピーは変更されません。

REBIND PACKAGE(collid.package) SWITCH(PREVIOUS)

SWITCH(PREVIOUS)オプションを使用したREBINDによって、パッケージを直前のコピーに戻す(アクセス・パスを直前の状態に戻す)ことができます。

REBIND PACKAGE(collid.package) SWITCH(PREVIOUS)

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以降で使用可能です。

Db2 V9までは、DB2PLI8プログラムを使用して同等の機能(Db2カタログ表の更新ステートメントの生成)を実施することができました。

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のオプティマイザーは人智をはるかに超えています。時折、予測が不可能な振る舞いをする可能性があることを念頭に置いて、ここでご紹介した内容を参考にしていただければ幸いです。

  • Zabbix Enterprise Appliance
  • 低コスト・短納期で提供するまるごとおまかせZabbix