-------------------------------------------------------------------------------
--
-- Script:	buffer_busy_waits.sql
-- Purpose:	to report the block classes and tablespace affect by bbwaits
-- For:		8.0
--
-- Copyright:	(c) Ixora Pty Ltd
-- Author:	Steve Adams
--
-------------------------------------------------------------------------------
@save_sqlplus_settings

column block_class format a30
column buffer_pool format a30

select
  w.class  block_class,
  w.count  total_waits,
  w.time  time_waited
from
  sys.v_$waitstat  w
where
  w.count > 0
order by 3 desc
/
select
  d.tablespace_name,
  sum(x.count)  total_waits,
  sum(x.time)  time_waited
from
  sys.x_$kcbfwait  x,
  sys.dba_data_files  d
where
  x.inst_id = userenv('Instance') and
  x.count > 0 and
  d.file_id = x.indx + 1
group by
  d.tablespace_name
order by 3 desc
/
select
  p.bp_name  buffer_pool,
  sum(s.bbwait)  total_waits
from
  sys.x_$kcbwds s,
  sys.x_$kcbwbpd p
where
  s.inst_id = userenv('Instance') and
  p.inst_id = userenv('Instance') and
  s.set_id >= p.bp_lo_sid and
  s.set_id <= p.bp_hi_sid and
  p.bp_size != 0
group by
  p.bp_name
having
  sum(s.bbwait) > 0
/

@restore_sqlplus_settings