Note
This view is introduced in OceanBase Database V1.4.
Purpose
The GV$SQL_PLAN_MONITOR view displays statistics at the operation level for slow queries on all OBServer nodes in the tenant. For each slow query, statistics are provided for each operation, including the time consumed by the operator and the number of rows returned.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| CON_ID | bigint(20) | NO | The ID of the tenant. |
| REQUEST_ID | bigint(20) | NO | The ID of the request. |
| KEY | bigint(0) unsigned | NO | The foreign key for joining with the related virtual table of SQL_MONITOR. |
| STATUS | varchar(19) | NO | The status of the operator: |
| SVR_IP | varchar(46) | NO | The IP address of the server where the operator is located. |
| SVR_PORT | bigint(20) | NO | The port of the server where the operator is located. |
| TRACE_ID | varchar(64) | NO | The trace ID of the operator. |
| DB_TIME | bigint(20) | NO | The CPU time actually consumed by the operator. |
| USER_IO_WAIT_TIME | bigint(20) | NO | The total time of all user_io events. |
| OTHER_WAIT_TIME | bigint(0) unsigned | NO | The waiting time of other events. |
| FIRST_REFRESH_TIME | timestamp(6) | YES | The start time of monitoring for the operator. |
| LAST_REFRESH_TIME | timestamp(6) | YES | The end time of monitoring for the operator. |
| FIRST_CHANGE_TIME | timestamp(6) | YES | The time when the operator outputs the first row of data. |
| LAST_CHANGE_TIME | timestamp(6) | YES | The time when the operator outputs the last row of data. |
| REFRESH_COUNT | bigint(0) unsigned | NO | The number of times that data is refreshed. |
| SID | bigint(0) unsigned | NO | The session ID. |
| PROCESS_NAME | bigint(20) | NO | The ID of the execution thread. |
| SQL_ID | varchar(32) | NO | The ID of the SQL statement. |
| SQL_EXEC_START | bigint(0) unsigned | NO | The start time of the execution of the SQL statement. |
| SQL_EXEC_ID | bigint(0) unsigned | NO | The execution ID of the SQL statement. |
| SQL_PLAN_HASH_VALUE | bigint(0) unsigned | NO | The hash value of the SQL plan. |
| SQL_CHILD_ADDRESS | varbinary(8) | NO | The address of the child SQL statement. The default value is NULL. |
| PLAN_PARENT_ID | bigint(0) unsigned | NO | The ID of the parent operator. |
| PLAN_LINE_ID | bigint(20) | NO | The default value is NULL. |
| PLAN_OPERATION | varchar(128) | NO | The name of the operator. |
| PLAN_OPTIONS | varchar(30) unsigned | NO | The default value is NULL. |
| PLAN_OBJECT_OWNER | varchar(128) | NO | The default value is NULL. |
| PLAN_OBJECT_NAME | varchar(128) | NO | The default value is NULL. |
| PLAN_OBJECT_TYPE | varchar(80) | NO | The default value is NULL. |
| PLAN_DEPTH | bigint(20) | NO | The depth of the operator in the plan tree. |
| PLAN_POSITION | bigint(0) unsigned | NO | The operator is the nth child of its parent. |
| PLAN_COST | bigint(0) unsigned | NO | The cost of the operator calculated by the optimizer. |
| PLAN_CARDINALITY | bigint(0) unsigned | NO | The number of rows of data output by the operator calculated by the optimizer. |
| PLAN_BYTES | bigint(0) unsigned | NO | The estimated number of bytes of data output by the operator calculated by the optimizer. |
| PLAN_TIME | bigint(0) unsigned | NO | The execution time of the operator calculated by the optimizer. |
| PLAN_PARTITION_START | bigint(0) unsigned | NO | The default value is NULL. |
| PLAN_PARTITION_STOP | bigint(0) unsigned | NO | The default value is NULL. |
| PLAN_CPU_COST | bigint(0) unsigned | NO | The estimated CPU cost of the operator calculated by the optimizer. |
| PLAN_IO_COST | bigint(0) unsigned | NO | The estimated IO cost of the operator calculated by the optimizer. |
| PLAN_TEMP_SPACE | bigint(0) unsigned | NO | The estimated space occupied by the operator calculated by the optimizer. |
| STARTS | bigint(20) | NO | The number of times that the operator is rescanned. |
| OUTPUT_ROWS | bigint(20) | NO | The total number of rows of data output by the operator (the sum of the number of rows of data output by all execution instances of the operator). |
| IO_INTERCONNECT_BYTES | bigint(0) unsigned | NO | The number of bytes of data exchanged between the operator and the storage layer. |
| PHYSICAL_READ_REQUESTS | bigint(0) unsigned | NO | The number of I/O read requests issued by the operator. |
| PHYSICAL_READ_BYTES | bigint(0) unsigned | NO | The number of bytes of data read by the operator. |
| PHYSICAL_WRITE_REQUESTS | bigint(0) unsigned | NO | The number of I/O write requests issued by the operator. |
| PHYSICAL_WRITE_BYTES | bigint(0) unsigned | NO | The number of bytes of data written by the operator. |
| WORKAREA_MEM | bigint(20) unsigned | NO | The memory occupied by the operator in the work area. |
| WORKAREA_MAX_MEM | bigint(20) unsigned | NO | The maximum memory that the operator can occupy in the work area. |
| WORKAREA_TEMPSEG | bigint(20) unsigned | NO | The disk dump space occupied by the operator. |
| WORKAREA_MAX_TEMPSEG | bigint(20) unsigned | NO | The maximum disk dump space that the operator can occupy. |
| OTHERSTAT_GROUP_ID | bigint(0) unsigned | NO | The default value is NULL. |
| OTHERSTAT_1_ID | bigint(20) | NO | The ID of the field. |
| OTHERSTAT_1_TYPE | bigint(0) unsigned | NO | Reserved. |
| OTHERSTAT_1_VALUE | bigint(20) | NO | For a TABLE SCAN operator, the number of bytes read by the operator IO_READ_BYTES.
Note
|
| OTHERSTAT_2_ID | bigint(20) | NO | The ID of the field. |
| OTHERSTAT_2_TYPE | bigint(0) unsigned | NO | Reserved. |
| OTHERSTAT_2_VALUE | bigint(20) | NO | For a TABLE SCAN operator, the number of bytes read by the SSStore SSSTORE_READ_BYTES.
Note
|
| OTHERSTAT_3_ID | bigint(20) | NO | The ID of the field. |
| OTHERSTAT_3_TYPE | bigint(0) unsigned | NO | Reserved. |
| OTHERSTAT_3_VALUE | bigint(20) | NO | For a TABLE SCAN operator, the number of rows read by the SSStore SSSTORE_READ_ROW_COUNT. The real-time statistics feature is supported.
Note
|
| OTHERSTAT_4_ID | bigint(20) | NO | The ID of the field. |
| OTHERSTAT_4_TYPE | bigint(0) unsigned | NO | Reserved. |
| OTHERSTAT_4_VALUE | bigint(20) | NO | For a TABLE SCAN operator, the number of rows read by the MEMStore MEMSTORE_READ_ROW_COUNT. The real-time statistics feature is supported. |
| OTHERSTAT_5_ID | bigint(20) | NO | Reserved. |
| OTHERSTAT_5_TYPE | bigint(0) unsigned | NO | Reserved. |
| OTHERSTAT_5_VALUE | bigint(20) | NO | Reserved. |
| OTHERSTAT_6_ID | bigint(20) | NO | Reserved. |
| OTHERSTAT_6_TYPE | bigint(0) unsigned | NO | Reserved. |
| OTHERSTAT_6_VALUE | bigint(20) | NO | Reserved. |
| OTHERSTAT_7_ID | bigint(20) | NO | Reserved. |
| OTHERSTAT_7_TYPE | bigint(0) unsigned | NO | Reserved. |
| OTHERSTAT_7_VALUE | bigint(20) | NO | Reserved. |
| OTHERSTAT_8_ID | bigint(20) | NO | Reserved. |
| OTHERSTAT_8_TYPE | bigint(0) unsigned | NO | Reserved. |
| OTHERSTAT_8_VALUE | bigint(20) | NO | Reserved. |
| OTHERSTAT_9_ID | bigint(20) | NO | Reserved. |
| OTHERSTAT_9_TYPE | bigint(0) unsigned | NO | Reserved. |
| OTHERSTAT_9_VALUE | bigint(20) | NO | Reserved. |
| OTHERSTAT_10_ID | bigint(20) | NO | Reserved. |
| OTHERSTAT_10_TYPE | bigint(0) unsigned | NO | Reserved. |
| OTHERSTAT_10_VALUE | bigint(20) | NO | Reserved. |
| OTHER_XML | varchar(255) | NO | The structured data that cannot be written to the reserved fields but needs to be provided to external tools. The external tools are responsible for parsing the data. |
| PLAN_OPERATION_INACTIVE | bigint(0) unsigned | NO | The default value is NULL. |
| OUTPUT_BATCHES | bigint(20) | NO | The number of times that the operator calls the get_next_batch interface in vectorized mode. |
| SKIPPED_ROWS_COUNT | bigint(20) | NO | The total number of rows of data that do not need to be calculated (filtered out) during operator computation in vectorized mode. |
You can query the values of the OTHERSTAT_X_ID fields from the V$SQL_MONITOR_STATNAME view.
Sample queries
Query the statistics of slow queries at the operation level across all OBServer nodes in all tenants.
obclient [oceanbase]> SELECT * FROM oceanbase.GV$SQL_PLAN_MONITOR limit 1\G
The query result is as follows:
*************************** 1. row ***************************
CON_ID: 1
REQUEST_ID: 0
KEY: NULL
STATUS: NULL
SVR_IP: xx.xx.xx.xx
SVR_PORT: 2882
TRACE_ID: YB42AC1E87C6-000XXXXXXXXXXX-0-0
DB_TIME: 5538
USER_IO_WAIT_TIME: 0
OTHER_WAIT_TIME: NULL
FIRST_REFRESH_TIME: 2024-10-23 15:28:58.252685
LAST_REFRESH_TIME: 2024-10-23 15:28:58.263195
FIRST_CHANGE_TIME: NULL
LAST_CHANGE_TIME: 2024-10-23 15:28:58.262182
REFRESH_COUNT: NULL
SID: NULL
PROCESS_NAME: 10272
SQL_ID: NULL
SQL_EXEC_START: NULL
SQL_EXEC_ID: NULL
SQL_PLAN_HASH_VALUE: NULL
SQL_CHILD_ADDRESS: NULL
PLAN_PARENT_ID: NULL
PLAN_LINE_ID: 8
PLAN_OPERATION: PHY_BLOCK_SAMPLE_SCAN
PLAN_OPTIONS: NULL
PLAN_OBJECT_OWNER: NULL
PLAN_OBJECT_NAME: NULL
PLAN_OBJECT_TYPE: NULL
PLAN_DEPTH: 8
PLAN_POSITION: NULL
PLAN_COST: NULL
PLAN_CARDINALITY: NULL
PLAN_BYTES: NULL
PLAN_TIME: NULL
PLAN_PARTITION_START: NULL
PLAN_PARTITION_STOP: NULL
PLAN_CPU_COST: NULL
PLAN_IO_COST: NULL
PLAN_TEMP_SPACE: NULL
STARTS: 1
OUTPUT_ROWS: 0
IO_INTERCONNECT_BYTES: NULL
PHYSICAL_READ_REQUESTS: NULL
PHYSICAL_READ_BYTES: NULL
PHYSICAL_WRITE_REQUESTS: NULL
PHYSICAL_WRITE_BYTES: NULL
WORKAREA_MEM: NULL
WORKAREA_MAX_MEM: 0
WORKAREA_TEMPSEG: NULL
WORKAREA_MAX_TEMPSEG: 0
OTHERSTAT_GROUP_ID: NULL
OTHERSTAT_1_ID: 36
OTHERSTAT_1_TYPE: NULL
OTHERSTAT_1_VALUE: 0
OTHERSTAT_2_ID: 37
OTHERSTAT_2_TYPE: NULL
OTHERSTAT_2_VALUE: 0
OTHERSTAT_3_ID: 38
OTHERSTAT_3_TYPE: NULL
OTHERSTAT_3_VALUE: 0
OTHERSTAT_4_ID: 39
OTHERSTAT_4_TYPE: NULL
OTHERSTAT_4_VALUE: 0
OTHERSTAT_5_ID: 0
OTHERSTAT_5_TYPE: NULL
OTHERSTAT_5_VALUE: 0
OTHERSTAT_6_ID: 0
OTHERSTAT_6_TYPE: NULL
OTHERSTAT_6_VALUE: 0
OTHERSTAT_7_ID: 0
OTHERSTAT_7_TYPE: NULL
OTHERSTAT_7_VALUE: 0
OTHERSTAT_8_ID: 0
OTHERSTAT_8_TYPE: NULL
OTHERSTAT_8_VALUE: 0
OTHERSTAT_9_ID: 0
OTHERSTAT_9_TYPE: NULL
OTHERSTAT_9_VALUE: 0
OTHERSTAT_10_ID: 0
OTHERSTAT_10_TYPE: NULL
OTHERSTAT_10_VALUE: 0
OTHER_XML: NULL
PLAN_OPERATION_INACTIVE: NULL
OUTPUT_BATCHES: 1
SKIPPED_ROWS_COUNT: 0
1 row in set (0.073 sec)