Note
This view is available starting with V1.4.
Purpose
The V$SQL_PLAN_MONITOR view displays statistics of slow queries in the current tenant on each OBServer node. Each operation has one statistics record.
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 used to join this view with the SQL_MONITOR virtual table. |
| STATUS | VARCHAR2(19) | NO | The execution status of the operator. Valid values: |
| SVR_IP | VARCHAR2(46) | NO | The IP address of the server where the operator is located. |
| SVR_PORT | NUMBER(38) | NO | The port number of the server where the operator is located. |
| TRACE_ID | VARCHAR2(64) | NO | The trace ID of the operator. |
| FIRST_REFRESH_TIME | TIMESTAMP(6) | NO | The time when the operator started to be monitored. |
| LAST_REFRESH_TIME | TIMESTAMP(6) | NO | The time when the operator stopped being monitored. |
| FIRST_CHANGE_TIME | TIMESTAMP(6) | NO | The time when the operator output the first row of data. |
| LAST_CHANGE_TIME | TIMESTAMP(6) | NO | The time when the operator output the last row of data. |
| REFRESH_COUNT | NUMBER | NO | The number of times the statistics were refreshed. |
| SID | NUMBER | NO | The session ID. |
| PROCESS_NAME | VARCHAR2(10) | NO | The thread ID. |
| SQL_ID | VARCHAR2(13) | NO | The SQL ID. |
| SQL_EXEC_START | TIMESTAMP(6) | NO | The time when the SQL statement started to be executed. |
| SQL_EXEC_ID | NUMBER | NO | The SQL execution ID. |
| SQL_PLAN_HASH_VALUE | NUMBER | NO | The hash value of the SQL plan. |
| SQL_CHILD_ADDRESS | RAW(8) | NO | NULL by default. |
| PLAN_PARENT_ID | NUMBER | NO | The ID of the parent operator. |
| PLAN_LINE_ID | NUMBER | NO | NULL by default. |
| PLAN_OPERATION | VARCHAR2(30) | NO | The name of the operator. |
| PLAN_OPTIONS | VARCHAR2(30) | NO | NULL by default. |
| PLAN_OBJECT_OWNER | VARCHAR2(128) | NO | NULL by default. |
| PLAN_OBJECT_NAME | VARCHAR2(128) | NO | NULL by default. |
| PLAN_OBJECT_TYPE | VARCHAR2(80) | NO | NULL by default. |
| PLAN_DEPTH | NUMBER | NO | The depth of the operator in the plan tree. |
| PLAN_POSITION | NUMBER | NO | The position of the operator among its parent's children. |
| PLAN_COST | NUMBER | NO | The cost of the operator calculated by the optimizer. |
| PLAN_CARDINALITY | NUMBER | NO | The number of rows output by the operator, calculated by the optimizer. |
| PLAN_BYTES | NUMBER | NO | The size in bytes of the data output by the operator, calculated by the optimizer. |
| PLAN_TIME | NUMBER | NO | The execution time of the operator, calculated by the optimizer. |
| PLAN_PARTITION_START | VARCHAR2(256) | NO | NULL by default. |
| PLAN_PARTITION_STOP | VARCHAR2(256) | NO | NULL by default. |
| PLAN_CPU_COST | NUMBER | NO | The CPU cost of the operator, calculated by the optimizer. |
| PLAN_IO_COST | NUMBER | NO | The I/O cost of the operator, calculated by the optimizer. |
| PLAN_TEMP_SPACE | NUMBER | NO | The size of the space occupied by the operator, calculated by the optimizer. |
| STARTS | NUMBER | NO | The number of times the operator was rescanned. |
| OUTPUT_ROWS | NUMBER | NO | The total number of rows output by the operator (the sum of the number of rows output by all instances of the operator). |
| IO_INTERCONNECT_BYTES | NUMBER | NO | The size in bytes of the 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 size in bytes of the I/O read requests issued 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 size in bytes of the I/O write requests issued by the operator. |
| WORKAREA_MEM | NUMBER | NO | The size in bytes of the work area occupied by the operator. |
| WORKAREA_MAX_MEM | NUMBER | NO | The maximum size in bytes of the work area that the operator can occupy. |
| WORKAREA_TEMPSEG | NUMBER | NO | The size in bytes of the disk dump space occupied by the operator. |
| WORKAREA_MAX_TEMPSEG | NUMBER | NO | The maximum size in bytes of the disk dump space that the operator can occupy. |
| OTHERSTAT_GROUP_ID | NUMBER | NO | NULL by default. |
| OTHERSTAT_1_ID | NUMBER | NO | Query the detailed information of the field by using the field ID. |
| OTHERSTAT_1_TYPE | NUMBER | NO | Reserved. |
| OTHERSTAT_1_VALUE | NUMBER | NO | For the TABLE SCAN operator, it indicates the number of bytes read from the I/O IO_READ_BYTES.
Note
|
| OTHERSTAT_2_ID | NUMBER | NO | Query the detailed information of the field by using the field ID. |
| OTHERSTAT_2_TYPE | NUMBER | NO | Reserved. |
| OTHERSTAT_2_VALUE | NUMBER | NO | For the TABLE SCAN operator, it indicates the number of bytes read from the SSStore SSSTORE_READ_BYTES.
Note
|
| OTHERSTAT_3_ID | NUMBER | NO | Query the detailed information of the field by using the field ID. |
| OTHERSTAT_3_TYPE | NUMBER | NO | Reserved. |
| OTHERSTAT_3_VALUE | NUMBER | NO | The total number of major SSTables in the range read in the push-down or non-push-down path. |
| OTHERSTAT_4_ID | NUMBER | NO | Query the detailed information of the field by using the field ID. |
| OTHERSTAT_4_TYPE | NUMBER | NO | Reserved. |
| OTHERSTAT_4_VALUE | NUMBER | NO | The number of rows in the minor SSTables, mini SSTables, and MemTables read in the push-down or non-push-down path. |
| OTHERSTAT_5_ID | NUMBER | NO | Reserved. |
| OTHERSTAT_5_TYPE | NUMBER | NO | Reserved. |
| OTHERSTAT_5_VALUE | NUMBER | NO | The number of microblocks opened in the push-down path.
NoteThis field is available starting from V4.5.0 in V4.5.x. |
| OTHERSTAT_6_ID | NUMBER | NO | Reserved. |
| OTHERSTAT_6_TYPE | NUMBER | NO | Reserved. |
| OTHERSTAT_6_VALUE | NUMBER | NO | The total number of rows in the microblocks opened in the push-down path within the range.
NoteThis field is available starting from V4.5.0 in V4.5.x. |
| OTHERSTAT_7_ID | NUMBER | NO | Reserved. |
| OTHERSTAT_7_TYPE | NUMBER | NO | Reserved. |
| OTHERSTAT_7_VALUE | NUMBER | NO | The total number of rows filtered by push-down or non-push-down filters.
NoteThis field is available starting from V4.5.0 in V4.5.x. |
| OTHERSTAT_8_ID | NUMBER | NO | Reserved. |
| OTHERSTAT_8_TYPE | NUMBER | NO | Reserved. |
| OTHERSTAT_8_VALUE | NUMBER | NO | The number of microblocks that do not require pre-fetching and can be accessed by using the Skip Index information.
NoteThis field is available starting from V4.5.0 in V4.5.x. |
| 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 | Structured data that cannot be written to reserved fields but needs to be provided for external use. This data is parsed by external tools. |
| PLAN_OPERATION_INACTIVE | NUMBER | NO | NULL by default. |
| OUTPUT_BATCHES | NUMBER(38) | NO | The number of times the operator calls the get_next_batch interface in the vectorized mode. |
| SKIPPED_ROWS_COUNT | NUMBER(38) | NO | The total number of rows not to be calculated in the operator computation in the vectorized mode (the total number of filtered rows). |
| DB_TIME | NUMBER(38) | NO | The CPU time consumed by the operator. |
| USER_IO_WAIT_TIME | NUMBER(38) | NO | The I/O wait time for accessing the intermediate index and data microblocks. |
| OTHER_WAIT_TIME | NULL | NO | The wait time for other events. |
| PROFILE | VARCHAR2(65535 ) | NO | Displays the extended monitoring metrics of the SQL PLAN MONITOR PROFILE.
NoteThis field is available starting from V4.4.1 in V4.4.x. |
The value of the OTHERSTAT_X_ID column can be queried for detailed information from the V$SQL_MONITOR_STATNAME view.
Sample query
Query the statistics of slow queries at the operation level for the current OBServer node in the current tenant.
obclient [SYS]> SELECT * FROM SYS.V$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-000XXXXXXXXXX-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.025 sec)