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 taken 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 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 every second and determines 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 condition for triggering eviction is met, and eviction is triggered. If the actual memory usage of SQL audit falls to the specified threshold, the condition for stopping eviction is met, and eviction is stopped. The following table describes the SQL audit eviction mechanism.
| Trigger mechanism | SQL Audit memory range | Condition for triggering eviction | Condition for stopping 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 | 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) | ID of the corresponding SQL plan. |
| 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 waiting event. |
| P1TEXT | varchar(64) | varchar(64) | Parameter 1 of the waiting event. |
| P1 | bigint(20) unsigned | number(38) | Value of parameter 1. |
| P2TEXT | varchar(64) | varchar(64) | Parameter 2 of the waiting event. |
| P2 | bigint(20) unsigned | number(38) | Value of parameter 2. |
| P3TEXT | varchar(64) | varchar(64) | Parameter 3 of the waiting event. |
| P3 | bigint(20) unsigned | number(38) | Value of parameter 3. |
| LEVEL | bigint(20) | number(38) | Level of the waiting event. |
| WAIT_CLASS_ID | bigint(20) | number(38) | ID of the class to which the waiting event belongs. |
| WAIT_CLASS# | bigint(20) | number(38) | Index of the class to which the waiting event belongs. |
| WAIT_CLASS | varchar(64) | varchar(64) | Name of the class to which the waiting event belongs. |
| STATE | varchar(19) | varchar(19) | Status of the waiting event. |
| WAIT_TIME_MICRO | bigint(20) | number(38) | Time that the waiting event waited, in microseconds. |
| TOTAL_WAIT_TIME_MICRO | bigint(20) | number(38) | Total waiting time of all waits, in microseconds. |
| TOTAL_WAITS | bigint(20) | number(38) | Total number of waits. |
| 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) | Specifies whether the request is an internal SQL request. |
| IS_EXECUTOR_RPC | tinyint(4) | number(38) | Specifies whether the current request is an RPC request. |
| IS_HIT_PLAN | tinyint(4) | number(38) | Specifies 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 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 in the queue, in microseconds. |
| DECODE_TIME | bigint(20) | number(38) | Decode time after dequeuing, 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 contained in the request. |
| CONSISTENCY_LEVEL | bigint(20) | number(38) | Consistency level. Valid values:
|
| 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 consumed by the request. |
| EXPECTED_WORKER_COUNT | bigint(20) | number(38) | Expected number of worker threads. |
| USED_WORKER_COUNT | bigint(20) | number(38) | Actual number of worker threads used. |
| SCHED_INFO | varchar(16384) | varchar(16384) | Scheduling information of the request. |
| FUSE_ROW_CACHE_HIT | bigint(20) | number(38) | Not supported. The default value is NULL. |
| PS_CLIENT_STMT_ID | bigint(20) | NUMBER(38) | Client prepare ID corresponding to the request:
|
| PS_INNER_STMT_ID | bigint(20) | NUMBER(38) | Prepare ID corresponding to the request in the database:
|
| 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. Valid values:
|
| IS_BATCHED_MULTI_STMT | tinyint(4) | number(38) | Specifies 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. |
| LOCK_FOR_READ_TIME | bigint(20) |
NoticeThe |
Time spent waiting for locks when data is read, in microseconds. |
| PARAMS_VALUE | longtext | CLOB | Parameter value. |
| FLT_TRACE_ID | varchar(1024) | varchar(1024) | Trace ID for full-stack tracing. If the field is empty, it indicates that the request is not monitored by full-stack tracing. The field is of the UUID type and has a value such as 000600d6-a5de-038c-6c80-df07e4e79149. |
| PL_TRACE_ID | varchar(128) | NO | Trace ID of the outer PL of the current SQL statement (NULL if there is no outer PL). |
| PLSQL_EXEC_TIME | bigint(20) | NO | Execution time of PL (excluding SQL execution time), in microseconds. |
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 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 GV$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