All Products
Search
Document Center

(g)v$sql_audit introduction

Last Updated: Jun 18, 2021

(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_audit is determined by avail_mem_limit = min (available memory at the OBServer * 10%, sql_audit_memory_limit).

      • If avail_mem_limit falls in the range of [64M, 100M], eviction is triggered when memory usage reaches avail_mem_limit - 20M.

      • If avail_mem_limit falls in the range of [100M, 5G], eviction is triggered when memory usage reaches avail_mem_limit*0.8.

      • If avail_mem_limit falls in the range of [5G, +∞], eviction is triggered when memory usage reaches avail_mem_limit - 1G.

    • Eviction is also triggered when the number of records in sql_audit exceeds 9 million.

  • sql_audit stops eviction by the following criteria:

    • If the eviction is triggered by reaching the memory usage limit:

      • If avail_mem_limit falls in the range of [64M, 100M], eviction is stopped when memory usage drops to avail_mem_limit - 40M.

      • If avail_mem_limit falls in the range [100M, 5G], the eviction is stopped when memory usage drops to avail_mem_limit*0.6.

      • If avail_mem_limit falls in the range [5G, +∞], the eviction is stopped when memory usage drops to avail_mem_limit - 2G.

    • 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