gv$sql_audit

gv$sql_audit is the global SQL audit table. It shows the client of each query, information about the 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 3 GB, and it can be configured in the range of [64 MB, +∞).

  obclient> ALTER SYSTEM SET sql_audit_memory_limit = '3G';
  Query OK, 0 rows affected
  

Eviction mechanism of sql_audit

• sql_audit checks 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 on the OBServer node × 10%, sql_audit_memory_limit).

    • If avail_mem_limit falls in the range of [64 MB, 100 MB], eviction is triggered when the memory used reaches avail_mem_limit - 20 MB.
    • If avail_mem_limit falls in the range of [100 MB, 5 GB], eviction is triggered when the memory used reaches avail_mem_limit × 0.8.
    • If avail_mem_limit falls in the range of [5 GB, +∞), eviction is triggered when memory usage reaches avail_mem_limit - 1 GB.

  • 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 upper memory usage limit:

    • If avail_mem_limit falls in the range of [64 MB, 100 MB], eviction is stopped when the memory used drops to avail_mem_limit - 40 MB.
    • If avail_mem_limit falls in the range [100 MB, 5 GB], eviction is stopped when the memory used drops to avail_mem_limit × 0.6.
    • If avail_mem_limit falls in the range [5 GB, +∞), eviction is stopped when the memory used drops to avail_mem_limit - 2 GB.

  • If the eviction is triggered by the upper record number limit, it is stopped when the number of record rows drops to 8 million.

sql_audit fields