Tuesday, January 29, 2013
Top Waits by Object
SQL to Identify Objects Creating Cluster-wide bottlenecks (in the past 24 hours)
set linesize 140
set pagesize 50
col sample_time format a26
col event format a30
col object format a45
--col num_sql heading '# SQL' format 9,999
select
ash.sql_id,
-- count(distinct ash.sql_id) Num_SQL,
ash.event,
ash.current_obj#,
o.object_type,
o.owner||'.'||o.object_name||'.'||o.subobject_name object,
count(*)
from gv$active_session_history ash,
all_objects o
where ash.current_obj# = o.object_id
and ash.current_obj# != -1
and ash.event is not null
and ash.sample_time between sysdate - 1 and sysdate
-- and ash.sample_time between sysdate - 4 and sysdate - 3
-- and to_date ('24-SEP-2010 14:28:00','DD-MON-YYYY HH24:MI:SS') and to_date ('24-SEP-2010 14:29:59','DD-MON-YYYY HH24:MI:SS')
group by
ash.sql_id,
ash.event,
ash.current_obj#,
o.object_type,
o.owner||'.'||o.object_name||'.'||o.subobject_name
having count(*) > 20
order by count(*) desc
/
exit
/
Labels:
11g,
Monitoring,
Oracle,
Performance,
Waits
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment