Note
This view is introduced since OceanBase Database V4.2.2.
Purpose
The oceanbase.DBA_WR_SQLSTAT view displays the basic performance statistics of SQL statements executed in the current tenant. Each row indicates one SQL statement. To be specific, each row is uniquely identified by SQL_ID and Plan_Hash. A column whose name contains _DELTA indicates the incremental value since a Workload Repository (WR) snapshot was last collected.
Columns
Column |
Type |
Nullable? |
Description |
|---|---|---|---|
| SNAP_ID | bigint(20) | NO | The ID of the snapshot. |
| SVR_IP | varchar(46) | NO | The IP address of the OBServer node. |
| SVR_PORT | bigint(20) | NO | The port number of the OBServer node. |
| SQL_ID | varchar(32) | NO | The ID of the SQL statement. |
| PLAN_HASH | bigint(20) unsigned | NO | The hash value of the execution plan. |
| PLAN_ID | bigint(20) | NO | The ID of the execution plan. |
| PLAN_TYPE | bigint(20) | NO | For SQL plan caching, this column indicates the type of the plan. Valid values:
For PL object caching, this column indicates the type of the PL object. Valid values:
|
| MODULE | varchar(64) | YES | The application module by which the statement was first parsed. |
| ACTION | varchar(64) | YES | The application action by which the statement was first parsed. |
| PARSING_DB_ID | bigint(20) | NO | The ID of the database where the statement was parsed. |
| PARSING_DB_NAME | varchar(128) | NO | The name of the database where the statement was parsed. |
| PARSING_USER_ID | bigint(20) | NO | The ID of the user who parsed the statement. |
| EXECUTIONS_TOTAL | bigint(20) | NO | The total number of executions of the plan in the plan cache. |
| EXECUTIONS_DELTA | bigint(20) | NO | The incremental number of executions of the plan in the plan cache. |
| DISK_READS_TOTAL | bigint(20) | NO | The total number of disk reads. |
| DISK_READS_DELTA | bigint(20) | NO | The incremental number of disk reads. |
| BUFFER_GETS_TOTAL | bigint(20) | NO | The total number of logical reads from the cache. |
| BUFFER_GETS_DELTA | bigint(20) | NO | The incremental number of logical reads from the cache. |
| ELAPSED_TIME_TOTAL | bigint(20) | NO | The total execution time of the statement. If the statement is executed by multiple threads in parallel, this value is the sum of the execution time taken by each thread. |
| ELAPSED_TIME_DELTA | bigint(20) | NO | The incremental execution time of the statement. |
| CPU_TIME_TOTAL | bigint(20) | NO | The total CPU time. |
| CPU_TIME_DELTA | bigint(20) | NO | The incremental amount of CPU time. |
| CCWAIT_TOTAL | bigint(20) | NO | The total amount of time waited for events of the concurrency class. |
| CCWAIT_DELTA | bigint(20) | NO | The incremental amount of time waited for events of the concurrency class. |
| USERIO_WAIT_TOTAL | bigint(20) | NO | The total amount of time waited for events of the user I/O class. |
| USERIO_WAIT_DELTA | bigint(20) | NO | The incremental amount of time waited for events of the user I/O class. |
| APWAIT_TOTAL | bigint(20) | NO | The total amount of time waited for events of the application class. |
| APWAIT_DELTA | bigint(20) | NO | The incremental amount of time waited for events of the application class. |
| PHYSICAL_READ_REQUESTS_TOTAL | bigint(20) | NO | The total number of physical reads. |
| PHYSICAL_READ_REQUESTS_DELTA | bigint(20) | NO | The incremental number of physical reads. |
| PHYSICAL_READ_BYTES_TOTAL | bigint(20) | NO | The total size in bytes of physical reads. |
| PHYSICAL_READ_BYTES_DELTA | bigint(20) | NO | The incremental size in bytes of physical reads. |
| WRITE_THROTTLE_TOTAL | bigint(20) | NO | The total amount of time of throttling for writes to the MemStore. |
| WRITE_THROTTLE_DELTA | bigint(20) | NO | The incremental amount of time of throttling for writes to the MemStore. |
| ROWS_PROCESSED_TOTAL | bigint(20) | NO | The total number of rows processed by the statement. |
| ROWS_PROCESSED_DELTA | bigint(20) | NO | The incremental number of rows processed by the statement. |
| MEMSTORE_READ_ROWS_TOTAL | bigint(20) | NO | The total number of rows read from the MemStore. |
| MEMSTORE_READ_ROWS_DELTA | bigint(20) | NO | The incremental number of rows read from the MemStore. |
| MINOR_SSSTORE_READ_ROWS_TOTAL | bigint(20) | NO | The total number of rows read from the minor SSStore. |
| MINOR_SSSTORE_READ_ROWS_DELTA | bigint(20) | NO | The incremental number of rows read from the minor SSStore. |
| MAJOR_SSSTORE_READ_ROWS_TOTAL | bigint(20) | NO | The total number of rows read from the major SSStore. |
| MAJOR_SSSTORE_READ_ROWS_DELTA | bigint(20) | NO | The incremental number of rows read from the major SSStore. |
| RPC_TOTAL | bigint(20) | NO | The total number of remote procedure call (RPC) requests. |
| RPC_DELTA | bigint(20) | NO | The incremental number of RPC requests. |
| FETCHES_TOTAL | bigint(20) | NO | The total number of result set fetches. |
| FETCHES_DELTA | bigint(20) | NO | The incremental number of result set fetches. |
| RETRY_TOTAL | bigint(20) | NO | The total number of retries of the SQL statement. |
| RETRY_DELTA | bigint(20) | NO | The incremental number of retries of the SQL statement. |
| PARTITION_TOTAL | bigint(20) | NO | The total number of partitions scanned by the SQL statement. |
| PARTITION_DELTA | bigint(20) | NO | The incremental number of partitions scanned by the SQL statement. |
| NESTED_SQL_TOTAL | bigint(20) | NO | The total number of executions of nested SQL statements. The value increases by 1 each time a nested SQL statement is executed. |
| NESTED_SQL_DELTA | bigint(20) | NO | The incremental number of executions of nested SQL statements. |
| SOURCE_IP | varchar(46) | NO | The IP address of the request source. |
| SOURCE_PORT | bigint(20) | NO | The port number of the request source. |
Sample query
obclient [oceanbase]> SELECT * FROM oceanbase.CDB_WR_SQLSTAT LIMIT 1;
The query result is as follows:
+---------+----------------+----------+----------------------------------+---------------------+---------+-----------+--------+--------+---------------+-----------------+-----------------+------------------+------------------+------------------+------------------+-------------------+-------------------+--------------------+--------------------+----------------+----------------+--------------+--------------+-------------------+-------------------+--------------+--------------+------------------------------+------------------------------+---------------------------+---------------------------+----------------------+----------------------+----------------------+----------------------+--------------------------+--------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------+-----------+---------------+---------------+-------------+-------------+-----------------+-----------------+------------------+------------------+----------------+-------------+
| SNAP_ID | SVR_IP | SVR_PORT | SQL_ID | PLAN_HASH | PLAN_ID | PLAN_TYPE | MODULE | ACTION | PARSING_DB_ID | PARSING_DB_NAME | PARSING_USER_ID | EXECUTIONS_TOTAL | EXECUTIONS_DELTA | DISK_READS_TOTAL | DISK_READS_DELTA | BUFFER_GETS_TOTAL | BUFFER_GETS_DELTA | ELAPSED_TIME_TOTAL | ELAPSED_TIME_DELTA | CPU_TIME_TOTAL | CPU_TIME_DELTA | CCWAIT_TOTAL | CCWAIT_DELTA | USERIO_WAIT_TOTAL | USERIO_WAIT_DELTA | APWAIT_TOTAL | APWAIT_DELTA | PHYSICAL_READ_REQUESTS_TOTAL | PHYSICAL_READ_REQUESTS_DELTA | PHYSICAL_READ_BYTES_TOTAL | PHYSICAL_READ_BYTES_DELTA | WRITE_THROTTLE_TOTAL | WRITE_THROTTLE_DELTA | ROWS_PROCESSED_TOTAL | ROWS_PROCESSED_DELTA | MEMSTORE_READ_ROWS_TOTAL | MEMSTORE_READ_ROWS_DELTA | MINOR_SSSTORE_READ_ROWS_TOTAL | MINOR_SSSTORE_READ_ROWS_DELTA | MAJOR_SSSTORE_READ_ROWS_TOTAL | MAJOR_SSSTORE_READ_ROWS_DELTA | RPC_TOTAL | RPC_DELTA | FETCHES_TOTAL | FETCHES_DELTA | RETRY_TOTAL | RETRY_DELTA | PARTITION_TOTAL | PARTITION_DELTA | NESTED_SQL_TOTAL | NESTED_SQL_DELTA | SOURCE_IP | SOURCE_PORT |
+---------+----------------+----------+----------------------------------+---------------------+---------+-----------+--------+--------+---------------+-----------------+-----------------+------------------+------------------+------------------+------------------+-------------------+-------------------+--------------------+--------------------+----------------+----------------+--------------+--------------+-------------------+-------------------+--------------+--------------+------------------------------+------------------------------+---------------------------+---------------------------+----------------------+----------------------+----------------------+----------------------+--------------------------+--------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------+-----------+---------------+---------------+-------------+-------------+-----------------+-----------------+------------------+------------------+----------------+-------------+
| 1 | 172.xx.xxx.xxx | 2882 | 01058E374A5EF26873BA4D99C4A74582 | 5547575357486659143 | 181 | 1 | NULL | NULL | 201001 | oceanbase | 200001 | 1 | 1 | 0 | 0 | 0 | 0 | 1424 | 1424 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 172.xx.xxx.xxx | 2882 |
+---------+----------------+----------+----------------------------------+---------------------+---------+-----------+--------+--------+---------------+-----------------+-----------------+------------------+------------------+------------------+------------------+-------------------+-------------------+--------------------+--------------------+----------------+----------------+--------------+--------------+-------------------+-------------------+--------------+--------------+------------------------------+------------------------------+---------------------------+---------------------------+----------------------+----------------------+----------------------+----------------------+--------------------------+--------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------+-----------+---------------+---------------+-------------+-------------+-----------------+-----------------+------------------+------------------+----------------+-------------+
1 row in set