本記事では、APのパフォーマンス問題を診断および分析する際に使用できる主要なビューについて説明します。
GV$SQL_WORKAREA
ビューの紹介：
SQLの自動メモリ管理によって実行されるすべてのOperatorのworkarea統計情報を表示します。
このビューをクエリすることで、実行計画の各演算子のメモリ使用量やディスクに落ちたデータ量を把握できます。その後、ビューのデータに基づいてテナントのsql_work_areaサイズを適切に調整することで、計画をメモリ内で完全に実行し、クエリ性能を向上させることができます。
よく使われるフィールドの説明：
プロパティ名 |
説明 |
|---|---|
CHILD_NUMBER |
このオペレーターが属するプランID |
OPERATION_TYPE |
物理演算子名 |
OPERATION_ID |
物理演算子番号 |
LAST_MEMORY_USED |
このオペレーターの前回の平均メモリ使用量 |
LAST_DEGREE |
このオペレーターの前回の実行並列度 |
LAST_TEMPSEG_SIZE |
このオペレーターの前回の平均ディスクフラッシュデータ量 |
MAX_TEMPSEG_SIZE |
このオペレーターの全実行回数における最大ディスクフラッシュデータ量 |
使用例：
select CHILD_NUMBER,OPERATION_TYPE,OPERATION_ID,LAST_MEMORY_USED,LAST_DEGREE,MAX_TEMPSEG_SIZE,LAST_TEMPSEG_SIZE from oceanbase.gv$sql_workarea where sql_id='80FAF8DB736A82604D54DD82005238EC';
関連ドキュメント：
以下のドキュメントを参照して、このビューの詳細をご確認ください。
GV$SQL_PLAN_MONITOR
ビューの紹介：
このビューは、すべてのOBServerノードにおけるスロークエリのプランレベルの統計情報を表示します。各スロークエリには統計情報が1件ずつ記録され、そのプランのTrace情報も同時に記録されます。
使用例：
以下の方法で実行し、スローSQLの実行情報を取得して分析に使用します。
- 分析対象のスローSQLにHINT（/*+ monitor */）を追加します。
- HINTを追加した後のスローSQLを実行し、Trace_idを取得して、以下のYxxxxxxxxxに置き換えます。
- 以下のSQLを実行し、返された結果を保存して分析に使用します。
--説明：open_dtはオペレーターがOpenからCloseまでの時間間隔を表します
--説明：row_dtはオペレーターが最初の行を吐き出してからOB_ITER_ENDを吐き出すまでの時間間隔を表します
--説明：値がNULLの場合、最初の行を吐き出したことがないか、OB_ITER_ENDを吐き出したことがないことを意味します
MySQLテナント：
-- 集計
select op_id, op, rows, rescan, threads, (close_time - open_time) open_dt, (last_row_eof_time-first_row_time) row_dt, open_time, close_time, first_row_time, last_row_eof_time FROM
(
select plan_line_id op_id, concat(lpad('', plan_depth, ' '), plan_operation) op, sum(output_rows) rows, 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 oceanbase.gv$sql_plan_monitor where trace_id = 'Yxxxxxxxxx' group by plan_line_id, plan_operation order by plan_line_id
) a;
-- 詳細
select op_id, thread, op, rows, rescan, (close_time - open_time) open_dt, (last_row_eof_time-first_row_time) row_dt, open_time, close_time, first_row_time, last_row_eof_time FROM
(
select plan_line_id op_id, PROCESS_NAME thread, concat(lpad('', plan_depth, ' '), plan_operation) op, output_rows rows, STARTS rescan, first_refresh_time open_time, last_refresh_time close_time, first_change_time first_row_time, last_change_time last_row_eof_time from oceanbase.gv$sql_plan_monitor where trace_id = 'Yxxxxxxxxx' order by plan_line_id, PROCESS_NAME
) a;
Oracleテナント：
--説明：open_dtはオペレーターがOpenからCloseまでの時間間隔を表します
--説明：row_dtはオペレーターが最初の行を吐き出してからOB_ITER_ENDを吐き出すまでの時間間隔を表します
--説明：値がNULLの場合、最初の行を吐き出したことがないか、OB_ITER_ENDを吐き出したことがないことを意味します
-- 集計
select op_id, op, output_rows, rescan,threads ,(close_time - open_time) open_dt, (last_row_eof_time-first_row_time) row_dt, open_time, close_time, first_row_time, last_row_eof_time FROM
(
select plan_line_id op_id, concat(lpad(' ', max(plan_depth), ' '), plan_operation) op, sum(output_rows) output_rows, 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 sys.gv$sql_plan_monitor where trace_id = 'Yxxxxxxxxx' group by plan_line_id, plan_operation,plan_depth order by plan_line_id
) a;
-- 詳細
select op_id, thread, op, output_rows, rescan, (close_time - open_time) open_dt, (last_row_eof_time-first_row_time) row_dt, open_time, close_time, first_row_time, last_row_eof_time FROM
(
select plan_line_id op_id, PROCESS_NAME thread, concat(lpad(' ', plan_depth, ' '), plan_operation) op, output_rows, STARTS rescan, first_refresh_time open_time, last_refresh_time close_time, first_change_time first_row_time, last_change_time last_row_eof_time from sys.gv$sql_plan_monitor where trace_id = 'Yxxxxxxxxx' order by plan_line_id, process_name
) a;
関連ドキュメント：
以下のドキュメントを参照すると、このビューの詳細な情報をさらに詳しく理解できます。
GV$OB_SQL_AUDIT
ビューの紹介：
このビューは、すべてのOBServerノード上で実行された各SQLリクエストの情報を表示するために使用されます。ソース、実行状態、リソース消費量、待機時間などの情報に加え、SQLテキストや実行計画などの重要な情報も記録されています。このビューはテナントごとに分割されており、システムテナントを除く他のテナントでは、テナント間でのクエリはできません。
使用例：
GV$OB_SQL_AUDITビューを使用することで、SQL実行のさまざまな観点からの情報を容易に照会できます。
実行時間が100ミリ秒を超えるSQLを照会します。
select request_id,usec_to_time(request_time),ELAPSED_TIME,QUEUE_TIME,EXECUTE_TIME,query_sql
from v$OB_SQL_AUDIT where ELAPSED_TIME > 100000 limit 10;
+------------+----------------------------+--------------+------------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| request_id | usec_to_time(request_time) | ELAPSED_TIME | QUEUE_TIME | EXECUTE_TIME | query_sql |
+------------+----------------------------+--------------+------------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1538599798 | 2023-03-08 11:00:46.089711 | 335152 | 462 | 329196 | select request_id,usec_to_time(request_time),ELAPSED_TIME,QUEUE_TIME,EXECUTE_TIME,query_sql from v$OB_SQL_AUDIT where ELAPSED_TIME > 100000 limit 10 |
| 1538601580 | 2023-03-08 11:00:47.411316 | 276913 | 1420 | 275345 | select request_id,usec_to_time(request_time),ELAPSED_TIME,QUEUE_TIME,EXECUTE_TIME,query_sql from v$OB_SQL_AUDIT where ELAPSED_TIME > 100000 limit 10 |
| 1538603976 | 2023-03-08 11:00:49.258464 | 154873 | 461 | 154236 | select request_id,usec_to_time(request_time),ELAPSED_TIME,QUEUE_TIME,EXECUTE_TIME,query_sql from v$OB_SQL_AUDIT where ELAPSED_TIME > 100000 limit 10 |
| 1538613501 | 2023-03-08 11:00:56.123111 | 188973 | 688 | 188144 | select request_id,usec_to_time(request_time),ELAPSED_TIME,QUEUE_TIME,EXECUTE_TIME,query_sql from v$OB_SQL_AUDIT where ELAPSED_TIME > 100000 limit 10 |
| 1538712684 | 2023-03-08 11:02:07.504777 | 288516 | 1137 | 287180 | select request_id,usec_to_time(request_time),ELAPSED_TIME,QUEUE_TIME,EXECUTE_TIME,query_sql from v$OB_SQL_AUDIT where ELAPSED_TIME > 100000 limit 10 |
| 1538743161 | 2023-03-08 11:02:29.135127 | 289585 | 26 | 289380 | select request_id,usec_to_time(request_time),ELAPSED_TIME,QUEUE_TIME,EXECUTE_TIME,query_sql from v$OB_SQL_AUDIT where ELAPSED_TIME > 100000 limit 10 |
| 1538749786 | 2023-03-08 11:02:33.890317 | 294356 | 45 | 294180 | select request_id,usec_to_time(request_time),ELAPSED_TIME,QUEUE_TIME,EXECUTE_TIME,query_sql from v$OB_SQL_AUDIT where ELAPSED_TIME > 100000 limit 10 |
| 1538792259 | 2023-03-08 11:03:04.626596 | 192843 | 128 | 192569 | select request_id,usec_to_time(request_time),ELAPSED_TIME,QUEUE_TIME,EXECUTE_TIME,query_sql from v$OB_SQL_AUDIT where ELAPSED_TIME > 100000 limit 10 |
| 1538799117 | 2023-03-08 11:03:09.567622 | 201594 | 55 | 201388 | select request_id,usec_to_time(request_time),ELAPSED_TIME,QUEUE_TIME,EXECUTE_TIME,query_sql from v$OB_SQL_AUDIT where ELAPSED_TIME > 100000 limit 10 |
| 1538804299 | 2023-03-08 11:03:13.274090 | 235720 | 241 | 235302 | select request_id,usec_to_time(request_time),ELAPSED_TIME,QUEUE_TIME,EXECUTE_TIME,query_sql from v$OB_SQL_AUDIT where ELAPSED_TIME > 100000 limit 10 |
+------------+----------------------------+--------------+------------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.28 sec)
直近1000件のSQLの平均待ち時間を照会します。
select /*+ query_timeout(30000000) */ avg(queue_time) from v$OB_SQL_AUDIT
where request_id > (select max(request_id) from v$OB_SQL_AUDIT) - 1000 ;
+-----------------+
| avg(queue_time) |
+-----------------+
| 350.8740 |
+-----------------+
1 row in set (0.26 sec)
テナントのリソースを最も多く消費しているSQLを照会し、実行時間 × 実行回数の降順で並べ替えます。該当するテナントで現在容量不足（テナントCPU利用率が飽和状態）が発生している場合、このステートメントを用いてSQLが問題の原因であるかどうか、また疑わしいSQLを特定できます。
obclient>
select SQL_ID,
avg(ELAPSED_TIME),
avg(QUEUE_TIME),
avg(ROW_CACHE_HIT + BLOOM_FILTER_CACHE_HIT + BLOCK_CACHE_HIT + DISK_READS) avg_logical_read,
avg(execute_time) avg_exec_time,
count(*) cnt,
avg(execute_time - TOTAL_WAIT_TIME_MICRO ) avg_cpu_time,
avg( TOTAL_WAIT_TIME_MICRO ) avg_wait_time,
WAIT_CLASS,
avg(retry_cnt)
from v$OB_SQL_AUDIT
group by 1
order by avg_exec_time * cnt desc limit 10;
+----------------------------------+-------------------+-----------------+------------------+---------------+--------+--------------+---------------+------------+----------------+
| SQL_ID | avg(ELAPSED_TIME) | avg(QUEUE_TIME) | avg_logical_read | avg_exec_time | cnt | avg_cpu_time | avg_wait_time | WAIT_CLASS | avg(retry_cnt) |
+----------------------------------+-------------------+-----------------+------------------+---------------+--------+--------------+---------------+------------+----------------+
| 2705182A6EAB699CEC8E59DA80710B64 | 54976.9269 | 43.8605 | 17664.2727 | 54821.5828 | 11759 | 54821.5828 | 0.0000 | OTHER | 0.0000 |
| 32AB97A0126F566064F84DDDF4936F82 | 1520.9832 | 380.7903 | 63.7847 | 789.6781 | 63632 | 789.6781 | 0.0000 | OTHER | 0.0000 |
| A5F514E873BE9D1F9A339D0DA7481D69 | 44032.5553 | 44.5149 | 8943.7834 | 43878.1405 | 1039 | 43878.1405 | 0.0000 | OTHER | 0.0000 |
| 31FD78420DB07C11C8E3154F1658D237 | 7769857.0000 | 35.7500 | 399020.7500 | 7769682.7500 | 4 | 7769682.7500 | 0.0000 | NETWORK | 1.0000 |
| C48AEE941D985D8DEB66892228D5E845 | 8528.6227 | 0.0000 | 0.0000 | 8450.4047 | 1601 | 8450.4047 | 0.0000 | OTHER | 0.0000 |
| 101B7B79DFA9AE801BEE4F1A234AD294 | 158.2296 | 41.7211 | 0.0000 | 46.0345 | 286758 | 46.0345 | 0.0000 | OTHER | 0.0000 |
| 1D0BA376E273B9D622641124D8C59264 | 1774.5924 | 0.0049 | 0.0000 | 1737.4885 | 5081 | 1737.4885 | 0.0000 | OTHER | 0.0000 |
| 64CF75576816DB5614F3D5B1F35B1472 | 1801.8767 | 747.0343 | 0.0000 | 827.1674 | 10340 | 827.1674 | 0.0000 | OTHER | 0.0000 |
| 23D1C653347BA469396896AD9B20DCA1 | 5564.9419 | 0.0000 | 0.0000 | 5478.2228 | 1257 | 5478.2228 | 0.0000 | OTHER | 0.0000 |
| FA4F493FA5CE2DCC64F51CF3754F96C6 | 2478.3956 | 378.7557 | 3.1040 | 1731.1802 | 3357 | 1731.1802 | 0.0000 | OTHER | 0.0000 |
+----------------------------------+-------------------+-----------------+------------------+---------------+--------+--------------+---------------+------------+----------------+
10 rows in set (1.34 sec)
説明
- テナントのジッターが発生した場合、一般的に「テナントCPUがフル稼働」および「すべてのSQLのRTが急上昇」という症状が見られます。この際、まず判断すべきはSQLのRT急上昇が第一現場であるかどうかです（SQL自体の問題がRT急上昇を引き起こしたのか、それとも他の問題がSQLのRT急上昇を引き起こしたのか）。
- 上記で紹介したこのSQLは非常に有効なツールです。SQL_IDに基づいて集計し、リソース消費量の降順で並べ替え（リソース消費量はavg_exec_time × cntと見なせます）、上位のSQLに明らかな異常がないか観察することで、第一現場であるかどうかを判断できます。
関連ドキュメント：
以下のドキュメントを参照することで、このビューの詳細な情報をさらに詳しく理解できます。
- GV$OB_SQL_AUDIT（SYSテナント）
- GV$OB_SQL_AUDIT（MySQLテナント）
- GV$OB_SQL_AUDIT（Oracleテナント）
- SQL Audit、このビューの具体的な使用方法を確認する