latch cache buffers chains的解決步驟是什么

今天就跟大家聊聊有關(guān)latch cache buffers chains的解決步驟是什么，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

latch：cache buffers chains解決步驟

問題產(chǎn)生原因：
 某天查看v$session_wait時(shí)發(fā)現(xiàn)有很多cache buffer chains，但是情況緊急，所以就只是殺了幾個(gè)執(zhí)行時(shí)間較長(zhǎng)的sql，然后就發(fā)現(xiàn)等待事件漸漸消失了。
 

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

1. 先找到出問題時(shí)段的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 /*+ parallel 8 */ 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突然變?yōu)殡p數(shù)，并且持續(xù)了一段時(shí)間
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變?yōu)閱螖?shù)
1 72763990 2013-12-27 11:37:55.342 8
以上可以定位問題出現(xiàn)的時(shí)間段。

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 /*+ parallel 8 */ 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---在這個(gè)時(shí)間點(diǎn)，有一個(gè)sql在on cpu
1 72762620 2013-12-27 11:14:51.059 ON CPU 9---在這個(gè)時(shí)間點(diǎn)，有九個(gè)sql在on cpu
1 72762620 2013-12-27 11:14:51.059 library cache lock WAITING 1---在這個(gè)時(shí)間點(diǎn)，有一個(gè)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，但這里是有l(wèi)ibrary 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 /*+ parallel 8 */
 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正在執(zhí)行62h7yux977dmw的sql，與此同時(shí)在11:09:47這個(gè)時(shí)間點(diǎn)，有一個(gè)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
備注：這個(gè)sql很強(qiáng)大，可以找出是罪魁禍?zhǔn)椎哪莻€(gè)sql，這里就是62h7yux977dmw了，因?yàn)樗鸬膕ession等待最多。

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

結(jié)果：
1.調(diào)整58xvzzydq83f1，讓sql盡快執(zhí)行完畢，而不是一直執(zhí)行著，消耗著cpu
2.找出62h7yux977dmw的sql_text，再做調(diào)整。其實(shí)這里62h7yux977dmw可能只執(zhí)行了一次，可能由于shared_pool比較忙，所以很有可能在v$sql里找不到。

看完上述內(nèi)容，你們對(duì)latch cache buffers chains的解決步驟是什么有進(jìn)一步的了解嗎？如果還想了解更多知識(shí)或者相關(guān)內(nèi)容，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。