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$OB_SQL_AUDIT view for troubleshooting.

Perform the following steps to use (G)V$OB_SQL_AUDIT for troubleshooting:

1. In a production environment, if there is a sudden fluctuation in response time (RT) that is not persistent, we recommend that you immediately disable SQL auditing (by executing the ALTER SYSTEM SET enable_sql_audit = false;) command after the fluctuation occurs, to ensure that the SQL requests during this fluctuation are recorded in SQL auditing.

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

3. Find the requests with abnormal RT and analyze their SQL Audit records 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 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).