プロシジャの性能ボトルネックの調査方法

2024年3月23日テクニカルメモ

本記事ではプロシジャの処理が遅い時に、処理時間の確認方法や確認するポイントについて紹介しています。プロシジャの編集を伴う方法をご紹介していますので、事前にバックアップの取得をしておいてください。

対応後に得られる結果イメージ：

11/28/2022 14:30:56 TABLE FILE EMP
11/28/2022 14:30:56 SUM SAL
11/28/2022 14:30:56 BY ENAME
11/28/2022 14:30:56 ON TABLE HOLD FORMAT HTML
11/28/2022 14:30:56 END
11/28/2022 14:30:56 -RUN
11/28/2022 14:30:56 (FOC2689) 集計が終了しました…
11/28/2022 14:30:56 DBMSTRACE SELECT
11/28/2022 14:30:56 DBMSTRACE T1."ENAME",
11/28/2022 14:30:56 DBMSTRACE SUM(T1."SAL")
11/28/2022 14:30:56 DBMSTRACE FROM
11/28/2022 14:30:56 DBMSTRACE SCOTT.EMP T1
11/28/2022 14:30:56 DBMSTRACE GROUP BY
11/28/2022 14:30:56 DBMSTRACE T1."ENAME"
11/28/2022 14:30:56 DBMSTRACE ORDER BY
11/28/2022 14:30:56 DBMSTRACE T1."ENAME";
11/28/2022 14:30:56 0(INF32080) テーブルのレコード数 = 12 行数 = 12
11/28/2022 14:30:57 0 HTML FILE SAVED …
  • プロシジャを処理中の日時（ソース左の日付時間部分）
  • その時に何を処理しているか
  • 発行されたSQLと最適化できなかった場合のメッセージ
  • データを検索したレコード数とレポート上の表示行数

プロシジャの処理時間やSQLを確認する方法

デバッグコマンドを追加する

プロシジャの先頭行に以下のデバッグコマンドを追加します。

-SET &ECHO = ALL;
SET NWTIMESTAMP = ON
SET TRACEOFF    = ALL
SET TRACEON     = STMTRACE//CLIENT
SET TRACEON     = SQLAGGR//CLIENT
SET TRACEUSER   = ON
SET TRACESTAMP  = DLEFT
LET PCHOLD      = HOLD
-RUN

各コマンドの意味の詳細については以下の記事を参考にしてください。
「コマンドでプロシジャの処理時間を計測しよう！」
「WebFOCUSからデータベースに発行されているSQLを確認する」

処理と処理の間に「-RUN」を追加する

プロシジャ内で複数のTABLEリクエストが含まれている場合や、中間ファイル（HOLD）を作成しているような場合は、処理と処理の間に「-RUN」を追加します。

これによって、-RUNを境目にして上下の処理時間をそれぞれ確認することができるようになります。

TABLE FILE EMP
 SUM SAL
 BY ENAME
 ON TABLE HOLD FORMAT BINARY
END
-RUN

TABLE FILE HOLD
 PRINT ENAME SAL
 ON TABLE PCHOLD FORMAT HTML
END
-RUN

※最後の行にも -RUN を入れておきます。

最終出力コマンドの確認

最終出力のコマンドに「ON TABLE PCHOLD FORMAT xxx」が指定されていることを確認します。
「PCHOLD」はブラウザ画面に結果を返すコマンドですが、デバッグコマンドを追記しているため、画面にレポートは返らずデバッグ結果のみを確認できます。

TABLE FILE HOLD
 PRINT ENAME SAL
 ON TABLE PCHOLD FORMAT HTML
END

もしも「-HTMLFORM」といったコマンドを使って、出力後のHTML画面をレイアウト変更している場合は、-HTMLFORM の処理まで行わないようにGOTO文を利用したラベル分岐処理を入れるようにしてください。

以下は「-GOTO :L_SKIP」の行まで処理が進むと「-:L_SKIP」の行に処理を移動させる例です。

-GOTO :L_SKIP
-HTMLFORM BEGIN
<html>
～
</html>
-HTMLFORM END
-:L_SKIP

デバッグコマンドの結果の見方

処理時間の遅いところが無いかを確認する

プロシジャのどの行で時間が掛かっているか確認します。
処理時間は対象コマンドが書かれている行の時間と次行の時間の差を見てください。

▼処理①の開始
mm/dd/yyy hh:mm:ss  TABLE FILE xxx
　　：
mm/dd/yyy hh:mm:ss  END
mm/dd/yyy hh:mm:ss  -RUN
mm/dd/yyy hh:mm:ss  (FOC2689) 集計が終了しました...
mm/dd/yyy hh:mm:ss  DBMSTRACE    SELECT
　　：
mm/dd/yyy hh:mm:ss  DBMSTRACE   ;
mm/dd/yyy hh:mm:ss 0(INF32080) テーブルのレコード数 =    xxxxx  行数 =  xxxxx
▼処理①の終了
mm/dd/yyy hh:mm:ss 0 HTML FILE SAVED ...

「(FOC2590) 次の理由で、集計されませんでした」の表示が無いことを確認する

プロシジャ内で使用する関数やデータ型のフォーマット変換処理などの影響で、最適なSQLをデータベースに発行できていない場合があります。
(FOC2590)が表示されている場合は、以下のポイントを確認してください。

  • 集計（SUM）されているか
    集計のSUMを利用しているのに明細行でデータを取得してしまっている等
    このケースの場合「テーブルのレコード数 = xxxxx 行数 = xxxxx」の件数が不一致になり、WebFOCUS側でデータの再集計を行っています。
  • SQLでJOINされているか
    データベース違いやインスタンス違いのデータに対してJOINを発行している場合に、バインド変数を使った結合処理が行われます。この場合、SQLのWHEREを確認すると、SQLの変数が指定されておりJOINになっていません。キー項目値の数分のSQLが複数回発行されるため性能が大きく劣化します。
  • WHEREが反映されているか
    TABLEリクエストにはWHEREを指定しているのに、SQLにはWHEREが無い場合です。
    SQLに最適化できないWebFOCUS関数を利用している場合等に発生します。
    このケースの場合も「テーブルのレコード数 = xxxxx 行数 = xxxxx」の件数が不一致になり、WebFOCUS側で大量データを取得後にフィルタを掛けていることになります。

SQLが遅いのか、レポート作成が遅いのか区別をつける

SQLトレースで確認できたSQLをWebFOCUSを介さずに直接発行して確認してください。
WebFOCUSサーバ上からデータベース側で用意されているツールを利用してSQLを発行してみることが好ましい確認方法です。

なお、WebFOCUSのプロシジャは実行時にデータベースへのログインとログアウト処理まで行っているため、SQLツールと処理時間が多少異なる場合があります。

別のサーバからも検索してみてパフォーマンスが異なる場合は、ネットワークに依存する可能性もあります。例えば、開発サーバだと早いが本番サーバでは遅いといった場合、開発と本番のデータ量の違いやネットワーク経路の違いを確認します。

SQL処理単体で遅い場合（WebFOCUSを介さない場合でも遅い場合）

データベース側での改善が必要です。
インデックスやView、データマートの検討や見直しを行ってください。

レポート作成が遅い場合（SQLは早いがアウトプットまでが遅い場合）

プロシジャのコーディングを見直すことで改善できる可能性があります。
そもそものレポートサイズが大きい場合は改善が難しい場合もありますが、リアルタイム性を求められるものかを確認して、ReportCasterでのスケジュール実行等も検討してください。

ボトルネックが見当たらない場合

TABLEリクエストの処理数が多くないか？（HOLDファイルの生成が多い）

中間ファイルを大量に作成するような場合、短時間の処理が積み重なって結果的に遅く感じるケースがあります。また、何度もSQLを発行する処理を行っている場合は、一度のSQLで必要なデータを取得してHOLDファイルの二次検索や三次検索で加工を行った方が処理が早くなるケースもあります。

WHEREを指定するタイミングを前にできないか？

中間ファイルを作成するタイミングで全てのWHEREを掛けているかどうかです。
中間ファイルに対してWHEREを掛けてもデータ取得時間は短縮できないため、SQLに反映させられるかがポイントになります。

上記に該当しない場合

サポートセンターに問い合わせを行ってみてください。

サポートの問い合わせ対応でパフォーマンスチューニングの成果保証を行うことはできませんが、ボトルネック箇所の目星をつけるお手伝いは可能です。

お問い合わせ時は、以下のファイルを一緒に添付すると対応がスムーズになります。

  • デバッグの結果（計測時間や発行されたSQLを確認できるもの）
  • 対象のプログラム（プロシジャやHTML等）
  • 利用しているシノニム

テクニカルメモ