以下脚本用于列出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')
/
