We recommend that you use Tars, an external diagnostic tool, to analyze the problem, or use the
(g)v$sql_audit view for troubleshooting.
Troubleshooting process when using
In case of online response time (RT) jittering, where the RT is not constantly high, you may consider immediately disabling sql_audit (
alter system set ob_enable_sql_audit = 0) once jittering occurs to ensure that the SQL request causing the jitter exists in
Run an SQL Audit query to check the TOP N requests in terms of RT that were received around the period when the jitter took place, and analyze if any abnormal SQL statements exist.
Find the requests with abnormal RT, and analyze their record in
RETRY_CNTfield for times of retry. A large number indicates possible lock conflicts or follower-to-leader switchovers.
QUEUE_TIMEfield for excessively large queue time values.
GET_PLAN_TIMEfield for the time spent in getting the execution plan. A long period of time often leads to
IS_HIT_PLAN = 0, meaning a failure to hit the plan cache.
Check the value of
EXECUTE_TIME. If the value is excessively large, perform the following steps:
a. Check for time-consuming pending events.
b. Analyze and check for an unusually large number of logical reads, which is possible in the case of sudden accesses by a great number of accounts.
Logical reads = 2* ROW_CACHE_HIT + 2 * BLOOM_FILTER_CACHE_HIT + BLOCK_INDEX_CACHE_HIT + BLOCK_CACHE_HIT + DISK_READS
If the request data corresponding to the RT jitter has been evicted from SQL Audit, you need to check the OBServer for the trace logs of slow queries at the time of jitter, and analyze these trace logs, if any.