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') /
posted on 2009-06-17 01:24 Oracle和MySQL 阅读(238) 评论(0) 编辑 收藏 举报