This topic describes the views that you can use to diagnose and analyze analytical processing (AP) performance issues.
GV$SQL_WORKAREA
Overview
The GV$SQL_WORKAREA view displays the workarea statistics of all operators subject to automatic SQL memory management.
You can query this view for the memory usage of each operator in the execution plan and the amount of data flushed to the disk. Based on the data of this view, you can properly modify the sql_work_area parameter for a tenant so that the plan can be completely executed in memory to improve query performance.
Common columns
Column |
Description |
|---|---|
CHILD_NUMBER |
The ID of the plan to which the operator belongs. |
OPERATION_TYPE |
The name of the physical operator. |
OPERATION_ID |
The ID of the physical operator. |
LAST_MEMORY_USED |
The average memory usage of the operator in the last execution. |
LAST_DEGREE |
The degree of parallelism (DOP) of the operator in the last execution. |
LAST_TEMPSEG_SIZE |
The average amount of data flushed to the disk in the last execution of the operator. |
MAX_TEMPSEG_SIZE |
The maximum amount of data flushed to the disk in all executions of the operator. |
Example
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';
References
For more information about this view, see the following topics:
GV$SQL_PLAN_MONITOR
Overview
The GV$SQL_PLAN_MONITOR view displays plan-level statistics about slow queries on all OBServer nodes. Each slow query corresponds to one row in this view. It also records trace information of the plan.
Example
Query the execution information of a slow SQL statement for analysis.
- Add the
/* + monitor */hint to the slow SQL statement. - Execute the slow SQL statement with the hint, find the trace ID, and replace
Yxxxxxxxxxin the following code with the trace ID. - Execute the following SQL statement to store the return result for analysis.
-- Note: `open_dt` indicates the interval from when the operator is opened to when the operator is closed.
-- Note: `row_dt` indicates the interval from when the operator generates the first row to when the operator generates `OB_ITER_END`.
-- Note: If the value of `row_dt` is `NULL`, the operator has not generated the first row or `OB_ITER_END`.
MySQL tenant:
-- Summary
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;
-- Details
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 mode:
-- Note: `open_dt` indicates the interval from when the operator is opened to when the operator is closed.
-- Note: `row_dt` indicates the interval from when the operator generates the first row to when the operator generates `OB_ITER_END`.
-- Note: If the value of `row_dt` is `NULL`, the operator has not generated the first row or `OB_ITER_END`.
-- Summary
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;
-- Details
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;
References
For more information about this view, see the following topics:
- GV$SQL_PLAN_MONITOR (
systenant) - GV$SQL_PLAN_MONITOR (MySQL tenant)
- GV$SQL_PLAN_MONITOR (Oracle tenant)
GV$OB_SQL_AUDIT
Overview
The GV$OB_SQL_AUDIT view displays the statistics about each SQL request on all OBServer nodes, such as the source, execution status, resource usage, and wait time. It also records key information such as the SQL text and execution plans. This view is tenant-specific, and you can query this view of other tenants only from the sys tenant.
Example
You can query the GV$OB_SQL_AUDIT view for execution information of SQL statements in various dimensions.
Query SQL statements whose execution time exceeds 100 ms.
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)
Query the average queuing time of the last 1,000 SQL statements.
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)
Query SQL statements that occupy the most resources of a tenant. The SQL statements are sorted in descending order by the amount of resources occupied, which is calculated by using the following formula: Execution time × Number of executions. If CPU resources of the tenant are fully used, you can use the following statement to check whether the issue is caused by SQL statements and if yes, query the suspicious SQL statements.
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)
Note
- When an SQL response time (RT) jitter occurs in a tenant, the CPU resource of the tenant is fully used and the RT of all SQL statements soars. In this case, you must first determine whether the issue is caused by the SQL statements or other problems.
- The SQL statement described in the preceding example is quite useful. It aggregates executed SQL statements based on
SQL_IDvalues and sorts the statements in descending order by the amount of resources occupied, which is the product ofavg_exec_timemultiplied bycnt. This way, you can check the top SQL statements for exceptions.
References
For more information about this view, see the following topics: