Purpose
The GV$OB_SQL_AUDIT view displays the statistics of each SQL request on all OBServer nodes, such as its source and execution status. This view is tenant-specific and cannot be queried by other tenants, except for the sys tenant.
Fields
Field |
Type |
Nullable? |
Description |
|---|---|---|---|
| SVR_IP | varchar(46) | NO | The IP address of the server. |
| SVR_PORT | bigint(20) | NO | The port number of the server. |
| REQUEST_ID | bigint(20) | NO | The ID of the request. |
| SQL_EXEC_ID | bigint(20) | NO | The ID of the execution, which monotonically increases on a single server. |
| TRACE_ID | varchar(128) | NO | The trace ID of the statement. |
| SID | bigint(20) unsigned | NO | The ID of the session connection. |
| CLIENT_IP | varchar(46) | NO | |
| CLIENT_PORT | bigint(20) | NO | The port number of the client that sent the request. |
| TENANT_ID | bigint(20) | NO | The ID of the tenant that sent the request. |
| TENANT_NAME | varchar(64) | NO | The name of the tenant that sent the request. |
| EFFECTIVE_TENANT_ID | bigint(20) | NO | The ID of the tenant. |
| USER_ID | bigint(20) | NO | The ID of the user that sent the request. |
| USER_NAME | varchar(64) | NO | The name of the user that sent the request. |
| USER_GROUP | bigint(20) | YES | The ID of the resource group to which the user belongs. |
| USER_CLIENT_IP | varchar(46) | NO | The IP address of the client that sent the request. |
| DB_ID | bigint(20) unsigned | NO | The ID of the database. |
| DB_NAME | varchar(128) | NO | The name of the database. |
| SQL_ID | varchar(32) | NO | The ID of the SQL statement. |
| QUERY_SQL | longtext | NO | The actual SQL statement. |
| PLAN_ID | bigint(20) | NO | The ID of the execution plan. |
| AFFECTED_ROWS | bigint(20) | NO | The number of rows affected. |
| RETURN_ROWS | bigint(20) | NO | The number of rows returned. |
| PARTITION_CNT | bigint(20) | NO | The number of partitions scanned by the request. |
| RET_CODE | bigint(20) | NO | The return code of the execution. Valid values: 0: indicates that the statement is successfully executed without errors. |
| QC_ID | bigint(20) unsigned | NO | The ID of the scheduler in the parallel execution scenario. |
| DFO_ID | bigint(20) | NO | The ID of the sub-plan being executed in the parallel execution scenario. |
| SQC_ID | bigint(20) | NO | The ID of the local coordinator in the parallel execution scenario. |
| WORKER_ID | bigint(20) | NO | The ID of the worker thread in the parallel execution scenario. |
| EVENT | varchar(64) | NO | The name of the wait event with the longest wait time. |
| P1TEXT | varchar(64) | NO | The first parameter of the wait event. |
| P1 | bigint(20) unsigned | NO | The value of the first parameter of the wait event. |
| P2TEXT | varchar(64) | NO | The second parameter of the wait event. |
| P2 | bigint(20) unsigned | NO | The value of the second parameter of the wait event. |
| P3TEXT | varchar(64) | NO | The third parameter of the wait event. |
| P3 | bigint(20) unsigned | NO | The value of the third parameter of the wait event. |
| LEVEL | bigint(20) | NO | The level of the wait event. |
| WAIT_CLASS_ID | bigint(20) | NO | The ID of the class to which the wait event belongs. |
| WAIT_CLASS# | bigint(20) | NO | The subscript of the class to which the wait event belongs. |
| WAIT_CLASS | varchar(64) | NO | The name of the class to which the wait event belongs. |
| STATE | varchar(19) | NO | The status of the wait event. |
| WAIT_TIME_MICRO | bigint(20) | NO | The wait time of the wait event in microseconds. |
| TOTAL_WAIT_TIME_MICRO | bigint(20) | NO | The total wait time in microseconds during execution. |
| TOTAL_WAITS | bigint(20) | NO | The total number of waits during the execution. |
| RPC_COUNT | bigint(20) | NO | The number of remote procedure calls (RPCs) sent. |
| PLAN_TYPE | bigint(20) | NO | The type of the execution plan. Valid values: |
| IS_INNER_SQL | tinyint(4) | NO | Indicates whether the request is an internal SQL request. |
| IS_EXECUTOR_RPC | tinyint(4) | NO | Indicates whether the current request is an RPC request. |
| IS_HIT_PLAN | tinyint(4) | NO | Indicates whether the plan cache is hit. |
| REQUEST_TIME | bigint(20) | NO | The time when the execution starts, in microseconds. |
| ELAPSED_TIME | bigint(20) | NO | 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) | NO | The amount of time elapsed from when the RPC was sent to when the request was received, in microseconds. |
| NET_WAIT_TIME | bigint(20) | NO | The amount of time elapsed from when the request was received to when it entered the queue, in microseconds. |
| QUEUE_TIME | bigint(20) | NO | The time that the request waits in the queue, in microseconds. |
| DECODE_TIME | bigint(20) | NO | The time spent on decoding the request after it left the queue, in microseconds. |
| GET_PLAN_TIME | bigint(20) | NO | The amount of time elapsed from when the processing started to when the plan was obtained, in microseconds. |
| EXECUTE_TIME | bigint(20) | NO | The time consumed for plan execution, in microseconds. |
| APPLICATION_WAIT_TIME | bigint(20) unsigned | NO | The total amount of time spent on waiting for events of the application class, in microseconds. |
| CONCURRENCY_WAIT_TIME | bigint(20) unsigned | NO | The total amount of time spent on waiting for events of the concurrency class, in microseconds. |
| USER_IO_WAIT_TIME | bigint(20) unsigned | NO | The total amount of time spent on waiting for events of the user_io class, in microseconds. |
| SCHEDULE_TIME | bigint(20) unsigned | NO | The total amount of time spent on waiting for events of the schedule class, in microseconds. |
| ROW_CACHE_HIT | bigint(20) | NO | The number of row cache hits. |
| BLOOM_FILTER_CACHE_HIT | bigint(20) | NO | The number of Bloom filter cache hits. |
| BLOCK_CACHE_HIT | bigint(20) | NO | The number of block cache hits. |
| DISK_READS | bigint(20) | NO | The number of physical reads. |
| RETRY_CNT | bigint(20) | NO | The number of retries. |
| TABLE_SCAN | tinyint(4) | NO | Indicates whether the request contains a full table scan. |
| CONSISTENCY_LEVEL | bigint(20) | NO | The consistency level. Valid values include:
|
| MEMSTORE_READ_ROW_COUNT | bigint(20) | NO | The number of rows read in the MemStore. |
| SSSTORE_READ_ROW_COUNT | bigint(20) | NO | The number of rows consecutively 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) | NO | The memory consumed by the request. |
| EXPECTED_WORKER_COUNT | bigint(20) | NO | The number of worker threads expected by the request. |
| USED_WORKER_COUNT | bigint(20) | NO | The number of worker threads used by the request. |
| SCHED_INFO | varchar(16384) | YES | The scheduling information about the request. |
| FUSE_ROW_CACHE_HIT | bigint(20) | NO | At present, this field is not supported and is NULL by default. |
| PS_CLIENT_STMT_ID | bigint(20) | NO | The ID of the prepared statement corresponding to the request. Valid values:
|
| PS_INNER_STMT_ID | bigint(20) | NO | The ID of the prepared statement 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 version of the current statement. |
| REQUEST_TYPE | bigint(20) | NO | The type of the request. Valid values: 0: indicates an invalid request.1: indicates an internal request.2: indicates a local request whose execution plan is a local plan.3: indicates a remote request.4: indicates a distributed request.5: indicates an SQL PREPARE request.6: indicates an SQL Execute Stmt request. |
| IS_BATCHED_MULTI_STMT | tinyint(4) | NO | Indicates whether optimization has been performed for batch execution of multiple statements. |
| OB_TRACE_INFO | varchar(4096) | NO | The trace information configured by the user. |
| PLAN_HASH | bigint(20) unsigned | NO | The hash value of the execution plan. |
| LOCK_FOR_READ_TIME | bigint(20) | NO | The time in microseconds spent on waiting for locked data to be unlocked before it can be read. |
| PARAMS_VALUE | longtext | NO | The parameter value during the execution of the PS statement. |
General SQL information
SQL routing information
In OceanBase Database, SQL plans are divided into the following four types:
Local plans: The transaction session and the Leader of the partitions involved in the SQL statement are on the same OBServer node. The SQL statement may involve multiple partitions, but a table cannot contain multiple partitions.
Remote plans: The transaction session and the Leader of the partitions involved in the SQL statement are on different OBServer nodes. The SQL statement may involve multiple partitions, but a table cannot contain multiple partitions.
Distributed plans: The SQL statement involves operations on multiple partitions. The partitions can be in one or more OBServer nodes.
Uncertain plans: The number of partitions involved cannot be determined when the SQL statement is executed. These SQL execution plans typically exist in scenarios with global indexes and foreign keys.
Local transactions are committed more efficiently than cross-server transactions. Therefore, you need to check whether the percentages of different types of SQL execution plans are as expected. You can execute the following statement to collect statistics on SQL plans by type.
select plan_type, count(1) from GV$OB_SQL_AUDIT where tenant_id = *** and request_time > time_to_usec('2021-10-08 12:00:00') group by plan_type;
The value of plan_type can be 1, 2, 3, or 4, indicating a local plan, a remote plan, a distributed plan, or an uncertain plan, respectively.
Slow SQL statistics
You can query the SQL statements whose execution time exceeds the specified threshold during a period, and thereby optimize the SQL plans based on the query result.
select tenant_id, elapsed_time, usec_to_time(request_time), substr(query_sql, 1, 50) from GV$OB_SQL_AUDIT where tenant_id=1001 and elapsed_time > 100000 and request_time > time_to_usec('2021-10-08 12:00:00') order by elapsed_time desc limit 40;
SQL statistics within a transaction
Each SQL statement entry in sql_audit records the unique identifier of the current SQL transaction in transaction_hash. You can check this field to find all SQL information about the current transaction. Then, you can determine whether the transaction model, including single-SQL transaction and multi-SQL transaction, in business stress testing meets your expectation.
select tenant_id, substr(query_sql,1,50) from GV$OB_SQL_AUDIT where tenant_id=1002 and TX_ID='***' order by request_time;
SQL statements executed in the same session
You can query all business requests by SID to analyze the business model.
select tenant_id, substr(query_sql,1,50) from GV$OB_SQL_AUDIT where tenant_id=1002 and sid=1001 and request_time > time_to_usec('2021-10-08 12:00:00') order by request_time;