The GV$OB_SQL_AUDIT view shows information about each SQL audit request, such as the client source, server information, execution status, wait time, and time spent in each execution phase.
Columns in the GV$OB_SQL_AUDIT view
| Column | Type (MySQL-compatible mode) | Type (Oracle-compatible mode) | Description |
|---|---|---|---|
| SVR_IP | varchar(46) | varchar(46) | IP address |
| SVR_PORT | bigint(20) | number(38) | Port number |
| REQUEST_ID | bigint(20) | number(38) | ID of the request |
| SQL_EXEC_ID | bigint(20) | number(38) | If an SQL plan is being executed, this column shows the corresponding SQL ID. |
| 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) |
|
| CLIENT_PORT | bigint(20) | number(38) | Client port that sends the request |
| TENANT_ID | bigint(20) | number(38) | ID of the tenant that sends the request |
| TENANT_NAME | varchar(64) | varchar(46) | Name of the tenant that sends the request |
| EFFECTIVE_TENANT_ID | bigint(20) | number(38) | Tenant ID |
| USER_ID | bigint(20) | number(38) | ID of the user that sends the request |
| USER_NAME | varchar(64) | varchar(46) | Name of the user that sends 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 sends 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 | The actual SQL statement.
NoteTThis field can be queried only by the SYS tenant starting from V4.3.5 BP4. |
| 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) | Thread ID |
| EVENT | varchar(64) | varchar(64) | Name of the longest waiting event |
| P1TEXT | varchar(64) | varchar(64) | Parameter 1 of the waiting event |
| P1 | bigint(20) unsigned | number(38) | Value of parameter 1 of the waiting event |
| P2TEXT | varchar(64) | varchar(64) | Parameter 2 of the waiting event |
| P2 | bigint(20) unsigned | number(38) | Value of parameter 2 of the waiting event |
| P3TEXT | varchar(64) | varchar(64) | Parameter 3 of the waiting event |
| P3 | bigint(20) unsigned | number(38) | Value of parameter 3 of the waiting event |
| LEVEL | bigint(20) | number(38) | Level of the waiting event |
| WAIT_CLASS_ID | bigint(20) | number(38) | ID of the class 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) | State of the waiting event |
| WAIT_TIME_MICRO | bigint(20) | number(38) | The time that the waiting event waits, in microseconds. |
| TOTAL_WAIT_TIME_MICRO | bigint(20) | number(38) | The total time of all waiting events during the 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 RPCs sent. |
| PLAN_TYPE | bigint(20) | number(38) | The type of the execution plan:
|
| IS_INNER_SQL | tinyint(4) | number(38) | Indicates whether the SQL request is an internal 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 start time of the execution, in microseconds. |
| ELAPSED_TIME | bigint(20) | number(38) | The total time from when the request is received to when the execution is completed, in microseconds. |
| NET_TIME | bigint(20) | number(38) | The time from when the RPC is sent to when the request is received, in microseconds. |
| NET_WAIT_TIME | bigint(20) | number(38) | The time from when the request is received to when it is enqueued, 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 after the request is dequeued, in microseconds. |
| GET_PLAN_TIME | bigint(20) | number(38) | The time from the start of the execution to when the plan is obtained, in microseconds. |
| EXECUTE_TIME | bigint(20) | number(38) | The time that the plan takes to execute, in microseconds. |
| APPLICATION_WAIT_TIME | bigint(20) unsigned | number(38) | The total time of all application events, in microseconds. |
| CONCURRENCY_WAIT_TIME | bigint(20) unsigned | number(38) | The total time of all concurrency events, in microseconds. |
| USER_IO_WAIT_TIME | bigint(20) unsigned | number(38) | The total time of all user_io events, in microseconds. |
| SCHEDULE_TIME | bigint(20) unsigned | number(38) | The time of all schedule 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. |
| MEMSTORE_READ_ROW_COUNT | bigint(20) | number(38) | The number of rows read from the MemStore. |
| SSSTORE_READ_ROW_COUNT | bigint(20) | number(38) | The number of rows read from 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 on one side. |
| BLOCKSCAN_ROW_CNT | bigint(20) | NO | The number of data rows scanned on one side. |
| PUSHDOWN_STORAGE_FILTER_ROW_CNT | bigint(20) | NO | The number of rows filtered by the pushed-down storage filter. |
| 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 for the request. |
| USED_WORKER_COUNT | bigint(20) | number(38) | The number of worker threads actually used for the request. |
| SCHED_INFO | varchar(16384) | varchar(16384) | The scheduling information of the request. |
| PS_CLIENT_STMT_ID | bigint(20) | NUMBER(38) | This field records the client-side Prepare ID corresponding to the request.
|
| PS_INNER_STMT_ID | bigint(20) | NUMBER(38) | This field records the internal (database-level) Prepare ID corresponding to the request.
|
| TX_ID | bigint(20) | NO | The hash value of the transaction to which the request belongs. |
| SNAPSHOT_VERSION | bigint(20)unsigned | NO | The snapshot read version number of the current statement. |
| REQUEST_TYPE | bigint(20) | number(38) | The type of the request:
|
| IS_BATCHED_MULTI_STMT | tinyint(4) | number(38) | Indicates whether batch multi-statement optimization is performed. |
| OB_TRACE_INFO | VARCHAR2(4096) | VARCHAR2(4096) | The trace information set by the user. |
| PLAN_HASH | bigint(20) unsigned | number(38) | The hash value of the execution plan. |
| PARAMS_VALUE | longtext | CLOB | The parameter value. |
| RULE_NAME | varchar(256) | VARCHAR2(256) | The name of the rule. |
| TX_INTERNAL_ROUTING | bigint(20) | NUMBER | Indicates whether transaction routing is enabled for the current transaction. |
| TX_STATE_VERSION | bigint(20) unsigned | NUMBER(38) | The version number of the current transaction state. |
| FLT_TRACE_ID | varchar(1024) | varchar(1024) | The end-to-end trace ID. If this field is empty, the statement is not monitored by end-to-end tracing. This field is a UUID and is different from a trace ID. An example is: 000600d6-a5de-038c-6c80-df07e4e79149. |
| PL_TRACE_ID | varchar(128) | VARCHAR2(128) | The trace ID of the outer PL statement. |
| PLSQL_EXEC_TIME | bigint(20) | NUMBER(38) | The time that the PL procedure takes to execute. |
| TOTAL_MEMSTORE_READ_ROW_COUNT | bigint(20) | NUMBER(38) | The total number of rows read from the MemStore during the entire process. |
| TOTAL_SSSTORE_READ_ROW_COUNT | bigint(20) | NUMBER(38) | The total number of rows read from the SSSTORE during the entire process. |
| PROXY_USER | varchar(128) | VARCHAR2(128) | The name of the proxy user in a proxy user login scenario. |
| SEQ_NUM | bigint(20) | NUMBER(38) | The sequence number of the statement in the transaction.
NoteThis field is introduced in V4.3.5 and is NULL by default. |
| USER_CLIENT_PORT | bigint(20) | NO | The port number of the client.
NoteThis field is introduced in V4.3.5 BP4 and is NULL by default. |
| TRANS_STATUS | varchar(256) | NO | Indicates whether the transaction is explicitly started, implicitly started, or not started.
NoteThis field is introduced in V4.3.5 BP4 and is NULL by default. |
| FUSE_ROW_CACHE_HIT | bigint(20) | number(38) | This field is not supported. The value of this field is NULL by default. |
| LOCK_FOR_READ_TIME | bigint(20) |
NoticeThe |
The time spent waiting for a lock during data reading, in microseconds. |
| PARTITION_HIT | tinyint(4) | - | Indicates whether the local partition is hit. |
| PLSQL_COMPILE_TIME | bigint(20) | NUMBER(38) | The time taken for PL compilation.
Note
|
| INSERT_DUPLICATE_ROW_COUNT | bigint(20) | NUMBER(38) | The number of duplicate rows during insertup or replace into operations.
Note |
SQL audit-related settings
Enable or disable te SQL audit feature
Enable SQL audit:
ALTER SYSTEM SET enable_sql_audit = true;Disable SQL audit:
ALTER SYSTEM SET enable_sql_audit = false;
Percentage of memory allocated for SQL audit operations
ob_sql_audit_percentage specifies the percentage of tenant memory used by SQL audit. The default value is 3 and the range is [0, 80].
SET GLOBAL ob_sql_audit_percentage = 3;
For more information about ob_sql_audit_percentage, see ob_sql_audit_percentage.
Eviction mechanism of SQL audit
The backend task of the tenant will determine whether to trigger SQL eviction based on the OBServer node and the SQL audit memory usage, approximately every 1 second. The maximum amount of memory for SQL audit is allocated up to the specified threshold by using the parameter ob_sql_audit_percentage.
The SQL audit eviction mechanism is triggered when the actual memory usage of SQL audit reaches the specified threshold and it is stopped when the actual memory usage of SQL audit drops to the specified threshold. The following table describes the SQL audit eviction mechanism.
| Trigger mechanism | SQL audit memory range | Eviction trigger condition | Eviction stop condition |
|---|---|---|---|
| Memory usage | [0,64 M] | Maximum memory usage *50% | 0 M |
| Memory usage | [64M,100M] | Maximum memory usage -20M | Memory Limit -40M |
| Memory usage | [100M,5G] | 80% of Maximum memory usage | 60% of Memory Limit |
| Memory usage | (5G, +∞) | Maximum memory usage -1G | Maximum memory usage -2G |
| Number of records | None | 9 million | 8 million |
Example
You can easily query multi-dimensional information about SQL executions through the GV$OB_SQL_AUDIT view. The following example shows how to query the FLT_TRACE_ID for SQL executions that took more than 100 ms.
Enable end-to-end tracing at the session level. The tracing records the SQL resource usage, which is recorded at a sampling frequency of 50%.
CALL DBMS_MONITOR.OB_SESSION_TRACE_ENABLE(null,1,0.5,'ALL');For more information about DBMS_MONITOR.OB_SESSION_TRACE_ENABLE, see OB_SESSION_TRACE_ENABLE.
View the top 10 entries where the total time from receiving the request to the execution end exceeds 100,000 microseconds.
SELECT request_id, usec_to_time(request_time), ELAPSED_TIME, QUEUE_TIME, EXECUTE_TIME, FLT_TRACE_ID, QUERY_SQL FROM oceanbase.V$OB_SQL_AUDIT WHERE ELAPSED_TIME > 100000 LIMIT 10;The result is as follows:
+------------+----------------------------+--------------+------------+--------------+--------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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
References
- For more information about end-to-end tracing, see End-to-end tracing.
- For more information about the
GV$OB_SQL_AUDITview, see GV$OB_SQL_AUDIT (MySQL-compatible mode) or GV$OB_SQL_AUDIT (Oracle-compatible mode).