GV$OB_SQL_AUDIT is the global SQL audit view. It displays the client of each query and 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 percentage of the tenant memory used by SQL Audit. The default value is 3. You can set the value in the range of [0,80].
obclient> SET global ob_sql_audit_percentage = 3; Query OK, 0 rows affected
Eviction mechanism of SQL Audit
OceanBase Database runs a background task for each tenant to check the memory usage of OBServer nodes and SQL Audit every second and determines whether to trigger SQL eviction. The maximum memory that can be used by SQL Audit depends on ob_sql_audit_percentage.
Eviction is triggered when the memory usage of SQL Audit reaches the specified threshold corresponding to its maximum allowed memory, and stops when usage falls below this threshold. The following table describes the eviction mechanism of SQL Audit.
| Trigger condition | Memory range for SQL Audit | Eviction triggering threshold | Eviction stopping threshold |
|---|---|---|---|
| Memory usage | [0 MB,64 MB] | Maximum memory × 50% | 0 MB |
| Memory usage | [64 MB,100 MB] | Maximum memory - 20 MB | Maximum memory - 40 MB |
| Memory usage | [100 MB,5 GB] | Maximum memory × 80% | Maximum memory × 60% |
| Memory usage | [5 GB,+∞) | Maximum memory - 1 GB | Maximum memory - 2 GB |
| Records | N/A | 9 million | 8 million |
Columns in GV$OB_SQL_AUDIT
| Column | Type (MySQL mode) | Type (Oracle mode) | Description |
|---|---|---|---|
| SVR_IP | varchar(46) | varchar(46) | The IP address of the server. |
| SVR_PORT | bigint(20) | number(38) | The port number of the server. |
| REQUEST_ID | bigint(20) | number(38) | The ID of the request. |
| SQL_EXEC_ID | bigint(20) | number(38) | If an SQL plan is being executed, the value is the corresponding SQL ID. |
| TRACE_ID | varchar(128) | varchar(128) | The trace ID of the statement. |
| SID | bigint(20) unsigned | number(38) | The ID of the session. |
| CLIENT_IP | varchar(46) | varchar(46) |
|
| CLIENT_PORT | bigint(20) | number(38) | The port number of the client that sent the request. |
| TENANT_ID | bigint(20) | number(38) | The ID of the tenant that sent the request. |
| TENANT_NAME | varchar(64) | varchar(46) | The name of the tenant that sent the request. |
| EFFECTIVE_TENANT_ID | bigint(20) | number(38) | The ID of the tenant. |
| USER_ID | bigint(20) | number(38) | The ID of the user that sent the request. |
| USER_NAME | varchar(64) | varchar(46) | The name of the user that sent the request. |
| USER_GROUP | bigint(20) | number(38) | The ID of the resource group to which the user belongs. |
| USER_CLIENT_IP | varchar(32) | varchar(32) | The IP address of the client that sent the request. |
| DB_ID | bigint(20) unsigned | number(38) | The ID of the database. |
| DB_NAME | varchar(128) | varchar(128) | The name of the database. |
| SQL_ID | varchar(32) | varchar(32) | The ID of the SQL statement. |
| QUERY_SQL | longtext | CLOB | The actual SQL statement. |
| PLAN_ID | bigint(20) | number(38) | The ID of the execution plan. |
| AFFECTED_ROWS | bigint(20) | number(38) | The number of rows affected. |
| RETURN_ROWS | bigint(20) | number(38) | The number of rows returned. |
| PARTITION_CNT | bigint(20) | number(38) | The number of partitions scanned by the request. |
| RET_CODE | bigint(20) | number(38) | The return code of the execution. |
| QC_ID | bigint(20) unsigned | number(38) | The query coordinator (QC) ID in the parallel query. |
| DFO_ID | bigint(20) | number(38) | The data flow operation (DFO) ID in the parallel query. |
| SQC_ID | bigint(20) | number(38) | The subquery coordinator (SQC) ID in the parallel query. |
| WORKER_ID | bigint(20) | number(38) | The thread ID. |
| EVENT | varchar(64) | varchar(64) | The name of the wait event with the longest wait time. |
| P1TEXT | varchar(64) | varchar(64) | The first parameter of the wait event. |
| P1 | bigint(20) unsigned | number(38) | The value of the first parameter of the wait event. |
| P2TEXT | varchar(64) | varchar(64) | The second parameter of the wait event. |
| P2 | bigint(20) unsigned | number(38) | The value of the second parameter of the wait event. |
| P3TEXT | varchar(64) | varchar(64) | The third parameter of the wait event. |
| P3 | bigint(20) unsigned | number(38) | The value of the third parameter of the wait event. |
| LEVEL | bigint(20) | number(38) | The level of the wait event. |
| WAIT_CLASS_ID | bigint(20) | number(38) | The ID of the class to which the wait event belongs. |
| WAIT_CLASS# | bigint(20) | number(38) | The subscript of the class to which the wait event belongs. |
| WAIT_CLASS | varchar(64) | varchar(64) | The name of the class to which the wait event belongs. |
| STATE | varchar(19) | varchar(19) | The status of the wait event. |
| WAIT_TIME_MICRO | bigint(20) | number(38) | The amount of wait time of the wait event, in microseconds. |
| TOTAL_WAIT_TIME_MICRO | bigint(20) | number(38) | The total amount of wait time during execution, in microseconds. |
| TOTAL_WAITS | bigint(20) | number(38) | The total number of waits during the execution. |
| RPC_COUNT | bigint(20) | number(38) | The number of remote procedure calls (RPCs) sent. |
| PLAN_TYPE | bigint(20) | number(38) | The type of the execution plan. Valid values:
|
| IS_INNER_SQL | tinyint(4) | number(38) | Indicates whether the request is an internal SQL request. |
| IS_EXECUTOR_RPC | tinyint(4) | number(38) | Indicates whether the current request is an RPC request. |
| IS_HIT_PLAN | tinyint(4) | number(38) | Indicates whether the plan cache is hit. |
| REQUEST_TIME | bigint(20) | number(38) | The time when the execution starts, in microseconds. |
| ELAPSED_TIME | bigint(20) | number(38) | The amount of time elapsed from when the request was received to when the execution of the request ended, in microseconds. |
| NET_TIME | bigint(20) | number(38) | The amount of time elapsed from when the RPC was sent to when the request was received, in microseconds. |
| NET_WAIT_TIME | bigint(20) | number(38) | The amount of time elapsed from when the request was received to when it entered the queue, in microseconds. |
| QUEUE_TIME | bigint(20) | number(38) | The time that the request waits in the queue, in microseconds. |
| DECODE_TIME | bigint(20) | number(38) | The time spent on decoding the request after it left the queue, in microseconds. |
| GET_PLAN_TIME | bigint(20) | number(38) | The amount of time elapsed from when the processing started to when the plan was obtained, in microseconds. |
| EXECUTE_TIME | bigint(20) | number(38) | The time consumed for plan execution, in microseconds. |
| APPLICATION_WAIT_TIME | bigint(20) unsigned | number(38) | The total amount of wait time of application wait events, in microseconds. |
| CONCURRENCY_WAIT_TIME | bigint(20) unsigned | number(38) | The total amount of wait time of concurrency wait events, in microseconds. |
| USER_IO_WAIT_TIME | bigint(20) unsigned | number(38) | The total wait time of user I/O wait events, in microseconds. |
| SCHEDULE_TIME | bigint(20) unsigned | number(38) | The total amount of wait time of schedule wait events, in microseconds. |
| ROW_CACHE_HIT | bigint(20) | number(38) | The number of row cache hits. |
| BLOOM_FILTER_CACHE_HIT | bigint(20) | number(38) | The number of Bloom filter cache hits. |
| BLOCK_CACHE_HIT | bigint(20) | number(38) | The number of block cache hits. |
| DISK_READS | bigint(20) | number(38) | The number of physical reads. |
| RETRY_CNT | bigint(20) | number(38) | The number of retries. |
| TABLE_SCAN | tinyint(4) | number(38) | Indicates whether the request contains a full table scan. |
| CONSISTENCY_LEVEL | bigint(20) | number(38) | The consistency level. Valid values:
|
| MEMSTORE_READ_ROW_COUNT | bigint(20) | number(38) | The number of rows read in the MemStore. |
| SSSTORE_READ_ROW_COUNT | bigint(20) | number(38) | The number of rows read in the SSStore. |
| DATA_BLOCK_READ_CNT | bigint(20) | NO | The number of data microblocks accessed. |
| DATA_BLOCK_CACHE_HIT | bigint(20) | NO | The number of data microblock cache hits. |
| INDEX_BLOCK_READ_CNT | bigint(20) | NO | The number of intermediate-layer microblocks accessed. |
| INDEX_BLOCK_CACHE_HIT | bigint(20) | NO | The number of intermediate-layer microblock cache hits. |
| BLOCKSCAN_BLOCK_CNT | bigint(20) | NO | The number of data microblocks scanned during a unilateral scan. |
| BLOCKSCAN_ROW_CNT | bigint(20) | NO | The number of data rows scanned during a unilateral scan. |
| PUSHDOWN_STORAGE_FILTER_ROW_CNT | bigint(20) | NO | The number of rows that remain after the filter condition is pushed down to and applied in the storage layer. |
| REQUEST_MEMORY_USED | bigint(20) | number(38) | The memory consumed by the request. |
| EXPECTED_WORKER_COUNT | bigint(20) | number(38) | The number of worker threads expected by the request. |
| USED_WORKER_COUNT | bigint(20) | number(38) | The number of worker threads used by the request. |
| SCHED_INFO | varchar(16384) | varchar(16384) | The scheduling information about the request. |
| FUSE_ROW_CACHE_HIT | bigint(20) | number(38) | At present, this column is not supported and is NULL by default. |
| PS_CLIENT_STMT_ID | bigint(20) | number(38) | The ID of the prepared statement on the client corresponding to the request. Valid values:
|
| PS_INNER_STMT_ID | bigint(20) | number(38) | The ID of the prepared statement in the database corresponding to the request. Valid values:
|
| TX_ID | bigint(20) | NO | The hash value of the transaction corresponding to the request. |
| SNAPSHOT_VERSION | bigint(20) unsigned | NO | The snapshot read version of the current statement. |
| REQUEST_TYPE | bigint(20) | number(38) | The type of the request. Valid values:
|
| IS_BATCHED_MULTI_STMT | tinyint(4) | number(38) | Indicates whether optimization has been performed for batch execution of multiple statements. |
| OB_TRACE_INFO | VARCHAR2(4096) | VARCHAR2(4096) | The trace information configured by the user. |
| PLAN_HASH | bigint(20) unsigned | number(38) | The hash value of the execution plan. |
| LOCK_FOR_READ_TIME | bigint(20) | number(38) | The time spent on waiting for locked data to be unlocked before it can be read, in microseconds. |
| PARAMS_VALUE | longtext | CLOB | Parameter values bound to the prepared statement during its execution. |
| FLT_TRACE_ID | varchar(1024) | varchar(1024) | The ID of the trace for end-to-end diagnostics. If this column is empty, end-to-end diagnostics is not enabled. The value is a universally unique identifier (UUID), such as 000600d6-a5de-038c-6c80-df07e4e79149, which is different from the value of the TRACE_ID column. |
| PL_TRACE_ID | varchar(128) | NO | The trace ID of the outer PL block of the current SQL statement. If the SQL statement does not have an outer PL block, the value of this column is NULL. |
| PLSQL_EXEC_TIME | bigint(20) | NO | The amount of time consumed in PL execution, in microseconds, which does not include the amount of time consumed in SQL execution. |
| FORMAT_SQL_ID | varchar(32) | NO | The MD5 value generated based on the formatted text of the SQL statement.
NoteThis column is available in OceanBase Database V4.2.3 and later. |
| NETWORK_WAIT_TIME | bigint(20) unsigned | YES | The total wait time of network wait events, in microseconds.
NoteThis column is available in OceanBase Database V4.2.3 and later. |
| STMT_TYPE | varchar(128) | YES | The type of the SQL statement. Valid values:
NoteThis column is available in OceanBase Database V4.2.3 and later. |
| TOTAL_MEMSTORE_READ_ROW_COUNT | bigint(20) | NO | The total number of rows read in the MemStore in the whole working process. This column is displayed only in a thread that contains the query_text field.
NoteThis column is available in OceanBase Database V4.2.3 and later. |
| TOTAL_SSSTORE_READ_ROW_COUNT | bigint(20) | NO | The total number of rows read in the SSStore in the whole working process. This column is displayed only in a thread that contains the query_text field.
NoteThis column is available in OceanBase Database V4.2.3 and later. |
| PROXY_USER | varchar(128) | YES |
NoteThis column is available in OceanBase Database V4.2.3 and later. |
Examples
You can query the GV$OB_SQL_AUDIT view for information about SQL execution in various dimensions with ease. The following example queries SQL statements that take more than 100 ms to execute and their FLT_TRACE_ID.
/* Enable session-level end-to-end tracing and record the time consumed for executing each SQL statement in the current session, at a sampling rate of 50%. */
obclient> CALL DBMS_MONITOR.OB_SESSION_TRACE_ENABLE(null,1,0.5,'ALL');
Query OK, 0 rows affected
obclient [oceanbase]> SELECT request_id,usec_to_time(request_time),ELAPSED_TIME,QUEUE_TIME,EXECUTE_TIME,FLT_TRACE_ID,QUERY_SQL FROM GV$OB_SQL_AUDIT where ELAPSED_TIME > 100000 limit 10 ;
+------------+----------------------------+--------------+------------+--------------+--------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| request_id | usec_to_time(request_time) | ELAPSED_TIME | QUEUE_TIME | EXECUTE_TIME | FLT_TRACE_ID | QUERY_SQL |
+------------+----------------------------+--------------+------------+--------------+--------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 5950244 | 2023-09-07 16:20:47.465958 | 127623 | 26 | 127206 | 000604c0-8981-9184-518a-e234439d873c | CREATE TABLE tbl2(c1 INT PRIMARY KEY,c2 INT) |
| 5951861 | 2023-09-07 16:21:07.887121 | 333776 | 38 | 310298 | | ALTER TABLE tbl2 ADD CONSTRAINT fk1 FOREIGN KEY (c2) REFERENCES tbl3(c1) ON UPDATE SET NULL |
| 5953177 | 2023-09-07 16:21:28.215377 | 174416 | 24 | 174186 | 000604c0-8bef-5afb-f9d3-2ee0dfab4c8f | SELECT request_id,usec_to_time(request_time),ELAPSED_TIME,QUEUE_TIME,EXECUTE_TIME,FLT_TRACE_ID,QUERY_SQL FROM v$OB_SQL_AUDIT where ELAPSED_TIME > 100000 |
| 5954522 | 2023-09-07 16:21:48.317360 | 128803 | 27 | 128542 | 000604c0-8d22-1659-7b0c-a0ac0645894d | SELECT request_id,usec_to_time(request_time),ELAPSED_TIME,QUEUE_TIME,EXECUTE_TIME,FLT_TRACE_ID,QUERY_SQL FROM v$OB_SQL_AUDIT where ELAPSED_TIME > 100000 limit 10 |
+------------+----------------------------+--------------+------------+--------------+--------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set