目次


db2look を使用してのオプティマイザーのアクセス・プランの再作成

照会のチューニングを支援するツール

Comments

はじめに

DB2 UDB のサポート・アナリストを務めていると、オプティマイザーまたは照会プラン作成に関する問題に取り組む顧客から次のような質問をよく受けます。

「実稼働環境で使用する照会アクセス・プランと同じプランをテスト環境に再作成するにはどうすればいいですか?」

テスト環境に実稼働環境の再作成を必要とする機会はよくあります。照会分析のための同じアクセス・プランの再作成などがそれに含まれます。

例えば、実稼働環境で貧弱なアクセス・プランでの照会が原因で、パフォーマンスの問題が発生している場合に、パフォーマンスを向上させるため、テスト環境上にこのアクセス・プランを複製して、統計情報の操作、最適化レベルの変更、レジストリー変数の変更の試行など、さまざまな方法を試してみたいと考えているとします。

理想的には、実稼働環境にできる限り厳密に一致するテスト環境を作成することが期待されます。つまり、ハードウェア、オペレーティング・システムのメンテナンス・レベル、構成、DB2 レベル、および両方の環境の構成に至るまでまったく同じものを使用し、さらに、実稼働環境とまったく同じデータをテスト環境でも使用するべきだと考えます。しかし、常にこれらが実現できるわけではありません。実稼働環境で膨大な容量のデータを保持している場合は、物理的に実稼働環境のテスト・コピーを維持できる容量がない可能性もあります。

db2look ユーティリティーを使用すると、実稼働環境の詳細をすべて複製することができなくても、目的を達成することができます。

ここでは、照会プラン作成についての問題を再現するために、実際のデータをコピーする必要なく、テスト環境上に実稼働環境をどのように模倣することができるかを説明します。この機能を使用すると、照会のデバッグが可能になり、アクセス・プランの問題を理解することができます。実稼働環境での作業を中断することもありません。ただし、結果として得たアクセス・プランの実行をテストする場合は、テスト環境上に実稼働環境のデータ (できる限り多くのデータ) をロードする必要があります。テスト環境と実稼働環境との間の違いによって、両方の実行特性が一致しない可能性は常にあります。分析のこの部分 (パフォーマンス・チューニング) は、技術でもありますが、ある意味芸術とも言えるでしょう。

SQL0901N エラー、またはインスタンス・クラッシュなど、オプティマイザーや照会コンパイラーの分野に関するその他の問題も、ここで説明する方法で再現できます。最新のフィックスパック (システムが古いレベルのフィックスである場合)、別の最適化レベル、別のレジストリー変数をテストするなど、さまざまな方法を試して、これらの変更によって問題が解決されるかどうかを確かめることができます。

この目的を達成するために、db2look で使用するオプションについて学習しましょう。

db2look コマンドとそのオプション

以下は、実稼働環境から必要な情報を取得するために使用するコマンドです。

リスト 1. オプティマイザーに関する問題を再現するdb2look コマンド
db2look -d <dbname> -l -o storage.out
db2look -d <dbname> -f -fd -o config.out
db2look -d <dbname> -e -a -m -o db2look.out
db2look -d <dbname> -e -a -m -t table1 table2 .... tableX -o table.ddl

では、これらの db2look コマンドのオプションについて詳しく説明します。

バッファー・プール、表スペース、およびデータベース・パーティション・グループ情報の生成
db2look -d <dbname> -l -o storage.out
  • -d:データベース名 ‐ この指定は必須です。
  • -l:データベース・レイアウトを作成します。これは、データベース・パーティション・グループ、バッファー・プール、および表スペースのレイアウトです。
  • -o:所定のファイル名に出力をリダイレクトします。-o オプションが指定されていない場合、出力先は 標準出力 (stdout) となり、通常は画面となります。

なお、-l オプションは実稼働環境を模倣する上で重要です。理想としては、同じバッファー・プール、データベース・パーティション・グループ (マルチ・パーティション環境の場合)、そして表スペース情報 (一時表スペースを含む) が期待されます。しかし、メモリーに制約があり、実稼働環境にある大容量のバッファー・プールを割り当てることができない場合は、db2fopt コマンドを使用します。このコマンドの詳細については、このセクションで後述します。

実稼働環境と同じ表スペースを、テスト環境でもセットアップできるとは限りません。例えば、大きいサイズでデバイスをセットアップすることはできますが、テスト環境に同じデバイス・サイズを作成できる柔軟性を持たせることはできません。また、テスト環境に使用可能な個別の表スペース・デバイスを用意することが不可能な場合もあります。さらに、実稼働環境と同じパスをテスト環境にセットアップできないことがほとんどなので、テスト環境に適合するようパス、デバイス、およびファイルを変更する必要があります。

オプティマイザーが使用する表スペースの重要な情報は、次のとおりです。以下がテスト環境、および実稼働環境の両方で同じであることを確認する必要のある内容です。(注:この例に示されている数字は一例です。両方の環境で同じ設定を使用します)。

PREFETCHSIZE 16
EXTENTSIZE 16
OVERHEAD 12.670000
TRANSFERRATE 0.180000

表スペースが実稼働環境で「データベースによって管理(DMS 表スペース)」されている場合は、テスト環境でも「データベースによって管理」されている必要があります。実稼働環境で「システムによって管理(SMS 表スペース)」されている場合は、テスト環境でもそのように設定する必要があります。

注:これが MPP 環境 (multiple physical partitions)システムである場合、データベース・パーティション・グループのパーティション数をテスト環境でも同数にする必要があります。ただし、物理的なマシン数は同じにする必要はありません。MPP 環境 環境全体の論理的なパーティション数は、テスト、および実稼働環境の両方で同数である必要があります。

構成パラメーターおよびレジストリー変数の生成
db2look -d <dbname> -f -fd -o config.out

ここで使用しているオプションについて説明します。

  • -f:構成パラメーター、およびレジストリー変数を抽出します。このオプションが指定されている場合、-wrapper オプションと -server オプションは無視されます。
  • -f:構成パラメーター、およびレジストリー変数を抽出します。このオプションが指定されている場合、-wrapper オプションと -server オプションは無視されます。

このコマンドによる出力は次のようになります。

リスト 2. db2look コマンドの出力サンプル
$ db2look -d sample -f -fd
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: SKAPOOR
-- This CLP file was created using DB2LOOK Version 8.2
-- Timestamp: Sat Mar 26 00:13:36 EST 2005
-- Database Name: SAMPLE
-- Database Manager Version: DB2/6000 Version 8.2.2
-- Database Codepage: 819
-- Database Collating Sequence is: UNIQUE

CONNECT TO SAMPLE;

--------------------------------------------------------
-- Database and Database Manager configuration parameters
--------------------------------------------------------

UPDATE DBM CFG USING cpuspeed 6.523521e-07;
UPDATE DBM CFG USING intra_parallel NO;
UPDATE DBM CFG USING federated NO;
UPDATE DBM CFG USING fed_noauth NO;

!db2fopt SAMPLE update opt_buffpage 50000;
!db2fopt SAMPLE update opt_sortheap 10000;
UPDATE DB CFG FOR SAMPLE USING locklist 1000;
UPDATE DB CFG FOR SAMPLE USING dft_degree 1;
UPDATE DB CFG FOR SAMPLE USING maxlocks 10;
UPDATE DB CFG FOR SAMPLE USING avg_appls 1;
UPDATE DB CFG FOR SAMPLE USING stmtheap 2048;
UPDATE DB CFG FOR SAMPLE USING dft_queryopt 5;

---------------------------------
-- Environment Variables settings
---------------------------------

!db2set DB2_ANTIJOIN=yes;
!db2set DB2_INLIST_TO_NLJN=yes;

COMMIT WORK;
CONNECT RESET;
TERMINATE;

-f オプション、および-fd オプションは、オプティマイザーがアクセス・プラン・フェーズで使用する構成パラ メーター、および環境変数の抽出のために重要なオプションです。なお、このリスト 2 では、-fd オプションの 結果として次のような出力が得られます。

!db2fopt SAMPLE update opt_buffpage 50000;
!db2fopt SAMPLE update opt_sortheap 10000;

db2fopt コマンドは、オプティマイザーに「バッファー・プール・サイズ」に指定の値を使用するよう指示します。使用可能なバッファー・プールのページ数を合算するように指示するわけではありません。(db2exfmt 出力のバッファー・プール・サイズの詳細については、「バッファー・プール・サイズ」のセクションで後述します。)例えば、テスト環境上ではメモリー制約のため大容量のバッファー・プールを用意することができないために、実際にそのようなバッファー・プールを用意することなく、サイズを同じにして構成する必要があるとします。このような場合に、-fd オプションを使用します。このオプションにより、db2fopt コマンドが生成され、このデータベースに対して使用可能なバッファー・プールに基づいて計算するのではなく、指定サイズを使用するようオプティマイザーに指示します。

ソート・ヒープの場合にも同じ方法が有効です。後述のソート・ヒープのセクションでその内容を確認します。

次は、db2fopt コマンドの使用方法を説明します。なお、db2look で指定している -fd オプションは、ユーザー指定の値を取得しますが、その使用方法と、値がリセットされることに注意して、データベース構成で bufferpoolページや sortheap で、syscat.bufferpools を使用して元の方法に戻すことができるようにしておく必要があります。

C:¥>db2fopt
Usage: db2fopt <database-alias> update [opt_buffpage <value>] [opt_sortheap <value>f]
or db2fopt <database-alias> get [opt_buffpage] [opt_sortheap]]

opt_buffpage および opt_sortheap の値を設定する場合は、次のコマンドを実行します。

db2fopt <dbname> update opt_buffpage <value> opt_sortheap <value>

例えば、次のような出力が生成されます。

C:¥>db2fopt sample update opt_buffpage 50000 opt_sortheap 10000
Update succeeded

データベースを終了し、再接続していることを確認します。

値を表示する場合は、次のコマンドを実行します。

C:¥>db2fopt sample get opt_buffpage opt_sortheap opt_buffpage value is 50000 opt_sortheap value is 10000

また、値をリセットして、これらの 2 つのパラメーターを使用せずに、bufferpool ページを概算するsyscat.bufferpools を使用する方法に戻し、sortheap サイズのデータベース構成に戻す場合は、次のコマンドを実行します。

C:¥>db2fopt sample update opt_buffpage -1 opt_sortheap -1
Update succeeded

データベースを終了し、再接続していることを確認します。

値がリセットされていることを確認するには、再度、db2fopt で get オプションを使用します。

C:¥>db2fopt sample get opt_buffpage opt_sortheap
opt_buffpage value is -1
opt_sortheap value is -1

データベース管理者の多くは、SQL のアクセス・プランを把握するため、DB2 SQL Explain ツール (db2exfmt) を使用します。db2exfmt ツールは、アクセス・プランを含む説明表の内容の書式を設定するために使用できます。実稼働環境から db2exfmt を使用してアクセス・プランの出力を表示し、最上部にある次の部分を参照します。(注:db2look 出力では、ほとんどの部分で -f や -fd オプションでこれらのパラメーターが取得されます。ただし、dbheap 設定は除きます。)

リスト 3. db2exfmt の出力サンプル
Database Context:
----------------
        Parallelism:               None
        CPU Speed:                 6.523521e-07
        Comm Speed:                100
        Buffer Pool size:          50000
        Sort Heap size:            10000
        Database Heap size:        5120
        Lock List size:            1000
        Maximum Lock List:         10
        Average Applications:      1
        Locks Available:           7849
        
Package Context:
---------------
        SQL Type:                  Dynamic
        Optimization Level:        5
        Blocking:                  Block All Cursors
        Isolation Level:           Cursor Stability
        
---------------- STATEMENT 1 SECTION 201 ----------------
        QUERYNO:                   1
        QUERYTAG:                  CLP
        Statement Type:            Select
        Updatable:                 No
        Deletable:                 No
        Query Degree:              1

db2exfmt 出力のアクセス・プランが表示されているすぐ下の部分で、オプティマイザーのプランに影響を与えているレジストリー変数設定があるかどうかを確認できます。

注:なお、db2look -f では、関連するレジストリー変数のすべてがリストされるわけではありません。リストされていない変数は追加する必要があります。通常、レジストリー変数の設定は、テスト環境と実稼働環境で同一であるか、またはできる限り近い必要があります。

リスト 4. アクセス・プランに影響を与えるレジストリー変数設定
1) RETURN: (Return Result)
        Cumulative Total Cost: 57.6764
        Cumulative CPU Cost: 191909
        Cumulative I/O Cost: 2
        Cumulative Re-Total Cost: 5.37264
        Cumulative Re-CPU Cost: 134316
        Cumulative Re-I/O Cost: 0
        Cumulative First Row Cost: 26.9726
        Estimated Buffer pool Buffers: 2
        
        Arguments:
        ---------
        BLDLEVEL: (Build level)
                DB2 v8.1.0.80 : s041221
        ENVVAR : (Environment Variable)
                 DB2_ANTIJOIN=yes
                DB2_INLIST_TO_NLJN = yes
        STMTHEAP: (Statement heap size)
                2048

データ定義言語 (DDL) の作成

次の db2look コマンドでは、DDL を作成して、設定情報および統計情報と一緒にすべてのデータベース・オブ ジェクトを複写します。

db2look -d <dbname> -e -a -m -o db2look.out

ここで使用しているオプションについて説明します。

  • -a:すべてのユーザーの統計情報を生成します。このオプションが指定されている場合、-u オプションは無視されます。
  • -e:データベースの複写に必要な DDL ファイルを抽出します。このオプションでは、DDL ステートメントを含むスクリプトを生成します。このスクリプトを別のデータベースに対して実行すると、データベース・オブジェクトを再作成することができます。
  • -m:db2look ユーティリティーを模倣モードで実行します。このオプションでは、SQL UPDATE ステートメントを含むスクリプトを生成します。これらの SQL UPDATE ステートメントではすべての統計情報を取り込みます。このスクリプトを別のデータベースに対して実行し、オリジナルのスクリプトを複製することができます。-m オプションが指定された場合、-p、-g、および –s の各オプションは無視されます。

データベース・サブセットの統計情報および DDL の収集

特定の表と関連するオブジェクトのみの統計情報と ddl を収集するには、次のコマンドを使用します。

db2look -d <dbname> -e -a -m -t <table1> <table2> .. <tableX> -o table.ddl

この例では、次のようなオプションを使用しています。

  • -t:指定された表の統計情報を生成します。指定可能な表の最大数は、30 です。さらに、-a オプションを使用しない場合、次のように -z オプションを使用することができます。
  • -z:スキーマ名。-z および -a の両方が指定されている場合、-z は無視されます。スキーマ名は、フェデレーテッド・セクションに対しては無視されます。
Note: The -m option is very important. This option will collect all the statistics from the system tables. The
statistics must be the same in test as they are in production and are key to your being able to mimic the production
environment in your test environment.

db2exfmt 出力の詳細

データベース・マネージャー・レベルの構成パラメーター

Parallelism (並列処理):
このパラメーターでは、パーティション間並列処理、またはパーティション内並列処理が有効にするかどうかを指定します。複数パーティションを持つ DPF である場合、Inter Partition Parallelism (パーティション間並列処理)と表示されます。SMP 環境 (intra_parallel が有効) 単一ノード環境のみである場合、Intra Partition Parallelism (パーティション内並列処理) と表示されます。intra_parallel および複数パーティション環境の両方が有効である場合は、このパラメーターには、Inter Partition Parallelism (パーティション間並列処理) および Intra PartitionParallelism (パーティション内並列処理) の両方が表示されます。また、どちらの並列処理もない場合は、このパラメーターは NONE と表示されます。

CPU Speed (CPU スピード)(cpuspeed):
CPU スピード (命令ごとミリ秒単位) は、SQL オプティマイザーによって使用され、特定の操作の実行のコストを概算します。

Communications speed (通信スピード):(comm_bandwidth)
通信スピード (MB/秒) に対して指定される値は、SQL オプティマイザーによって使用され、パーティション・データベース・システムのパーティション・サーバー間の特定の操作の実行のコストを概算します。

データベース・レベルの構成パラメーター

注:db2 のコマンド「get db cfg for <dbname>」を使用して、これらのパラメーターを確認し、db2 の「update db cfg for <dbname> using <パラメーター> <値>」コマンドを使用してデータベースの構成パラメーターを更新します。

Buffer pool size (バッファー・プール・サイズ):
db2exfmt 出力で示されるバッファー・プール・サイズは、1 つのバッファー・プールのデフォルトとして buffpageを使用している場合、または syscat.bufferpools の内容に基づいて計算している buffpage パラメーターによって決定されます。表示されている数字は、データベースに割り当てられているバッファー・プール・ページの総数です。例えば、次のバッファー・プールを設定したとしましょう。

表 1. バッファー・プールの設定
バッファー・プール名サイズ
IBMDEFAULTBP1000
BP11000
BP24000
BPIND11000
BPIND21000
BPLONG1000
BPTEMP1000
合計:0,000

db2exfmt 出力では、バッファー・プールの合計ページ数として合計サイズが表示されます。上記の例では、10,000 です。注:ページ・サイズは問題ではありません。重要なのは、ページ数です。

実稼働環境と同じ容量のバッファー・プールをテスト環境に割り当てることができない場合、db2look で -fd オ プションを指定し、db2fopt の代わりに使用できます。

MPP 環境 では、オプティマイザーが照会を実行しているノードに対するバッファー・プール情報の合計を使用 するので、opt_buffpage をノードごとに計算します。そのため、この変更は、このツールが実行されているノー ドに対してのみ適用されます。

Sort heap size (ソート・ヒープ・サイズ) (SORTHEAP)
このパラメーターでは、専用ソートに使用する専用メモリー・ページの最大数、または共有ソートに使用する共 有メモリー・ページの最大数を定義します。

この値については、実稼働環境と同じ値を設定する必要があります。また、db2look で -fd オプションを使用す ると、次の行が表示されます。

!db2fopt SAMPLE update opt_sortheap 256;

sortheap パラメーターをオーバーライドし、オプティマイザーが sortheap 値として使用します。実際には、データベース構成パラメーターの sortheap 設定によって、実行時に割り当てられる実際の sortheap が決定されます。opt_buffpage の場合と同様に、実稼働環境に割り当てることができるのと同じサイズの sortheap をテスト環境に割り当てることができない場合は、opt_sortheap を使用することができます。

Database heap size (データベース・ヒープ・サイズ):(DBHEAP)
データベースごとに 1 つのデータベース・ヒープがあり、そのデータベースに接続されているすべてのアプリケーションを代表して、データベース・マネージャーがデータベース・ヒープを使用します。データベース・ヒープには表、索引、表スペース、およびバッファー・プールの制御ブロック情報が含まれています。

Lock list size (ロック・リスト・サイズ):(LOCKLIST)
ロック・リストに割り当てられている容量を指定します。

Maximum lock list (最大ロック・リスト):(MAXLOCKS)
アプリケーションによって保持されるロック・リストのパーセンテージを定義します。これは、データベース・マネージャーがロック・エスカレーションを実行する前に入力されている必要があります。

locklist および maxlocks では、特定のスキャン (索引スキャンまたは表スキャン) 中に保持されるロックの種類と分離レベルの識別に役立ちます。例えば、このプランでは、次のような索引スキャン操作が記述されています。

      IXSCAN: (Index Scan)
      
             TABLOCK : (Table Lock intent)
             INTENT SHARE

注:実稼働環境と比較してテスト環境の db2exfmt 出力の Locks Available が異なる場合でも、心配する必要はあ りません。この違いは照会プランニングには影響がありません。

Average applications (平均アプリケーション):(AVG_APPLS)
SQL オプティマイザーによって使用され、選択されたアクセス・プランに対して、実行時にバッファー・プールがどの程度使用可能であるかを概算するのに役立ちます (バッファー・プールはデータベースに接続されているすべてのアクティブなアプリケーションによって共有されるため)。

Query Degree (照会の度合い):(DFT_DEGREE)
SQL ステートメントのパーティション内並列処理のレベルです。ANY に設定されている場合、オプティマイザーは、オンライン状態である cpus の実際の数値に依存します。ANY を使用する場合は、intra_parallel が無効でない限り、テスト環境、および実稼働環境上の cpus の数値は同じ値に構成される必要があります。

上記の変更のほかにも同じ値であることを確認する必要のある項目があります。

Number of frequent values retained (保持される頻度の回数):(NUM_FREQVALUES)
RUNSTATS で WITH DISTRIBUTION オプションが指定されるときに収集される「最頻値」の数値を指定することができます。

Number of quantiles retained (保持される変位値の数):(NUM_QUANTILES)
RUNSTATS で WITH DISTRIBUTION オプションが指定されているときに収集される変位値の数値を制御します。

これら NUM_FREQVALUES および NUM_QUANTILES は、テスト環境と実稼働環境で同じ値である必要があります。このことにより、テスト環境上で実稼働環境と同じ頻度と変位値の数値を収集していることを確認します。

SQL statement heap (SQL ステートメント・ヒープ) (4KB):(STMTHEAP)
ステートメント・ヒープは、SQL ステートメントのコンパイル中に SQL コンパイラーのワークスペースとして使用されます。このパラメーターでは、このワークスペースのサイズを指定します。実稼働環境よりもテスト環境でこのパラメーターが小さい場合、照会のコンパイルに必要なステートメント・ヒープでのスペース不足が原因である SQL0101N メッセージが表示される可能性があります。同様に、動的な結合のための十分なステートメント・ヒープがないために、グリーディー・ジョインまでドロップダウンする SQL0437W RC=1 が表示されることがあります。

テスト環境上でのオプティマイザー/照会プランの問題の再作成の例

例 1:

OS:Windows 2000
DB2LEVEL:V8.2 フィックスパック 8 ESE 単一パーティション

テスト環境および実稼働環境で OS と db2level が同じ。
実動データベース:SAMPLE
テスト・データベース:DUMMYDB

実動データベースの作成で使用したコマンド:db2sampl
テスト・データベースの作成で使用したコマンド:

db2 create db DUMMYDB

実稼働環境:

--------------------------------------------------------
-- Database SAMPLE and Database Manager configuration parameters
--------------------------------------------------------
UPDATE DBM CFG USING cpuspeed 9.446886e-007;
UPDATE DBM CFG USING intra_parallel NO;
UPDATE DBM CFG USING federated NO;
UPDATE DBM CFG USING fed_noauth NO;
!db2fopt SAMPLE update opt_buffpage 250;
!db2fopt SAMPLE update opt_sortheap 256;
UPDATE DB CFG FOR SAMPLE USING locklist 50;
UPDATE DB CFG FOR SAMPLE USING dft_degree 1;
UPDATE DB CFG FOR SAMPLE USING maxlocks 22;
UPDATE DB CFG FOR SAMPLE USING avg_appls 1;
UPDATE DB CFG FOR SAMPLE USING stmtheap 2048;
UPDATE DB CFG FOR SAMPLE USING dft_queryopt 5;
---------------------------------
-- Environment Variables settings
---------------------------------
!db2set DB2_INLIST_TO_NLJN=yes;
!db2set DB2_HASH_JOIN=yes;

このほかに、データベース構成パラメーターには次の内容も記述されています。

db2 get db cfg for sample > dbcfg_sample.out

Database heap (4KB)                   (DBHEAP) = 600
SQL statement heap (4KB)               (STMTHEAP) = 2048
Number of frequent values retained      (NUM_FREQVALUES) = 10
Number of quantiles retained            (NUM_QUANTILES) = 20

データベース・マネージャー構成パラメーター (dbm cfg) の変更を行った後にインスタンスを停止、開始するようにしてください。次に、ORG 表および SALES 表に対して RUNSTATS を実行します。

db2 connect to sample
db2 runstats on table <schema>.org with distribution and indexes all
db2 runstats on table <schema>.sales with distribution and indexes all
db2 terminate

次に、<インストール・ディレクトリー>¥sqllib¥misc ディレクトリーの下にある EXPLAIN.DDL ファイルを実行 して EXPLAIN 表を生成します。

db2 connect to sample
db2 -tvf <intall path>¥EXPLAIN.DDL
db2 terminate

query.sql というファイルに次のコマンドを保存します。

connect to sample
set current explain mode explain
select * from org a, staff b where a.deptnumb=b.dept and b.dept=15
set current explain mode no
terminate

次のようにファイルを実行します。

db2 -tvf query.sql

この例では、説明モードで照会をコンパイルするだけです。画面は次のように表示されます。

C:¥>db2 -tvf query.sql
connect to sample

   Database Connection Information
Database server      = DB2/NT 8.2.1
SQL authorization ID = SKAPOOR
Local database alias = SAMPLE

set current explain mode explain
DB20000I The SQL command completed successfully.

select * from org a, staff b where a.deptnumb=b.dept and b.dept=15
SQL0217W The statement was not executed as only Explain information requests
are being processed. SQLSTATE=01604

set current explain mode no
DB20000I The SQL command completed successfully.

C:¥>db2 terminate
DB20000I The TERMINATE command completed successfully.

db2exfmt を使用してアクセス・プランを生成します。

db2exfmt -d SAMPLE -g TIC -w -1 -n % -s % -# 0 -o prod_sample_exfmt.txt

prod_sample_exfmt.txt で、アクセス・プランを確認します。

Access Plan:
-----------
        Total Cost: 25.8823
        Query Degree: 1
        
           Rows
          RETURN
           ( 1)
           Cost
            I/O
            |
             4
          HSJOIN
           ( 2)
          25.8823
             2
        /-----+-----¥
      4              1
    TBSCAN          TBSCAN
     ( 3)           ( 4)
   12.9682          12.913
     1               1
    |               |
    35               8
TABLE: SKAPOOR   TABLE: SKAPOOR
    STAFF            ORG

これは、実稼働環境で使用する予定のプランです。テスト環境で、このプランを模倣する必要があります。

実稼働環境から次の情報を収集します。

db2look -d SAMPLE -l -o storage.out
db2look -d SAMPLE -f -fd -o config.out
db2look -d SAMPLE -e -a -m -t ORG SALES -o table.ddl

テスト環境:
SAMPLE から DUMMYDB に接続を切り替える場合は、上記の収集後のファイル内の、それぞれのデータベース名を変更します。

例えば、3 つのファイルから次の行を、

CONNECT TO SAMPLE;

以下のように変更します。

CONNECT TO DUMMYDB;

テスト環境全体にこれらのファイルを継承します。この例では、デフォルトの表スペース、USERSPACE1 にすべての表が作成されます。そのため、テスト環境上でも、同じ SMS 表スペースにこれらを作成し、PREFETCHSIZE、EXTENTSIZE などの値を storage.out にダンプアウトするように、同じ構成を設定する必要があります。

config.out ファイルに 1 箇所変更を加えます。次を、

UPDATE DB CFG FOR SAMPLE USING dft_queryopt 5;

以下のように変更します。

UPDATE DB CFG FOR SAMPLE USING dft_queryopt 3;

そして、この config.out ファイルを保存します。

次に、storage.out、config.out、および table.ddl を実行します。

db2 -tvf storage.out > storage_output.out
db2 -tvf config.out > config_output.out
db2 -tvf table.ddl > table.out

出力ファイルからすべてのコマンド実行が完了していることを確認します。また、実稼働環境のセットアップで示した手順に従って、テスト環境の DBHEAP、STMTHEAP、NUM_FREQVALUES、 NUM_QUANTILES の値を変更します。また、レジストリー変数ができる限り同一であることを確認します。

db2stop および db2start を使用して、インスタンスを停止、および開始します。 次に、テスト環境に対してEXPLAIN 表を生成します。

db2 connect to dummydb;
<install path>¥sqllib¥misc¥db2 -tvf EXPLAIN.DDL
db2 terminate;

テスト環境で照会を実行し、実稼働環境に対して実行するときに生成した query.sql ファイルのデータベース名を SAMPLE から DUMMYDB に変更します。

:¥>db2 -tvf query.sql
connect to dummydb

   Database Connection Information

 Database server = DB2/NT 8.2.1
 SQL authorization ID = SKAPOOR
 Local database alias = DUMMYDB
 
set current explain mode explain
DB20000I The SQL command completed successfully.

select * from org a, staff b where a.deptnumb=b.dept and b.dept=15
SQL0217W The statement was not executed as only Explain information requests
are being processed. SQLSTATE=01604

set current explain mode no
DB20000I The SQL command completed successfully.

C:¥>db2 terminate
DB20000I The TERMINATE command completed successfully.

db2exfmt を使用してアクセス・プランを生成します。

db2exfmt -d DUMMYDB -g TIC -w -1 -n % -s % -# 0 -o test_dummydb_exfmt.txt

test_dummydb_exfmt.txt でアクセス・プランを確認します。

Access Plan:
-----------
       Total Cost:        25.8843
       Query Degree:      1
         Rows
        RETURN
         ( 1)
         Cost
          I/O
          |
           4
        MSJOIN
         ( 2)
       25.8843
          2
     /-----+-----¥
    1             4
  TBSCAN        TBSCAN
   ( 3)          ( 5)
  12.913        12.9682
    1             1
   |            |
    8           35
TABLE: SKAPOOR  TABLE: SKAPOOR
     ORG           STAFF

テスト環境と実稼働環境とでは異なるアクセス・プランが得られます。テスト環境で DFT_QUERYOPT (デフォルトの照会最適化) を 5 から 3 に変更したので、このことは明らかです。そのため、ジョインの手法が、マージ・ジョインとハッシュ・ジョインとで異なっていること、Total Cost も若干異なっていることが分かります。

両方のプランが一致しないので (理由をあえて不明として)、db2exfmt による出力の構成を確認してみます。表 2を参照します。

テスト環境と実稼働環境の違いは最適化レベルのみであり、これは故意に 5 から 3 に変更したものです。理由は、テスト環境に実稼働環境のアクセス・プランを複製できなかったことを示すためです。

この場合、次の UPDATE ステートメントを使用して DFT_QUERYOPT を 5 に更新する必要があります。

UPDATE DB CFG FOR SAMPLE USING dft_queryopt 5

次に、データベースから接続解除し、再接続します。テスト環境に対して、再度 query.sql を実行し、db2exfmt コマンドを使用してアクセス・プランを生成します。今度は、同じアクセス・プランが表示されます。同じでない場合は、ここで説明するオプティマイザー関連のすべてのパラメーターが同一であるかどうかを確認してください。

例 2:

この例では、db2look の –m オプションの重要性を確認します。すでに説明したように、この統計情報は、-m オプションを指定すると収集されます。また、統計情報はテスト環境でも実稼働環境でも同じである必要があります。この例では、統計情報が正しく更新されない場合に、アクセス・プランがどのように変更されるか確認します。

データベース・マネージャー構成パラメーター、データベース構成パラメーター、およびレジストリー変数は、上記の例 1 と同じです。この例では、スキーマ名は SKAPOOR です。これをユーザー表のスキーマに置き換えます。例 1 と同様に、データベース名は同じで、実稼動環境ではSAMPLE 、テスト環境では DUMMY です。この例で使用したOS プラットフォームと db2level は、AIX 5.1 with DB2 UDB ESE V8.2、フィックスパック 8、単一パーティションです。

実稼動環境で次のコマンドを実行します。

db2 "connect to sample"
db2 "create index name_ind on staff (name,id)"
db2 "runstats on table skapoor.staff with distribution and indexes all"
db2 "set current explain mode explain"
db2 "select name from staff where id=10 order by name"
db2 "set current explain mode no"
db2 "terminate"

db2exfmt を使用してアクセス・プランを生成します。次のアクセス・プランが表示されます。

Access Plan:
-----------
       Total Cost:        0.111065
       Query Degree:      1
     Rows
    RETURN
     ( 1)
     Cost
     I/O
     |
      1
    IXSCAN
     ( 2)
   0.111065
      0
      |
      35
INDEX: SKAPOOR
   NAME_IND

次のように、実稼動環境から db2look 情報を収集します。

db2look -d sample -l -o storage.out
db2look -d sample -e -a -m -t STAFF -o db2look.out
db2look -d sample -f -fd -o config.out

ファイルを変更して、上記の例 1 で実行したように、実稼動環境ではなくテスト環境に接続します。

統計情報の 1 つを手動で変更します。db2look.out ファイルで次のステートメントを検索します (なお、スキーマ名 TABSCHEMA および INDSCHEMA はユーザーのシステムでは異なります)。

UPDATE SYSSTAT.INDEXES
SET NLEAF=1,
   NLEVELS=1,
   FIRSTKEYCARD=35,
   FIRST2KEYCARD=35,
   FIRST3KEYCARD=-1,
   FIRST4KEYCARD=-1,
   FULLKEYCARD=35,
   CLUSTERFACTOR=-1.000000,
   CLUSTERRATIO=100,
   SEQUENTIAL_PAGES=0,
   DENSITY=0,
   AVERAGE_SEQUENCE_GAP=0.000000,
   AVERAGE_SEQUENCE_FETCH_GAP=0.000000,
   AVERAGE_SEQUENCE_PAGES=0.000000,
   AVERAGE_SEQUENCE_FETCH_PAGES=0.000000,
   AVERAGE_RANDOM_PAGES=1.000000,
   AVERAGE_RANDOM_FETCH_PAGES=0.000000,
   NUMRIDS=35,
   NUMRIDS_DELETED=0,
   NUM_EMPTY_LEAFS=0
WHERE INDNAME = 'NAME_IND' AND INDSCHEMA = 'SKAPOOR '
     AND TABNAME = 'STAFF' AND TABSCHEMA = 'SKAPOOR ';

今度は、FIRSTKEYCARD、FIRST2KEYCARD、FULLKEYCARD AND NUMRIDS を 35 から 37 に変更します。次のように db2look.out ファイルを保存し、3 つのファイルを実行します。

db2 -tvf config.out > config_output.out
db2 -tvf storage.out > storage_output.out
db2 terminate
db2stop
db2start
db2 -tvf db2look.out > db2look_output.out

最初の 2 つのファイル config_output.out および storage_output.out の内容から正常に実行されていることを確認します。次に、db2look_output.out ファイルの内容を確認します。次の update ステートメントが失敗したことが分かります。

UPDATE SYSSTAT.INDEXES SET NLEAF=1, NLEVELS=1, FIRSTKEYCARD=37, FIRST2KEYCARD=37
, FIRST3KEYCARD=-1, FIRST4KEYCARD=-1, FULLKEYCARD=37, CLUSTERFACTOR=-1.000000, C
LUSTERRATIO=100, SEQUENTIAL_PAGES=0, DENSITY=0, AVERAGE_SEQUENCE_GAP=0.000000, A
VERAGE_SEQUENCE_FETCH_GAP=0.000000, AVERAGE_SEQUENCE_PAGES=0.000000, AVERAGE_SEQ
UENCE_FETCH_PAGES=0.000000, AVERAGE_RANDOM_PAGES=1.000000, AVERAGE_RANDOM_FETCH_
PAGES=0.000000, NUMRIDS=37, NUMRIDS_DELETED=0, NUM_EMPTY_LEAFS=0 WHERE INDNAME =
'NAME_IND' AND INDSCHEMA = 'SKAPOOR ' AND TABNAME = 'STAFF' AND TABSCHEMA = 'SK
APOOR '
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1227N The catalog statistic "37" for column "FULLKEYCARD" is out of range
for its target column, has an invalid format, or is inconsistent in relation
to some other statistic. Reason Code = "8". SQLSTATE=23521

索引 NAME_IND 用の上記の UPDATE ステートメントは失敗しました。FULLKEYCARD が、表のカーディナリティー (CARD) より大きいためです。db2look.out ファイルの次の update ステートメントで確認できるように、CARD は 35 です。

UPDATE SYSSTAT.TABLES
SET CARD=35,
   NPAGES=1,
   FPAGES=1,
   OVERFLOW=0,
   ACTIVE_BLOCKS=0
WHERE TABNAME = 'STAFF' AND TABSCHEMA = 'SKAPOOR ';

再度、同じ照会を実行します。

db2 "select name from staff where id=10 order by name"

説明モードで、アクセス・プランを生成します。次のように、違いが分かります。

Access Plan:
-----------
       Total Cost: 12.972
       Query Degree: 1
       
    Rows
   RETURN
    ( 1)
    Cost
    I/O
    |
     1
   TBSCAN
    ( 2)
   12.972
     1
    |
     1
   SORT
    ( 3)
  12.9708
    1
   |
    1
   TBSCAN
    ( 4)
   12.9682
     1
    |
    35
TABLE: SKAPOOR
    STAFF

表で発生している WRITE アクティビティーが存在する時に RUNSTATS が実行されていると、この例にあるように統計情報に不整合があるということが示されます。このため、統計情報を更新するための UPDATE ステートメントが SQL1227N エラー・メッセージを表示して失敗することがあります。UPDATE ステートメントをすべて正常に実行することは非常に重要です。また、不整合がある場合は、修正後に再実行する必要があります。この場合、KEYCARDS および NUMRIDS を 37 から 35 に変更し元に戻します。

例 3:

実稼働環境のデータベース全体を、単一パーティションでテスト環境に複製する必要があるとします。

注:実稼働環境とテスト環境を比較するとデータベース名が異なる場合、db2look の各出力でデータベース名を変 更する必要があります。

1. ステップ 1:db2look を -l オプションを指定して収集し、tablespace/bufferpoo/database ノード・グループ情報 を収集します。

db2look -d <dbname> -l -o storage.out

表スペース情報を変更して、テスト環境に合わせます。例えば、実稼働環境では次の表スペースがあるとします。

------------------------------------
-- DDL Statements for TABLESPACES --
------------------------------------
CREATE REGULAR TABLESPACE DMS1 IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
PAGESIZE 4096 MANAGED BY DATABASE
       USING ( FILE '/data/dms1'20000,
       FILE '/data/dms2'20000,
       FILE '/data/dms3'20000)
       EXTENTSIZE 32
       PREFETCHSIZE 32
       BUFFERPOOL IBMDEFAULTBP
       OVERHEAD 12.670000
       TRANSFERRATE 0.180000
       DROPPED TABLE RECOVERY ON;

テスト環境上に同じパスをセットアップしない場合は、上記のロケーションを変更します。環境を模倣するためプランを作成するだけで、データをコピーしない場合は、ファイル・サイズを小さくし、必要に応じてより小さなコンテナーを使用します。同じバッファー・プールを作成しない場合は、バッファー・プール名を変更することもできます。ただし、バッファー・プールのページ・サイズは同じになるように設定する必要があります。また、表スペースのページ・サイズは変更しないでください。これらを実行した後、データベースを作成し、storage.out ファイルを実行します。

db2 -tvf storage.out

必要に応じて出力をリダイレクトし、すべてが正常に実行されているか確認します。例えば、次のように記述します。

db2 -tvf storage.out > storage_results.out

2. ステップ 2:実稼働環境から構成情報、および環境変数情報を収集し、テスト環境でそれらの情報を実行し ます。

db2look -d sample -f -fd -o config.out

なお、MPP 環境では、この情報は上記を実行したノードのある場所にて収集されます。データベース・マネージャー構成パラメーター、データベース構成パラメーター、およびレジストリー変数が、データベース・パーティションごとに異なる場合は、各ノードから個別に情報を収集します。ただし、実稼働環境と同じパーティションをテスト環境に構成できない場合は、実稼働環境で照会を実行するノードの情報を収集して、テスト環境上で使用します。

なお、テスト環境上のパーティション数が実稼働環境と異なる場合、シミュレーションは不完全になります。

テスト環境上で、次のように config.out ファイルを実行します。

db2 -tvf config.out

オプティマイザーは db2fopt 情報を使用して、テスト環境での設定であるバッファー・プールの総割り当て容量と sortheap の値を考慮しています。これは、メモリーの制約や sortheap の値が原因で実稼働環境と同じバッファー・プールをテスト環境に設定することができない場合に使用する手法です。また、すでに説明した、そのほかの構成パラメーターと環境変数も更新されます。

3. ステップ 3:データベース全体を模倣しようとしているため、実稼働環境のすべてのオブジェクトの DDL 情報を収集し、テスト環境で db2look を実行します。

実稼働環境では、次のコマンドを実行します。

db2look -d sample -e -a -m -o db2look.out

テスト環境では、次のコマンドを実行します。

db2 -tvf db2look.out

出力結果を確認するには、次のコマンドを実行します。

db2look -tvf db2look.out > db2look.results

上記のステップが完了したら、dbheap データベース構成パラメーターが両方で同じ値に設定されていることを確認します。

4. ステップ 4:両方で db2exfmt を使用してアクセス・プランを取得し、次の内容が同じであることを確認します。

Database Context:
----------------
      Parallelism:           None
      CPU Speed:             4.762804e-07
      Comm Speed:            100
      Buffer Pool size:      128500
      Sort Heap size:        128
      Database Heap size:    5120
      Lock List size:        12250
      Maximum Lock List:     10
      Average Applications:  4
      Locks Available:       78400
	  
	 
Package Context:
---------------
      SQL Type:              Dynamic
      Optimization Level:    3
      Blocking:              Block All Cursors
      Isolation Level:       Cursor Stability
	  
	  
---------------- STATEMENT 1 SECTION 201 ----------------
      QUERYNO:               1
      QUERYTAG:              CLP
      Statement Type:        Select
      Updatable:             No
      Deletable:             No
      Query Degree:          1

今度は、アクセス・プランに注目します。両方のアクセス・プランが同じ場合、アクセス・プランの再作成は成功です。なお、db2exfmt 出力の一番下の方に注目し、表スペース構成が一致していることを確認する必要もあります。

例 4:

実稼働環境:16 個の物理パーティション、またはパーティションごとに 4 個の論理パーティションを持つ MPP環境。
テスト環境:パーティションごとに 4 つの論理パーティションが使用可能な4 台の物理マシンを持つ MPP 環境。照会に必要な表、ビューまたは MQT。

この例では、シミュレーションは正しく機能しません。テスト環境、および実稼働環境の両方で同数のパーティションを持つ必要があります。再作成を試行することもできますが、正しい結果が得られない可能性があります。

そのため、16*4=64 のパーティションをテスト環境に追加して、正しい再作成を行う必要があります。テスト環境に 16 台の物理マシンは必要ありません。物理マシンごとに 16 個の論理パーティションを持つ 4 台の物理マシンがあれば十分です。ユーザー次第ですが、実稼働環境と同様にするためには、全体で 64 個の論理パーティションを持つ必要があります。

テスト環境に同数の論理パーティションを追加する変更を行った後、以下の表に示すように、テスト環境は実稼働設定と同じようになるはずです。

表 3. 実稼働環境セットアップ
DBPARTITIONS
ALLNODES (1 から 64 のノード上)
NODE1 (ノード 1 で定義されている db パーティション)
NODE2 (ノード 5 で定義されている db パーティション)
TABLESPACES
TABSPACE1 (dbpartition ALLNODES で定義されているデバイスを使用する DMS)
TABSPACE2 (dbpartition NODE1 で定義されている SMS を使用する DMS)
TABSPACE3 (dbpartition NODE2 で定義されている DMS を使用する DMS)
TABLES
TABSPACE1 の TAB1
TABSPACE2 の TAB2
TABSPACE3 の TAB3
MQT:
TAB3 上で定義されている MQT
VIEW:
TAB1 および TAB2 の 2 つの表を含む定義されている VIEW1

照会の実行元のノードから -f および –fd オプションを使用して db2look を収集していることを確認します。これは、すでに説明したバッファー・プール情報がこのノードから取得され、データベース・マネージャー構成パラメーター、データベース構成パラメーター、およびレジストリー変数が照会を実行しているノードから取得されていることを確認するためです。すべてのノードで、非常に重要な設定であるバッファー・プール以外を同じ構成に設定しているケースが多いです。

1. ステップ 1:実稼働環境のストレージ情報を収集します。

db2look -d <dbname> -l -o storage.out

2. ステップ 2:表スペース情報、またはバッファー・プール情報を変更して、テスト環境に合わせます。使用可能なデバイスがない場合、DMS ファイル・コンテナーを使用します。また、実稼働環境と同数のコンテナーをテスト環境で使用しない場合は、リストを短縮して、コンテナーの数を減らして使用します。しかし、実稼働環境上で DMS または SMS 表スペースのどちらか確認する必要があります。テスト環境上でも同じタイプを使用する必要があります。

3. ステップ 3:次のオプションを使用して構成情報を収集します。

db2look -d <dbname> -f -fd -o config.out

4. ステップ 4:今度は、必要なオブジェクトだけの db2look 情報を収集します。この例では、表、 DDL、ビュー、および表に関連する MQT などすべて関連する情報を必要としています。

これらの情報をすべて収集し、表スペースまたはバッファー・プール情報を変更したら、db2look 出力ファイルを実行し、実稼働環境とテスト環境の db2exfmt 出力を取得して比較します。

例 5:

表のアクティビティーでの統計情報の収集に関する古典的な例です。SQL1227N エラー・メッセージを取得すると、統計情報を手動で変更することなく問題を再作成することはできません。

例えば、表に 100 万行があり、整数列で定義されている主キーがあるとします。Distribution を指定してRUNSTATS を実行し、すべてのオプションを索引化して、表への書き込みアクセスを許可します。表統計を取得するまでに、さらに 100,000 件のレコードが表に挿入されました。そのため表統計では、CARD が 1,100,000と表示されます。ただし、索引統計の収集を開始するまでに、例えば整数列に定義されている主キーには、さらに 10,000 件のレコードが挿入されました。そのため、表の行数は 1,110,000 となり、主索引であるFIRSTKEYCARD は、1,110,000 になります。そのため、不整合が見られるようになりました。表統計の CARD は、表には 1,100,000 件のレコードが存在すると示しています。その一方で、主索引統計の FIRSTKEYCARD は、表に 1,110,000 件のレコードが存在すると示しています。索引統計の更新は、SQL1227N rc=8 (この場合) というエラー・メッセージが表示されて失敗します。これは、索引の FIRSTKEYCARD が表の CARD より大きいためです。この不整合は手動で修正する必要があります。この例では、FIRSTKEYCARD を CARD と等しくなるように 1,100,000 とするか、反対に、CARD の値を増やして FIRSTKEYCARD と等しくなるように、つまり1,110,000 とします。

発生する可能性のある不整合はそのほかにも多くあります。-m オプションを指定して db2look を実行するときに、テスト環境で出力がファイルに保存されており、不整合をすべて確認して修正します。ここでは、不整合の一例のみ示していますが、発生する可能性のある不整合はほかにも多くあります。また、これらの不整合の修正は、ユーザーに一任されており、db2look を再実行して、出力をファイルにリダイレクトし、すべての統計情報の更新が問題なく正常に実行されることを確認します。

例 6:

実稼働環境で SQL0437W rc=1 の警告メッセージが表示され、テスト環境では、表示されない例です。この場合、上記の例に従って問題を再現します。STMTHEAP が同じであることを確認してください。異なっている場合 (例えば、何らかの理由でテスト環境の方が実稼働環境より高いなど)、同じ警告は表示されません。また、これまでに検討してきたほかのパラメーターも重要です。

SQL0437W rc=2 とその他の戻りコードは、同様に同じ方法に従うと再現できます。

SQL0101N および SQL0901N など、そのほかのエラー・メッセージも同じ方法を使用すると再現できます。コンパイラー/オプティマイザー・エリアのトラップでも再現可能です。これは、古いフィックスパック・レベルで、最新のフィックス・パック・レベルを試して、問題を避けることができるかどうかを確認する場合や、ほかの最適化レベルで、問題を一時的に解決できるかどうかを確認するのに非常に便利です。

まとめ

db2look は非常に強力なユーティリティーで、アクセス・プランに関する問題や、ここで説明したコンパイラーに関する問題の再現に使用できます。問題が再現されたら、実稼働環境に影響を与えることなく、最適化レベルを変更したり、レジストリー変数を試したり、統計情報を更新したり、新しいフィックスパック・レベルをテストするなど、パフォーマンスに影響を与える多くの変数をテストすることができます。問題をデバッグしたり、照会パフォーマンスを向上させるために便利なユーティリティーであることが理解いただけたでしょう。


ダウンロード可能なリソース


コメント

コメントを登録するにはサインインあるいは登録してください。

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=60
Zone=Information Management
ArticleID=405908
ArticleTitle=db2look を使用してのオプティマイザーのアクセス・プランの再作成
publish-date=08042005