GV$OB_SQL_AUDIT is a global SQL audit table that can be used to view information such as the client source, execution server information, execution status, waiting time, and time spent in each stage for each request.
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 for SQL audit relative to the tenant's memory. 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 an SQL audit eviction based on the memory usage of the OBServer nodes and the SQL audit. The maximum available memory for SQL audit is allocated based on the value of the ob_sql_audit_percentage parameter.
When the actual memory usage of the SQL audit reaches the specified threshold, the eviction is triggered. When the actual memory usage of the SQL audit drops to the specified threshold, the eviction is stopped. 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 M less than the memory upper limit | 40 M 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 G less than the memory upper limit | 2 G less than the memory upper limit |
| Record count | N/A | 9 million | 8 million |
Description of the GV$OB_SQL_AUDIT fields
| Field | 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) | 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) | Session ID |
| CLIENT_IP | varchar(46) | varchar(46) |
|
| CLIENT_PORT | bigint(20) | number(38) | The client port 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) | Tenant ID |
| 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) | Database ID |
| DB_NAME | varchar(128) | varchar(128) | Database name |
| 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) | Execution plan ID |
| 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) | 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 class name of the wait event. |
| STATE | varchar(19) | varchar(19) | The status of the wait event. |
| WAIT_TIME_MICRO | bigint(20) | number(38) | The time that the wait event waits for, in microseconds. |
| TOTAL_WAIT_TIME_MICRO | bigint(20) | number(38) | The total time of all waits 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 current 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 decode 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 in 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 the MemStore. |
| SSSTORE_READ_ROW_COUNT | bigint(20) | number(38) | The number of rows read from the 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 microblocks that hit the cache. |
| 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 microblocks that hit the cache. |
| BLOCKSCAN_BLOCK_CNT | bigint(20) | NO | The number of data microblocks scanned in a one-sided scan. |
| BLOCKSCAN_ROW_CNT | bigint(20) | NO | The number of data rows scanned in a one-sided scan. |
| 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 consumed by the request. |
| EXPECTED_WORKER_COUNT | bigint(20) | number(38) | The number of worker threads expected for the request. |
| USED_WORKER_COUNT | bigint(20) | number(38) | The number of worker threads actually used by the request. |
| SCHED_INFO | varchar(16384) | varchar(16384) | The scheduling information of the request. |
| PS_CLIENT_STMT_ID | bigint(20) | NUMBER(38) | This field records the client-side Prepare ID corresponding to the request:
|
| PS_INNER_STMT_ID | bigint(20) | NUMBER(38) | This field records the internal (database-side) 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 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 values. |
| 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 full-link tracing. If this field is empty, it indicates that the request is not monitored by full-link tracing. This field is a UUID, which is different from a trace. An example of its format is: 000600d6-a5de-038c-6c80-df07e4e79149 |
| PL_TRACE_ID | varchar(128) | VARCHAR2(128) | The trace ID of the outer PL for the current SQL statement. |
| PLSQL_EXEC_TIME | bigint(20) | NUMBER(38) | The execution time of PL. |
| TOTAL_MEMSTORE_READ_ROW_COUNT | bigint(20) | NUMBER(38) | The total number of rows read from MemStore during the entire process. |
| TOTAL_SSSTORE_READ_ROW_COUNT | bigint(20) | NUMBER(38) | The total number of rows read from 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 |
The time spent waiting for a lock when reading data, in microseconds. |
| PARTITION_HIT | tinyint(4) | - | Whether to hit the local partition. |
| PLSQL_COMPILE_TIME | bigint(20) | NUMBER(38) | The compilation time of PL. |
| INSERT_DUPLICATE_ROW_COUNT | bigint(20) | NUMBER(38) | The number of duplicate rows inserted by using the insertup or replace into statement. |
| USER_CLIENT_PORT | bigint(20) | NUMBER(38) | The client port number. |
| TRANS_STATUS | varchar(256) | VARCHAR2(256) | The transaction status. |
| TX_TABLE_READ_CNT | bigint(20) | NUMBER(38) | The number of times that the transaction status table is queried. |
| OUTROW_LOB_CNT | bigint(20) | NUMBER(38) | The number of outrow LOB columns read during a query. |
| COMMIT_TIME | bigint(20) | NUMBER(38) | The time consumed by a transaction to commit. If the transaction does not 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 SQL statements in various dimensions. The following example shows how to query SQL statements that take more than 100 ms and their FLT_TRACE_ID.
/* 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