Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


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

如何定位和解决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 

相关文章

Retest Exadata IO Performance
Oracle实用的故障诊断工具
Exadata IO性能如何
Oracle blocking session monitor
如何定位和解决hot block问题
Oracle Invisible Index的妙用
ASH Viewer

联系我们

韩朝阳



    Phone:186-9589-1286

    E-mail:ohsdba@qq.com

    微  信: ohsdba


电话(186-9589-1286)

QQ(375349564)

微信(ohsdba)


常用链接

Oracle
eDelivery
My Oracle Support

Exadata 12.1 Document

Exadata 12.2 Document

Oracle 11gR2 Document

Oracle 12cR1 Document

Oracle 12cR2 Document

GoldenGate 12c

Oracle Fusion Middleware

Mysql Document

PostgreSQL

Python

github

sourceforge



Top
Cell186-9589-1286
QQ 375349564
E-mail ohsdba@qq.com