診断の実践：SQL_PLAN_MONITORを使用したパフォーマンス問題の分析

APシナリオにおけるSQLは、演算子レベルでの実行監視が欠けています。例えば、並列実行タスクがいくつに分割されたか、偏りがあるかどうか、HASH競合が深刻かどうか、実行がハングした場合にどの演算子で停止したかなどの問題です。診断はログに依存しています。これらの問題を解決するため、OceanBaseデータベースはOracle互換ビューGV$SQL_PLAN_MONITORを導入しました。このビューを通じて、各演算子の実行に関する重要な詳細を正確に把握できます。

ビューの紹介

SQL演算子レベルの監視をサポートしており、各行は演算子インスタンスのランタイム監視データに対応しています。一般的な監視データと演算子固有の監視データが含まれています。一般的な監視データには、演算子が処理したデータ行数、openとcloseの時間、使用メモリ量などがあります。演算子固有の監視データには、HASH競合率、GI分割ブロック数、table scanスキャン行数などがあります。

ビューの主要なフィールドの詳細は以下のとおりです。

異なる演算子に対して、OTHERSTAT_N_VALUEには異なる意味を付与し、異なる値を入力することができます。

具体的には、OTHERSTAT_N_IDの値を使用して、V$SQL_MONITOR_STATNAMEテーブルをクエリし、ID列でフィルタリングできます。

パフォーマンス問題分析の例

SQL_PLAN_MONITORは、パフォーマンス問題の分析によく使用されます。

インデックスの最適化不足による問題分析

obclient [SYS]> select output_rows, plan_operation, OTHERSTAT_3_VALUE scans from gv$sql_plan_monitor where trace_id = 'xxx' and plan_operation like '%TABLE_SCAN';
+-------------+--------------------+--------+
| OUTPUT_ROWS | PLAN_OPERATION     | SCANS  |
+-------------+--------------------+--------+
|           5 | PHY_VEC_TABLE_SCAN | 500072 |

上記のSQLを使用すると、table scan演算子がスキャンした行数と出力した行数を照会できます。この2つの数値に大きな差がある場合、例えば上記の例で50万行をスキャンしてわずか5行しか出力していない場合、そのテーブルに適切なインデックスが欠けていることを示しています。

JOINメソッドの選択が最適でない問題分析

一般的にはhash joinを実行すべきですが、実際にはnested-loop joinが生成されています。左テーブルの行数が多い場合、右テーブルを非常に頻繁に再スキャンするため、パフォーマンスが低下します。以下のSQLを使用して、演算子の最大再スキャン回数を照会できます。この値が大きい場合、JOINメソッドの選択が誤っている可能性が高いです。

select max(starts) rescans from gv$sql_plan_monitor where trace_id = 'xxx';

汎用的な分析方法

上記の問題が存在するかどうかを個別に判断するのは少し面倒かもしれませんが、ほとんどの場合、汎用的な方法を直接使用して、プランのどの部分が時間を消費しているかを判断できます。

1. まず、以下のSQLを使用して、このqueryに対応するSQL_PLAN_MONITORレコードを照会します。クエリ結果には、各演算子のopen、close、最初の行の出力、最後の行の出力の時間が含まれます。

   select plan_line_id, concat(lpad(' ', plan_depth, ' '), plan_operation) op, sum(output_rows) rowss, sum(STARTS) rescan, min(first_refresh_time) open_time, max(last_refresh_time) close_time, min(first_change_time) first_row_time, max(last_change_time) last_row_eof_time, count(1) threads from gv$sql_plan_monitor where trace_id = 'xxx' group by plan_line_id, plan_operation, plan_depth order by 1;
   

2. クエリ結果を上から下へと確認します。まず、一番上の0番演算子のLAST_ROW_EOF_TIMEは、CLOSE_TIMEとほぼ等しいはずです。それがhash join演算子であると仮定すると、hash joinの実行時は、まず左側から全データを取得してhashテーブルを構築し、次に右側の子供からデータを取り出して、hashテーブルに対して行ごとにprobeします。そこで、1番演算子、つまりhash joinの左側の子供のLAST_ROW_EOF_TIMEを確認します。

   1. 1番演算子のLAST_ROW_EOF_TIMEが大きい場合、ほとんどの時間がhash joinの左側の部分の実行に費やされていることを意味します。その場合、1番演算子の下のサブプランをさらに確認します。
   2. 1番演算子のLAST_ROW_EOF_TIMEが小さい場合、ほとんどの時間がhash joinの右側の部分の実行およびhashテーブルのprobeに費やされていることを意味します。その場合、0番hash joinの右側の子供のサブプランをさらに確認します。

以下のプランを例に、分析方法を説明します。

まず、トップレベルがmerge join演算子であることがわかります。この演算子は、まず左側の子供から1行を取り出し、次に右側の子供から1行を取り出します。演算子のopen時間は25.792777秒であり、merge joinの左側の子供が最初の行を出力する時間は25.793831秒、merge joinの右側の子供が最初の行を出力する時間は27.572661秒です。これにより、merge joinの右側の部分が最初の行のデータを出力するために1秒以上の時間を費やしていることがわかります。

次に、merge joinの右側の子供を確認すると、sort演算子であることがわかります。この演算子は、下層のすべてのデータを受け取り、ソートした後に上層に出力します。ROWSSフィールドから、この演算子が160万行のデータを受け取り、ソートするのに大量の時間がかかったことがわかります。ソートキーにインデックスを作成してソートを排除することで、パフォーマンスを向上させることができます。

| ==========================================================        |
| |ID|OPERATOR                  |NAME|EST.ROWS|EST.TIME(us)|        |
| ----------------------------------------------------------        |
| |0 |MERGE JOIN                |    |1       |5           |        |
| |1 |├─SORT                    |    |1       |3           |        |
| |2 |│ └─COLUMN TABLE FULL SCAN|T1  |1       |3           |        |
| |3 |└─SORT                    |    |1       |3           |        |
| |4 |  └─TABLE FULL SCAN       |T0  |1       |3           |        |
| ==========================================================        |

select plan_line_id, concat(lpad(' ', plan_depth, ' '), plan_operation) op, sum(output_rows) rowss, sum(STARTS) rescan, min(first_refresh_time) open_time, max(last_refresh_time) close_time, min(first_change_time) first_row_time, max(last_change_time) last_row_eof_time, count(1) threads from gv$sql_plan_monitor where trace_id = 'YC3500BA2DAC4-0006198CC947196A-0-0' group by plan_line_id, plan_operation, plan_depth order by 1;

+--------------+-----------------------+---------+--------+----------------------------+----------------------------+----------------------------+----------------------------+---------+
| PLAN_LINE_ID | OP                    | ROWSS   | RESCAN | OPEN_TIME                  | CLOSE_TIME                 | FIRST_ROW_TIME             | LAST_ROW_EOF_TIME          | THREADS |
+--------------+-----------------------+---------+--------+----------------------------+----------------------------+----------------------------+----------------------------+---------+
|            0 | PHY_MERGE_JOIN        |       0 |      0 | 2024-05-29 16:29:25.792777 | 2024-05-29 16:29:27.664014 | NULL                       | 2024-05-29 16:29:27.664014 |       1 |
|            1 |  PHY_SORT             |       2 |      0 | 2024-05-29 16:29:25.792777 | 2024-05-29 16:29:27.664014 | 2024-05-29 16:29:25.793831 | NULL                       |       1 |
|            2 |   PHY_VEC_TABLE_SCAN  |       2 |      0 | 2024-05-29 16:29:25.792777 | 2024-05-29 16:29:27.664014 | 2024-05-29 16:29:25.793831 | 2024-05-29 16:29:25.793831 |       1 |
|            3 |  PHY_SORT             | 1599984 |      0 | 2024-05-29 16:29:25.792777 | 2024-05-29 16:29:27.664014 | 2024-05-29 16:29:27.572661 | 2024-05-29 16:29:27.664014 |       1 |
|            4 |   PHY_VEC_TABLE_SCAN  | 1599984 |      0 | 2024-05-29 16:29:25.792777 | 2024-05-29 16:29:27.664014 | 2024-05-29 16:29:25.793831 | 2024-05-29 16:29:26.642023 |       1 |
+--------------+-----------------------+---------+--------+----------------------------+----------------------------+----------------------------+----------------------------+---------+

SQL PLAN MONITORを使用してパフォーマンス問題を分析するには、さまざまな演算子の実行方法についてある程度理解しておく必要があることがわかります。以下に一般的な演算子の実行方法をいくつか挙げます。

1. ストリーミング出力。下部層からデータを受信しながら、上部層にデータを出力します。

   limit, merge group by, merge distinct, subplan scan

2. ブロッキング。下部層からすべてのデータを受信した後、上部層にデータを出力します。

   sort, hash group by, hash distinct, material

3. その他

   演算子	実行方法
   merge join/union/intersect/except	2つの子から同時にデータを受信し、左側から先に受信します。
   nested-loop join, subplan filter	最初の子から1行ずつデータを受信し、その後他の子を再スキャンしてデータを受信します。
   hash join/union/intersect/except	左の子からすべてのデータを受信した後、右の子からデータを受信します。

関連ドキュメント

• GV$SQL_PLAN_MONITORビューの詳細については、GV$SQL_PLAN_MONITOR(MySQLモード)およびGV$SQL_PLAN_MONITOR(Oracleモード)をご参照ください。