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

159次閱讀

共計(jì) 4671 個(gè)字符，預(yù)計(jì)需要花費(fèi) 12 分鐘才能閱讀完成。

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

latch：cache buffers chains 解決步驟

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

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

1. 先找到出問(wèn)題時(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
以上可以定位問(wèn)題出現(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，但這里是有 library cache lock 引起的，所以給我們的感覺(jué)是 cache buffer chains，這里并不能通過(guò) p1，p2 來(lái)定位問(wèn)題。

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)樗鸬?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

結(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)注丸趣 TV 行業(yè)資訊頻道，感謝大家的支持。

正文完