Overview
The GV$SQL_PLAN_MONITOR view displays the operation-level statistics for slow queries on all OBServer nodes under all tenants. Statistical information is provided for each operation of each slow query.
Columns
Field |
Type |
Nullable |
Description |
|---|---|---|---|
| CON_ID | bigint(20) | NO | Tenant ID. |
| REQUEST_ID | bigint(20) | NO | The ID of the request. |
| KEY | bigint(0) unsigned | NO | Foreign key, used to perform join queries with virtual tables related to SQL_MONITOR. |
| STATUS | varchar(19) | NO | Operator execution status:
|
| SVR_IP | varchar(46) | NO | The IP address of the server where the operator resides. |
| SVR_PORT | bigint(20) | NO | Port number of the server where the operator resides. |
| TRACE_ID | varchar(64) | NO | The trace ID of the operator. |
| DB_TIME | bigint(20) | NO | The actual CPU time consumed by this operator. |
| USER_IO_WAIT_TIME | bigint(20) | NO | The duration of IO wait when fetching intermediate-layer indexes and data microblocks. |
| OTHER_WAIT_TIME | bigint(0) unsigned | NO | Waiting time for other events. |
| FIRST_REFRESH_TIME | timestamp(6) | YES | The time when the operator starts monitoring. |
| LAST_REFRESH_TIME | timestamp(6) | YES | End time for operator monitoring. |
| FIRST_CHANGE_TIME | timestamp(6) | YES | The time when the operator outputs the first row of data. |
| LAST_CHANGE_TIME | timestamp(6) | YES | Time when the operator outputs the last row of data. |
| REFRESH_COUNT | bigint(0) unsigned | NO | Number of statistics refreshes. |
| SID | bigint(0) unsigned | NO | The ID of the session. |
| PROCESS_NAME | bigint(20) | NO | Execution thread ID. |
| SQL_ID | varchar(32) | NO | SQL ID. |
| SQL_EXEC_START | bigint(0) unsigned | NO | The time when the SQL statement starts to be executed. |
| SQL_EXEC_ID | bigint(0) unsigned | NO | SQL execution ID. |
| SQL_PLAN_HASH_VALUE | bigint(0) unsigned | NO | The HASH value of the SQL plan. |
| SQL_CHILD_ADDRESS | varbinary(8) | NO | The default value is NULL. |
| PLAN_PARENT_ID | bigint(0) unsigned | NO | Parent operator ID. |
| PLAN_LINE_ID | bigint(20) | NO | The default value is NULL. |
| PLAN_OPERATION | varchar(128) | NO | Operator name. |
| 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 an operator in the plan tree. |
| PLAN_POSITION | bigint(0) unsigned | NO | The operator indicates which child node the parent node is. |
| PLAN_COST | bigint(0) unsigned | NO | The operator cost calculated by the optimizer. |
| PLAN_CARDINALITY | bigint(0) unsigned | NO | The number of data rows output by the operator, as calculated by the optimizer. |
| PLAN_BYTES | bigint(0) unsigned | NO | The number of bytes of output data estimated by the optimizer. |
| PLAN_TIME | bigint(0) unsigned | NO | The operator execution time 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 CPU cost of an operator estimated by the optimizer. |
| PLAN_IO_COST | bigint(0) unsigned | NO | The estimated I/O cost of an operator as calculated by the optimizer. |
| PLAN_TEMP_SPACE | bigint(0) unsigned | NO | The estimated operator space usage as estimated by the optimizer. |
| STARTS | bigint(20) | NO | The number of times the operator has been rescanned. |
| OUTPUT_ROWS | bigint(20) | NO | Total rows output by the operator (the cumulative sum of rows processed by all execution instances of this operator). |
| IO_INTERCONNECT_BYTES | bigint(0) unsigned | NO | The number of data bytes 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 requested for reading 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 written in I/O requests issued by the operator. |
| WORKAREA_MEM | bigint(20) unsigned | NO | The memory occupied by the operator's work area. |
| WORKAREA_MAX_MEM | bigint(20) unsigned | NO | The maximum memory of the work area that an operator can occupy. |
| 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 an operator can occupy. |
| OTHERSTAT_GROUP_ID | bigint(0) unsigned | NO | The default value is NULL. |
| OTHERSTAT_1_ID | bigint(20) | NO | Query field details by field ID. |
| OTHERSTAT_1_TYPE | bigint(0) unsigned | NO | Reserved. |
| OTHERSTAT_1_VALUE | bigint(20) | NO | For the TABLE SCAN operator, indicates the number of bytes read by I/O. IO_READ_BYTES. |
| OTHERSTAT_2_ID | bigint(20) | NO | Query field details by field ID. |
| OTHERSTAT_2_TYPE | bigint(0) unsigned | NO | Reserved. |
| OTHERSTAT_2_VALUE | bigint(20) | NO | For the TABLE SCAN operator, indicates the number of bytes read by the SSStore. SSSTORE_READ_BYTES. |
| OTHERSTAT_3_ID | bigint(20) | NO | Query field details by field ID. |
| OTHERSTAT_3_TYPE | bigint(0) unsigned | NO | Reserved. |
| OTHERSTAT_3_VALUE | bigint(20) | NO | The total number of rows within the specified range read from the major SSTable on the pushdown or non-pushdown path. |
| OTHERSTAT_4_ID | bigint(20) | NO | Query field details by field ID. |
| OTHERSTAT_4_TYPE | bigint(0) unsigned | NO | Reserved. |
| OTHERSTAT_4_VALUE | bigint(20) | NO | The number of rows in the minor SSTable, mini SSTable, and MemTable read from the pushdown or non-pushdown path. |
| OTHERSTAT_5_ID | bigint(20) | NO | Reserved. |
| OTHERSTAT_5_TYPE | bigint(0) unsigned | NO | Reserved. |
| OTHERSTAT_5_VALUE | bigint(20) | NO | The number of microblocks opened under the pushdown path. |
| OTHERSTAT_6_ID | bigint(20) | NO | Reserved. |
| OTHERSTAT_6_TYPE | bigint(0) unsigned | NO | Reserved. |
| OTHERSTAT_6_VALUE | bigint(20) | NO | The total number of rows within the range that are opened in microblocks under the pushdown path. |
| OTHERSTAT_7_ID | bigint(20) | NO | Reserved. |
| OTHERSTAT_7_TYPE | bigint(0) unsigned | NO | Reserved. |
| OTHERSTAT_7_VALUE | bigint(20) | NO | Total rows filtered by pushdown/non-pushdown filters. |
| OTHERSTAT_8_ID | bigint(20) | NO | Reserved. |
| OTHERSTAT_8_TYPE | bigint(0) unsigned | NO | Reserved. |
| OTHERSTAT_8_VALUE | bigint(20) | NO | The number of microblocks that can be skipped based on skip index information without requiring prefetching. |
| 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 | Other structured data that cannot be written into reserved fields but needs to be provided for external use. This data is parsed by external tools. |
| PLAN_OPERATION_INACTIVE | bigint(0) unsigned | NO | The default value is 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 | In vectorized mode, the total number of rows that do not need to be calculated during operator execution (i.e., the total number of filtered rows). |
| PROFILE | varchar(65535) | NO | Displays the PROFILE metric of the SQL PLAN MONITOR extended monitoring. |
You can query the V$SQL_MONITOR_STATNAME view for detailed information about the values in the OTHERSTAT_X_ID field.
Sample query
Query the statistics of slow queries at the operation level on all OBServer nodes under 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)