Search This Blog

Total Pageviews

Saturday, 1 May 2010

oracle 10g waits in last 30 min

--- last 30 minutes ( sysdate . 60/2880 )
--- WHERE (date) > sysdate - 7/1440; Past 7 minutes

spool wait_last_30min.txt
set pagesize 200
select to_char( sysdate, 'DD/MM/YY HH24:MI:SS' ) "Right Now" from dual;

prompt What resource is currently in high demand last 30 minutes?

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 - 60/2880 and sysdate
group by active_session_history.event
order by 2
/

-- Which user is waiting the most last 30 minutes?

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 - 60/2880 and sysdate
and active_session_history.session_id = sesion.sid
group by sesion.sid, sesion.username
order by 3
/

prompt What SQL is currently using the most resources last 30 minutes ?

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 - 60/2880 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
/


col oBJECT_NAME format a30
col OBJECT_TYPE format a15
col EVENT format a30

prompt What object is currently causing the highest resource waits last 30 minutes?

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 - 60/2880 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
/

No comments:

Oracle DBA

anuj blog Archive