GV$OB_SQL_AUDIT is a global SQL audit table that can be used to view the client source of each request, execution server information, execution status, wait time, and time spent in each phase of execution.
SQL Audit settings
Enable SQL Audit.
obclient> ALTER SYSTEM SET enable_sql_audit = true; /*Enable SQL Audit*/ obclient> ALTER SYSTEM SET enable_sql_audit = false; /*Disable SQL Audit*/Set the percentage of memory for SQL Audit. The default value is 3, and the value range is [0,80].
obclient> SET global ob_sql_audit_percentage = 3; Query OK, 0 rows affected
SQL Audit eviction mechanism
The background task of the tenant checks the memory usage of SQL Audit and that of the OBServer node every second, and determines whether to trigger eviction. The upper limit of SQL Audit memory is the maximum memory allocated to SQL Audit based on the value of ob_sql_audit_percentage.
If the actual memory usage of SQL Audit reaches the specified threshold, the conditions for triggering eviction are met, and eviction is enabled. If the actual memory usage of SQL Audit falls to the specified threshold, the conditions for stopping eviction are met, and eviction is stopped. The following table describes the SQL Audit eviction mechanism.
| Trigger mechanism | SQL Audit memory range | Trigger condition | Stop condition |
|---|---|---|---|
| Memory usage | [0,64M] | Memory upper limit × 50% | 0 M |
| Memory usage | [64M,100M] | Memory upper limit-20M | Memory upper limit-40 M |
| Memory usage | [100M,5G] | Memory upper limit × 80% | Memory upper limit × 60% |
| Memory usage | [5G,+∞) | Memory upper limit-1G | Memory upper limit-2 G |
| Number of records | N/A | 900 million | 800 million |
Fields of GV$OB_SQL_AUDIT
| Field | Type (MySQL mode) | Type (Oracle mode) | Description |
|---|---|---|---|
| SVR_IP | varchar(46) | varchar(46) | IP address of the server. |
| SVR_PORT | bigint(20) | number(38) | Port number of the server. |
| REQUEST_ID | bigint(20) | number(38) | ID of the request. |
| SQL_EXEC_ID | bigint(20) | number(38) | ID of the executed SQL statement if the SQL plan is being executed. |
| TRACE_ID | varchar(128) | varchar(128) | Trace ID of the statement. |
| SID | bigint(20) unsigned | number(38) | Session ID. |
| CLIENT_IP | varchar(46) | varchar(46) |
|
| CLIENT_PORT | bigint(20) | number(38) | Port number of the client that sends the request. |
| TENANT_ID | bigint(20) | number(38) | ID of the tenant that sends the request. |
| TENANT_NAME | varchar(64) | varchar(46) | Name of the tenant that sends the request. |
| EFFECTIVE_TENANT_ID | bigint(20) | number(38) | ID of the tenant. |
| USER_ID | bigint(20) | number(38) | ID of the user that sends the request. |
| USER_NAME | varchar(64) | varchar(46) | Name of the user that sends the request. |
| USER_GROUP | bigint(20) | number(38) | ID of the resource group to which the user belongs. |
| USER_CLIENT_IP | varchar(32) | varchar(32) | IP address of the client that sends the request. |
| DB_ID | bigint(20) unsigned | number(38) | ID of the database. |
| DB_NAME | varchar(128) | varchar(128) | Name of the database. |
| SQL_ID | varchar(32) | varchar(32) | ID of the SQL statement. |
| QUERY_SQL | longtext | CLOB | Actual SQL statement. |
| PLAN_ID | bigint(20) | number(38) | ID of the execution plan. |
| AFFECTED_ROWS | bigint(20) | number(38) | Number of affected rows. |
| RETURN_ROWS | bigint(20) | number(38) | Number of returned rows. |
| PARTITION_CNT | bigint(20) | number(38) | Number of partitions involved in the request. |
| RET_CODE | bigint(20) | number(38) | Return code of the execution result. |
| QC_ID | bigint(20) unsigned | number(38) | qc_id in parallel queries. |
| DFO_ID | bigint(20) | number(38) | dfo_id in parallel queries. |
| SQC_ID | bigint(20) | number(38) | sqc_id in parallel queries. |
| WORKER_ID | bigint(20) | number(38) | ID of the thread. |
| EVENT | varchar(64) | varchar(64) | Name of the longest waited event. |
| P1TEXT | varchar(64) | varchar(64) | Event parameter 1. |
| P1 | bigint(20) unsigned | number(38) | Value of event parameter 1. |
| P2TEXT | varchar(64) | varchar(64) | Event parameter 2. |
| P2 | bigint(20) unsigned | number(38) | Value of event parameter 2. |
| P3TEXT | varchar(64) | varchar(64) | Event parameter 3. |
| P3 | bigint(20) unsigned | number(38) | Value of event parameter 3. |
| LEVEL | bigint(20) | number(38) | Level of the waited event. |
| WAIT_CLASS_ID | bigint(20) | number(38) | ID of the waited event class. |
| WAIT_CLASS# | bigint(20) | number(38) | Index of the waited event class. |
| WAIT_CLASS | varchar(64) | varchar(64) | Name of the waited event class. |
| STATE | varchar(19) | varchar(19) | Status of the waited event. |
| WAIT_TIME_MICRO | bigint(20) | number(38) | Time waited for the waited event, in microseconds. |
| TOTAL_WAIT_TIME_MICRO | bigint(20) | number(38) | Total time waited in microseconds. |
| TOTAL_WAITS | bigint(20) | number(38) | Total number of waits. |
| RPC_COUNT | bigint(20) | number(38) | Number of RPCs. |
| PLAN_TYPE | bigint(20) | number(38) | Type of the execution plan.
|
| IS_INNER_SQL | tinyint(4) | number(38) | Indicates whether the request is an internal SQL request. |
| IS_EXECUTOR_RPC | tinyint(4) | number(38) | Indicates whether the request is an RPC request. |
| IS_HIT_PLAN | tinyint(4) | number(38) | Indicates whether the plan is hit in the cache. |
| REQUEST_TIME | bigint(20) | number(38) | Start time of execution, in microseconds. |
| ELAPSED_TIME | bigint(20) | number(38) | Total time consumed from receiving the request to the end of execution, in microseconds. |
| NET_TIME | bigint(20) | number(38) | Time from sending the RPC to receiving the request, in microseconds. |
| NET_WAIT_TIME | bigint(20) | number(38) | Time from receiving the request to entering the queue, in microseconds. |
| QUEUE_TIME | bigint(20) | number(38) | Time spent waiting for the request in the queue, in microseconds. |
| DECODE_TIME | bigint(20) | number(38) | Decode time after the request is dequeued, in microseconds. |
| GET_PLAN_TIME | bigint(20) | number(38) | Time spent from the start of execution to obtaining the plan, in microseconds. |
| EXECUTE_TIME | bigint(20) | number(38) | Execution time of the plan, in microseconds. |
| APPLICATION_WAIT_TIME | bigint(20) unsigned | number(38) | Total time of all application-related events, in microseconds. |
| CONCURRENCY_WAIT_TIME | bigint(20) unsigned | number(38) | Total time of all concurrency-related events, in microseconds. |
| USER_IO_WAIT_TIME | bigint(20) unsigned | number(38) | Total time of all user_io-related events, in microseconds. |
| SCHEDULE_TIME | bigint(20) unsigned | number(38) | Time of all schedule-related events, in microseconds. |
| ROW_CACHE_HIT | bigint(20) | number(38) | Number of hits in the row cache. |
| BLOOM_FILTER_CACHE_HIT | bigint(20) | number(38) | Number of hits in the Bloom filter cache. |
| BLOCK_CACHE_HIT | bigint(20) | number(38) | Number of hits in the block cache. |
| DISK_READS | bigint(20) | number(38) | Number of physical reads. |
| RETRY_CNT | bigint(20) | number(38) | Number of retries. |
| TABLE_SCAN | tinyint(4) | number(38) | Indicates whether a full table scan is included in the request. |
| CONSISTENCY_LEVEL | bigint(20) | number(38) | Consistency level. |
| MEMSTORE_READ_ROW_COUNT | bigint(20) | number(38) | Number of rows read from the MemStore. |
| SSSTORE_READ_ROW_COUNT | bigint(20) | number(38) | Number of rows read from the SSStore. |
| DATA_BLOCK_READ_CNT | bigint(20) | NO | Number of data microblocks accessed. |
| DATA_BLOCK_CACHE_HIT | bigint(20) | NO | Number of hits in the data microblock cache. |
| INDEX_BLOCK_READ_CNT | bigint(20) | NO | Number of intermediate layer microblocks accessed. |
| INDEX_BLOCK_CACHE_HIT | bigint(20) | NO | Number of hits in the intermediate layer microblock cache. |
| BLOCKSCAN_BLOCK_CNT | bigint(20) | NO | Number of data microblocks scanned in one direction. |
| BLOCKSCAN_ROW_CNT | bigint(20) | NO | Number of data rows scanned in one direction. |
| PUSHDOWN_STORAGE_FILTER_ROW_CNT | bigint(20) | NO | Number of rows filtered by the storage filter. |
| REQUEST_MEMORY_USED | bigint(20) | number(38) | Memory used by the request. |
| EXPECTED_WORKER_COUNT | bigint(20) | number(38) | Number of worker threads expected for the request. |
| USED_WORKER_COUNT | bigint(20) | number(38) | Number of worker threads actually used for the request. |
| SCHED_INFO | varchar(16384) | varchar(16384) | Scheduling information of the request. |
| PS_CLIENT_STMT_ID | bigint(20) | NUMBER(38) | The value of this field is the client prepare ID corresponding to the request:
|
| PS_INNER_STMT_ID | bigint(20) | NUMBER(38) | The value of this field is the internal prepare ID corresponding to the request:
|
| TX_ID | bigint(20) | NO | Hash value of the transaction corresponding to the request. |
| SNAPSHOT_VERSION | bigint(20)unsigned | NO | Snapshot read version of the current statement. |
| REQUEST_TYPE | bigint(20) | number(38) | Type of the request.
|
| IS_BATCHED_MULTI_STMT | tinyint(4) | number(38) | Indicates whether batch multi-statement optimization is performed. |
| OB_TRACE_INFO | VARCHAR2(4096) | VARCHAR2(4096) | User-defined trace information. |
| PLAN_HASH | bigint(20) unsigned | number(38) | Hash value of the execution plan. |
| PARAMS_VALUE | longtext | CLOB | Parameter values. |
| RULE_NAME | varchar(256) | VARCHAR2(256) | Rule name |
| TX_INTERNAL_ROUTING | bigint(20) | NUMBER | Indicates whether transaction routing is enabled for the transaction. |
| TX_STATE_VERSION | bigint(20) unsigned | NUMBER(38) | Version number of the transaction state. |
| FLT_TRACE_ID | varchar(1024) | varchar(1024) | Trace ID for end-to-end tracing. If the field is empty, it indicates that end-to-end tracing is not enabled. The field stores a UUID, which is different from the Trace ID. For example, 000600d6-a5de-038c-6c80-df07e4e79149 |
| PLSQL_EXEC_TIME | bigint(20) | NUMBER(38) | Execution time of the PL statement, in microseconds. |
| TOTAL_MEMSTORE_READ_ROW_COUNT | bigint(20) | NUMBER(38) | Total number of rows read from the MemStore. |
| TOTAL_SSSTORE_READ_ROW_COUNT | bigint(20) | NUMBER(38) | Total number of rows read from the SSStore. |
| PROXY_USER | varchar(128) | VARCHAR2(128) | The name of the proxy user in proxy login scenarios. |
| FUSE_ROW_CACHE_HIT | bigint(20) | number(38) | Not supported. The default value is NULL. |
| LOCK_FOR_READ_TIME | bigint(20) |
NoticeThis view does not contain the |
Time spent waiting for a read lock when data is read, in microseconds. |
| PARTITION_HIT | tinyint(4) | - | Indicates whether the local partition is hit. |
Examples
You can query various dimensions of SQL execution information through the GV$OB_SQL_AUDIT view. The following example queries SQL statements whose execution time exceeds 100 ms and their FLT_TRACE_ID values.
/* Enable session-level tracing to record the execution time and other related information of all SQL statements executed in the current session. The sampling frequency is 50%. */
obclient> CALL DBMS_MONITOR.OB_SESSION_TRACE_ENABLE(null,1,0.5,'ALL');
Query OK, 0 rows affected
obclient [oceanbase]> SELECT request_id,usec_to_time(request_time),ELAPSED_TIME,QUEUE_TIME,EXECUTE_TIME,FLT_TRACE_ID,QUERY_SQL FROM v$OB_SQL_AUDIT where ELAPSED_TIME > 100000 limit 10 ;
+------------+----------------------------+--------------+------------+--------------+--------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| request_id | usec_to_time(request_time) | ELAPSED_TIME | QUEUE_TIME | EXECUTE_TIME | FLT_TRACE_ID | QUERY_SQL |
+------------+----------------------------+--------------+------------+--------------+--------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 5950244 | 2023-09-07 16:20:47.465958 | 127623 | 26 | 127206 | 000604c0-8981-9184-518a-e234439d873c | CREATE TABLE tbl2(c1 INT PRIMARY KEY,c2 INT) |
| 5951861 | 2023-09-07 16:21:07.887121 | 333776 | 38 | 310298 | | ALTER TABLE tbl2 ADD CONSTRAINT fk1 FOREIGN KEY (c2) REFERENCES tbl3(c1) ON UPDATE SET NULL |
| 5953177 | 2023-09-07 16:21:28.215377 | 174416 | 24 | 174186 | 000604c0-8bef-5afb-f9d3-2ee0dfab4c8f | SELECT request_id,usec_to_time(request_time),ELAPSED_TIME,QUEUE_TIME,EXECUTE_TIME,FLT_TRACE_ID,QUERY_SQL FROM v$OB_SQL_AUDIT where ELAPSED_TIME > 100000 |
| 5954522 | 2023-09-07 16:21:48.317360 | 128803 | 27 | 128542 | 000604c0-8d22-1659-7b0c-a0ac0645894d | SELECT request_id,usec_to_time(request_time),ELAPSED_TIME,QUEUE_TIME,EXECUTE_TIME,FLT_TRACE_ID,QUERY_SQL FROM v$OB_SQL_AUDIT where ELAPSED_TIME > 100000 limit 10 |
+------------+----------------------------+--------------+------------+--------------+--------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set