We recommend that you use Tars, an external diagnostic tool, to analyze the issue, or use the (G)V$OB_SQL_AUDIT view for troubleshooting.
Perform the following steps to use (G)V$OB_SQL_AUDIT for troubleshooting:
In case of an online response time (RT) jitter where the RT is not constantly long, you may consider immediately disabling SQL Audit after the jitter occurs. This ensures that the SQL request that caused the jitter exists in the SQL Audit records. You can disable SQL Audit by executing the
ALTER SYSTEM SET enable_sql_audit = false;statement.Run an SQL Audit query to check the TOP N requests with the longest RT around the point in time when the jitter occurred. Then, analyze if any abnormal SQL requests exist.
Find the requests with abnormal RT and analyze their SQL Audit records for troubleshooting:
a. Check for time-consuming wait events.
b. Analyze and check for an unusually large number of logical reads, which is possible in case of sudden access by a great number of accounts.
Logical reads = 2 × ROW_CACHE_HIT
+ 2 × BLOOM_FILTER_CACHE_HIT
+ BLOCK_CACHE_HIT + DISK_READS
If the request data corresponding to the RT jitter has been evicted from SQL Audit, check the OBServer node for the trace logs of slow queries at the time of jitter and analyze these trace logs, if any.