GV$OB_SQL_AUDIT

Description

This view displays the statistics of each SQL request on all OBServer nodes, including the source and execution status. The view is tenant-based. Except for the sys tenant, other tenants cannot query this view across tenants.

Field description

Important event intervals

The following figure shows the relationships between the important events in the ELAPSED_TIME, REQUEST_TIME, NET_WAIT_TIME, QUEUE_TIME, DECODE_TIME, GET_PLAN_TIME, EXECUTE_TIME, and COMMIT_TIME columns of this view:

Overview of common SQL statements

SQL routing information

The SQL plan types in OceanBase Database are categorized into the following four types:

• Local plan: The transaction session and the leader of the partition involved in the statement are on the same OBServer node. It can involve multiple partitions, but multiple partitions cannot exist in the same table.

• Remote plan: The transaction session and the leader of the partition involved in the statement are on different OBServer nodes. It can involve multiple partitions, but multiple partitions cannot exist in the same table.

• Distribute plan: The statement involves multiple partitions. The partitions can be distributed across one or more OBServer nodes.

• Uncertain plan: The number of partitions involved in the statement cannot be determined during the execution of the statement. This type of plan typically occurs in scenarios such as global indexes and foreign keys.

Single-machine transactions are more efficient than cross-machine transactions. Therefore, users need to verify whether the proportion of the above plans meets their expectations. Here is an example of a statement for statistics of SQL plans:

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 plan_type field can take the values 1, 2, 3, and 4, which indicate Local plans, Remote plans, Distribute plans, and Uncertain plans, respectively.

Slow SQL statistics

Query the SQL statements that take longer than a specified threshold within a specific time period and optimize the SQL plans based on the query results:

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 in the sql_audit table is expected to record the unique transaction identifier: transaction_hash. You can use this field to find all SQL statements within the current transaction and determine whether the transaction model for business stress testing meets your expectations, such as single-SQL transactions or multi-SQL transactions.

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 based on the session ID (SID). This is mainly used for business model analysis.

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;