Note
This view is introduced since OceanBase Database V4.2.2.
Purpose
The DBA_WR_SQLSTAT view displays the basic performance statistics of historical SQL statements executed in the current tenant. The value of the column whose name ends with _DELTA indicates the statistical increment from the last workload repository (WR) snapshot to the current time.
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 server. |
| SVR_PORT | NUMBER(38) | No | The port number of the server. |
| 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 field indicates the type of the plan. Valid values:
For PL object caching, this field 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 is parsed. |
| PARSING_DB_NAME | VARCHAR2(128) | No | The name of the database where the statement is 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 increment of the total 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 increment of the total 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 increment of the total 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 increment of the total execution time of the statement. |
| CPU_TIME_TOTAL | NUMBER(38) | No | The total amount of CPU time consumed. |
| CPU_TIME_DELTA | NUMBER(38) | No | The increment of the total amount of CPU time consumed. |
| CCWAIT_TOTAL | NUMBER(38) | No | The total time consumed by wait events of the CONCURRENCY class. |
| CCWAIT_DELTA | NUMBER(38) | No | The increment of the total time consumed by wait events of the CONCURRENCY class. |
| USERIO_WAIT_TOTAL | NUMBER(38) | No | The total time spent waiting for I/O requests. |
| USERIO_WAIT_DELTA | NUMBER(38) | No | The increment of the total time spent waiting for I/O requests. |
| APWAIT_TOTAL | NUMBER(38) | No | The total time consumed by wait events of the APPLICATION class. |
| APWAIT_DELTA | NUMBER(38) | No | The increment of the total time consumed by wait events of the APPLICATION class. |
| PHYSICAL_READ_REQUESTS_TOTAL | NUMBER(38) | No | The total number of physical read requests. |
| PHYSICAL_READ_REQUESTS_DELTA | NUMBER(38) | No | The increment of the total number of physical read requests. |
| PHYSICAL_READ_BYTES_TOTAL | NUMBER(38) | No | The total number of physically read bytes. |
| PHYSICAL_READ_BYTES_DELTA | NUMBER(38) | No | The increment of the total number of physically read bytes. |
| WRITE_THROTTLE_TOTAL | NUMBER(38) | No | The total time of throttling on data writes to MemStore. |
| WRITE_THROTTLE_DELTA | NUMBER(38) | No | The increment of the total time of throttling on data writes to MemStore. |
| ROWS_PROCESSED_TOTAL | NUMBER(38) | No | The total number of rows processed by the statement. |
| ROWS_PROCESSED_DELTA | NUMBER(38) | No | The increment of the total number of rows processed by the statement. |
| MEMSTORE_READ_ROWS_TOTAL | NUMBER(38) | No | The total number of rows read from MemStore. |
| MEMSTORE_READ_ROWS_DELTA | NUMBER(38) | No | The increment of the total number of rows read from MemStore. |
| MINOR_SSSTORE_READ_ROWS_TOTAL | NUMBER(38) | No | The total number of rows read from MINOR SSStore. |
| MINOR_SSSTORE_READ_ROWS_DELTA | NUMBER(38) | No | The increment of the total number of rows read from MINOR SSStore. |
| MAJOR_SSSTORE_READ_ROWS_TOTAL | NUMBER(38) | No | The total number of rows read from MAJOR SSStore. |
| MAJOR_SSSTORE_READ_ROWS_DELTA | NUMBER(38) | No | The increment of the total number of rows read from MAJOR SSStore. |
| RPC_TOTAL | NUMBER(38) | No | The total number of RPCs. |
| RPC_DELTA | NUMBER(38) | No | The increment of the total number of RPCs. |
| FETCHES_TOTAL | NUMBER(38) | No | The total number of obtained result sets. |
| FETCHES_DELTA | NUMBER(38) | No | The increment of the total number of obtained result sets. |
| RETRY_TOTAL | NUMBER(38) | No | The total number of retries of the SQL statement. |
| RETRY_DELTA | NUMBER(38) | No | The increment of the total number of retries of the SQL statement. |
| PARTITION_TOTAL | NUMBER(38) | No | The total number of partitions scanned during the execution of the SQL statement. |
| PARTITION_DELTA | NUMBER(38) | No | The increment of the total number of partitions scanned during the execution of the SQL statement. |
| NESTED_SQL_TOTAL | NUMBER(38) | No | The total number of executions of nested SQL queries, which increases by 1 each time an SQL subquery is executed. |
| NESTED_SQL_DELTA | NUMBER(38) | No | The increment of the total number of executions of nested SQL queries. |
| SOURCE_IP | VARCHAR2(46) | No | The IP address of the request source. |
| SOURCE_PORT | NUMBER(38) | No | The port number of the request source. |
Example
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
