Analyze SQL queries that cause an abrupt RT jitter

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 (g)v$sql_audit:

1. 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 sql_audit.

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

3. Find the requests with abnormal RT, and analyze their record in sql_audit for troubleshooting:

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.