パッケージ・キャッシュ情報を使用して、パフォーマンス調整の対象候補となるステートメントを識別する

パッケージ・キャッシュ・イベント・モニターをメモリー内のメトリックと一緒に使用すると、パッケージ・キャッシュのどのステートメントの実行コストが高いかを識別できます。実行に長い時間がかかるステートメントを把握できたなら、それらに対してパフォーマンス調整を実行できます。

1. ステートメントがパッケージ・キャッシュから削除される (追い出される) 際に、そうしてステートメントをキャプチャーするためのパッケージ・キャッシュ・イベント・モニターを作成します。
   例えば、EXPENSIVESTMTS というイベント・モニターを作成するには、以下の SQL を使用できます。

   CREATE EVENT MONITOR EXPENSIVESTMTS FOR PACKAGE CACHE WRITE TO UNFORMATTED EVENT TABLE

   このステートメントは、イベント・モニター EXPENSIVESTMTS と同じ名前で、データベースのデフォルト表スペースにある UE 表に書き込むパッケージ・キャッシュ・イベント・モニターを作成します。TABLE table-name 節を使用して、UE 表のデフォルト名をオーバーライドできます。また、IN tablespace-name 節を使用すると、UE 表に使用される表スペースをオーバーライドできます。

   デフォルトでは、パッケージ・キャッシュから追い出されるすべてのステートメントは、パッケージ・キャッシュ・イベント・モニターによってキャプチャーされます。収集される情報量を制限するには、CREATE EVENT MONITOR ステートメントの一部として、収集される情報を制限するオプションを指定できます。詳しくは、CREATE EVENT MONITOR (パッケージ・キャッシュ) ステートメントに関する資料を参照してください。

2. 次に、イベント・モニターを活動状態にします。

   SET EVENT MONITOR EXPENSIVESTMTS STATE 1

   注: デフォルトでは、このイベント・モニターはデータベースの活動化時に自動的に開始されます。AUTOSTART オプションがデフォルトで適用されるためです。ただし、このイベント・モニターは既にアクティブなデータベースで作成されるので、SET EVENT MONITOR コマンドを使用して手動で開始する必要があります。
3. データベースに接続し、パフォーマンス分析の対象となるステートメント、ワークロード、またはアプリケーションを実行します。収集する情報量は、希望に応じて決めることができます。ただし、このタイプのパフォーマンス調整は、対象のアプリケーションやワークロードが定期的に実行される場合に最も効率的になります。その他の場合には、以前に実行されたステートメントに対して加えた調整が、今後実行されるステートメントに対して全く影響を及ぼさないことがあります。
4. データの収集が終了したら、イベント・モニターを非アクティブ化状態にします。

   SET EVENT MONITOR EXPENSIVESTMTS STATE 0

5. EVMON_FORMAT_UE_TO_TABLES プロシージャーを使用して、イベント・モニターによりデータが追加された UE 表からデータを抽出します。

   CALL EVMON_FORMAT_UE_TO_TABLES ('PKGCACHE', NULL, NULL, NULL, NULL, NULL,
   	NULL, -1, 'SELECT * FROM EXPENSIVESTMTS')

   このプロシージャーでは、イベント・モニターによって生成された UE 表 TRACKSTMTS を調べます。 UE 表のすべてのレコードが選択され、その中から、パッケージ・キャッシュ・イベント・モニターによって収集されたデータに基づいて以下の 2 つのリレーショナル表が作成されます。

   • PKGCACHE_EVENT
   • PCKCACHE_METRICS

   1 番目の表には、キャプチャーされた各イベントと関連する、最も頻繁に使用されるモニター・エレメントとメトリックが含まれます。2 番目の表には、各イベントの詳細メトリックが入っています。

   注: PKGCACHE_METRICS の列に入っている値は、PKGCACHE_EVENT 表の METRICS 列に含まれている XML 文書にもあります。列指向のアクセスを行うためにより便利な方法として、PKGCACHE_METRICS 表でも提供されています。
6. イベント・モニターからの出力を照会し、実行により時間のかかったステートメントを判別します。この例では、合計 CPU 時間 (total_cpu_time - 合計 CPU 時間 : モニター・エレメント) が全体のコストを判別するために使用される消費時間モニター・エレメントです。

   WITH STMTS AS 
        (
       ┌ SELECT SUM(TOTAL_CPU_TIME) AS TOTAL_CPU_TIME, EXECUTABLE_ID, VARCHAR(STMT_TEXT, 3000) AS STMT_TEXT
    1  │ FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-2)) AS T
       └ GROUP BY EXECUTABLE_ID, VARCHAR(STMT_TEXT, 3000)
        UNION ALL
       ┌ SELECT SUM(TOTAL_CPU_TIME) AS TOTAL_CPU_TIME, EXECUTABLE_ID, VARCHAR(STMT_TEXT, 3000) AS STMT_TEXT
    2  │ FROM PKGCACHE_EVENT E, PKGCACHE_METRICS M WHERE E.XMLID = M.XMLID
       └ GROUP BY EXECUTABLE_ID, VARCHAR(STMT_TEXT, 3000) 
        )
   SELECT SUM(TOTAL_CPU_TIME) AS TOTAL_CPU_TIME, STMT_TEXT, EXECUTABLE_ID
   FROM STMTS
   GROUP BY EXECUTABLE_ID, STMT_TEXT
   ORDER BY TOTAL_EXEC_TIME DESC
   FETCH FIRST 10 ROWS ONLY;

   前述の例の場合、MON_GET_PKG_CACHE_STMT 表関数によって戻されたデータ ( 1  参照) と、パッケージ・キャッシュ・イベント・モニターによって戻されたデータ ( 2  参照) の両方が取り出されます。 両方のデータ・セットを調べると、依然としてパッケージ・キャッシュ内に存在するステートメントのデータと、パッケージ・キャッシュから既に追い出されたステートメントのデータの両方を把握できます。 これにより、どのステートメントの実行コストが高いかを評価する際に、ある特定の期間に実行されたすべてのステートメントを考慮に入れることができます。

   この照会は、以下の結果を返します。

   TOTAL_CPU_TIME       STMT_TEXT                      EXECUTABLE_ID
   -------------------- ------------------------------ -------------------------------------------------------------------
                 656250 CALL EVMON_FORMAT_UE_TO_TABLES x'0100000000000000070000000000000000000000020020101207125759221000'
   SQL0445W  Value "WITH STMTS AS ( SELECT SUM(TOTAL_CPU_TIME) AS TOTAL_CPU_TIME" has been truncated.  SQLSTATE=01004
                 500000 CALL XSR_COMPLETE(?,?,NULL,1)  x'0100000000000000160000000000000000000000020020101207125801112004'
                 156250 CALL XSR_ADDSCHEMADOC(?,?,?,?, x'0100000000000000090000000000000000000000020020101207125759877000'
                 156250 CREATE INDEX PKGCACHE_EVENT_IN x'0100000000000000120000000000000000000000020020101207125800565003'
                  93750 CALL XSR_REGISTER(?,?,?,?, NUL x'0100000000000000080000000000000000000000020020101207125759643000'
                  93750 CALL XDB_DECOMP_XML_FROM_QUERY x'0100000000000000180000000000000000000000020020101207125801862001'
                  78125 CREATE INDEX PKGCACHE_METRICS_ x'0100000000000000140000000000000000000000020020101207125800924000'
                  46875 CREATE EVENT MONITOR EXPENSIVE x'0100000000000000010000000000000000000000020020101207125758299000'
                  46875 SET EVENT MONITOR EXPENSIVESTM x'0100000000000000050000000000000000000000020020101207125758768001'
                  46875 CALL SYSPROC.SYSINSTALLOBJECTS x'0100000000000000240000000000000000000000020020101207125936286002'
   
     10 record(s) selected with 1 warning messages printed.

   注: 見やすく表示するために、STMT_TEXT 列は省略されています。