Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching, Zero-Downtime Upgrade, GoldenGate

Oracle Exadata ,Oracle ODA, Oracle ZDLRA


当前位置: 首页 » 技术文章 » 性能优化

如何定位和解决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;
通过上面的p1raw(hash latch address)
select obj, ts#, dbarfil, dbablk, tch
  from x$bh
 where hladdr = '&p1raw'
 order by tch;
通过上面的obj找到对象信息
select owner, object_name, object_type
  from dba_objects
 where data_object_id = '&data_obj_id';
根据sqlid查找执行计划的信息
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大小,如果你设置的足够足够大,这个情况很少发生

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



关键词:hotblock perf 

相关文章

Exadata上收集Cell节点的日志
Retest Exadata IO Performance
Oracle实用的故障诊断工具
Exadata IO性能如何
Oracle blocking session monitor
如何定位和解决hot block问题
Oracle Invisible Index的妙用
ASH Viewer
Top