Note
This view is introduced since OceanBase Database V4.2.2.
Purpose
The oceanbase.CDB_WR_SQLSTAT view displays the basic performance statistics of SQL statements executed in all tenants. 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 |
|---|---|---|---|
| TENANT_ID | bigint(20) | NO | The ID of the tenant. |
| 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:
+-----------+---------+----------------+----------+----------------------------------+---------------------+---------+-----------+--------+--------+---------------+-----------------+-----------------+------------------+------------------+------------------+------------------+-------------------+-------------------+--------------------+--------------------+----------------+----------------+--------------+--------------+-------------------+-------------------+--------------+--------------+------------------------------+------------------------------+---------------------------+---------------------------+----------------------+----------------------+----------------------+----------------------+--------------------------+--------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------+-----------+---------------+---------------+-------------+-------------+-----------------+-----------------+------------------+------------------+----------------+-------------+
| TENANT_ID | 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 |
+-----------+---------+----------------+----------+----------------------------------+---------------------+---------+-----------+--------+--------+---------------+-----------------+-----------------+------------------+------------------+------------------+------------------+-------------------+-------------------+--------------------+--------------------+----------------+----------------+--------------+--------------+-------------------+-------------------+--------------+--------------+------------------------------+------------------------------+---------------------------+---------------------------+----------------------+----------------------+----------------------+----------------------+--------------------------+--------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------+-----------+---------------+---------------+-------------+-------------+-----------------+-----------------+------------------+------------------+----------------+-------------+
| 1002 | 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