Performance - Waits

Top ten hotest objects by touch count

Waits by file

Segment Waits

Time waited for latches

Identify hot blocks

Look at the performance stats for the instance

Top ten hotest objects by touch count

col owner  format a20 trunc
col object_name format a30
col touches  format 9,999,999
select *
from (
 select count(*)
 , sum(tch) TOUCHES
 , u.name OWNER
 , o.name OBJECT_NAME
 from  x$bh x
 , obj$ o
 , user$ u
 where x.obj = o.obj#
 and o.owner# = u.user#
 group  by u.name, o.name
   order by 2 desc
 )
where rownum < 11
/

Waits by file

col name format a60
select name
, count 
from x$kcbfwait
, v$datafile
where indx + 1 = file#
order by 2
/

Segment Waits

select object_name
, obj#
, statistic_name
, value
from v$segment_statistics
where owner like '&owner'
and statistic_name like '%waits%'
and value > 0
order by statistic_name
,  value desc
/

Time waited for latches

col event format a30
select event
, time_waited
, round(time_waited*100/ SUM (time_waited) OVER(),2) wait_pct
from (
 select event
 , time_waited
 from v$system_event
 where event not in (
  'Null event'
  ,'client message'
  ,'rdbms ipc reply'
  ,'smon timer'
  ,'rdbms ipc message'
  ,'PX Idle Wait'
  ,'PL/SQL lock timer'
  ,'file open'
  ,'pmon timer'
  ,'WMON goes to sleep'
  ,'virtual circuit status'
  ,'dispatcher timer'
  ,'SQL*Net message from client'
  ,'parallel query dequeue wait'
  ,'pipe get')
 union
  (
  select name
  ,  value
  from v$sysstat
  where name like 'CPU used when call started'
  )
 )
order by 2 desc
/

Identify hot blocks

Look at all waits for the instance

col event format a30
select  event
, total_waits
, time_waited
from v$system_event 
where event like '%wait%'
order  by 2,3
/

If there are lots of 'data block' waits, get a break-down of them

select *
from v$waitstat
/

Then run this to identify the file, block and reason code...
select p1 "File #"
, p2 "Block #"
, p3 "Reason Code"
from v$session_wait
where event = 'buffer busy waits'
/
 
Note. You might need to run this a few times before anything is displayed.

 

Look at the performance stats for the instance
select n.name
, s.value
from v$statname n
, v$sysstat s
where n.statistic# = s.statistic#
order  by n.class
, n.name
/