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的含义    记得p1\p3\p3中应该有一个是表示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后发现问题比较大的为 第一个语句在一个小时的时间里执行了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语句一直循环，导致频繁的对日志表进行查询访问。