如何定位和解决hot block问题

获取‘latch: cache buffers chains’的address，和sqlid

select p1raw, sql_id, count(*)
  from v$session s
 where event = 'latch: cache buffers chains'
   and wait_time = 0
 group by p1raw, sql_id;

select obj, ts#, dbarfil, dbablk, tch
  from x$bh
 where hladdr = '&p1raw'
 order by tch;

select owner, object_name, object_type
  from dba_objects
 where data_object_id = '&data_obj_id';

select sum(executions) executions,
       sum(buffer_gets) buffergets,
       sum(disk_reads) diskreads,
       sum(cpu_time) / 1000000 cpu_time,
       sum(elapsed_time) / 1000000 elapsed_time,
       sum(parse_calls) parse_calls,
       plan_hash_value,
       count(*),
       max(sql_text) sql_text
  from v$sql
 where sql_id = '&sql_id'
 group by plan_hash_value;

select event, count(*)
  from v$session
 where wait_time = 0
   and wait_class != 'Idle'
 group by event;

下面的查询语句来自163424.1，一个语句就够了。注意：在生产上执行时慎重

How to Identify Hot Blocks Within the Database Buffer Cache that may be 
Associated with 'latch: cache buffers chains' Wait Contention (Doc ID 
163424.1)
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;

如果是表调整表的pct free阈值

采用hash分区技术，将存储内容分块

采用reverse key indexes，比如用sequence作为序列

减小Buffer Cache大小，如果你设置的足够足够大，这个情况很少发生

减少执行次数，数据库资源时昂贵的，能少执行则少执行