GV$OB_SQL_AUDIT is a global SQL audit table. You can use it to view the client source of each request, execution server information, execution status information, wait time, and the time consumed in each execution phase.
SQL Audit settings
Set the SQL Audit switch.
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 tenant memory occupied by the memory used by SQL Audit. The default memory percentage is 3, and the valid range is [0,80].
obclient> SET global ob_sql_audit_percentage = 3; Query OK, 0 rows affected
SQL Audit eviction mechanism
A background task of a tenant determines whether to trigger SQL eviction every 1s based on the memory usage of the OBServer node and SQL Audit. The SQL Audit memory upper limit is the maximum available memory allocated based on ob_sql_audit_percentage.
When the actual memory usage of SQL Audit reaches the specified threshold, the eviction trigger condition is met and eviction starts. When the actual memory usage of SQL Audit drops to the specified threshold, the eviction stop condition is met and eviction stops. The SQL Audit eviction mechanism is shown in the following table.
Trigger mechanism |
SQL Audit memory range |
Conditions that trigger eviction |
Conditions for stopping eviction |
|---|---|---|---|
| Memory usage | [0,64M] | Memory upper limit *50% | 0 M |
| Memory usage | [64M,100M] | Memory limit - 20M | Memory limit - 40 MB |
| Memory usage | [100M,5G] | Memory upper limit *80% | Memory upper limit * 60% |
| Memory usage | [5G,+∞) | Memory limit - 1 GB | Memory limit - 2 GB |
| Number of records | None | 9 million | 8 million |
GV$OB_SQL_AUDIT columns
Field name |
Type (MySQL mode) |
Type (Oracle mode) |
Description |
|---|---|---|---|
| SVR_IP | varchar(46) | varchar(46) | IP address |
| SVR_PORT | bigint(20) | number(38) | Port number |
| REQUEST_ID | bigint(20) | number(38) | Requested ID number |
| SQL_EXEC_ID | bigint(20) | number(38) | If the SQL plan is being executed, the corresponding SQL ID is displayed |
| TRACE_ID | varchar(128) | varchar(128) | Trace ID of this statement |
| SID | bigint(20) unsigned | number(38) | Session ID |
| CLIENT_IP | varchar(46) | varchar(46) |
|
| CLIENT_PORT | bigint(20) | number(38) | Client port that sends requests |
| TENANT_ID | bigint(20) | number(38) | Tenant ID that sends the request |
| TENANT_NAME | varchar(64) | varchar(46) | Name of the tenant sending the request |
| EFFECTIVE_TENANT_ID | bigint(20) | number(38) | Tenant ID |
| USER_ID | bigint(20) | number(38) | User ID that sent the request |
| USER_NAME | varchar(64) | varchar(46) | Username of the user sending 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) | The ID of this SQL |
| QUERY_SQL | longtext | CLOB | Actual SQL statement. Actual SQL statement.
NoteThis field can be viewed only by the SYS tenant starting from V4.2.5 BP2. |
| PLAN_ID | bigint(20) | number(38) | Execution plan ID |
| AFFECTED_ROWS | bigint(20) | number(38) | Number of affected rows |
| RETURN_ROWS | bigint(20) | number(38) | Number of rows returned |
| PARTITION_CNT | bigint(20) | number(38) | Number of partitions involved in the request |
| RET_CODE | bigint(20) | number(38) | Execution result return code |
| QC_ID | bigint(20) unsigned | number(38) | qc_id in parallel query |
| 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) | Longest wait event name |
| P1TEXT | varchar(64) | varchar(64) | Wait event parameter 1 |
| P1 | bigint(20) unsigned | number(38) | Value of wait event parameter 1 |
| P2TEXT | varchar(64) | varchar(64) | Wait event parameter 2 |
| P2 | bigint(20) unsigned | number(38) | Value of wait event parameter 2 |
| P3TEXT | varchar(64) | varchar(64) | Wait event parameter 3 |
| P3 | bigint(20) unsigned | number(38) | Value of wait event parameter 3 |
| LEVEL | bigint(20) | number(38) | Level of the wait event |
| WAIT_CLASS_ID | bigint(20) | number(38) | Class ID of the wait event |
| WAIT_CLASS# | bigint(20) | number(38) | Index of the class to which the wait event belongs |
| WAIT_CLASS | varchar(64) | varchar(64) | Name of the class to which the wait event belongs |
| STATE | varchar(19) | varchar(19) | Status of the wait event |
| WAIT_TIME_MICRO | bigint(20) | number(38) | Time waited by this wait event, in microseconds |
| TOTAL_WAIT_TIME_MICRO | bigint(20) | number(38) | Total time of all waits during execution, in microseconds |
| 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) | Execution plan type:
|
| IS_INNER_SQL | tinyint(4) | number(38) | Whether it is an internal SQL request |
| IS_EXECUTOR_RPC | tinyint(4) | number(38) | Whether the current request is an RPC request |
| IS_HIT_PLAN | tinyint(4) | number(38) | Whether the plan cache is hit |
| REQUEST_TIME | bigint(20) | number(38) | Start execution time point, in microseconds |
| ELAPSED_TIME | bigint(20) | number(38) | Total time consumed from receiving the request to completion, 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) | Request wait time 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 from start of execution to obtaining the plan, in microseconds |
| EXECUTE_TIME | bigint(20) | number(38) | Plan execution time, in microseconds |
| APPLICATION_WAIT_TIME | bigint(20) unsigned | number(38) | Total time of all Application events, in microseconds |
| CONCURRENCY_WAIT_TIME | bigint(20) unsigned | number(38) | Total time of all Concurrency class events, in microseconds |
| USER_IO_WAIT_TIME | bigint(20) unsigned | number(38) | Alluser_ioTotal time of class events, in microseconds |
| SCHEDULE_TIME | bigint(20) unsigned | number(38) | Time of all Schedule events, in microseconds |
| ROW_CACHE_HIT | bigint(20) | number(38) | Row cache hit count |
| BLOOM_FILTER_CACHE_HIT | bigint(20) | number(38) | Bloom Filter cache hits |
| BLOCK_CACHE_HIT | bigint(20) | number(38) | Block cache hit count |
| DISK_READS | bigint(20) | number(38) | Physical reads |
| RETRY_CNT | bigint(20) | number(38) | Number of retries |
| TABLE_SCAN | tinyint(4) | number(38) | Determine whether the request contains a full table scan |
| CONSISTENCY_LEVEL | bigint(20) | number(38) | Consistency level. Valid values are as follows:
|
| MEMSTORE_READ_ROW_COUNT | bigint(20) | number(38) | Number of rows read from MemStore |
| SSSTORE_READ_ROW_COUNT | bigint(20) | number(38) | SSSTORENumber of rows read |
| DATA_BLOCK_READ_CNT | bigint(20) | NO | Number of data microblocks accessed |
| DATA_BLOCK_CACHE_HIT | bigint(20) | NO | Number of data microblock Cache hits |
| INDEX_BLOCK_READ_CNT | bigint(20) | NO | Number of intermediate-layer microblocks accessed |
| INDEX_BLOCK_CACHE_HIT | bigint(20) | NO | Number of middle-layer microblock cache hits |
| BLOCKSCAN_BLOCK_CNT | bigint(20) | NO | Number of data microblocks scanned on one side |
| BLOCKSCAN_ROW_CNT | bigint(20) | NO | Number of data rows scanned on one side |
| PUSHDOWN_STORAGE_FILTER_ROW_CNT | bigint(20) | NO | Number of rows after filtering by the pushed-down storage Filter |
| REQUEST_MEMORY_USED | bigint(20) | number(38) | Memory consumed by this request |
| EXPECTED_WORKER_COUNT | bigint(20) | number(38) | Number of worker threads expected by the request |
| USED_WORKER_COUNT | bigint(20) | number(38) | Number of worker threads actually used by the request |
| SCHED_INFO | varchar(16384) | varchar(16384) | Requested scheduling information |
| FUSE_ROW_CACHE_HIT | bigint(20) | number(38) | This field is not supported currently. The field defaults toNULL |
| PS_CLIENT_STMT_ID | bigint(20) | NUMBER(38) | This field records the client Prepare ID corresponding to the request:
|
| PS_INNER_STMT_ID | bigint(20) | NUMBER(38) | This field records the internal (database-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 number of the current statement |
| REQUEST_TYPE | bigint(20) | number(38) | Type corresponding to the request:
|
| IS_BATCHED_MULTI_STMT | tinyint(4) | number(38) | Whether to optimize Batch Multi Stmt |
| OB_TRACE_INFO | VARCHAR2(4096) | VARCHAR2(4096) | User-set trace information |
| PLAN_HASH | bigint(20) unsigned | number(38) | Hash value of the execution plan |
| LOCK_FOR_READ_TIME | bigint(20) |
NoteThis view does not currently have the |
Time spent waiting for locks when reading data, in microseconds |
| PARAMS_VALUE | longtext | CLOB | Parameter value |
| FLT_TRACE_ID | varchar(1024) | varchar(1024) | Records the Trace ID for end-to-end tracing. If it is empty, it indicates that it is not monitored by end-to-end tracing. This field is a UUID, which is different from Trace. An example of its representation is: 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 | PL execution duration (excluding SQL execution time), in microseconds |
| FORMAT_SQL_ID | varchar(32) | NO | Indicates the MD5 value generated for the record by Format SQL text
NoteThis field was introduced in V4.2.3. |
| NETWORK_WAIT_TIME | bigint(20) unsigned | YES | Total time of all Network events, in microseconds
NoteThis field was introduced in V4.2.3. |
| STMT_TYPE | varchar(128) | YES | The DML type is returned as needed:
NoteThis field was introduced in V4.2.3. |
| TOTAL_MEMSTORE_READ_ROW_COUNT | bigint(20) | NO | Total number of rows read from MEMSTORE during the entire work process (this variable is displayed only in the thread that displays query_text)
NoteThis field was introduced in V4.2.3. |
| TOTAL_SSSTORE_READ_ROW_COUNT | bigint(20) | NO | Total number of rows read from SSSTORE during the entire work process (this variable is displayed only in the thread that displays query_text)
NoteThis field was introduced starting from V4.2.3. |
| PROXY_USER | varchar(128) | YES |
NoteThis field was introduced in V4.2.3. |
| SEQ_NUM | bigint(20) | NUMBER(38) | Statement sequence number in the transaction.
NoteThis field was introduced in V4.2.5, and its default value is NULL. |
| PLSQL_COMPILE_TIME | bigint(20) | NUMBER(38) | PL compilation time.
NoteThis field was introduced starting from V4.2.5 BP2. The default value of this field is NULL. |
| USER_CLIENT_PORT | bigint(20) | NUMBER(38) | Used to display the client port number.
NoteThis field was introduced starting from V4.2.5 BP2. |
| TRANS_STATUS | varchar(256) | VARCHAR2(256) | Used to display whether transactions are enabled, implicitly enabled, or not enabled.
NoteThis field was introduced in V4.2.5 BP2. |
Sample query
You can use the GV$OB_SQL_AUDIT view to conveniently query SQL execution information from various dimensions. The following example queries SQL statements whose execution time exceeds 100 ms and their FLT_TRACE_ID.
/* Enable full-link tracing at the Session-level Trace to record information such as the time consumed by all SQL statements in the current Session, with a sampling rate of 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