Oracle Tuning - Identifying SQL Statements with the Most Waits

To find the queries that are waiting the most,you must sum the values in the wait_time and time_waited columns of V$ACTIVE_SESSION_HISTORY for a specific SQL statement. To do this, you must join the V$SQLAREA view with the V$ACTIVE_SESSION_HISTORY view, using SQL_ID as the join column.

Execute the following query to identify the SQL statements that are experiencing the most waits in your database:

SQL>col username format a6
SQL>col sql_text format a45

SQL>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.