Oracle sql waiting / resources in last n min

 

 

 

 

-- SELECT TO_CHAR(SYSDATE - INTERVAL '20' MINUTE, 'HH:MI:SS') FROM dual;

--------------------------------------------

wmin.sql

prompt  &&min


prompt  currently in high demand?

select active_session_history.event,sum(active_session_history.wait_time + active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history
where active_session_history.sample_time between ( sysdate  - INTERVAL '&&min' MINUTE ) and sysdate
group by active_session_history.event
order by 2;
 

prompt waiting the most?
 
select sesion.sid, sesion.username,
sum(active_session_history.wait_time + active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history, v$session sesion
where active_session_history.sample_time between ( sysdate  - INTERVAL '&&min' MINUTE ) and sysdate
and active_session_history.session_id = sesion.sid
group by sesion.sid, sesion.username
order by 3;
 
 
 
prompt currently using the most resources?
 
select active_session_history.user_id, dba_users.username, sqlarea.sql_text,
sum(active_session_history.wait_time + active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history, v$sqlarea sqlarea, dba_users
where active_session_history.sample_time between ( sysdate  - INTERVAL '&&min' MINUTE )  and sysdate
and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
group by active_session_history.user_id,sqlarea.sql_text, dba_users.username
order by 4;



prompt currently causing the highest resource waits?

col OBJECT_NAME format a20
select dba_objects.object_name, dba_objects.object_type, active_session_history.event,
sum(active_session_history.wait_time + active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history, dba_objects
where active_session_history.sample_time between ( sysdate  - INTERVAL '&&min' MINUTE ) and sysdate
and active_session_history.current_obj# = dba_objects.object_id
group by dba_objects.object_name, dba_objects.object_type, active_session_history.event
order by 4;

-------------