GV$OB_SQL_AUDIT is a global SQL audit table that can be used to view the source of each client request, server information, execution status, wait time, and time spent in each phase.
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 in the tenant memory. 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 at intervals of 1 second to determine whether to trigger the eviction of SQL statements. The upper limit of the memory for SQL audit is calculated based on the value of the ob_sql_audit_percentage parameter and the maximum available memory.
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 disabled. The following table describes the SQL audit eviction mechanism.
| Trigger mechanism | SQL Audit memory range | Trigger condition for eviction | Stop condition for eviction |
|---|---|---|---|
| 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 |
| Record count | N/A | 9 million | 8 million |
GV$OB_SQL_AUDIT columns
| Column | 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) | If an SQL plan is being executed, the corresponding SQL ID is displayed. |
| 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) | Tenant ID of the client that sends the request. |
| TENANT_NAME | varchar(64) | varchar(46) | Tenant name of the client that sends the request. |
| EFFECTIVE_TENANT_ID | bigint(20) | number(38) | Tenant ID. |
| USER_ID | bigint(20) | number(38) | User ID of the client that sends the request. |
| USER_NAME | varchar(64) | varchar(46) | Username of the client 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) | Database ID. |
| DB_NAME | varchar(128) | varchar(128) | Database name. |
| 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) | Thread ID. |
| 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) | Class ID to which the waited event belongs. |
| WAIT_CLASS# | bigint(20) | number(38) | Index of the class to which the waited event belongs. |
| WAIT_CLASS | varchar(64) | varchar(64) | Name of the class to which the waited event belongs. |
| STATE | varchar(19) | varchar(19) | Status of the waited event. |
| WAIT_TIME_MICRO | bigint(20) | number(38) | Time, in microseconds, that the waited event waited. |
| TOTAL_WAIT_TIME_MICRO | bigint(20) | number(38) | Total time, in microseconds, spent in waiting during execution. |
| TOTAL_WAITS | bigint(20) | number(38) | Total number of waits during execution. |
| RPC_COUNT | bigint(20) | number(38) | Number of RPCs sent. |
| 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 plan cache. |
| REQUEST_TIME | bigint(20) | number(38) | Start time of execution, in microseconds. |
| ELAPSED_TIME | bigint(20) | number(38) | Total time, in microseconds, spent from receiving the request to the end of execution. |
| NET_TIME | bigint(20) | number(38) | Time, in microseconds, spent from sending the RPC to receiving the request. |
| NET_WAIT_TIME | bigint(20) | number(38) | Time, in microseconds, spent from receiving the request to entering the queue. |
| QUEUE_TIME | bigint(20) | number(38) | Queue waiting time of the request, 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, in microseconds, spent from the start of execution to obtaining the plan. |
| EXECUTE_TIME | bigint(20) | number(38) | Execution time of the plan, in microseconds. |
| APPLICATION_WAIT_TIME | bigint(20) unsigned | number(38) | Total time, in microseconds, spent in all application-related events. |
| CONCURRENCY_WAIT_TIME | bigint(20) unsigned | number(38) | Total time, in microseconds, spent in all concurrency-related events. |
| USER_IO_WAIT_TIME | bigint(20) unsigned | number(38) | Total time, in microseconds, spent in all user_io-related events. |
| SCHEDULE_TIME | bigint(20) unsigned | number(38) | Time, in microseconds, spent in all schedule-related events. |
| 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 the request contains a full-table scan. |
| 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 index microblocks accessed. |
| INDEX_BLOCK_CACHE_HIT | bigint(20) | NO | Number of hits in the index microblock cache. |
| BLOCKSCAN_BLOCK_CNT | bigint(20) | NO | Number of data microblocks scanned. |
| BLOCKSCAN_ROW_CNT | bigint(20) | NO | Number of data rows scanned. |
| PUSHDOWN_STORAGE_FILTER_ROW_CNT | bigint(20) | NO | Number of rows filtered by the storage filter. |
| REQUEST_MEMORY_USED | bigint(20) | number(38) | Memory, in bytes, consumed by the request. |
| EXPECTED_WORKER_COUNT | bigint(20) | number(38) | Expected number of worker threads for the request. |
| USED_WORKER_COUNT | bigint(20) | number(38) | Actual number of worker threads used by 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 column records the client prepare ID corresponding to the request:
|
| PS_INNER_STMT_ID | bigint(20) | NUMBER(38) | The value of this column records 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 current transaction. |
| TX_STATE_VERSION | bigint(20) unsigned | NUMBER(38) | Version number of the current transaction state. |
| FLT_TRACE_ID | varchar(1024) | varchar(1024) | Trace ID for end-to-end diagnostics. If the field is empty, it indicates that the request is not monitored by end-to-end diagnostics. The field is of the UUID type and has a different format from that of a trace. For example, 000600d6-a5de-038c-6c80-df07e4e79149 |
| PL_TRACE_ID | varchar(128) | VARCHAR2(128) | Trace ID of the outer PL of the current SQL statement. |
| 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, in microseconds, spent waiting for a read lock when data is read, |
| 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 the corresponding FLT_TRACE_ID values.
/* Enable session-level tracing to record the time costs 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