(g)v$sql_audit is the global SQL audit table. It shows the client of every query, information about execution server, execution status, wait events, and time consumed for each execution stage.
sql_audit settings
Set the switch of
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 maximum memory usage of
sql_audit. By default, the maximum memory usage is 3G, and it can be configured in the range of [64M, +∞].obclient>ALTER SYSTEM SET sql_audit_memory_limit = '3G';
sql_audit eviction mechanism
sql_audit check its backend task once every other 1s and determines whether to start eviction based on the following criteria:
- The maximum memory that can be utilized by
sql_auditis determined byavail_mem_limit = min (available memory at the OBServer * 10%,sql_audit_memory_limit).
- Eviction is also triggered when the number of records in
sql_auditexceeds 9 million.
- The maximum memory that can be utilized by
sql_audit stops eviction by the following criteria:
- If the eviction is triggered by reaching the memory usage limit:
- If the eviction is triggered by the record number limit, it is stopped when the number of record rows drops to 8 million.
Description of sql_audit fields
| Field | Type | Description |
|---|---|---|
| SVR_IP | varchar(32) | The IP address of the server. |
| SVR_PORT | bigint(20) | The port number of the server. |
| REQUEST_ID | bigint(20) | Request ID |
| TRACE_ID | varchar(128) | The trace ID of the statement |
| CLIENT_IP | varchar(32) | The IP address of the client that sends the request |
| CLIENT_PORT | bigint(20) | The port of the client that sends the request |
| TENANT_ID | bigint(20) | The ID of the tenant that sends the request |
| TENANT_NAME | varchar(64) | The name of the tenant that sends the request |
| USER_ID | bigint(20) | The ID of the user who sends the request |
| USER_NAME | varchar(64) | The name of the user who sends the request |
| SQL_ID | varchar(32) | The ID of the SQL query. |
| QUERY_SQL | varchar(32768) | The actual SQL statement |
| PLAN_ID | bigint(20) | The ID of the execution plan |
| AFFECTED_ROWS | bigint(20) | The number of rows affected |
| RETURN_ROWS | bigint(20) | The number of rows returned |
| PARTITION_CNT | bigint(20) | The number of partitions involved in the request |
| RET_CODE | bigint(20) | The return code of the execution result |
| EVENT | varchar(64) | The name of the longest wait event |
| P1TEXT | varchar(64) | Parameter 1 of the wait event |
| P1 | bigint(20) unsigned | The value for parameter 1 of the wait event |
| P2TEXT | varchar(64) | Parameter 2 of the wait event |
| P2 | bigint(20) unsigned | The value for parameter 2 of the wait event |
| P3TEXT | varchar(64) | Parameter 3 of the wait event |
| P3 | bigint(20) unsigned | The value for parameter 3 of the wait event |
| LEVEL | bigint(20) | The level of the wait event |
| WAIT_CLASS_ID | bigint(20) | The ID of the wait event class |
| WAIT_CLASS# | bigint(20) | The index number of the wait event class |
| WAIT_CLASS | varchar(64) | The name of the wait event class |
| STATE | varchar(19) | The satus of the wait event |
| WAIT_TIME_MICRO | bigint(20) | The wait time of the wait event (ms) |
| TOTAL_WAIT_TIME_MICRO | bigint(20) | The total wait time in the execution process (ms) |
| TOTAL_WAITS | bigint(20) | The total number of waits in the execution process |
| RPC_COUNT | bigint(20) | The number of RPC sent |
| PLAN_TYPE | bigint(20) | The type of the execution plan (local/remote/distributed plan) |
| IS_INNER_SQL | tinyint(4) | Indicates whether this is an internal SQL request |
| IS_EXECUTOR_RPC | tinyint(4) | Indicates whether this is an RPC request |
| IS_HIT_PLAN | tinyint(4) | Indicates whether the plan cache is hit |
| REQUEST_TIME | bigint(20) | The time when execution starts |
| ELAPSED_TIME | bigint(20) | The total time consumed from request reception to the end of execution |
| NET_TIME | bigint(20) | The time consumed from RPC sending to request reception |
| NET_WAIT_TIME | bigint(20) | The time consumed from request reception to start of queuing |
| QUEUE_TIME | bigint(20) | The queue time of the request |
| DECODE_TIME | bigint(20) | The decoding time after the request is moved out of the queue |
| GET_PLAN_TIME | bigint(20) | The time consumed from execution start to plan acquisition |
| EXECUTE_TIME | bigint(20) | The time consumed for plan execution |
| APPLICATION_WAIT_TIME | bigint(20) unsigned | The total time consumed for all application events |
| CONCURRENCY_WAIT_TIME | bigint(20) unsigned | The total time consumed for all concurrency events |
| USER_IO_WAIT_TIME | bigint(20) unsigned | The total time consumed for all user_io events |
| SCHEDULE_TIME | bigint(20) unsigned | The total time consumed for all schedule events |
| ROW_CACHE_HIT | bigint(20) | The row cache hit count |
| BLOOM_FILTER_CACHE_HIT | bigint(20) | The bloom filter cache hit count |
| BLOCK_CACHE_HIT | bigint(20) | The block cache hit count |
| BLOCK_INDEX_CACHE_HIT | bigint(20) | The block index cache hit count |
| DISK_READS | bigint(20) | The number physical reads |
| EXECUTION_ID | bigint(20) | The ID of the execution |
| SESSION_ID | bigint(20) | The ID of the session |
| RETRY_CNT | bigint(20) | The number of retries |
| TABLE_SCAN | tinyint(4) | Indicates whether the request contains a full table scan |
| CONSISTENCY_LEVEL | bigint(20) | The consistency level |
| MEMSTORE_READ_ROW_COUNT | bigint(20) | The number of rows read from MEMSTORE |
| SSSTORE_READ_ROW_COUNT | bigint(20) | The number of rows read from SSSTORE. |
| REQUEST_MEMORY_USED | bigint(20) | The memory consumed by the request |