Queries from v$active_session_history

Top 10 queries from v$active_session_history

This query return top queries by resources consumed, you can easily return the top I/O, WAITS or CPU queries by changing the order by clause.

select * from (

              select

                              SQL_ID ,

                              sum(decode(session_state,'ON CPU',1,0)) as CPU,

                              sum(decode(session_state,'WAITING',1,0)) - sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as WAIT,

                              sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as IO,

                              sum(decode(session_state,'ON CPU',1,1)) as TOTAL

              from v$active_session_history

              where SQL_ID is not NULL

              group by sql_id

              order by sum(decode(session_state,'ON CPU',1,1))   desc

              )

where rownum <11

SQL Activity for the last hour

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.

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

Top 10 sessions from v$active_session_history

select * from (

select

     session_id,

     session_serial#,

     program,

     module,

     action,

     sum(decode(session_state,'WAITING',0,1)) "CPU",

     sum(decode(session_state,'WAITING',1,0)) - sum(decode(session_state,'WAITING',decode(wait_class,'User I/O',1,0),0)) "WAITING" ,

     sum(decode(session_state,'WAITING',decode(wait_class,'User I/O',1,0),0)) "IO" ,

     sum(decode(session_state,'WAITING',1,1)) "TOTAL"

from v$active_session_history

where session_type='FOREGROUND'

group by session_id,session_serial#,module,action,program

order by sum(decode(session_state,'WAITING',1,1)) desc)

where rownum <11;

SQL Activity for the last hour

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;

 Top 5 wait events from v$active_session_history

select * from (

              select

                              WAIT_CLASS ,

                              EVENT,

                              count(sample_time) as EST_SECS_IN_WAIT

              from v$active_session_history

              where sample_time between sysdate - interval '1' hour and sysdate

              group by WAIT_CLASS,EVENT

              order by count(sample_time) desc

              )

where rownum <6