Note
- This view is available starting with V4.3.5 for V4.3.x.
- This view is available starting with V4.2.2 for V4.2.x.
Purpose
The DBA_WR_SQLSTAT view displays basic performance statistics of SQL statements executed in the current tenant. The statistics with _DELTA in their names indicate the incremental values from the last WR snapshot collection to the current time.
Columns
Column |
Type |
Nullable |
Description |
|---|---|---|---|
| SNAP_ID | NUMBER(38) | NO | Snapshot ID |
| SVR_IP | VARCHAR2(46) | NO | Node IP |
| SVR_PORT | NUMBER(38) | NO | Node port number |
| SQL_ID | VARCHAR2(32) | NO | SQL statement ID |
| PLAN_HASH | NUMBER(38) | NO | Hash value of the execution plan |
| PLAN_ID | NUMBER(38) | NO | Execution plan ID |
| PLAN_TYPE | NUMBER(38) | NO | For SQL plan caching, this column indicates the type of the plan:
For PL/SQL object caching, this column indicates the type of the PL/SQL object:
|
| MODULE | VARCHAR2(64) | YES | The application module when the statement was first parsed. |
| ACTION | VARCHAR2(64) | YES | The application action when the statement was first parsed. |
| PARSING_DB_ID | NUMBER(38) | NO | The database ID when the statement was parsed. |
| PARSING_DB_NAME | VARCHAR2(128) | NO | The database name when the statement was parsed. |
| PARSING_USER_ID | NUMBER(38) | NO | The user ID when the statement was parsed. |
| EXECUTIONS_TOTAL | NUMBER(38) | NO | The total number of executions of the plan in the plan cache. |
| EXECUTIONS_DELTA | NUMBER(38) | NO | The incremental number of executions of the plan in the plan cache. |
| DISK_READS_TOTAL | NUMBER(38) | NO | The total number of disk reads. |
| DISK_READS_DELTA | NUMBER(38) | NO | The incremental number of disk reads. |
| BUFFER_GETS_TOTAL | NUMBER(38) | NO | The total number of logical reads from the cache. |
| BUFFER_GETS_DELTA | NUMBER(38) | NO | The incremental number of logical reads from the cache. |
| ELAPSED_TIME_TOTAL | NUMBER(38) | NO | The total time consumed by the statement. For parallel execution scenarios, this value is the sum of the time consumed by each execution thread. |
| ELAPSED_TIME_DELTA | NUMBER(38) | NO | The incremental time consumed by the statement. |
| CPU_TIME_TOTAL | NUMBER(38) | NO | The total CPU time consumed. |
| CPU_TIME_DELTA | NUMBER(38) | NO | The incremental CPU time consumed. |
| CCWAIT_TOTAL | NUMBER(38) | NO | The total time consumed by concurrency-related wait events. |
| CCWAIT_DELTA | NUMBER(38) | NO | The incremental time consumed by concurrency-related wait events. |
| USERIO_WAIT_TOTAL | NUMBER(38) | NO | The total time consumed by I/O waits. |
| USERIO_WAIT_DELTA | NUMBER(38) | NO | The incremental time consumed by I/O waits. |
| APWAIT_TOTAL | NUMBER(38) | NO | The total time consumed by application-related wait events. |
| APWAIT_DELTA | NUMBER(38) | NO | The incremental time consumed by application-related wait events. |
| PHYSICAL_READ_REQUESTS_TOTAL | NUMBER(38) | NO | The total number of physical read requests. |
| PHYSICAL_READ_REQUESTS_DELTA | NUMBER(38) | NO | The incremental number of physical read requests. |
| PHYSICAL_READ_BYTES_TOTAL | NUMBER(38) | NO | The total number of bytes read. |
| PHYSICAL_READ_BYTES_DELTA | NUMBER(38) | NO | The incremental number of bytes read. |
| WRITE_THROTTLE_TOTAL | NUMBER(38) | NO | The total time consumed by write throttling when data is written to the MemStore. |
| WRITE_THROTTLE_DELTA | NUMBER(38) | NO | The incremental time consumed by write throttling when data is written to the MemStore. |
| ROWS_PROCESSED_TOTAL | NUMBER(38) | NO | The total number of rows processed by the statement. |
| ROWS_PROCESSED_DELTA | NUMBER(38) | NO | The total number of rows processed by the statement. |
| MEMSTORE_READ_ROWS_TOTAL | NUMBER(38) | NO | The total number of rows read from the MemStore. |
| MEMSTORE_READ_ROWS_DELTA | NUMBER(38) | NO | The total number of rows read from the MemStore. |
| MINOR_SSSTORE_READ_ROWS_TOTAL | NUMBER(38) | NO | The total number of rows read from the MINOR SSStore. |
| MINOR_SSSTORE_READ_ROWS_DELTA | NUMBER(38) | NO | The total number of rows read from the MINOR SSStore. |
| MAJOR_SSSTORE_READ_ROWS_TOTAL | NUMBER(38) | NO | The total number of rows read from the MAJOR SSStore. |
| MAJOR_SSSTORE_READ_ROWS_DELTA | NUMBER(38) | NO | The total number of rows read from the MAJOR SSStore. |
| RPC_TOTAL | NUMBER(38) | NO | The total number of RPC calls. |
| RPC_DELTA | NUMBER(38) | NO | The number of RPC calls. |
| FETCHES_TOTAL | NUMBER(38) | NO | The total number of result set fetches. |
| FETCHES_DELTA | NUMBER(38) | NO | The number of result set fetches. |
| RETRY_TOTAL | NUMBER(38) | NO | The total number of SQL retries. |
| RETRY_DELTA | NUMBER(38) | NO | The number of SQL retries. |
| PARTITION_TOTAL | NUMBER(38) | NO | The total number of partitions scanned by the SQL statement. |
| PARTITION_DELTA | NUMBER(38) | NO | The number of partitions scanned by the SQL statement. |
| NESTED_SQL_TOTAL | NUMBER(38) | NO | The total number of nested SQL statements executed. Each time a sub-SQL statement is executed, the value increases by 1. |
| NESTED_SQL_DELTA | NUMBER(38) | NO | The number of nested SQL statements executed. |
| SOURCE_IP | VARCHAR2(46) | NO | The IP address of the request source. |
| SOURCE_PORT | NUMBER(38) | NO | The port number of the request source. |
| ROUTE_MISS_TOTAL | NUMBER(38) | NO | The total number of times that the request partition was not hit since the OBServer started.
Note
|
| ROUTE_MISS_DELTA | NUMBER(38) | NO | The number of times that the request partition was not hit since the last WR snapshot was collected.
Note
|
| FIRST_LOAD_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | YES | The time when the plan was generated.
Note
|
| PLAN_CACHE_HIT_TOTAL | NUMBER(38) | NO | The total number of times that the Plan Cache was hit since the OBServer started.
Note
|
| PLAN_CACHE_HIT_DELTA | NUMBER(38) | NO | The number of times that the Plan Cache was hit since the last WR snapshot was collected.
Note
|
| MUTI_QUERY_TOTAL | NUMBER | NO | The number of times that the SQL statement was in a multi-query since the execution plan was generated.
NoteFor V4.6.x, this column was introduced in V4.6.0. |
| MUTI_QUERY_DELTA | NUMBER | NO | The number of times that the SQL statement was in a multi-query since the last WR snapshot was collected.
NoteFor V4.6.x, this column was introduced in V4.6.0. |
| MUTI_QUERY_BATCH_TOTAL | NUMBER | NO | The number of times that the SQL batch was executed since the execution plan was generated.
NoteFor V4.6.x, this column was introduced in V4.6.0. |
| MUTI_QUERY_BATCH_DELTA | NUMBER | NO | The number of times the SQL batch has been executed since the last WR snapshot was collected.
NoteFor V4.6.x, this column is available starting from V4.6.0. |
| FULL_TABLE_SCAN_TOTAL | NUMBER | NO | The number of times a full table scan has been performed since the execution plan was generated.
NoteFor V4.6.x, this column is available starting from V4.6.0. |
| FULL_TABLE_SCAN_DELTA | NUMBER | NO | The number of times a full table scan has been performed since the last WR snapshot was collected.
NoteFor V4.6.x, this column is available starting from V4.6.0. |
| ERROR_COUNT_TOTAL | NUMBER | NO | The number of errors since the execution plan was generated.
NoteFor V4.6.x, this column is available starting from V4.6.0. |
| ERROR_COUNT_DELTA | NUMBER | NO | The number of errors since the last WR snapshot was collected.
NoteFor V4.6.x, this column is available starting from V4.6.0. |
| SAMPLE_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | NO | The time when the SQLSTAT snapshot was written to disk. Within a single WR snapshot interval, SQLSTAT may be written to disk multiple times.
NoteFor V4.6.x, this column is available starting from V4.6.0. |
| LATEST_ACTIVE_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | YES | The last active time of the plan corresponding to the SQL ID and plan hash on a node.
NoteFor V4.6.x, this column is available starting from V4.6.0. |
Sample query
Query the basic performance statistics of SQL statements executed in the current tenant and display the first record.
obclient [SYS]> SELECT * FROM SYS.DBA_WR_SQLSTAT WHERE ROWNUM = 1\G
The query result is as follows:
*************************** 1. row ***************************
SNAP_ID: 1
SVR_IP: xx.xx.xx.xx
SVR_PORT: 2882
SQL_ID: 01058E374A5EF26873BA4D99C4A74582
PLAN_HASH: 5547575357486659143
PLAN_TYPE: 0
MODULE: NULL
ACTION: NULL
PARSING_DB_ID: 0
PARSING_DB_NAME: NULL
PARSING_USER_ID: 0
EXECUTIONS_TOTAL: 1
EXECUTIONS_DELTA: 1
DISK_READS_TOTAL: 0
DISK_READS_DELTA: 0
BUFFER_GETS_TOTAL: 0
BUFFER_GETS_DELTA: 0
ELAPSED_TIME_TOTAL: 1487
ELAPSED_TIME_DELTA: 1487
CPU_TIME_TOTAL: 0
CPU_TIME_DELTA: 0
CCWAIT_TOTAL: 0
CCWAIT_DELTA: 0
USERIO_WAIT_TOTAL: 0
USERIO_WAIT_DELTA: 0
APWAIT_TOTAL: 0
APWAIT_DELTA: 0
PHYSICAL_READ_REQUESTS_TOTAL: 0
PHYSICAL_READ_REQUESTS_DELTA: 0
PHYSICAL_READ_BYTES_TOTAL: 0
PHYSICAL_READ_BYTES_DELTA: 0
WRITE_THROTTLE_TOTAL: 0
WRITE_THROTTLE_DELTA: 0
ROWS_PROCESSED_TOTAL: 0
ROWS_PROCESSED_DELTA: 0
MEMSTORE_READ_ROWS_TOTAL: 0
MEMSTORE_READ_ROWS_DELTA: 0
MINOR_SSSTORE_READ_ROWS_TOTAL: 0
MINOR_SSSTORE_READ_ROWS_DELTA: 0
MAJOR_SSSTORE_READ_ROWS_TOTAL: 0
MAJOR_SSSTORE_READ_ROWS_DELTA: 0
RPC_TOTAL: 0
RPC_DELTA: 0
FETCHES_TOTAL: 0
FETCHES_DELTA: 0
RETRY_TOTAL: 0
RETRY_DELTA: 0
PARTITION_TOTAL: 1
PARTITION_DELTA: 1
NESTED_SQL_TOTAL: 0
NESTED_SQL_DELTA: 0
SOURCE_IP: xx.xx.xx.xx
SOURCE_PORT: 2882
ROUTE_MISS_TOTAL: 0
ROUTE_MISS_DELTA: 0
FIRST_LOAD_TIME: 24-FEB-26 05.52.04.124652 PM
PLAN_CACHE_HIT_TOTAL: 0
PLAN_CACHE_HIT_DELTA: 0
MUTI_QUERY_TOTAL: 0
MUTI_QUERY_DELTA: 0
MUTI_QUERY_BATCH_TOTAL: 0
MUTI_QUERY_BATCH_DELTA: 0
FULL_TABLE_SCAN_TOTAL: 0
FULL_TABLE_SCAN_DELTA: 0
ERROR_COUNT_TOTAL: 0
ERROR_COUNT_DELTA: 0
SAMPLE_TIME: 24-FEB-26 05.52.04.000000 PM
LATEST_ACTIVE_TIME: 24-FEB-26 05.52.04.154727 PM
1 row in set (0.176 sec)