Note
This view is available starting with V4.2.2.
Purpose
The DBA_WR_SQLSTAT view displays basic performance statistics of SQL statements executed by the current tenant. Each SQL statement occupies one row (that is, each unique value of SQL_ID + Plan_Hash occupies one row). Columns containing _DELTA indicate the incremental statistics from the last collected WR snapshot to the current time.
Columns
Column |
Type |
Nullable? |
Description |
|---|---|---|---|
| SNAP_ID | bigint(20) | NO | Snapshot ID |
| SVR_IP | varchar(46) | NO | Node IP |
| SVR_PORT | bigint(20) | NO | Node port number |
| SQL_ID | varchar(32) | NO | SQL statement ID |
| PLAN_HASH | bigint(20) unsigned | NO | Hash value of the execution plan |
| PLAN_ID | bigint(20) | NO | Execution plan ID |
| PLAN_TYPE | bigint(20) | NO | For SQL plan caching, this field indicates the plan type:
For the PL object cache, this field indicates the type of the PL object:
|
| MODULE | varchar(64) | YES | The application Module when the statement was parsed for the first time, viaDBMS_APPLICATION_INFO.SET_MODULEPackage settings |
| ACTION | varchar(64) | YES | The application Action when this statement was first parsed, viaDBMS_APPLICATION_INFO.SET_ACTIONPackage settings |
| PARSING_DB_ID | bigint(20) | NO | Database ID during statement parsing |
| PARSING_DB_NAME | varchar(128) | NO | Database name during statement parsing |
| PARSING_USER_ID | bigint(20) | NO | User ID during statement parsing |
| EXECUTIONS_TOTAL | bigint(20) | NO | Total number of executions of this plan in the plan cache |
| EXECUTIONS_DELTA | bigint(20) | NO | Increment of the total number of executions of this plan in the plan cache |
| DISK_READS_TOTAL | bigint(20) | NO | Total number of disk reads |
| DISK_READS_DELTA | bigint(20) | NO | Increment in disk reads |
| BUFFER_GETS_TOTAL | bigint(20) | NO | Total number of logical reads from cache |
| BUFFER_GETS_DELTA | bigint(20) | NO | Increment in the number of logical reads from the cache |
| ELAPSED_TIME_TOTAL | bigint(20) | NO | The total time consumed by this statement. In parallel execution scenarios, this value is the cumulative value for each execution thread. |
| ELAPSED_TIME_DELTA | bigint(20) | NO | Incremental time consumed by this statement |
| CPU_TIME_TOTAL | bigint(20) | NO | Total CPU time consumed |
| CPU_TIME_DELTA | bigint(20) | NO | Increment of total CPU time consumed |
| CCWAIT_TOTAL | bigint(20) | NO | Total time consumed by concurrency wait events |
| CCWAIT_DELTA | bigint(20) | NO | Increment in total time spent on concurrency wait events |
| USERIO_WAIT_TOTAL | bigint(20) | NO | Total time spent waiting for I/O |
| USERIO_WAIT_DELTA | bigint(20) | NO | Incremental total duration of I/O waits |
| APWAIT_TOTAL | bigint(20) | NO | Total time consumed by application wait events |
| APWAIT_DELTA | bigint(20) | NO | Increment of the total time consumed by application-type wait events |
| PHYSICAL_READ_REQUESTS_TOTAL | bigint(20) | NO | Total number of physical read requests |
| PHYSICAL_READ_REQUESTS_DELTA | bigint(20) | NO | Increment in the number of physical read requests |
| PHYSICAL_READ_BYTES_TOTAL | bigint(20) | NO | Total bytes read physically |
| PHYSICAL_READ_BYTES_DELTA | bigint(20) | NO | Increment in physical read bytes |
| WRITE_THROTTLE_TOTAL | bigint(20) | NO | Total time throttled when writing to MemStore |
| WRITE_THROTTLE_DELTA | bigint(20) | NO | Increment of the total time throttled when writing to MemStore |
| ROWS_PROCESSED_TOTAL | bigint(20) | NO | Total number of rows processed by the statement |
| ROWS_PROCESSED_DELTA | bigint(20) | NO | Increment in the total number of rows processed by the statement |
| MEMSTORE_READ_ROWS_TOTAL | bigint(20) | NO | Total number of rows read from MemStore |
| MEMSTORE_READ_ROWS_DELTA | bigint(20) | NO | Increment in the total number of rows read from MemStore |
| MINOR_SSSTORE_READ_ROWS_TOTAL | bigint(20) | NO | Total number of rows read from MINOR SSStore |
| MINOR_SSSTORE_READ_ROWS_DELTA | bigint(20) | NO | Increment in the total number of rows read from MINOR SSStore |
| MAJOR_SSSTORE_READ_ROWS_TOTAL | bigint(20) | NO | Total number of rows read from MAJOR SSStore |
| MAJOR_SSSTORE_READ_ROWS_DELTA | bigint(20) | NO | Increment in the total number of rows read from MAJOR SSStore |
| RPC_TOTAL | bigint(20) | NO | Total number of RPCs |
| RPC_DELTA | bigint(20) | NO | Increment in the number of RPC calls |
| FETCHES_TOTAL | bigint(20) | NO | Total number of times result sets are retrieved |
| FETCHES_DELTA | bigint(20) | NO | Increment in the number of times the result set was obtained |
| RETRY_TOTAL | bigint(20) | NO | Total number of SQL retries |
| RETRY_DELTA | bigint(20) | NO | Increment in the number of SQL retries |
| PARTITION_TOTAL | bigint(20) | NO | Total number of partitions scanned during SQL execution |
| PARTITION_DELTA | bigint(20) | NO | Increment in the number of partitions scanned during SQL execution |
| NESTED_SQL_TOTAL | bigint(20) | NO | Total number of nested SQL executions, incremented by 1 for each additional sub-SQL execution |
| NESTED_SQL_DELTA | bigint(20) | NO | Increment of nested SQL execution |
| SOURCE_IP | varchar(46) | NO | Request source IP |
| SOURCE_PORT | bigint(20) | NO | Port number of the request source |
| ROUTE_MISS_TOTAL | bigint(20) | NO | Total number of times the requested partition was not correctly hit since the execution plan was generated |
| ROUTE_MISS_DELTA | bigint(20) | NO | Total number of times the request partition was not correctly hit since the last WR snapshot collection |
| FIRST_LOAD_TIME | timestamp(6) | YES | Execution plan generation time |
| PLAN_CACHE_HIT_TOTAL | bigint(20) | NO | Total number of Plan Cache hits since the execution plan was generated |
| PLAN_CACHE_HIT_DELTA | bigint(20) | NO | Number of times the Plan Cache has been hit since the last WR snapshot collection |
| MUTI_QUERY_TOTAL | bigint(20) | NO | Number of times this SQL has been in Muti Query since the execution plan was generated
NoteFor V4.2.5, this field was introduced starting from V4.2.5 BP7. |
| MUTI_QUERY_DELTA | bigint(20) | NO | Number of times this SQL has been in Muti Query since the last WR snapshot collection
NoteFor V4.2.5, this field was introduced starting from V4.2.5 BP7. |
| MUTI_QUERY_BATCH_TOTAL | bigint(20) | NO | Number of times this SQL Batch has been executed since the execution plan was generated
NoteFor V4.2.5, this field was introduced starting from V4.2.5 BP7. |
| MUTI_QUERY_BATCH_DELTA | bigint(20) | NO | Number of times this SQL Batch has been executed since the last WR snapshot collection
NoteFor V4.2.5, this field was introduced starting from V4.2.5 BP7. |
| FULL_TABLE_SCAN_TOTAL | bigint(20) | NO | The number of full table scans performed by the SQL since the execution plan was generated
NoteFor V4.2.5, this field was introduced starting from V4.2.5 BP7. |
| FULL_TABLE_SCAN_DELTA | bigint(20) | NO | The number of full table scans by this SQL since the last WR snapshot collection
NoteFor V4.2.5, this field was introduced starting from V4.2.5 BP7. |
| ERROR_COUNT_TOTAL | bigint(20) | NO | Number of times this SQL reported errors since the execution plan was generated
NoteFor V4.2.5, this field was introduced starting from V4.2.5 BP7. |
| ERROR_COUNT_DELTA | bigint(20) | NO | Number of times this SQL has reported errors since the last WR snapshot collection
NoteFor V4.2.5, this field was introduced starting from V4.2.5 BP7. |
Sample query
Query the basic performance statistics of SQL statements executed by the current tenant and display the first record.
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_WR_SQLSTAT LIMIT 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: 1
MODULE: NULL
ACTION: NULL
PARSING_DB_ID: 201001
PARSING_DB_NAME: oceanbase
PARSING_USER_ID: 200001
EXECUTIONS_TOTAL: 3
EXECUTIONS_DELTA: 3
DISK_READS_TOTAL: 1
DISK_READS_DELTA: 1
BUFFER_GETS_TOTAL: 1
BUFFER_GETS_DELTA: 1
ELAPSED_TIME_TOTAL: 4864
ELAPSED_TIME_DELTA: 4864
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: 1
PHYSICAL_READ_REQUESTS_DELTA: 1
PHYSICAL_READ_BYTES_TOTAL: 194
PHYSICAL_READ_BYTES_DELTA: 194
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: 2025-10-15 11:11:19.638416
PLAN_CACHE_HIT_TOTAL: 1
PLAN_CACHE_HIT_DELTA: 1
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
1 row in set