All Products
Search
Document Center

Use SQL Audit to analyze wait events in a query

Last Updated: Jun 18, 2021

SQL Audit records the following information about wait events:

  • The wait time of 4 types of wait events: APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME, USER_IO_WAIT_TIME, and SCHEDULE_TIME. Each type involves many specific wait events.

  • The name of the most time-consuming wait event, which is indicated by EVENT, and the time consumed, which is indicated by WAIT_TIME_MICRO.

  • The number of occurrences of all wait events, which is indicated by TOTAL_WAITS, and the total time consumed, which is indicated by TOTAL_WAIT_TIME_MICRO.

If a large amount of time is consumed by wait events, you can check EVENT for the most time-consuming wait event and see what caused the problem.

In the following example, I/O wait is the main cause of time consumption.

obclient>SELECT SQL_ID,  ELAPSED_TIME, QUEUE_TIME,  GET_PLAN_TIME, EXECUTE_TIME, 
     APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME, USER_IO_WAIT_TIME, SCHEDULE_TIME, EVENT, 
      WAIT_CLASS,  WAIT_TIME_MICRO, TOTAL_WAIT_TIME_MICRO 
     FROM v$sql_audit 
     WHERE TRACE_ID = 'YB42AC1E87E6-0005B8AB2D578471'\G;

************************** 1. row ***************************
               SQL_ID: CAFC81EE933820AEC5A86CBBAC1D0F6D
         ELAPSED_TIME: 2168
           QUEUE_TIME: 33
        GET_PLAN_TIME: 276
         EXECUTE_TIME: 1826
APPLICATION_WAIT_TIME: 0
CONCURRENCY_WAIT_TIME: 0
    USER_IO_WAIT_TIME: 0
        SCHEDULE_TIME: 0
                EVENT: sync rpc
           WAIT_CLASS: NETWORK
      WAIT_TIME_MICRO: 1596
TOTAL_WAIT_TIME_MICRO: 1596