今天遇到一个闩锁问题，可是以前学的方法都忘光了....趁这机会复习一下

-------------------------------------------------------------------------

具体查找热点块所属对象的方法：

1、执行sql：

select CHILD# "cCHILD"
,      ADDR    "sADDR"
,      GETS    "sGETS"
,      MISSES "sMISSES"
,      SLEEPS "sSLEEPS"
from v$latch_children
where name = 'cache buffers chains'
order by 5, 1, 2, 3;
找到sleep count较高的地址（ADDR），再执行下面的语句

column segment_name format a35
select
e.owner ||'.'|| e.segment_name segment_name,
e.extent_id extent#,
x.dbablk - e.block_id + 1 block#,
x.tch,
l.child#
from
sys.v$latch_children l,
sys.x$bh x,
sys.dba_extents e
where
x.hladdr = 'ADDR' and
e.file_id = x.file# and
x.hladdr = l.addr and
x.dbablk between e.block_id and e.block_id + e.blocks -1
order by x.tch desc ;

2、由于上面的方法执行起来可能比较慢，而且有时候是针对某个session的事件进行查找的，因此可以用下面的方法：

v$session_wait中找到P1RAW地址，然后执行：

SELECT FILE# , dbablk, class, state ,tch
FROM x$bh WHERE hladdr='' order by tch;

找出touch较高的文件及数据块，最后执行

select * from dba_extents where file_id= and between block_id and block_id + blocks -1

造成latch free的对象就找到了（其实两种方法是一样的，只不过有些大系统分解了执行更快些，呵呵）。

--------------------------------------------------------------------------------------------

转：latch: cache buffers chains问题分析

有同事反应当前一个数据库跑过程非常慢，

登录到服务器上topas后发现cpu使用率已经达到100%，占用cpu多的进程均为oracle进程

通过pid查询cpu使用率大于4%的进程在oracle中执行的sql语句

SELECT b.sid, b.serial#, b.status, b.osuser, b.username, b.machine, b.sql_id, c.sql_text
FROM v$process a, v$session b, v$sqlarea c
WHERE a.spid = 455554
AND a.addr = b.paddr
AND b.sql_id = c.sql_id;

发现等待事件均为latch: cache buffers chains

google一下此事件的原因，网络上高手如是说

------------------------------

1、检查下v$waitstat,结果贴出来，看看是哪种类型的block为热点块
2、select sid,p1,p2,p3 from v$session_wait where event not like '%SQL%' and event = 'cache buffer chain'
3、select * from v$event_name where name= 'cache buffer chain' ,看看P1，P2，P3的含义
记得p1p3p3中应该有一个是表示latch地址的
4、查询x$bh where hladdr = (根据2和3中得到的latch地址),x$bh表中的 dbarfil,dbablk即为文件号和块号，从而根据dba_extents定位出来是哪个对象

5、要看是哪些SQL造成cache buffer chain,直接根据v$session_wait中的sid去查v$session获取sql_hash_value和sql_address就可以了啊。

cache buffer chain 一般来说是因为短时间里对少量块有高并发的访问。
1）热点块问题，
2）latch数量不够，而每个latch管理的cache buffer chain又太多（你的数据库版本如果已经是9i或者以上,那么这个应该不是问题）。

如果是热点块
1）很可能是因为SQL效率很差，造成很多不必要的访问
如9i前的版本，cluster_factor的问题，本来应该full table scan 而ORACLE采用index range scan造成了对同一个表的重复访问。但在9i以后该情况就得到了改善。
又或者采用了不正确的连接方式，如nest loop情况下，非驱动表上无索引或者索引的选择性很差，也会造成对非驱动表块的重复访问
2）如果热点块是索引块，可考虑反转索引
--------
就是说由于热块导致的
查询到的sql语句频繁的在查询表GP_PRC_LOG
做了一个近一个小时的awr后发现问题比较大的为
cache <wbr>buffers <wbr>chains集合

第一个语句在一个小时的时间里执行了9200362次，每秒钟执行2500多次
通过sql_id找到对应的sql语句为
SELECT COUNT(*) FROM GP_PRC_LOG WHERE PRC_NAME = 'jinzhou.prc_gp_rong_service' AND TO_CHAR(START_TIME, 'yyyymmdd') = TO_CHAR(SYSDATE, 'yyyymmdd') AND END_TIME IS NOT NULL
仍然是对GP_PRC_LOG表的频繁访问
下面需要找到此语句的出处
SELECT DISTINCT NAME from dba_source a WHERE lower(a.text) LIKE '%jinzhou.prc_gp_rong_service%' AND owner='JINZHOU';
GP_PRC_QQYJ_G
GP_PRC_TEL_YEAR_BEFORE
GP_PRC_WO_FAMILY
PRC_GP_RONG_SERVICE_NEW
GP_PRC_WO_BUSI
GP_PRC_QQJT_NEW
PRC_GP_RONG_SERVICE
GP_PRC_LM_JCHA
PRC_GP_RONG_SERVICE_MON
GP_PRC_LM_LT_NEW

依次打开找到的这些过程，在其中找到了问题的所在
这些过程中都有个前驱判断语句
----等待存储过程执行完毕
select count(*)
into num
from gp_prc_log
where prc_name = 'jinzhou.prc_gp_rong_service'
and to_char(start_time, 'yyyymmdd') = to_char(sysdate, 'yyyymmdd')
and end_time is not null;

while (num < 1) loop
---DBMS_LOCK.sleep(60);
select count(*)
into num
from gp_prc_log
where prc_name = 'jinzhou.prc_gp_rong_service'
and to_char(start_time, 'yyyymmdd') = to_char(sysdate, 'yyyymmdd')
and end_time is not null;
end loop;
当前驱不满足时，while语句一直循环，导致频繁的对日志表进行查询访问。

-------------------------------------------------------------------------------------------------------

未完待续

ITPub博客

cache buffers chains集合

转：latch: cache buffers chains问题分析