17. DB CACHE优化小技巧（6）查找放入KEEP POOL的对象
放入KEEP POOL的对象最好从业务角度考虑
通过X$BH中的TCH字段可以进行辅助筛选
18. 经验分享：分析X$BH的脚本Select
decode(pd.bp_id,1, 'KEEP', 2,'RECYCLE', 3,'DEFAULT', 4, '2K SUBCACHE',
5,'4K SUBCACHE', 6,'8K SUBCACHE',
7, '16K SUBCACHE', 8,'32K SUBCACHE', 'UNKNOWN') subcache,
bh.object_name object_name,
bh.blocks,
tch
from
x$kcbwds ds,
x$kcbwbpd pd,
(select /*+ use_hash(x) */ set_ds,
o.name object_name, count(*) BLOCKS,tch
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,tch) 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
order by subcache,object_name;
26. 查找热块的技巧（1）AWR报告Segments by Buffer Busy Waits DB/Inst: SFOSS/sfoss1 Snaps: 10825-10826
-> % of Capture shows % of Buffer Busy Waits for each top segment compared
-> with total Buffer Busy Waits for all segments captured by the Snapshot
Buffer
Tablespace Subobject Obj. Busy % of
Owner Name Object Name Name Type Waits Capture
---------- ---------- -------------------- ---------- ----- ------------ -------
EXP5 EXP5_TEMP_ TT_WAYBILL_EXPORT_ES TABLE 39,627 61.55
EXP5 EXP5_BASE_ IPK_TT_THRASH_BILL INDEX 7,024 10.91
EXP5 EXP5_BASE_ IDX_TT_IMAGE_SCAN_IN INDEX 5,623 8.73
EXP5 EXP5_BASE_ IPK_TT_IMAGE_SCAN_IN INDEX 3,281 5.10
EXP5 EXP5_BASE_ IPK_TM_SYS_MES INDEX 2,214 3.44
-------------------------------------------------------------
27. 查找热块的技巧（2）select event,p1,p2,p3 from v$session_wait
where event like 'buffer busy wait%'EVENT P1 P2 P3
---------------------- ---------- ---------- ----------
buffer busy waits 17 26121 130
buffer busy waits 49 78591 130
buffer busy waits 17 26121 130
buffer busy waits 17 26121 130
buffer busy waits 17 26121 130
buffer busy waits 49 78582 130select owner,segment_name from dba_extents
where file_id=&p1 and block_id<=&p2 and (block_id+blocks)>=&p2
