rem Name:    bh.sql
rem Purpose: Display buffer cache (x$bh) usage by object
rem Usage:   @bh <%owner.name%> <%minbuf%> <%bufname%>
rem Subject: tuning
rem Attrib:  sql sys v80
rem Descr:
rem Notes:
rem SeeAlso: @hits

@setup1
define min="to_number(replace('&&2','%','0'))"
define buf="upper('&&3')"
column oname format a35 heading "OBJECT"
column bufname format a10 heading "BUFNAME"
column bufcnt format 999,990 heading "COUNT"

break on report
compute sum of buffer_count on report

select
owner||'.'||object_name||','||to_char(object_id) oname
,buffer_count bufcnt
,buffer_name bufname
from (
  select
   b.obj object_id
  , max(u.name) owner
  , max(o.name) object_name
  , max(p.name) buffer_name
  , count(*) buffer_count
  from x$bh b, v$buffer_pool p, sys.obj$ o, sys.user$ u
  where b.obj = o.dataobj#(+)
  and o.owner# = u.user#(+)
  and b.buf# >= p.lo_bnum(+)
  and b.buf# <= p.hi_bnum(+)
  group by b.obj
) b
where (owner like &&o1 or (owner is null and &&o1='%'))
and (object_name like &&n1 or (object_name is null and &&n1='%'))
and buffer_count >= &&min
and buffer_name like &&buf
order by decode(&&min,0,0,buffer_count),owner,object_name
;

undef min buf

@setdefs

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips