SQL> select * from (
select sql_id, inst_id,
sum(decode(vash.session_state, 'ON CPU', 1,0)) as "Number on CPU",
sum(decode(vash.session_state, 'WAITING', 1, 0)) as "Number Waiting on CPU"
from gv$active_session_history cash
where sample_time > sys date -5/(60*24)
group by sql_id, instead
order by 3 desc
) where rownum <11;
Query top 10 SQL_ID's in the last 10 minutes
select * from
(select NVL(SQL_ID,'NULL') AS SQL_ID,
SUM(1) AS "DBTime IN SECONDES" FROM V$ACTIVE_SESSION_HISTORY
WHERE sample_time > sys date - 10/(24*60)
GROUP BY SQL_ID
ORDER BY 2 DESC)
WHERE ROWNUM < 11;
Wait Events Across Nodes
select * from (
select sql_id, inst_id,
sum(decode(vash.session_state, 'ON CPU', 1,0)) as "Number on CPU",
sum(decode(vash.session_state, 'WAITING', 1, 0)) as "Number Waiting on CPU",
event , count(distinct(session_id||session_serial#)) as "SESSION COUNT"
from gv$active_session_history cashwhere sample_time > sys date -5/(60*24)
group by event,sql_id, instead
order by 3 desc
) where rownum <11;
SELECT TW.*, DO.object_name from (
select current_obj#,
ROUND(SUM(CASE WHEN TIME_WAITED >= 1000000 THEN 1
ELSE 1000000 / TIME_WAITED END)) AS "Estimated IO Waits",
SUM(1) AS "ESTIMATED DBTIME"
FROM V$ACTIVE_SESSION_HISTORY
WHERE sample_time > sysdate - 5/(24*60) and TIME_WAITED > 0
and WAIT_CLASS = 'User I/O'
GROUP BY CURRENT_OBJ#
ORDER BY 2 DESC) TW, DBA_OBJECTS DO
WHERE DO.object_id=TW.current_obj#
and ROWNUM < 11;
