GV$OB_SQL_AUDIT

Description

This view displays the source, execution status, and other statistics of each SQL request on all OBServer nodes. The view is tenant-based. Only the system tenant can query this view. 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 in this view:

Overview of SQL

SQL routing information

OceanBase Database SQL plans are categorized into four types:

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

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

• Distribute plan: The current statement involves multiple partitions. Partitions can be distributed across one or more OBServers.

• Uncertain plan: The number of partitions cannot be determined during statement execution. This 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 if the proportion of the above plans meets expectations. Here is an example of SQL plan statistics:

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 correspond to Local, Remote, Distribute, and Uncertain plans, respectively.

Slow SQL statistics

Query for SQL statements that take longer than a specified threshold within a given time period and optimize the SQL plan 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 if the transaction model meets expectations, such as single-SQL 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 executed in the same session

You can query all business requests based on the SID. This is mainly used for analyzing business models.

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;