本記事では、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ノードの低速クエリのPlanレベルの統計情報を表示します。各低速クエリに対して1つの統計情報が記録され、同時にそのPlanのTrace情報も記録されます。
使用例：
以下の方法に従って、遅いSQLの実行情報を取得し、分析に使用します。
- 分析対象の遅いSQLにHINT（/*+ monitor */）を追加します。
- HIINTを追加した遅い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実行に関するさまざまな次元の情報を簡単にクエリできます。
実行時間が100msを超える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とみなすことができます)。topのいくつかのSQLに明らかな異常がないか確認することで、最初の発生源かどうかを判断できます。
関連ドキュメント：
このビューの詳細については、以下のドキュメントを参照してください。
- GV$OB_SQL_AUDIT (SYSテナント)
- GV$OB_SQL_AUDIT (MySQLテナント)
- GV$OB_SQL_AUDIT (Oracleテナント)
- SQL Audit。このビューの具体的な使用方法について説明します。