Note
- In V4.3.x, this view was introduced in V4.3.5.
- In V4.2.x, this view was introduced in V4.2.2.
Purpose
The CDB_WR_SQLSTAT view displays the basic performance statistics of all SQL statements executed in all tenants, with each SQL statement represented by a row (each unique combination of SQL_ID and Plan_Hash). The statistics with _DELTA in the name indicate the incremental statistics from the last WR snapshot to the current time.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| TENANT_ID | bigint(20) | NO | Tenant ID |
| SNAP_ID | bigint(20) | NO | Snapshot ID |
| SVR_IP | varchar(46) | NO | Server IP |
| SVR_PORT | bigint(20) | NO | Server port |
| 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 | ID of the execution plan |
| PLAN_TYPE | bigint(20) | NO | For cached SQL plans, this field indicates the type of the plan:
For cached PL objects, this field indicates the type of the PL object:
|
| MODULE | varchar(64) | YES | Application module when the statement was parsed for the first time |
| ACTION | varchar(64) | YES | Application action when the statement was parsed for the first time |
| PARSING_DB_ID | bigint(20) | NO | Database ID when the statement was parsed |
| PARSING_DB_NAME | varchar(128) | NO | Database name when the statement was parsed |
| PARSING_USER_ID | bigint(20) | NO | User ID when the statement was parsed |
| EXECUTIONS_TOTAL | bigint(20) | NO | Total number of times the plan has been executed in the plan cache |
| EXECUTIONS_DELTA | bigint(20) | NO | Increment of the total number of times the plan has been executed in the plan cache |
| DISK_READS_TOTAL | bigint(20) | NO | Total number of disk reads |
| DISK_READS_DELTA | bigint(20) | NO | Increment of the number of disk reads |
| BUFFER_GETS_TOTAL | bigint(20) | NO | Total number of logical reads from the cache |
| BUFFER_GETS_DELTA | bigint(20) | NO | Increment of the number of logical reads from the cache |
| ELAPSED_TIME_TOTAL | bigint(20) | NO | Total time consumed by the statement. For parallel execution scenarios, this value is the accumulated value of each execution thread. |
| ELAPSED_TIME_DELTA | bigint(20) | NO | Increment of the time consumed by the statement |
| CPU_TIME_TOTAL | bigint(20) | NO | Total CPU time |
| CPU_TIME_DELTA | bigint(20) | NO | Increment of the total CPU time |
| CCWAIT_TOTAL | bigint(20) | NO | Total duration of concurrency-related wait events |
| CCWAIT_DELTA | bigint(20) | NO | Increment of the total duration of concurrency-related wait events |
| USERIO_WAIT_TOTAL | bigint(20) | NO | Total duration of I/O wait events |
| USERIO_WAIT_DELTA | bigint(20) | NO | Increment of the total duration of I/O wait events |
| APWAIT_TOTAL | bigint(20) | NO | Total duration of application-related wait events |
| APWAIT_DELTA | bigint(20) | NO | Increment of the total duration of application-related wait events |
| PHYSICAL_READ_REQUESTS_TOTAL | bigint(20) | NO | Total number of physical read requests |
| PHYSICAL_READ_REQUESTS_DELTA | bigint(20) | NO | Increment of the number of physical read requests |
| PHYSICAL_READ_BYTES_TOTAL | bigint(20) | NO | Total number of bytes read physically |
| PHYSICAL_READ_BYTES_DELTA | bigint(20) | NO | Increment of the number of bytes read physically |
| WRITE_THROTTLE_TOTAL | bigint(20) | NO | Total duration that the write to MemStore was throttled |
| WRITE_THROTTLE_DELTA | bigint(20) | NO | Increment of the total duration that the write to MemStore was throttled |
| ROWS_PROCESSED_TOTAL | bigint(20) | NO | Total number of rows processed by the statement |
| ROWS_PROCESSED_DELTA | bigint(20) | NO | Increment of 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 of 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 of 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 of the total number of rows read from MAJOR SSStore |
| RPC_TOTAL | bigint(20) | NO | Total number of RPC calls |
| RPC_DELTA | bigint(20) | NO | Increment of the number of RPC calls |
| FETCHES_TOTAL | bigint(20) | NO | Total number of result set fetches |
| FETCHES_DELTA | bigint(20) | NO | Increment of the number of result set fetches |
| RETRY_TOTAL | bigint(20) | NO | Total number of SQL retries |
| RETRY_DELTA | bigint(20) | NO | Increment of the number of SQL retries |
| PARTITION_TOTAL | bigint(20) | NO | Total number of partitions scanned by the SQL statement |
| PARTITION_DELTA | bigint(20) | NO | Increment of the number of partitions scanned by the SQL statement |
| NESTED_SQL_TOTAL | bigint(20) | NO | Total number of nested SQL statements executed. The value increases by 1 each time a sub-SQL statement is executed. |
| NESTED_SQL_DELTA | bigint(20) | NO | Increment of the total number of nested SQL statements executed |
| SOURCE_IP | varchar(46) | NO | IP address of the request source |
| SOURCE_PORT | bigint(20) | NO | Port of the request source |
| ROUTE_MISS_TOTAL | bigint(20) | NO | Total number of times the request has not hit the requested partition since the OBServer node started
Note
|
| ROUTE_MISS_DELTA | bigint(20) | NO | Number of times the request has not hit the requested partition since the last WR snapshot was collected
Note
|
| FIRST_LOAD_TIME | timestamp(6) | YES | Plan generation time
Note
|
| PLAN_CACHE_HIT_TOTAL | bigint(20) | NO | Total number of times the plan cache has been hit since the OBServer node started
Note
|
| PLAN_CACHE_HIT_DELTA | bigint(20) | NO | Number of times the plan cache has been hit since the last WR snapshot was collected
Note
|
Sample query
obclient [oceanbase]> SELECT * FROM oceanbase.CDB_WR_SQLSTAT LIMIT 1\G
The sample query result is as follows:
*************************** 1. row ***************************
TENANT_ID: 1
SNAP_ID: 1
SVR_IP: xx.xx.xx.xx
SVR_PORT: 2882
SQL_ID: 05498258D0EEE84DAEDC4031BE9404CC
PLAN_HASH: 5462864837301675214
PLAN_TYPE: 1
MODULE: NULL
ACTION: NULL
PARSING_DB_ID: 201001
PARSING_DB_NAME: oceanbase
PARSING_USER_ID: 200001
EXECUTIONS_TOTAL: 8
EXECUTIONS_DELTA: 8
DISK_READS_TOTAL: 0
DISK_READS_DELTA: 0
BUFFER_GETS_TOTAL: 0
BUFFER_GETS_DELTA: 0
ELAPSED_TIME_TOTAL: 4444
ELAPSED_TIME_DELTA: 4444
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: 8
ROWS_PROCESSED_DELTA: 8
MEMSTORE_READ_ROWS_TOTAL: 16
MEMSTORE_READ_ROWS_DELTA: 16
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: 8
PARTITION_DELTA: 8
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: 2024-12-09 11:02:27.095548
PLAN_CACHE_HIT_TOTAL: 7
PLAN_CACHE_HIT_DELTA: 7
1 row in set (0.005 sec)