Note
This view is introduced since OceanBase Database V4.2.2.
Purpose
The DBA_WR_SQLSTAT view displays the basic performance statistics of SQL statements executed in the current tenant. 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 | NUMBER(38) | NO | The ID of the snapshot. |
| SVR_IP | VARCHAR2(46) | NO | The IP address of the OBServer node. |
| SVR_PORT | NUMBER(38) | NO | The port number of the OBServer node. |
| SQL_ID | VARCHAR2(32) | NO | The ID of the SQL statement. |
| PLAN_HASH | NUMBER(38) | NO | The hash value of the execution plan. |
| PLAN_ID | NUMBER(38) | NO | The ID of the execution plan. |
| PLAN_TYPE | NUMBER(38) | 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 | VARCHAR2(64) | YES | The application module by which the statement was first parsed. |
| ACTION | VARCHAR2(64) | YES | The application action by which the statement was first parsed. |
| PARSING_DB_ID | NUMBER(38) | NO | The ID of the database where the statement was parsed. |
| PARSING_DB_NAME | VARCHAR2(128) | NO | The name of the database where the statement was parsed. |
| PARSING_USER_ID | NUMBER(38) | NO | The ID of the user who parsed the statement. |
| 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 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 | NUMBER(38) | NO | The incremental execution time of the statement. |
| CPU_TIME_TOTAL | NUMBER(38) | NO | The total CPU time. |
| CPU_TIME_DELTA | NUMBER(38) | NO | The incremental amount of CPU time. |
| CCWAIT_TOTAL | NUMBER(38) | NO | The total amount of time waited for events of the concurrency class. |
| CCWAIT_DELTA | NUMBER(38) | NO | The incremental amount of time waited for events of the concurrency class. |
| USERIO_WAIT_TOTAL | NUMBER(38) | NO | The total amount of time waited for events of the user I/O class. |
| USERIO_WAIT_DELTA | NUMBER(38) | NO | The incremental amount of time waited for events of the user I/O class. |
| APWAIT_TOTAL | NUMBER(38) | NO | The total amount of time waited for events of the application class. |
| APWAIT_DELTA | NUMBER(38) | NO | The incremental amount of time waited for events of the application class. |
| PHYSICAL_READ_REQUESTS_TOTAL | NUMBER(38) | NO | The total number of physical reads. |
| PHYSICAL_READ_REQUESTS_DELTA | NUMBER(38) | NO | The incremental number of physical reads. |
| PHYSICAL_READ_BYTES_TOTAL | NUMBER(38) | NO | The total size in bytes of physical reads. |
| PHYSICAL_READ_BYTES_DELTA | NUMBER(38) | NO | The incremental size in bytes of physical reads. |
| WRITE_THROTTLE_TOTAL | NUMBER(38) | NO | The total amount of time of throttling for writes to the MemStore. |
| WRITE_THROTTLE_DELTA | NUMBER(38) | NO | The incremental amount of time of throttling for writes 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 incremental 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 incremental 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 incremental 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 incremental number of rows read from the major SSStore. |
| RPC_TOTAL | NUMBER(38) | NO | The total number of remote procedure call (RPC) requests. |
| RPC_DELTA | NUMBER(38) | NO | The incremental number of RPC requests. |
| FETCHES_TOTAL | NUMBER(38) | NO | The total number of result set fetches. |
| FETCHES_DELTA | NUMBER(38) | NO | The incremental number of result set fetches. |
| RETRY_TOTAL | NUMBER(38) | NO | The total number of retries of the SQL statement. |
| RETRY_DELTA | NUMBER(38) | NO | The incremental number of retries of the SQL statement. |
| PARTITION_TOTAL | NUMBER(38) | NO | The total number of partitions scanned by the SQL statement. |
| PARTITION_DELTA | NUMBER(38) | NO | The incremental number of partitions scanned by the SQL statement. |
| NESTED_SQL_TOTAL | NUMBER(38) | 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 | NUMBER(38) | NO | The incremental number of executions of nested SQL statements. |
| SOURCE_IP | VARCHAR2(46) | NO | The IP address of the request source. |
| SOURCE_PORT | NUMBER(38) | NO | The port number of the request source. |
Sample query
obclient [SYS]> SELECT * FROM SYS.DBA_WR_SQLSTAT WHERE ROWNUM = 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 | 223 | 1 | NULL | NULL | 201001 | oceanbase | 200001 | 1 | 1 | 1 | 1 | 1 | 1 | 1699 | 1699 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 193 | 193 | 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