GV$OB_SQL_AUDIT is a global SQL audit table that you can use 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. 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 to determine whether to trigger the eviction of SQL. 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 triggered. 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 eviction condition | Stop eviction 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 |
| Record count | N/A | 900 million | 800 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) | The 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 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 current 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, taken by the system to process the request from receiving the request to the end of execution. |
| NET_TIME | bigint(20) | number(38) | Time, in microseconds, taken by the system to process the request from sending the RPC to receiving the request. |
| NET_WAIT_TIME | bigint(20) | number(38) | Time, in microseconds, taken by the system to process the request from receiving the request to entering the queue. |
| QUEUE_TIME | bigint(20) | number(38) | Time, in microseconds, spent by the request waiting in the queue. |
| DECODE_TIME | bigint(20) | number(38) | Decoding time of the request, in microseconds, after the request is dequeued. |
| GET_PLAN_TIME | bigint(20) | number(38) | Time, in microseconds, taken by the system to process the request from the start of execution to the time when the plan is obtained. |
| 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 by all application events. |
| CONCURRENCY_WAIT_TIME | bigint(20) unsigned | number(38) | Total time, in microseconds, spent by all concurrency events. |
| USER_IO_WAIT_TIME | bigint(20) unsigned | number(38) | Total time, in microseconds, spent by all user_io events. |
| SCHEDULE_TIME | bigint(20) unsigned | number(38) | Time, in microseconds, spent by all schedule 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 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, in bytes, consumed 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 current transaction. |
| TX_STATE_VERSION | bigint(20) unsigned | NUMBER(38) | Version number of the current transaction state. |
| FLT_TRACE_ID | varchar(1024) | varchar(1024) | The trace ID for end-to-end tracing. If the field is empty, it indicates that the request is not monitored by end-to-end tracing. The value of this field is a UUID, which is different from the trace ID. For example, 000600d6-a5de-038c-6c80-df07e4e79149. |
| PL_TRACE_ID | varchar(128) | VARCHAR2(128) | The 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 the 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