Use SQL Audit to analyze wait events in a query

SQL Audit records the following information about wait events:

• The wait time of the four classes of wait events, each of which is respectively recorded in the APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME, USER_IO_WAIT_TIME, and SCHEDULE_TIME fields. Each class 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 locate the cause of 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$OB_SQL_AUDIT
     WHERE TRACE_ID = 'YB42AC1E87E6-0005B8AB2D578471'\G;

************************** 1. row ***************************
               SQL_ID: YB42AC1E87E6-0005B8AB2D578471
         ELAPSED_TIME: 953
           QUEUE_TIME: 18
        GET_PLAN_TIME: 58
         EXECUTE_TIME: 867
APPLICATION_WAIT_TIME: 0
CONCURRENCY_WAIT_TIME: 0
    USER_IO_WAIT_TIME: 550
        SCHEDULE_TIME: 0
                EVENT: db file data index read
           WAIT_CLASS: USER_IO
      WAIT_TIME_MICRO: 352
TOTAL_WAIT_TIME_MICRO: 550