All Products
Search
Document Center

Analyze SQL queries that cause an abrupt RT jitter

Last Updated: Jun 18, 2021

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:

    • Check the RETRY_CNT field for times of retry. A large number indicates possible lock conflicts or follower-to-leader switchovers.

    • Check the QUEUE_TIME field for excessively large queue time values.

    • Check the GET_PLAN_TIME field 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.