GV$OB_SQL_AUDIT

Feature

GV$OB_SQL_AUDIT displays the statistics of each SQL request on all OBServers, such as its source and execution status. This view is tenant-specific, and only the sys tenant can query this view in other tenants.

Fields

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. 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 OBServers. 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 OBServers.

• 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-OBServer 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 local plans, remote plans, distributed plans, or uncertain plans, 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 the 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;