GV$OB_SQL_AUDIT is a global SQL audit table that can be used to view the client source, execution server information, execution status, wait time, and time spent in each phase for each request.
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 that SQL Audit uses out of 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 decides 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 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 | Conditions for triggering eviction | Conditions 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) | Tenant ID. |
| 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) | 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 waiting event. |
| P1TEXT | varchar(64) | varchar(64) | Parameter 1 of the waiting event. |
| P1 | bigint(20) unsigned | number(38) | 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) | 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) | Value of parameter 3 of the waiting event. |
| LEVEL | bigint(20) | number(38) | Level of the waiting event. |
| WAIT_CLASS_ID | bigint(20) | number(38) | Class ID 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 for, in microseconds. |
| TOTAL_WAIT_TIME_MICRO | bigint(20) | number(38) | Total time spent waiting 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) | Type of the execution plan:
|
| IS_INNER_SQL | tinyint(4) | number(38) | 1 if the request is an internal SQL request; 0 otherwise. |
| IS_EXECUTOR_RPC | tinyint(4) | number(38) | 1 if the current request is an RPC request; 0 otherwise. |
| IS_HIT_PLAN | tinyint(4) | number(38) | 1 if the plan is hit in the cache; 0 otherwise. |
| REQUEST_TIME | bigint(20) | number(38) | Start time of execution, in microseconds. |
| ELAPSED_TIME | bigint(20) | number(38) | Total time taken from receiving the request to the end of execution, in microseconds. |
| NET_TIME | bigint(20) | number(38) | Time taken from sending the RPC to receiving the request, in microseconds. |
| NET_WAIT_TIME | bigint(20) | number(38) | Time taken from receiving the request to entering the queue, in microseconds. |
| QUEUE_TIME | bigint(20) | number(38) | Time spent waiting in the queue. |
| DECODE_TIME | bigint(20) | number(38) | Decode time after leaving the queue, in microseconds. |
| GET_PLAN_TIME | bigint(20) | number(38) | Time taken 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 cache hits in the row cache. |
| BLOOM_FILTER_CACHE_HIT | bigint(20) | number(38) | Number of cache hits in the Bloom filter cache. |
| BLOCK_CACHE_HIT | bigint(20) | number(38) | Number of cache 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) | 1 if the request contains a full table scan; 0 otherwise. |
| 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 cache 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 cache 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) | Number of worker threads expected. |
| USED_WORKER_COUNT | bigint(20) | number(38) | Number of worker threads actually 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) | NO | Prepare ID corresponding to the request:
|
| PS_INNER_STMT_ID | bigint(20) | NO | 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) | 1 if batch multi-statement optimization is performed; 0 otherwise. |
| 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) |
NoticeThis view does not contain the |
Time spent waiting for locks when reading data, in microseconds. |
| PARAMS_VALUE | longtext | CLOB | Parameter value. |
