Identifying SQL Statements with the Most Waits

Identifying SQL Statements with the Most Waits

select ash.user_id,
u.username,
s.sql_text,
sum(ash.wait_time +
ash.time_waited) ttl_wait_time
from v$active_session_history ash,
v$sqlarea s,
dba_users u
where ash.sample_time between sysdate - 60/2880 and sysdate
and ash.sql_id = s.sql_id
and ash.user_id = u.user_id
group by ash.user_id,s.sql_text, u.username
order by ttl_wait_time

The preceding query ranks queries that ran during the past 30 minutes, according to the total time
waited by each query.

When you’re experiencing a performance problem, it’s a good idea to see which SQL statements are
waiting the most. These are the statements that are using most of the database’s resources. To find the
queries that are waiting the most, you must sum the values in the wait_time and the time_waited
columns of the V$ACTIVE_SESSION_HISTORY for a specific SQL statement. In order to do this, you must join
the V$SQLAREA view with the V$ACTIVE_SESSION_HISTORY view, using SQL_ID as the join column.
Besides the SQL_ID of the SQL statements, the V$ACTIVE_SESSION_HISTORY view also contains
information about the execution plans used by the SQL statements. You can use this information to
identify why a SQL statement is experiencing a high amount of waits. You can also run an Active Session
History (ASH) report, using a SQL script or through Oracle Enterprise Manager, to get details about the