Script:List Buffer Cache Details

以下脚本用于列出Oracle Buffer Cache的详细信息:

REM List Buffer Cache Details

SET LINESIZE 200 PAGESIZE 1400

SELECT /*+ ORDERED USE_HASH(o u) MERGE */
 DECODE(obj#,
        NULL,
        to_char(bh.obj),
        u.name || '.' || o.name) name,
 COUNT(*) total,
 SUM(DECODE((DECODE(lru_flag, 8, 1, 0) + DECODE(SIGN(tch - 2), 1, 1, 0)),
            2,
            1,
            1,
            1,
            0)) hot,
 SUM(DECODE(DECODE(SIGN(lru_flag - 8), 1, 0, 0, 0, 1) +
            DECODE(tch, 2, 1, 1, 1, 0, 1, 0),
            2,
            1,
            1,
            0,
            0)) cold,
 SUM(DECODE(BITAND(flag, POWER(2, 19)), 0, 0, 1)) fts,
 SUM(tch) total_tch,
 ROUND(AVG(tch), 2) avg_tch,
 MAX(tch) max_tch,
 MIN(tch) min_tch
  FROM x$bh bh, sys.obj$ o, sys.user$ u
 WHERE 
    bh.obj <> 4294967295
   AND bh.state in (1, 2, 3)
   AND bh.obj = o.dataobj#(+)
   AND bh.inst_id = USERENV('INSTANCE')
 AND o.owner# = u.user#(+)
--   AND o.owner# > 5
   AND u.name NOT like 'AURORA$%'
 GROUP BY DECODE(obj#,
                 NULL,
                 to_char(bh.obj),
                 u.name || '.' || o.name)
 ORDER BY  total desc 
 /
 
 
 COLUMN object_name FORMAT A30

SELECT t.name AS tablespace_name,
       o.object_name,
       SUM(DECODE(bh.status, 'free', 1, 0)) AS free,
       SUM(DECODE(bh.status, 'xcur', 1, 0)) AS xcur,
       SUM(DECODE(bh.status, 'scur', 1, 0)) AS scur,
       SUM(DECODE(bh.status, 'cr', 1, 0)) AS cr,
       SUM(DECODE(bh.status, 'read', 1, 0)) AS read,
       SUM(DECODE(bh.status, 'mrec', 1, 0)) AS mrec,
       SUM(DECODE(bh.status, 'irec', 1, 0)) AS irec
FROM   v$bh bh
       JOIN dba_objects o ON o.data_object_id = bh.objd
       JOIN v$tablespace t ON t.ts# = bh.ts#
GROUP BY t.name, o.object_name
order by xcur desc 
/


 
set pages 999
set lines 92
 
ttitle 'Contents of Data Buffers'
 
drop view buffer_map;
 
create view buffer_map as
select
   o.owner          owner,
   o.object_name    object_name,
   o.subobject_name subobject_name,
   o.object_type    object_type,
   count(distinct file# || block#)         num_blocks
from
   dba_objects  o,
   v$bh         bh
where
   o.data_object_id  = bh.objd
-- and  o.owner not in ('SYS','SYSTEM')
and
   bh.status != 'free'
group by
   o.owner,
   o.object_name,
   o.subobject_name,
   o.object_type
order by
   count(distinct file# || block#) desc
;
 
column c0 heading "Owner"                                    format a12
column c1 heading "Object|Name"                              format a30
column c2 heading "Object|Type"                              format a8
column c3 heading "Number of|Blocks in|Buffer|Cache"         format 99,999,999
column c4 heading "Percentage|of object|blocks in|Buffer"    format 999
column c5 heading "Buffer|Pool"                              format a7
column c6 heading "Block|Size"                               format 99,999
 
select
   buffer_map.owner                                          c0,
   object_name                                       c1,
   case when object_type = 'TABLE PARTITION' then 'TAB PART'
        when object_type = 'INDEX PARTITION' then 'IDX PART'
        else object_type end c2,
   sum(num_blocks)                                     c3,
   (sum(num_blocks)/greatest(sum(blocks), .001))*100 c4,
   buffer_pool                                       c5,
   sum(bytes)/sum(blocks)                            c6
from
   buffer_map,
   dba_segments s
where
   s.segment_name = buffer_map.object_name
and
   s.owner = buffer_map.owner
and
   s.segment_type = buffer_map.object_type
and
   nvl(s.partition_name,'-') = nvl(buffer_map.subobject_name,'-')
group by
   buffer_map.owner,
   object_name,
   object_type,
   buffer_pool
having
   sum(num_blocks) > 10
order by
   sum(num_blocks) desc
;

REM dbbuffer

select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
         4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
         7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
         bh.object_name,bh.blocks
from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,
         o.name object_name,count(*) BLOCKS
         from obj$ o, x$bh x where o.dataobj# = x.obj
         and x.state !=0 and o.owner# !=0
         group by set_ds,o.name) bh 
where ds.set_id >= pd.bp_lo_sid
and ds.set_id <= pd.bp_hi_sid
and pd.bp_size != 0
and ds.addr=bh.set_ds;

column segment_name format a35
     select /*+ RULE */
       e.owner ||'.'|| e.segment_name  segment_name,
       e.extent_id  extent#,
       x.dbablk - e.block_id + 1  block#,
       x.tch,
       l.child#
     from
       sys.v$latch_children  l,
       sys.x$bh  x,
       sys.dba_extents  e
     where
       x.hladdr  = '&ADDR' and
       e.file_id = x.file# and
       x.hladdr = l.addr and
       x.dbablk between e.block_id and e.block_id + e.blocks -1
     order by x.tch desc 
/



with bh_lc as
       (select /*+ ORDERED */
          lc.addr, lc.child#, lc.gets, lc.misses, lc.immediate_gets,
          lc.immediate_misses, lc.spin_gets, lc.sleeps,
          bh.hladdr, bh.tch tch, bh.file#, bh.dbablk, bh.class,
          bh.state, bh.obj
        from
          x$kslld ld,
          v$session_wait sw,
          v$latch_children lc,
          x$bh bh
        where lc.addr =sw.p1raw
          and sw.p2= ld.indx
          and ld.kslldnam='cache buffers chains'
          and lower(sw.event) like '%latch%'
          and sw.state='WAITING'
          and bh.hladdr=lc.addr
       )
     select bh_lc.hladdr, bh_lc.tch, o.owner, o.object_name, o.object_type,
            bh_lc.child#, bh_lc.gets,
            bh_lc.misses, bh_lc.immediate_gets,
            bh_lc.immediate_misses, spin_gets, sleeps
     from
       bh_lc,
       dba_objects o
     where bh_lc.obj = o.object_id(+)
   union
     select bh_lc.hladdr, bh_lc.tch, o.owner, o.object_name, o.object_type,
            bh_lc.child#, bh_lc.gets, bh_lc.misses, bh_lc.immediate_gets,
            bh_lc.immediate_misses, spin_gets, sleeps
     from
       bh_lc,
       dba_objects o
     where bh_lc.obj = o.data_object_id(+)
  order by 1,2 desc
/

col class form A10 
select decode(greatest(class,10),10,decode(class,1,'Data',2 
            ,'Sort',4,'Header',to_char(class)),'Rollback') "Class", 
       sum(decode(bitand(flag,1),1,0,1)) "Not Dirty", 
 sum(decode(bitand(flag,1),1,1,0)) "Dirty", 
       sum(dirty_queue) "On Dirty",count(*) "Total" 
from x$bh 
group by decode(greatest(class,10),10,decode(class,1,'Data',2 
         ,'Sort',4,'Header',to_char(class)),'Rollback') 
/