latch cache buffers chains的解决步骤是什么

今天就跟大家聊聊有关latch cache buffers chains的解决步骤是什么，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。

latch：cache buffers chains解决步骤

问题产生原因：
 某天查看v$session_wait时发现有很多cache buffer chains，但是情况紧急，所以就只是杀了几个执行时间较长的sql，然后就发现等待事件渐渐消失了。
 

找到为何引起此等待事件：

1. 先找到出问题时段的ash
SQL> create table mao_ash  as select * from dba_hist_active_sess_history where SAMPLE_TIME between TO_TIMESTAMP ('2013-12-27 10:00:00', 'YYYY-MM-DD HH24:MI:SS') and TO_TIMESTAMP ('2013-12-27 12:00:00', 'YYYY-MM-DD HH24:MI:SS');

2. Verify the issue time frame:

select instance_number,sample_id, sample_time, count(*)  from  mao_ash   t
group by instance_number,sample_id, sample_time
order by 3;

INSTANCE_NUMBER SAMPLE_ID SAMPLE_TIME COUNT(*)
2 72736930 2013-12-27 11:14:48.374 1
1 72762620 2013-12-27 11:14:51.059 11 <<<<< Begin--active session突然变为双数，并且持续了一段时间
1 72762630 2013-12-27 11:15:01.161 11

1 72762970 2013-12-27 11:20:44.756 10
1 72762980 2013-12-27 11:20:54.856 11
1 72762990 2013-12-27 11:21:04.956 15
1 72763000 2013-12-27 11:21:15.056 16
......
1 72763940 2013-12-27 11:37:04.830 11
1 72763950 2013-12-27 11:37:14.930 11
1 72763960 2013-12-27 11:37:25.032 11
1 72763970 2013-12-27 11:37:35.142 12
1 72763980 2013-12-27 11:37:45.242 9 <<<< End---acive session变为单数
1 72763990 2013-12-27 11:37:55.342 8
以上可以定位问题出现的时间段。

3. Verify the wait events:

select t.instance_number,
 t.sample_id,
 t.sample_time,
 t.event,
 t.session_state,
 --t.r,
 t.c
 from (select t.*,
 --row_number() over(partition by instance_number, sample_id order by c desc) r
 rank() over(partition by instance_number, sample_id order by c desc) r
 from (select t.*,
 count(*) over(partition by instance_number, sample_id, event) c,
 row_number() over(partition by instance_number, sample_id, event order by 1) r1
 from  mao_ash   t) t
 where r1 = 1) t
 where r < 3
 order by sample_time, r;

INSTANCE_NUMBER SAMPLE_ID SAMPLE_TIME EVENT SESSION_STATE C
2 72736930 2013-12-27 11:14:48.374 ON CPU 1---在这个时间点，有一个sql在on cpu
1 72762620 2013-12-27 11:14:51.059 ON CPU 9---在这个时间点，有九个sql在on cpu
1 72762620 2013-12-27 11:14:51.059 library cache lock WAITING 1---在这个时间点，有一个library cache lock WAITING 等待事件
1 72762620 2013-12-27 11:14:51.059 cursor: pin S wait on X WAITING 1
......
1 72763100 2013-12-27 11:22:56.079 ON CPU 7
1 72763100 2013-12-27 11:22:56.079 library cache lock WAITING 4
......
1 72763290 2013-12-27 11:26:08.193 ON CPU 10
1 72763300 2013-12-27 11:26:18.291 ON CPU 12
2 72737620 2013-12-27 11:26:25.403 ON CPU 1
1 72763310 2013-12-27 11:26:28.391 ON CPU 11
......
1 72763720 2013-12-27 11:33:22.568 ON CPU 17
1 72763730 2013-12-27 11:33:32.689 ON CPU 18
1 72763740 2013-12-27 11:33:42.788 ON CPU 18
.....;.
备注：等待事件是cache buffers chains，但这里是有library cache lock引起的，所以给我们的感觉是cache buffer chains，这里并不能通过p1，p2来定位问题。

4. Find out the holders：
select t.instance_number,
 t.sample_time,
 t.sample_id,
 t.session_id,
 t.sql_id,
 t.session_type,
 t.event,
 t.session_state,
 --t.blocking_session,
 --t.blocking_inst_id,
 --t.blocking_session_status,
 --t.lv,
 --t.r,
 t.c
 from (select t.*,
 row_number() over(partition by instance_number, sample_id order by c desc) r
 --rank() over(partition by instance_number, sample_id order by c desc) r
 from (select t.*,
 count(*) over(partition by instance_number, sample_id, session_id) c,
 row_number() over(partition by instance_number, sample_id, session_id order by 1) r1
 from (select
 level lv, connect_by_isleaf isleaf, t.*
 from  mao_ash   t
 start with blocking_session is not null
 connect by nocycle
 prior blocking_session = session_id
 and prior t.blocking_session_serial# =
 session_serial#
 and ((prior sample_time) - sample_time between
 interval '-3' second and interval '3' second)) t
 where t.isleaf = 1) t
 where r1 = 1) t
 where r < 3
 order by sample_time, r;

INSTANCE_NUMBER SAMPLE_TIME SAMPLE_ID SESSION_ID SQL_ID SESSION_TYPE EVENT SESSION_STATE C
1 2013-12-27 11:09:47.982 72762320 2697 62h7yux977dmw FOREGROUND db file parallel read WAITING 1
1 2013-12-27 11:09:58.082 72762330 2697 62h7yux977dmw FOREGROUND GC cr multi block request WAITING 1
1 2013-12-27 11:10:08.183 72762340 2697 62h7yux977dmw FOREGROUND ON CPU 1
1 2013-12-27 11:10:18.282 72762350 2697 62h7yux977dmw FOREGROUND ON CPU 1
1 2013-12-27 11:10:28.382 72762360 2697 62h7yux977dmw FOREGROUND gc current block 2-way WAITING 1
1 2013-12-27 11:10:38.482 72762370 2697 62h7yux977dmw FOREGROUND ON CPU 1
......sid为2697正在执行62h7yux977dmw的sql，与此同时在11:09:47这个时间点，有一个session正在等待

1 2013-12-27 11:28:39.723 72763440 2720 dts1t1fjha4m2 FOREGROUND gc current block 2-way WAITING 1
1 2013-12-27 11:43:18.608 72764310 2753 BACKGROUND log file parallel write WAITING 1
备注：这个sql很强大，可以找出是罪魁祸首的那个sql，这里就是62h7yux977dmw了，因为它引起的session等待最多。

5. Find out the which SQL cause the most CPU usage:

 select sql_id,count(*)
 from  mao_ash   t
 where sample_time >
 to_timestamp('2013-12-27 11:30:40', 'yyyy-mm-dd hh34:mi:ss')
 and session_state = 'ON CPU'
 group by sql_id order by 2 desc;

SQL_ID COUNT(*)
58xvzzydq83f1 350
4fk8mz3jx2898 63
6zwy49juu8wxa 52
ayvngp9bb3Dum 48
a3v2gkv5r4gj6 47
451xth7g96cx7 35

结果：
1.调整58xvzzydq83f1，让sql尽快执行完毕，而不是一直执行着，消耗着cpu
2.找出62h7yux977dmw的sql_text，再做调整。其实这里62h7yux977dmw可能只执行了一次，可能由于shared_pool比较忙，所以很有可能在v$sql里找不到。

看完上述内容，你们对latch cache buffers chains的解决步骤是什么有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注编程网数据库频道，感谢大家的支持。