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 all tenants. 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 tenant ID. |
| 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 resides. |
| SVR_PORT | bigint(20) | NO | The port of the server where the operator resides. |
| TRACE_ID | varchar(64) | NO | The trace ID of the operator. |
| DB_TIME | bigint(20) | NO | The CPU time 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 the operator. |
| LAST_REFRESH_TIME | timestamp(6) | YES | The end time of monitoring the operator. |
| FIRST_CHANGE_TIME | timestamp(6) | YES | The time when the operator returned the first row of data. |
| LAST_CHANGE_TIME | timestamp(6) | YES | The time when the operator returned the last row of data. |
| REFRESH_COUNT | bigint(0) unsigned | NO | The number of times the statistics are 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 SQL statement execution. |
| SQL_EXEC_ID | bigint(0) unsigned | NO | The ID of the SQL statement execution. |
| SQL_PLAN_HASH_VALUE | bigint(0) unsigned | NO | The hash value of the SQL plan. |
| SQL_CHILD_ADDRESS | varbinary(8) | NO | Default value: NULL. |
| PLAN_PARENT_ID | bigint(0) unsigned | NO | The ID of the parent operator. |
| PLAN_LINE_ID | bigint(20) | NO | Default value: NULL. |
| PLAN_OPERATION | varchar(128) | NO | The name of the operator. |
| PLAN_OPTIONS | varchar(30) unsigned | NO | Default value: NULL. |
| PLAN_OBJECT_OWNER | varchar(128) | NO | Default value: NULL. |
| PLAN_OBJECT_NAME | varchar(128) | NO | Default value: NULL. |
| PLAN_OBJECT_TYPE | varchar(80) | NO | Default value: 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 child of its parent operator. |
| 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 number of bytes of data output by the operator estimated 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 | Default value: NULL. |
| PLAN_PARTITION_STOP | bigint(0) unsigned | NO | Default value: NULL. |
| PLAN_CPU_COST | bigint(0) unsigned | NO | The CPU cost of the operator estimated by the optimizer. |
| PLAN_IO_COST | bigint(0) unsigned | NO | The I/O cost of the operator estimated by the optimizer. |
| PLAN_TEMP_SPACE | bigint(0) unsigned | NO | The space occupied by the operator estimated by the optimizer. |
| STARTS | bigint(20) | NO | The number of times 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 in the I/O read requests issued 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 in the I/O write requests issued 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 upper limit of the memory occupied by the operator 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 occupied by the operator. |
| OTHERSTAT_GROUP_ID | bigint(0) unsigned | NO | Default value: 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, which supports real-time statistics.
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, which supports real-time statistics. |
| 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 reserved fields but is required for external use. The external tool is responsible for parsing the data. |
| PLAN_OPERATION_INACTIVE | bigint(0) unsigned | NO | Default value: NULL. |
| OUTPUT_BATCHES | bigint(20) | NO | The number of times 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 are not calculated by the operator in vectorized mode (the total number of rows filtered out). |
The value of the OTHERSTAT_X_ID field can be queried from the V$SQL_MONITOR_STATNAME view.
Sample query
The following example queries the statistics of slow queries at the operation level on 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)
