Note
This view is introduced since OceanBase Database V4.0.0.
Purpose
The GV$OB_SQL_AUDIT view displays the statistics about each SQL request on all OBServer nodes, such as the source and execution status. This view is tenant-specific, and only the sys tenant can query this view in other tenants.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| SVR_IP | varchar(46) | NO | The IP address of the server. |
| SVR_PORT | bigint(20) | NO | The port number of the server. |
| REQUEST_ID | bigint(20) | NO | The ID of the request. |
| SQL_EXEC_ID | bigint(20) | NO | The ID of the current execution. |
| TRACE_ID | varchar(128) | NO | The trace ID of the statement. |
| SID | bigint(20) unsigned | NO | The ID of the session connection. |
| CLIENT_IP | varchar(46) | NO | |
| CLIENT_PORT | bigint(20) | NO | The port number of the client that sent the request. |
| TENANT_ID | bigint(20) | NO | The ID of the tenant that sent the request. |
| TENANT_NAME | varchar(64) | NO | The name of the tenant that sent the request. |
| EFFECTIVE_TENANT_ID | bigint(20) | NO | The ID of the tenant. |
| USER_ID | bigint(20) | NO | The ID of the user that sent the request. |
| USER_NAME | varchar(64) | NO | The name of the user that sent the request. |
| USER_GROUP | bigint(20) | YES | The ID of the resource group to which the user belongs. |
| USER_CLIENT_IP | varchar(46) | NO | The IP address of the client that sent the request. |
| DB_ID | bigint(20) unsigned | NO | The ID of the database. |
| DB_NAME | varchar(128) | NO | The name of the database. |
| SQL_ID | varchar(32) | NO | The ID of the SQL statement. |
| QUERY_SQL | longtext | NO | The actual SQL statement. |
| PLAN_ID | bigint(20) | NO | The ID of the execution plan. |
| AFFECTED_ROWS | bigint(20) | NO | The number of rows affected. |
| RETURN_ROWS | bigint(20) | NO | The number of rows returned. |
| PARTITION_CNT | bigint(20) | NO | The number of partitions scanned by the request. |
| RET_CODE | bigint(20) | NO | The return code of the execution. Valid values: 0: indicates that the statement is successfully executed without errors. |
| QC_ID | bigint(20) unsigned | NO | The ID of the scheduler in the parallel execution scenario. |
| DFO_ID | bigint(20) | NO | The ID of the sub-plan being executed in the parallel execution scenario. |
| SQC_ID | bigint(20) | NO | The ID of the local coordinator in the parallel execution scenario. |
| WORKER_ID | bigint(20) | NO | The ID of the worker thread in the parallel execution scenario. |
| EVENT | varchar(64) | NO | The name of the wait event with the longest wait time. |
| P1TEXT | varchar(64) | NO | The first parameter of the wait event. |
| P1 | bigint(20) unsigned | NO | The value of the first parameter of the wait event. |
| P2TEXT | varchar(64) | NO | The second parameter of the wait event. |
| P2 | bigint(20) unsigned | NO | The value of the second parameter of the wait event. |
| P3TEXT | varchar(64) | NO | The third parameter of the wait event. |
| P3 | bigint(20) unsigned | NO | The value of the third parameter of the wait event. |
| LEVEL | bigint(20) | NO | The level of the wait event. |
| WAIT_CLASS_ID | bigint(20) | NO | The ID of the class to which the wait event belongs. |
| WAIT_CLASS# | bigint(20) | NO | The subscript of the class to which the wait event belongs. |
| WAIT_CLASS | varchar(64) | NO | The name of the class to which the wait event belongs. |
| STATE | varchar(19) | NO | The status of the wait event. |
| WAIT_TIME_MICRO | bigint(20) | NO | The wait time of the wait event in microseconds. |
| TOTAL_WAIT_TIME_MICRO | bigint(20) | NO | The total wait time in microseconds during execution. |
| TOTAL_WAITS | bigint(20) | NO | The total number of waits during the execution. |
| RPC_COUNT | bigint(20) | NO | The number of remote procedure calls (RPCs) sent. |
| PLAN_TYPE | bigint(20) | NO | The type of the execution plan. Valid values: |
| IS_INNER_SQL | tinyint(4) | NO | Indicates whether the request is an internal SQL request. |
| IS_EXECUTOR_RPC | tinyint(4) | NO | Indicates whether the current request is an RPC request. |
| IS_HIT_PLAN | tinyint(4) | NO | Indicates whether the plan cache is hit. |
| REQUEST_TIME | bigint(20) | NO | The time when the execution started, in microseconds. |
| ELAPSED_TIME | bigint(20) | NO | The amount of time elapsed from when the request was received to when the execution of the request ended, in microseconds. |
| NET_TIME | bigint(20) | NO | The amount of time elapsed from when the RPC request was sent to when the request was received, in microseconds. |
| NET_WAIT_TIME | bigint(20) | NO | The amount of time elapsed from when the request was received to when it entered the queue, in microseconds. |
| QUEUE_TIME | bigint(20) | NO | The time that the request waited in the queue, in microseconds. |
| DECODE_TIME | bigint(20) | NO | The time spent on decoding the request after it left the queue, in microseconds. |
| GET_PLAN_TIME | bigint(20) | NO | The amount of time elapsed from when the processing started to when the plan was obtained, in microseconds. |
| EXECUTE_TIME | bigint(20) | NO | The time consumed for plan execution, in microseconds. |
| APPLICATION_WAIT_TIME | bigint(20) unsigned | NO | The total amount of time spent on waiting for events of the Application class, in microseconds. |
| CONCURRENCY_WAIT_TIME | bigint(20) unsigned | NO | The total amount of time spent on waiting for events of the Concurrency class, in microseconds. |
| USER_IO_WAIT_TIME | bigint(20) unsigned | NO | The total amount of time spent on waiting for events of the user_io class, in microseconds. |
| SCHEDULE_TIME | bigint(20) unsigned | NO | The total amount of time spent on waiting for events of the Schedule class, in microseconds. |
| ROW_CACHE_HIT | bigint(20) | NO | The number of row cache hits. |
| BLOOM_FILTER_CACHE_HIT | bigint(20) | NO | The number of Bloom filter cache hits. |
| BLOCK_CACHE_HIT | bigint(20) | NO | The number of block cache hits. |
| DISK_READS | bigint(20) | NO | The number of physical reads. |
| RETRY_CNT | bigint(20) | NO | The number of retries. |
| TABLE_SCAN | tinyint(4) | NO | Indicates whether the request contains a full table scan. |
| CONSISTENCY_LEVEL | bigint(20) | NO | The consistency level. Valid values include:
|
| MEMSTORE_READ_ROW_COUNT | bigint(20) | NO | The number of rows read in the MemStore. |
| SSSTORE_READ_ROW_COUNT | bigint(20) | NO | The number of rows consecutively read in the SSStore. |
| DATA_BLOCK_READ_CNT | bigint(20) | NO | The number of data microblocks accessed. |
| DATA_BLOCK_CACHE_HIT | bigint(20) | NO | The number of data microblock cache hits. |
| INDEX_BLOCK_READ_CNT | bigint(20) | NO | The number of intermediate-layer microblocks accessed. |
| INDEX_BLOCK_CACHE_HIT | bigint(20) | NO | The number of intermediate-layer microblock cache hits. |
| BLOCKSCAN_BLOCK_CNT | bigint(20) | NO | The number of data microblocks scanned during a unilateral scan. |
| BLOCKSCAN_ROW_CNT | bigint(20) | NO | The number of data rows scanned during a unilateral scan. |
| PUSHDOWN_STORAGE_FILTER_ROW_CNT | bigint(20) | NO | The number of rows that remain after the filter condition is pushed down to and applied in the storage layer. |
| REQUEST_MEMORY_USED | bigint(20) | NO | The memory consumed by the request. |
| EXPECTED_WORKER_COUNT | bigint(20) | NO | The number of worker threads expected by the request. |
| USED_WORKER_COUNT | bigint(20) | NO | The number of worker threads used by the request. |
| SCHED_INFO | varchar(16384) | YES | The scheduling information about the request. |
| FUSE_ROW_CACHE_HIT | bigint(20) | NO | At present, this column is not supported and is NULL by default. |
| PS_CLIENT_STMT_ID | bigint(20) | NO | The ID of the prepared statement corresponding to the request.
|
| PS_INNER_STMT_ID | bigint(20) | NO | The ID of the prepared statement corresponding to the request.
|
| TX_ID | bigint(20) | NO | The ID of the transaction corresponding to the request. |
| SNAPSHOT_VERSION | bigint(20) | NO | The snapshot version used by the SQL statement. |
| REQUEST_TYPE | bigint(20) | NO | The type of the request. Valid values: 0: indicates an invalid request.1: indicates an internal request.2: indicates a local request whose execution plan is a local plan.3: indicates a remote request.4: indicates a distributed request.5: indicates an SQL PREPARE request.6: indicates an SQL Execute Stmt request. |
| IS_BATCHED_MULTI_STMT | tinyint(4) | NO | Indicates whether optimization has been performed for batch execution of multiple statements. |
| OB_TRACE_INFO | varchar(4096) | NO | The trace information configured by the user. |
| PLAN_HASH | bigint(20) unsigned | NO | The hash value of the execution plan. |
| LOCK_FOR_READ_TIME | bigint(20) | NO | The time in microseconds spent on waiting for locked data to be unlocked before it can be read. |
| PARAMS_VALUE | longtext | NO | The PS-bound parameter value. |
| RULE_NAME | varchar(256) | NO | The name of the rule. |
| PARTITION_HIT | tinyint(4) | NO |
|
| TX_INTERNAL_ROUTING | bigint(20) | NO | Indicates whether the SQL statement is internally routed. Valid values
|
| TX_STATE_VERSION | bigint(20) unsigned | NO | The transaction status version of the SQL statement. |
| FLT_TRACE_ID | varchar(1024) | NO | The trace ID of this record in end-to-end diagnostics. If this column is left empty, the record is not monitored in end-to-end diagnostics. The value is a universally unique identifier (UUID), which is different from a trace. The format is similar to 000600d6-a5de-038c-6c80-df07e4e79149.
NoteThis column is introduced since OceanBase Database V4.2.1. |
| PL_TRACE_ID | varchar(128) | NO | The trace ID of the outer PL for the current SQL statement. (If there is no outer PL, the value is NULL.)
NoteThis column is introduced in V4.2.2. |
| PLSQL_EXEC_TIME | bigint(20) | NO | The time taken for PL execution (excluding SQL execution time), in microseconds.
NoteThis column is introduced in V4.2.2. |
Example
obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_SQL_AUDIT limit 1;
The query result is as follows:
+----------------+----------+------------+-------------+------------------------------------+------------+-----------+-------------+-----------+-------------+---------------------+---------+-----------+------------+----------------+--------+-----------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+-------+--------+------+--------+------+--------+------+-------+---------------+-------------+------------+--------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+----------------------+----------------------+-----------------------+---------------------+-------------------+---------------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+---------+---------------------+--------------+-----------------------+---------------+----------------------+--------------------+--------------+-----------+---------------+---------------------+------------------+--------------+-------------+-----------------+
| SVR_IP | SVR_PORT | REQUEST_ID | SQL_EXEC_ID | TRACE_ID | SID | CLIENT_IP | CLIENT_PORT | TENANT_ID | TENANT_NAME | EFFECTIVE_TENANT_ID | USER_ID | USER_NAME | USER_GROUP | USER_CLIENT_IP | DB_ID | DB_NAME | SQL_ID | QUERY_SQL | PLAN_ID | AFFECTED_ROWS | RETURN_ROWS | PARTITION_CNT | RET_CODE | QC_ID | DFO_ID | SQC_ID | WORKER_ID | EVENT | P1TEXT | P1 | P2TEXT | P2 | P3TEXT | P3 | LEVEL | WAIT_CLASS_ID | WAIT_CLASS# | WAIT_CLASS | STATE | WAIT_TIME_MICRO | TOTAL_WAIT_TIME_MICRO | TOTAL_WAITS | RPC_COUNT | PLAN_TYPE | IS_INNER_SQL | IS_EXECUTOR_RPC | IS_HIT_PLAN | REQUEST_TIME | ELAPSED_TIME | NET_TIME | NET_WAIT_TIME | QUEUE_TIME | DECODE_TIME | GET_PLAN_TIME | EXECUTE_TIME | APPLICATION_WAIT_TIME | CONCURRENCY_WAIT_TIME | USER_IO_WAIT_TIME | SCHEDULE_TIME | ROW_CACHE_HIT | BLOOM_FILTER_CACHE_HIT | BLOCK_CACHE_HIT | DISK_READS | RETRY_CNT | TABLE_SCAN | CONSISTENCY_LEVEL | MEMSTORE_READ_ROW_COUNT | SSSTORE_READ_ROW_COUNT | DATA_BLOCK_READ_CNT | DATA_BLOCK_CACHE_HIT | INDEX_BLOCK_READ_CNT | INDEX_BLOCK_CACHE_HIT | BLOCKSCAN_BLOCK_CNT | BLOCKSCAN_ROW_CNT | PUSHDOWN_STORAGE_FILTER_ROW_CNT | REQUEST_MEMORY_USED | EXPECTED_WORKER_COUNT | USED_WORKER_COUNT | SCHED_INFO | FUSE_ROW_CACHE_HIT | PS_CLIENT_STMT_ID | PS_INNER_STMT_ID | TX_ID | SNAPSHOT_VERSION | REQUEST_TYPE | IS_BATCHED_MULTI_STMT | OB_TRACE_INFO | PLAN_HASH | LOCK_FOR_READ_TIME | PARAMS_VALUE | RULE_NAME | PARTITION_HIT | TX_INTERNAL_ROUTING | TX_STATE_VERSION | FLT_TRACE_ID | PL_TRACE_ID | PLSQL_EXEC_TIME |
+----------------+----------+------------+-------------+------------------------------------+------------+-----------+-------------+-----------+-------------+---------------------+---------+-----------+------------+----------------+--------+-----------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+-------+--------+------+--------+------+--------+------+-------+---------------+-------------+------------+--------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+----------------------+----------------------+-----------------------+---------------------+-------------------+---------------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+---------+---------------------+--------------+-----------------------+---------------+----------------------+--------------------+--------------+-----------+---------------+---------------------+------------------+--------------+-------------+-----------------+
| xx.xx.xx.xx | 28824 | 17283006 | 57198930 | Y70980BA1CCFB-00060E2D136A1FF6-0-0 | 3221502837 | 0.0.0.0 | 0 | 1 | sys | 1 | 200001 | root | 0 | 0.0.0.0 | 201001 | oceanbase | 24D486F20861EBD9E0217692DB0E9C61 | update __all_weak_read_service set min_version=1704767124620100000, max_version=1704767124620100000 where tenant_id = 1 and level_id = 0 and level_value = '' and min_version = 1704767124420061000 and max_version = 1704767124420061000 | 12660 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | | | 0 | | 0 | | 0 | 0 | 100 | 0 | OTHER | MAX_WAIT TIME ZERO | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 1 | 1704767124820113 | 184 | 0 | 0 | 0 | 0 | 32 | 152 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | 0 | 0 | 0 | 3 | 2 | 2 | 2 | 2 | 4 | 2 | 0 | 0 | 0 | 254360 | 0 | 0 | NULL | 0 | -1 | -1 | 2328294 | 1704767124788549000 | 1 | 0 | NULL | 10435171333981900908 | 0 | | | 1 | 0 | 0 | | NULL | 0 |
+----------------+----------+------------+-------------+------------------------------------+------------+-----------+-------------+-----------+-------------+---------------------+---------+-----------+------------+----------------+--------+-----------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+-------+--------+------+--------+------+--------+------+-------+---------------+-------------+------------+--------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+----------------------+----------------------+-----------------------+---------------------+-------------------+---------------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+---------+---------------------+--------------+-----------------------+---------------+----------------------+--------------------+--------------+-----------+---------------+---------------------+------------------+--------------+-------------+-----------------+
1 row in set (0.040 sec)