SQL Activity for the last hour

Cyrille Modiano
Latest posts by Cyrille Modiano (see all)

This script can be used to show the top 10 SQL activity for the last hour.
It uses the v$active_session_history view to search top SQL by resource consumption.

Top 10 SQL Activity

SELECT trunc(sample_time,'MI'),
       sql_id,
       count(sql_id) as TOTAL
FROM v$active_session_history
WHERE sample_time between sysdate - interval '1' hour and sysdate
AND sql_id in (select sql_id from (
 select
     SQL_ID ,
     sum(decode(session_state,'WAITING',1,1))  as TOTAL_ACTIVITY
from v$active_session_history
WHERE sample_time between sysdate - interval '1' hour and sysdate
group by sql_id
order by sum(decode(session_state,'WAITING',1,1))   desc)
where rownum < 11)
group by trunc(sample_time,'MI'),sql_id 
order by trunc(sample_time,'MI') desc

Here is the result you can obtain:

SQL activity

and the active sessions history graph for the same period:

instance activity last hour

4 thoughts on “SQL Activity for the last hour

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.