Note
This view is introduced in OceanBase Database V1.4.
Purpose
The GV$SQL_PLAN_MONITOR view displays the statistics of slow queries at the operation level in all OBServer nodes of the tenant. For each slow query, statistics are provided for each operation, including the time consumed by operators and the number of rows returned.
Columns
Column |
Type |
Nullable? |
Description |
|---|---|---|---|
| CON_ID | NUMBER | NO | The tenant ID. |
| REQUEST_ID | NUMBER(38) | NO | The ID of the request. |
| KEY | NUMBER | NO | The foreign key for joining with the SQL_MONITOR related virtual table. |
| STATUS | VARCHAR2(19) | NO | The status of the operator: |
| SVR_IP | VARCHAR2(46) | NO | The IP address of the server where the operator resides. |
| SVR_PORT | NUMBER(38) | NO | The port number of the server where the operator resides. |
| TRACE_ID | VARCHAR2(64) | NO | The trace ID of the operator. |
| 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 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 | NUMBER | NO | The number of times the statistics are refreshed. |
| SID | NUMBER | NO | The session ID. |
| PROCESS_NAME | VARCHAR2(10) | NO | The ID of the execution thread. |
| SQL_ID | VARCHAR2(13) | NO | The SQL ID. |
| SQL_EXEC_START | TIMESTAMP(6) | NO | The start time of executing the SQL statement. |
| 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 | Default value is NULL. |
| PLAN_PARENT_ID | NUMBER | NO | The ID of the parent operator. |
| PLAN_LINE_ID | NUMBER | NO | Default value is NULL. |
| PLAN_OPERATION | VARCHAR2(30) | NO | The name of the operator. |
| PLAN_OPTIONS | VARCHAR2(30) | NO | Default value is NULL. |
| PLAN_OBJECT_OWNER | VARCHAR2(128) | NO | Default value is NULL. |
| PLAN_OBJECT_NAME | VARCHAR2(128) | NO | Default value is NULL. |
| PLAN_OBJECT_TYPE | VARCHAR2(80) | NO | Default value is NULL. |
| PLAN_DEPTH | NUMBER | NO | The depth of the operator in the plan tree. |
| PLAN_POSITION | NUMBER | NO | The position of the operator as a child of its parent operator. |
| PLAN_COST | NUMBER | NO | The cost of the operator calculated by the optimizer. |
| PLAN_CARDINALITY | NUMBER | NO | The number of rows of data output by the operator calculated by the optimizer. |
| PLAN_BYTES | NUMBER | NO | The number of bytes of data output by the operator estimated by the optimizer. |
| PLAN_TIME | NUMBER | NO | The execution time of the operator calculated by the optimizer. |
| PLAN_PARTITION_START | VARCHAR2(256) | NO | Default value is NULL. |
| PLAN_PARTITION_STOP | VARCHAR2(256) | NO | Default value is NULL. |
| PLAN_CPU_COST | NUMBER | NO | The CPU cost of the operator estimated by the optimizer. |
| PLAN_IO_COST | NUMBER | NO | The I/O cost of the operator estimated by the optimizer. |
| PLAN_TEMP_SPACE | NUMBER | NO | The space occupied by the operator estimated by the optimizer. |
| STARTS | NUMBER | NO | The number of times the operator is rescanned. |
| OUTPUT_ROWS | NUMBER | NO | The total number of rows output by the operator (the cumulative value of the number of rows output by all instances of the operator). |
| IO_INTERCONNECT_BYTES | NUMBER | NO | The number of bytes of data exchanged between the operator and the storage layer. |
| PHYSICAL_READ_REQUESTS | NUMBER | NO | The number of I/O read requests issued by the operator. |
| PHYSICAL_READ_BYTES | NUMBER | NO | The number of bytes of data read by the operator. |
| PHYSICAL_WRITE_REQUESTS | NUMBER | NO | The number of I/O write requests issued by the operator. |
| PHYSICAL_WRITE_BYTES | NUMBER | NO | The number of bytes of data written by the operator. |
| WORKAREA_MEM | NUMBER | NO | The amount of memory occupied by the operator in the work area. |
| WORKAREA_MAX_MEM | NUMBER | NO | The maximum amount of memory that the operator can occupy in the work area. |
| WORKAREA_TEMPSEG | NUMBER | NO | The amount of disk dump space occupied by the operator. |
| WORKAREA_MAX_TEMPSEG | NUMBER | NO | The maximum amount of disk dump space that the operator can occupy. |
| OTHERSTAT_GROUP_ID | NUMBER | NO | Default value is NULL. |
| OTHERSTAT_1_ID | NUMBER | NO | The ID of the field. |
| OTHERSTAT_1_TYPE | NUMBER | NO | Reserved. |
| OTHERSTAT_1_VALUE | NUMBER | NO | For the TABLE SCAN operator, the number of bytes read from the disk IO_READ_BYTES.
Note
|
| OTHERSTAT_2_ID | NUMBER | NO | The ID of the field. |
| OTHERSTAT_2_TYPE | NUMBER | NO | Reserved. |
| OTHERSTAT_2_VALUE | NUMBER | NO | For the TABLE SCAN operator, the number of bytes read from the SSStore SSSTORE_READ_BYTES.
Note
|
| OTHERSTAT_3_ID | NUMBER | NO | The ID of the field. |
| OTHERSTAT_3_TYPE | NUMBER | NO | Reserved. |
| OTHERSTAT_3_VALUE | NUMBER | NO | For the TABLE SCAN operator, the number of rows read from the SSStore SSSTORE_READ_ROW_COUNT, which supports real-time statistics.
Note
|
| OTHERSTAT_4_ID | NUMBER | NO | The ID of the field. |
| OTHERSTAT_4_TYPE | NUMBER | NO | Reserved. |
| OTHERSTAT_4_VALUE | NUMBER | NO | For the TABLE SCAN operator, the number of rows read from the MEMStore MEMSTORE_READ_ROW_COUNT, which supports real-time statistics. |
| OTHERSTAT_5_ID | NUMBER | NO | Reserved. |
| OTHERSTAT_5_TYPE | NUMBER | NO | Reserved. |
| OTHERSTAT_5_VALUE | NUMBER | NO | Reserved. |
| OTHERSTAT_6_ID | NUMBER | NO | Reserved. |
| OTHERSTAT_6_TYPE | NUMBER | NO | Reserved. |
| OTHERSTAT_6_VALUE | NUMBER | NO | Reserved. |
| OTHERSTAT_7_ID | NUMBER | NO | Reserved. |
| OTHERSTAT_7_TYPE | NUMBER | NO | Reserved. |
| OTHERSTAT_7_VALUE | NUMBER | NO | Reserved. |
| OTHERSTAT_8_ID | NUMBER | NO | Reserved. |
| OTHERSTAT_8_TYPE | NUMBER | NO | Reserved. |
| OTHERSTAT_8_VALUE | NUMBER | NO | Reserved. |
| OTHERSTAT_9_ID | NUMBER | NO | Reserved. |
| OTHERSTAT_9_TYPE | NUMBER | NO | Reserved. |
| OTHERSTAT_9_VALUE | NUMBER | NO | Reserved. |
| OTHERSTAT_10_ID | NUMBER | NO | Reserved. |
| OTHERSTAT_10_TYPE | NUMBER | NO | Reserved. |
| OTHERSTAT_10_VALUE | NUMBER | NO | Reserved. |
| OTHER_XML | VARCHAR2(1) | NO | Other unstructured data that cannot be written to reserved fields but must be provided to external tools. The external tools are responsible for parsing this field. |
| PLAN_OPERATION_INACTIVE | NUMBER | NO | Default value is NULL. |
| OUTPUT_BATCHES | NUMBER(38) | NO | The number of times the operator calls the get_next_batch interface in vectorized mode. |
| SKIPPED_ROWS_COUNT | NUMBER(38) | NO | The total number of rows that do not need to be calculated during operator computation (the total number of filtered rows). |
| DB_TIME | NUMBER(38) | NO | The actual CPU time consumed by the operator. |
| USER_IO_WAIT_TIME | NUMBER(38) | NO | The total time of user_io-related events. |
| OTHER_WAIT_TIME | NULL | NO | The waiting time of other events. |
The value of the OTHERSTAT_X_ID field can be queried from the V$SQL_MONITOR_STATNAME view.
Sample query
Query statistics about slow queries at the operation level on the current OBServer node in the 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: YB42AC1E87C6-000XXXXXXXXX-0-0
FIRST_REFRESH_TIME: 23-OCT-24 03.38.34.767694 PM
LAST_REFRESH_TIME: 23-OCT-24 03.38.34.777200 PM
FIRST_CHANGE_TIME: NULL
LAST_CHANGE_TIME: 23-OCT-24 03.38.34.777200 PM
REFRESH_COUNT: NULL
SID: NULL
PROCESS_NAME: 10352
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
DB_TIME: 5889
USER_IO_WAIT_TIME: 0
OTHER_WAIT_TIME: NULL
1 row in set (0.077 sec)