oracle active sql for last 5min

oracle active sql for last 5min
Oracle active session info



SELECT topsql.sql_id ,txt.sql_text
,topsql.active_hits ,topsql.pctload*100 pctload
FROM (SELECT sql_id ,COUNT(*) active_hits
,ROUND(COUNT(*)/SUM(COUNT(*)) OVER (), 2) pctload
FROM v$active_session_history
WHERE session_type!='BACKGROUND'
AND sample_time > sysdate -5/1440
GROUP BY sql_id
ORDER BY COUNT(*) DESC
) topsql
,v$sqltext txt
WHERE topsql.sql_id = txt.sql_id
AND txt.piece = 0
AND ROWNUM <= 30
;


active sql for last 1 min

select sql_id,count(*), count(*)*100/sum(count(*)) over () pctload
from v$active_session_history
where sample_time > sysdate - 1/24/60
and session_type <> 'BACKGROUND'
group by sql_id
order by count(*) desc ;



IO

select sql_id,count(*) from v$active_session_history
where sample_time > sysdate - 1/24/60
and wait_class='User I/O'
group by sql_id
order by count(*) desc ;

Wait

select sum(a.time_waited) total_time
from v$active_session_history a , v$event_name b
where a.event# = b.event# and
sample_time > '02-JUL-11 12:00:00 AM' and
sample_time <'02-JUL-11 05:00:00 AM' and
b.wait_class='User I/O'