How to identify recent WAIT events in a Oracle database ?

Problem:

You want to find out the recent important waits in your database, as well as the users, SQL statements, and objects that are responsible for most of those waits.

Solution

Oracle provides us with V$ACTIVE_SESSION_HISTORY to get information about the most common wait events, and the SQL statements, database objects, and users responsible for those waits.

SQL to find the wait events

Example

SELECT event,   SUM(wait_time + time_waited) total_wait_time FROM v$active_session_history GROUP BY event ORDER BY total_wait_time DESC;

To find out the users who experienced the most waits we can use below SQL.

Example

SELECT s.sid,   s.username,   SUM(a.wait_time + a.time_waited) total_wait_time FROM v$active_session_history a,   v$session s WHERE a.session_id=s.sid GROUP BY s.sid,   s.username ORDER BY total_wait_time DESC;

To find out the objects with the most waits we can use below SQL.

Example

SELECT a.current_obj#,   d.object_name,   d.object_type,   a.event,   SUM(a.wait_time + a.time_waited) total_wait_time FROM v$active_session_history a,   dba_objects d WHERE a.current_obj# = d.object_id GROUP BY a.current_obj#,   d.object_name,   d.object_type,   a.event ORDER BY total_wait_time;

Finally, we can identify the SQL statements that have been waiting the most with below query.

Example

SELECT a.user_id,   u.username,   s.sql_text,   SUM(a.wait_time + a.time_waited) total_wait_time FROM v$active_session_history a,   v$sqlarea s,   dba_users u WHERE  a.sql_id  = s.sql_id AND a.user_id = u.user_id GROUP BY a.user_id,   s.sql_text,   u.username;