Note
This view was introduced in OceanBase Database V1.4.
Purpose
The GV$SQL_PLAN_MONITOR view displays the operation-level statistics of slow queries on all OBServer nodes in the current tenant. The statistics of each operation of each slow query include the amount of time consumed by the operation and the number of rows returned.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| CON_ID | NUMBER | NO | The ID of the tenant. |
| REQUEST_ID | NUMBER(38) | NO | The ID of the request. |
| KEY | NUMBER | NO | The foreign key. It can be used for joining virtual tables related to SQL_MONITOR. |
| STATUS | VARCHAR2(19) | NO | The status of the operator. Valid values: EXECUTING: The operator is being executed.DONE(ERROR): An error occurred during the execution of the operator.DONE(FIRST N ROWS): The execution succeeded.DONE(ALL ROWS): The execution succeeded.DONE: The execution was interrupted. |
| SVR_IP | VARCHAR2(46) | NO | The IP address of the OBServer node where the operator is executed. |
| SVR_PORT | NUMBER(38) | NO | The port number of the OBServer node where the operator is executed. |
| TRACE_ID | VARCHAR2(64) | NO | The trace ID of the operator. |
| FIRST_REFRESH_TIME | TIMESTAMP(6) | YES | The time when the monitoring of the operator started. |
| LAST_REFRESH_TIME | TIMESTAMP(6) | YES | The time when the monitoring of the operator ended. |
| FIRST_CHANGE_TIME | TIMESTAMP(6) | YES | The time when the operator output the first row of data. |
| LAST_CHANGE_TIME | TIMESTAMP(6) | YES | The time when the operator output the last row of data. |
| REFRESH_COUNT | NUMBER | NO | The number of times the statistics data was refreshed. |
| SID | NUMBER | NO | The ID of the session. |
| PROCESS_NAME | VARCHAR2(10) | NO | The ID of the thread that executed the operator. |
| SQL_ID | VARCHAR2(13) | NO | The ID of the SQL statement. |
| SQL_EXEC_START | TIMESTAMP(6) | NO | The time when the execution of the SQL statement started. |
| SQL_EXEC_ID | NUMBER | NO | The ID of the SQL statement execution. |
| SQL_PLAN_HASH_VALUE | NUMBER | NO | The hash value of the SQL plan. |
| SQL_CHILD_ADDRESS | RAW(8) | NO | The default value of this column is NULL. |
| PLAN_PARENT_ID | NUMBER | NO | The ID of the parent operator of the plan. |
| PLAN_LINE_ID | NUMBER | NO | The default value of this column is NULL. |
| PLAN_OPERATION | VARCHAR2(30) | NO | The name of the operator. |
| PLAN_OPTIONS | VARCHAR2(30) | NO | The default value of this column is NULL. |
| PLAN_OBJECT_OWNER | VARCHAR2(128) | NO | The default value of this column is NULL. |
| PLAN_OBJECT_NAME | VARCHAR2(128) | NO | The default value of this column is NULL. |
| PLAN_OBJECT_TYPE | VARCHAR2(80) | NO | The default value of this column is NULL. |
| PLAN_DEPTH | NUMBER | NO | The depth of the operator in the plan tree. |
| PLAN_POSITION | NUMBER | NO | The position of the operator among the operators that share the same parent operator. |
| PLAN_COST | NUMBER | NO | The cost of executing the operator. This value is calculated by the optimizer. |
| PLAN_CARDINALITY | NUMBER | NO | The number of rows that will be returned by the execution of the operator. This value is calculated by the optimizer. |
| PLAN_BYTES | NUMBER | NO | The number of bytes that will be returned by the execution of the operator. This value is calculated by the optimizer. |
| PLAN_TIME | NUMBER | NO | The estimated amount of time that the execution of the operator will take. This value is calculated by the optimizer. |
| PLAN_PARTITION_START | VARCHAR2(256) | NO | The default value of this column is NULL. |
| PLAN_PARTITION_STOP | VARCHAR2(256) | NO | The default value of this column is NULL. |
| PLAN_CPU_COST | NUMBER | NO | The estimated CPU cost of executing the operator. This value is calculated by the optimizer. |
| PLAN_IO_COST | NUMBER | NO | The estimated I/O cost of executing the operator. This value is calculated by the optimizer. |
| PLAN_TEMP_SPACE | NUMBER | NO | The space that the operator is estimated to consume. This value is calculated by the optimizer. |
| STARTS | NUMBER | NO | The number of times the operator has been rescanned. |
| OUTPUT_ROWS | NUMBER | NO | The total number of rows returned from all the executions of the operator. |
| IO_INTERCONNECT_BYTES | NUMBER | NO | The number of bytes exchanged between the operator and the storage layer. |
| PHYSICAL_READ_REQUESTS | NUMBER | NO | The total number of I/O read requests sent by the operator. |
| PHYSICAL_READ_BYTES | NUMBER | NO | The total number of bytes in the I/O read requests sent by the operator. |
| PHYSICAL_WRITE_REQUESTS | NUMBER | NO | The total number of bytes that the operator requested to write. |
| PHYSICAL_WRITE_BYTES | NUMBER | NO | The total number of bytes that the operator requested to write. |
| WORKAREA_MEM | NUMBER | NO | The size of the workarea that the operator occupies in the memory. |
| WORKAREA_MAX_MEM | NUMBER | NO | The maximum size of the workarea that the operator can occupy. |
| WORKAREA_TEMPSEG | NUMBER | NO | The dump space that the operator occupies on the disk. |
| WORKAREA_MAX_TEMPSEG | NUMBER | NO | The maximum size of the dump space that the operator can occupy on the disk. |
| OTHERSTAT_GROUP_ID | NUMBER | NO | The default value of this column is NULL. |
| OTHERSTAT_1_ID | NUMBER | NO | The ID of the column. You can query the details of the column based on its ID. |
| OTHERSTAT_1_TYPE | NUMBER | NO | Reserved for future use. |
| OTHERSTAT_1_VALUE | NUMBER | NO | The IO_READ_BYTES value for the TABLE SCAN operator, which indicates the number of bytes read by the I/O request.
Note
|
| OTHERSTAT_2_ID | NUMBER | NO | The ID of the column. You can query the details of the column based on its ID. |
| OTHERSTAT_2_TYPE | NUMBER | NO | Reserved for future use. |
| OTHERSTAT_2_VALUE | NUMBER | NO | The SSSTORE_READ_BYTES value for the TABLE SCAN operator, which indicates the number of bytes read from the SSStore.
Note
|
| OTHERSTAT_3_ID | NUMBER | NO | The ID of the column. You can query the details of the column based on its ID. |
| OTHERSTAT_3_TYPE | NUMBER | NO | Reserved for future use. |
| OTHERSTAT_3_VALUE | NUMBER | NO | The SSSTORE_READ_ROW_COUNT value for the TABLE SCAN operator, which indicates the number of rows read from the SSStore. It can be counted in real time.
Note
|
| OTHERSTAT_4_ID | NUMBER | NO | The ID of the column. You can query the details of the column based on its ID. |
| OTHERSTAT_4_TYPE | NUMBER | NO | Reserved for future use. |
| OTHERSTAT_4_VALUE | NUMBER | NO | The MEMSTORE_READ_ROW_COUNT value for the TABLE SCAN operator, which indicates the number of rows read from the MemStore. It can be counted in real time. |
| OTHERSTAT_5_ID | NUMBER | NO | Reserved for future use. |
| OTHERSTAT_5_TYPE | NUMBER | NO | Reserved for future use. |
| OTHERSTAT_5_VALUE | NUMBER | NO | Reserved for future use. |
| OTHERSTAT_6_ID | NUMBER | NO | Reserved for future use. |
| OTHERSTAT_6_TYPE | NUMBER | NO | Reserved for future use. |
| OTHERSTAT_6_VALUE | NUMBER | NO | Reserved for future use. |
| OTHERSTAT_7_ID | NUMBER | NO | Reserved for future use. |
| OTHERSTAT_7_TYPE | NUMBER | NO | Reserved for future use. |
| OTHERSTAT_7_VALUE | NUMBER | NO | Reserved for future use. |
| OTHERSTAT_8_ID | NUMBER | NO | Reserved for future use. |
| OTHERSTAT_8_TYPE | NUMBER | NO | Reserved for future use. |
| OTHERSTAT_8_VALUE | NUMBER | NO | Reserved for future use. |
| OTHERSTAT_9_ID | NUMBER | NO | Reserved for future use. |
| OTHERSTAT_9_TYPE | NUMBER | NO | Reserved for future use. |
| OTHERSTAT_9_VALUE | NUMBER | NO | Reserved for future use. |
| OTHERSTAT_10_ID | NUMBER | NO | Reserved for future use. |
| OTHERSTAT_10_TYPE | NUMBER | NO | Reserved for future use. |
| OTHERSTAT_10_VALUE | NUMBER | NO | Reserved for future use. |
| OTHER_XML | VARCHAR2(1) | NO | Other structured data that needs to be provided for external access but cannot be written to the existing columns. The data in this column is parsed by external tools. |
| PLAN_OPERATION_INACTIVE | NUMBER | NO | The default value of this column is NULL. |
| OUTPUT_BATCHES | NUMBER(38) | NO | The number of times that the operator calls the get_next_batch operation in vectorized mode. |
| SKIPPED_ROWS_COUNT | NUMBER(38) | NO | The total number of rows that do not need to be calculated during operator calculation in vectorized mode. It is the total number of rows skipped. |
| DB_TIME | NUMBER(38) | NO | The amount of CPU time consumed by the operator. |
| USER_IO_WAIT_TIME | NUMBER(38) | NO | The total amount of wait time of User I/O wait events. |
| OTHER_WAIT_TIME | NULL | NO | The total amount of time spent on other wait events. |
You can query the V$SQL_MONITOR_STATNAME view for the value of the OTHERSTAT_X_ID column.
Sample query
Query the operation-level statistics of slow queries on the current OBServer node in the current tenant.
obclient [SYS]> SELECT * FROM SYS.GV$SQL_PLAN_MONITOR WHERE ROWNUM =1\G
The query result is as follows:
*************************** 1. row ***************************
CON_ID: 1004
REQUEST_ID: 0
KEY: NULL
STATUS: NULL
SVR_IP: xx.xx.xx.xx
SVR_PORT: 2882
TRACE_ID: YBXXXXXXXXXX-000XXXXXXXXXX-0-0
FIRST_REFRESH_TIME: 11-JUN-24 03.43.35.827752 PM
LAST_REFRESH_TIME: 11-JUN-24 03.43.35.828807 PM
FIRST_CHANGE_TIME: 11-JUN-24 03.43.35.827752 PM
LAST_CHANGE_TIME: 11-JUN-24 03.43.35.827752 PM
REFRESH_COUNT: NULL
SID: NULL
PROCESS_NAME: 16838
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: 0
PLAN_OPERATION: PHY_TABLE_SCAN
PLAN_OPTIONS: NULL
PLAN_OBJECT_OWNER: NULL
PLAN_OBJECT_NAME: NULL
PLAN_OBJECT_TYPE: NULL
PLAN_DEPTH: 0
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: 0
OUTPUT_ROWS: 1
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: 33
OTHERSTAT_1_TYPE: NULL
OTHERSTAT_1_VALUE: 0
OTHERSTAT_2_ID: 34
OTHERSTAT_2_TYPE: NULL
OTHERSTAT_2_VALUE: 11911
OTHERSTAT_3_ID: 35
OTHERSTAT_3_TYPE: NULL
OTHERSTAT_3_VALUE: 21
OTHERSTAT_4_ID: 36
OTHERSTAT_4_TYPE: NULL
OTHERSTAT_4_VALUE: 2
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: 0
SKIPPED_ROWS_COUNT: 0
DB_TIME: 78
USER_IO_WAIT_TIME: 0
OTHER_WAIT_TIME: NULL
1 row in set (0.074 sec)