GV$OB_SQL_AUDIT is a global SQL audit table that allows you to view the client source, server information, execution status, waited events, and time consumed in each phase of an operation.
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. 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 based on the memory usage. The upper limit of the memory for SQL Audit is calculated based on the value of ob_sql_audit_percentage and the maximum available memory.
If the actual memory usage of SQL Audit reaches the specified threshold, the conditions for triggering the eviction are met, and the eviction is enabled. If the actual memory usage of SQL Audit drops to the specified threshold, the conditions for stopping the eviction are met, and the eviction is disabled. The following table describes the SQL Audit eviction mechanism.
| Trigger mechanism | SQL Audit memory range | Trigger condition for eviction | Stop condition for 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) | If an SQL plan is being executed, the corresponding SQL ID is displayed. |
| 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) | IP address of the client that sent the request. |
| CLIENT_PORT | bigint(20) | number(38) | Port number of the client that sent the request. |
| TENANT_ID | bigint(20) | number(38) | Tenant ID of the client that sent the request. |
| TENANT_NAME | varchar(64) | varchar(46) | Tenant name of the client that sent the request. |
| EFFECTIVE_TENANT_ID | bigint(20) | number(38) | Tenant ID. |
| USER_ID | bigint(20) | number(38) | User ID of the client that sent the request. |
| USER_NAME | varchar(64) | varchar(46) | User name of the client that sent 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 sent 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) | ID of the thread. |
| EVENT | varchar(64) | varchar(64) | Name of the longest waiting event. |
| P1TEXT | varchar(64) | varchar(64) | Waiting event parameter 1. |
| P1 | bigint(20) unsigned | number(38) | Value of waiting event parameter 1. |
| P2TEXT | varchar(64) | varchar(64) | Waiting event parameter 2. |
| P2 | bigint(20) unsigned | number(38) | Value of waiting event parameter 2. |
| P3TEXT | varchar(64) | varchar(64) | Waiting event parameter 3. |
| P3 | bigint(20) unsigned | number(38) | Value of waiting event parameter 3. |
| 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 waiting 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) | 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 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 that the request waited in the queue, in microseconds. |
| DECODE_TIME | bigint(20) | number(38) | Decode time after the request was dequeued, in microseconds. |
| GET_PLAN_TIME | bigint(20) | number(38) | Time 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) | 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 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 layer 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 corresponding to 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. |