-- Description : Detects hot blocks.
-- Call Syntax : @hot_blocks
SET LINESIZE 200
SET VERIFY OFF
SELECT *
FROM (SELECT name,
addr,
gets,
misses,
sleeps
FROM v$latch_children
WHERE name = 'cache buffers chains'
AND misses > 0
ORDER BY misses DESC)
WHERE rownum < 11;
ACCEPT address PROMPT "Enter ADDR: "
COLUMN owner FORMAT A15
COLUMN object_name FORMAT A30
COLUMN subobject_name FORMAT A20
SELECT *
FROM (SELECT o.owner,
o.object_name,
o.subobject_name,
bh.tch,
bh.obj,
bh.file#,
bh.dbablk,
bh.class,
bh.state
FROM X$bh bh,
dba_objects o
WHERE o.data_object_id = bh.obj
AND hladdr = '&address'
ORDER BY tch DESC)
WHERE rownum < 11;
解决这些对象上的冲突的方法:
1.检查应用程序察看某些DML或者SQL是否可以重新组织避免在对象上冲突;
2.减少buffer cache,虽然仅在很少的情况下有帮助;
3.如果使用多DBWR,增加DBWR的数量;
4.通过ALTER TABLE增加PCTFREE的值或重建,使每个块的行数减少;
5.如果范围扫描不多,考虑使用反向键索引。
阅读(1191) | 评论(0) | 转发(0) |