The GV$OB_SQL_AUDIT table is a global SQL audit table that records information about each request, including the client source, execution server information, execution status, wait time, and time spent in each stage of execution.
SQL audit settings
Enable or disable 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 allocated to SQL audit. The default value is 3, and the 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 a tenant checks every 1 second whether to trigger SQL eviction based on the memory usage of the OBServer nodes and the SQL audit module. The maximum available memory for the SQL audit module is allocated based on the value of the ob_sql_audit_percentage parameter.
When the actual memory usage of the SQL audit module reaches the specified threshold, the eviction condition is met, and eviction is enabled. When the actual memory usage of the SQL audit module drops to the specified threshold, the eviction condition is no longer met, and eviction is disabled. The SQL audit eviction mechanism is described in the following table.
| Trigger mechanism | SQL audit memory range | Eviction trigger condition | Eviction stop condition |
|---|---|---|---|
| Memory usage | [0,64M] | 50% of the memory upper limit | 0 M |
| Memory usage | [64M,100M] | 20 MB less than the memory upper limit | 40 MB less than the memory upper limit |
| Memory usage | [100M,5G] | 80% of the memory upper limit | 60% of the memory upper limit |
| Memory usage | [5G,+∞) | 1 GB less than the memory upper limit | 2 GB less than the memory upper limit |
| Number of records | None | 9 million | 8 million |
Description of the GV$OB_SQL_AUDIT columns
| Column | Data type (MySQL mode) | Data type (Oracle mode) | Description |
|---|---|---|---|
| SVR_IP | varchar(46) | varchar(46) | The IP address of the server. |
| SVR_PORT | bigint(20) | number(38) | The port number. |
| REQUEST_ID | bigint(20) | number(38) | The ID of the request. |
| SQL_EXEC_ID | bigint(20) | number(38) | The SQL ID of the SQL plan being executed. |
| TRACE_ID | varchar(128) | varchar(128) | The trace ID of the statement. |
| SID | bigint(20) unsigned | number(38) | The session ID. |
| CLIENT_IP | varchar(46) | varchar(46) |
|
| CLIENT_PORT | bigint(20) | number(38) | The port number of the client that sends the request. |
| TENANT_ID | bigint(20) | number(38) | The ID of the tenant that sends the request. |
| TENANT_NAME | varchar(64) | varchar(46) | The name of the tenant that sends the request. |
| EFFECTIVE_TENANT_ID | bigint(20) | number(38) | The ID of the effective tenant. |
| USER_ID | bigint(20) | number(38) | The ID of the user that sends the request. |
| USER_NAME | varchar(64) | varchar(46) | The name of the user that sends the request. |
| USER_GROUP | bigint(20) | number(38) | The ID of the resource group to which the user belongs. |
| USER_CLIENT_IP | varchar(32) | varchar(32) | The IP address of the client that sends the request. |
| DB_ID | bigint(20) unsigned | number(38) | The ID of the database. |
| DB_NAME | varchar(128) | varchar(128) | The name of the database. |
| SQL_ID | varchar(32) | varchar(32) | The ID of the SQL statement. |
| QUERY_SQL | longtext | CLOB | The actual SQL statement. |
| PLAN_ID | bigint(20) | number(38) | The ID of the execution plan. |
| AFFECTED_ROWS | bigint(20) | number(38) | The number of affected rows. |
| RETURN_ROWS | bigint(20) | number(38) | The number of returned rows. |
| PARTITION_CNT | bigint(20) | number(38) | The number of partitions involved in the request. |
| RET_CODE | bigint(20) | number(38) | The return code of the execution result. |
| QC_ID | bigint(20) unsigned | number(38) | The qc_id in a parallel query. |
| DFO_ID | bigint(20) | number(38) | The dfo_id in a parallel query. |
| SQC_ID | bigint(20) | number(38) | The sqc_id in a parallel query. |
| WORKER_ID | bigint(20) | number(38) | The thread ID. |
| EVENT | varchar(64) | varchar(64) | The name of the longest waiting event. |
| P1TEXT | varchar(64) | varchar(64) | Parameter 1 of the waiting event. |
| P1 | bigint(20) unsigned | number(38) | The value of parameter 1 of the waiting event. |
| P2TEXT | varchar(64) | varchar(64) | Parameter 2 of the waiting event. |
| P2 | bigint(20) unsigned | number(38) | The value of parameter 2 of the waiting event. |
| P3TEXT | varchar(64) | varchar(64) | Parameter 3 of the waiting event. |
| P3 | bigint(20) unsigned | number(38) | The value of parameter 3 of the waiting event. |
| LEVEL | bigint(20) | number(38) | The level of the waiting event. |
| WAIT_CLASS_ID | bigint(20) | number(38) | The ID of the class to which the waiting event belongs. |
| WAIT_CLASS# | bigint(20) | number(38) | The index of the class to which the waiting event belongs. |
| WAIT_CLASS | varchar(64) | varchar(64) | The name of the class to which the wait event belongs. |
| STATE | varchar(19) | varchar(19) | The state of the wait event. |
| WAIT_TIME_MICRO | bigint(20) | number(38) | The time for which the wait event is waiting, in microseconds. |
| TOTAL_WAIT_TIME_MICRO | bigint(20) | number(38) | The total time spent waiting during the execution, in microseconds. |
| TOTAL_WAITS | bigint(20) | number(38) | The total number of waits during the execution. |
| RPC_COUNT | bigint(20) | number(38) | The number of RPCs sent. |
| PLAN_TYPE | bigint(20) | number(38) | The 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 cache is hit. |
| REQUEST_TIME | bigint(20) | number(38) | The start time of the execution, in microseconds. |
| ELAPSED_TIME | bigint(20) | number(38) | The total time consumed from receiving the request to the end of the execution, in microseconds. |
| NET_TIME | bigint(20) | number(38) | The time from sending the RPC to receiving the request, in microseconds. |
| NET_WAIT_TIME | bigint(20) | number(38) | The time from receiving the request to entering the queue, in microseconds. |
| QUEUE_TIME | bigint(20) | number(38) | The time the request waits in the queue, in microseconds. |
| DECODE_TIME | bigint(20) | number(38) | The decoding time after the request is dequeued, in microseconds. |
| GET_PLAN_TIME | bigint(20) | number(38) | The time from the start of the execution to obtaining the plan, in microseconds. |
| EXECUTE_TIME | bigint(20) | number(38) | The time consumed for executing the plan, in microseconds. |
| APPLICATION_WAIT_TIME | bigint(20) unsigned | number(38) | The total time of all Application events, in microseconds. |
| CONCURRENCY_WAIT_TIME | bigint(20) unsigned | number(38) | The total time of all Concurrency events, in microseconds. |
| USER_IO_WAIT_TIME | bigint(20) unsigned | number(38) | The total time of all user_io events, in microseconds. |
| SCHEDULE_TIME | bigint(20) unsigned | number(38) | The time of all Schedule events, in microseconds. |
| ROW_CACHE_HIT | bigint(20) | number(38) | The number of row cache hits. |
| BLOOM_FILTER_CACHE_HIT | bigint(20) | number(38) | The number of Bloom Filter cache hits. |
| BLOCK_CACHE_HIT | bigint(20) | number(38) | The number of block cache hits. |
| DISK_READS | bigint(20) | number(38) | The number of physical reads. |
| RETRY_CNT | bigint(20) | number(38) | The number of retries. |
| TABLE_SCAN | tinyint(4) | number(38) | Indicates whether the request contains a full table scan. |
| CONSISTENCY_LEVEL | bigint(20) | number(38) | The consistency level. |
| MEMSTORE_READ_ROW_COUNT | bigint(20) | number(38) | The number of rows read from MemStore. |
| SSSTORE_READ_ROW_COUNT | bigint(20) | number(38) | The number of rows read from SSSTORE. |
| DATA_BLOCK_READ_CNT | bigint(20) | NO | The number of data microblocks accessed. |
| DATA_BLOCK_CACHE_HIT | bigint(20) | NO | The number of data microblock cache hits. |
| INDEX_BLOCK_READ_CNT | bigint(20) | NO | The number of intermediate-level microblocks accessed. |
| INDEX_BLOCK_CACHE_HIT | bigint(20) | NO | The number of intermediate-level microblock cache hits. |
| BLOCKSCAN_BLOCK_CNT | bigint(20) | NO | The number of data microblocks scanned in one-sided scanning. |
| BLOCKSCAN_ROW_CNT | bigint(20) | NO | The number of data rows scanned in one-sided scanning. |
| PUSHDOWN_STORAGE_FILTER_ROW_CNT | bigint(20) | NO | The number of rows filtered by the storage filter pushed down. |
| REQUEST_MEMORY_USED | bigint(20) | number(38) | The amount of memory used by the request. |
| EXPECTED_WORKER_COUNT | bigint(20) | number(38) | The number of worker threads expected by the request. |
| USED_WORKER_COUNT | bigint(20) | number(38) | The number of worker threads used by the request. |
| SCHED_INFO | varchar(16384) | varchar(16384) | The scheduling information of the request. |
| PS_CLIENT_STMT_ID | bigint(20) | NUMBER(38) | The field records the client Prepare ID corresponding to the request:
|
| PS_INNER_STMT_ID | bigint(20) | NUMBER(38) | The field records the internal (database) Prepare ID corresponding to the request:
|
| TX_ID | bigint(20) | NO | The hash value of the transaction corresponding to the request. |
| SNAPSHOT_VERSION | bigint(20)unsigned | NO | The snapshot read version number of the current statement. |
| REQUEST_TYPE | bigint(20) | number(38) | The type of the request:
|
| IS_BATCHED_MULTI_STMT | tinyint(4) | number(38) | Whether to optimize for Batch Multi Stmt. |
| OB_TRACE_INFO | VARCHAR2(4096) | VARCHAR2(4096) | The trace information set by the user. |
| PLAN_HASH | bigint(20) unsigned | number(38) | The hash value of the execution plan. |
| PARAMS_VALUE | longtext | CLOB | The parameter value. |
| RULE_NAME | varchar(256) | VARCHAR2(256) | The name of the rule. |
| TX_INTERNAL_ROUTING | bigint(20) | NUMBER | Whether to enable transaction routing for the current transaction. |
| TX_STATE_VERSION | bigint(20) unsigned | NUMBER(38) | The version number of the current transaction state. |
| FLT_TRACE_ID | varchar(1024) | varchar(1024) | The trace ID for the full-link tracing. If this field is empty, the statement is not monitored by full-link tracing. This field is a UUID, which is different from the trace ID. The format of this field is as follows: 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) | The time consumed for PL execution. |
| TOTAL_MEMSTORE_READ_ROW_COUNT | bigint(20) | NUMBER(38) | The total number of rows read from the MemStore during the entire process. |
| TOTAL_SSSTORE_READ_ROW_COUNT | bigint(20) | NUMBER(38) | The total number of rows read from the SSSTORE during the entire process. |
| PROXY_USER | varchar(128) | VARCHAR2(128) | The name of the proxy user in the proxy user login scenario. |
| FUSE_ROW_CACHE_HIT | bigint(20) | number(38) | This field is not supported. The default value is NULL. |
| LOCK_FOR_READ_TIME | bigint(20) |
NoticeThe view does not contain the |
The time consumed for waiting for a lock during data reading, in microseconds. |
| PARTITION_HIT | tinyint(4) | - | Whether to hit the local partition. |
| PLSQL_COMPILE_TIME | bigint(20) | NUMBER(38) | The time consumed for PL compilation. |
| INSERT_DUPLICATE_ROW_COUNT | bigint(20) | NUMBER(38) | The number of duplicate rows inserted during the insertup or replace into operation. |
| USER_CLIENT_PORT | bigint(20) | NUMBER(38) | The client port number. |
| TRANS_STATUS | varchar(256) | VARCHAR2(256) | The transaction status, which can be either an explicit transaction, an implicit transaction, or no transaction. |
| TX_TABLE_READ_CNT | bigint(20) | NUMBER(38) | The number of times the transaction status table is queried. |
| OUTROW_LOB_CNT | bigint(20) | NUMBER(38) | The number of outrow LOB columns read during the query. |
| COMMIT_TIME | bigint(20) | NUMBER(38) | The time consumed for a transaction to commit. If the SQL statement does not trigger a commit, the value is 0. |
Important event intervals
The following figure shows the relationships between the important events corresponding to the ELAPSED_TIME, REQUEST_TIME, NET_WAIT_TIME, QUEUE_TIME, DECODE_TIME, GET_PLAN_TIME, EXECUTE_TIME, and COMMIT_TIME columns in this view:
Example
You can query the GV$OB_SQL_AUDIT view to obtain information about the execution of SQL statements from various dimensions. The following example queries SQL statements that take more than 100 ms to execute and their FLT_TRACE_ID values.
/* Enable full-link tracing at the session level to record the related time consumption of all SQL statements 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