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;