診断の実践：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が生成されてしまうケースがあります。左テーブルの行数が多い場合、右テーブルを非常に頻繁に再スキャン（rescan）するため、パフォーマンスが低下します。以下のSQLを使用して演算子の最大rescan回数を確認できます。この値が大きい場合、JOIN方法の選択が誤っている可能性が非常に高いです。

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

汎用分析手法

上記の問題が個別に存在するかどうかを一つずつ判断するのはやや面倒です。ほとんどの場合、計画内のどの部分が時間を消費しているかを直接判断する汎用的な方法を使用できます。

1. まず、以下のSQLを使用して、このクエリに対応する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モード)を参照してください。