Showing posts with label Waits. Show all posts
Showing posts with label Waits. Show all posts
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
/
Top 5 Timed Wait Events
SQL: (11.1 db required, min)
set feedback off
set verify off
col dbid new_value v_dbid
col min_snap new_value v_min_snap
col max_snap new_value v_max_snap
set termout off
select (select dbid from v$database) dbid,1,min(dhs.snap_id) min_snap, max(dhs.snap_id) max_snap
from dba_hist_snapshot dhs
where dhs.end_interval_time >= to_date(sysdate - 1)
and dhs.instance_number = 1
group by dbid
/
set termout on
set heading off
--select output from table(DBMS_WORKLOAD_REPOSITORY.awr_report_text (&v_dbid,1,&v_min_snap,&v_max_snap))
WITH aa AS
(SELECT output, ROWNUM r
FROM table(DBMS_WORKLOAD_REPOSITORY.awr_report_text (&v_dbid, 1, &v_min_snap, &v_max_snap)))
SELECT output top_five
FROM aa, (SELECT r FROM aa
WHERE output LIKE 'Top 5 Timed Foreground Events%') bb
WHERE aa.r BETWEEN bb.r AND bb.r + 10
order by bb.r
/
set heading on
prompt
prompt
exit
/
Sample Output:
Top 5 Timed Foreground
Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
log file sync 15,848,089 144,008 9 30.0 Commit
DB CPU 122,035 25.5
direct path read 600,016 111,336 186 23.2 User I/O
db file sequential read 4,367,904 32,126 7 6.7 User I/O
enq: TM - contention 49 18,344 4.E+05 3.8 Applicatio
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
log file sync 15,848,089 144,008 9 30.0 Commit
DB CPU 122,035 25.5
direct path read 600,016 111,336 186 23.2 User I/O
db file sequential read 4,367,904 32,126 7 6.7 User I/O
enq: TM - contention 49 18,344 4.E+05 3.8 Applicatio
Subscribe to:
Posts (Atom)