等待事件_cache_buffers_chains_latch
目的:
測試在高併發查詢下產生latch: cache buffers chains的兩種情況.
特別說明:
雖然從9i開始查詢sql以shared模式獲取cache buffers chains latch，但仍然會引起cache buffers chains latch爭用，可以這麼理解：
*查詢sql在從緩衝區中讀取塊時，需要以shared模式獲取/釋放buffer lock，在此過程中修改緩衝區頭資訊時必須臨時以exclusive模式獲取cache buffers chains latch，從而導致cache buffers chains latch爭用；
*查詢sql會導致資料塊移入/移出buffer cache，此類操作需要以exclusive模式獲取cache buffers chains latch，從而導致cache buffers chains latch爭用
1,低效SQL引起的cache buffers chains latch等待
1.1使用索引提示
drop table cbc_test;
create table cbc_test(id number,name char(100));
insert into cbc_test
select object_id,object_name from dba_objects;
commit;
select count(*) from cbc_test;
COUNT(*)
----------
51212
create index idx_cbc_test_id on cbc_test(id);
select object_name,object_id
from dba_objects
where object_name in('CBC_TEST','IDX_CBC_TEST_ID');
OBJECT_NAME OBJECT_ID
----------------- ----------
CBC_TEST 63023
IDX_CBC_TEST_ID 63024
create or replace procedure do_select is
begin
for z in 1..50 loop
for x in(select /*+ index(cbc_test idx_cbc_test_id) */ *
from cbc_test where id>0) loop
null;
end loop;
end loop;
end;
/
declare
i int;
begin
for x in 1..20 loop
dbms_job.submit(i,'do_select;');
end loop;
commit;
end;
/
set serveroutput on
exec session_pkg.get_snap;
exec do_select;
exec session_pkg.rpt_stat_event(0);
----v$session_wait_history---
# Event p1 p2 p3 wcnt wtim
1 SQL*Net message from 1413697536 1 0 1 0
2 SQL*Net message to c 1413697536 1 0 1 0
3 SQL*Net message from 1413697536 1 0 1 1
4 SQL*Net message to c 1413697536 1 0 1 0
5 SQL*Net message from 1413697536 1 0 1 0
6 SQL*Net message to c 1413697536 1 0 1 0
7 SQL*Net message from 1413697536 1 0 1 0
8 SQL*Net message to c 1413697536 1 0 1 0
9 SQL*Net message from 1413697536 1 0 1 0
10 SQL*Net message to c 1413697536 1 0 1 0
----session stats---
Name Value
buffer is pinned count 5,007,100
table fetch by rowid 2,560,550
session uga memory 1,627,280
session pga memory 1,507,328
buffer is not pinned count 139,500
session logical reads 119,553 ==>logical reads
consistent gets from cache 119,550
consistent gets 119,550
no work - consistent read gets 119,450
recursive calls 25,656
workarea memory allocated 6,332
DB time 5,214 ==>52sec
CPU used by this session 1,841
CPU used when call started 1,841
recursive cpu usage 1,722
bytes received via SQL*Net fro 1,703
redo size 1,148
bytes sent via SQL*Net to clie 1,055
undo change vector size 916
sorts (rows) 363
concurrency wait time 137 ==>等待時間
execute count 59
calls to get snapshot scn: kcm 53
consistent gets - examination 50
index scans kdiixs1 50
shared hash latch upgrades - n 50
user calls 15
opened cursors cumulative 8
parse count (total) 8
SQL*Net roundtrips to/from cli 8
db block changes 5
session cursor cache hits 5
enqueue releases 4
redo entries 3
db block gets from cache 3
db block gets 3
consistent changes 2
commit cleanout failures: bloc 2
commit cleanouts 2
sorts (memory) 1
parse count (hard) 1
heap block compress 1
calls to kcmgas 1
redo synch time 1
redo synch writes 1
enqueue requests 1
messages sent 1
user commits 1
opened cursors current 1
----session events---
Wait_Class Event Total_waits Time_waited
Commit log file sync 1 1
Concurrency latch: cache buffers chains 4 136 ==>出現等待
Idle SQL*Net message from client 8 6
Network SQL*Net message to client 8 0
Other events in waitclass Other 1 24
PL/SQL procedure successfully completed
--查詢可能出現競爭的latch
SQL> SELECT *
2 FROM (SELECT child#, gets, sleeps
3 FROM v$latch_children
4 WHERE NAME = 'cache buffers chains'
5 ORDER BY sleeps DESC)
6 WHERE rownum <= 20;
CHILD# GETS SLEEPS
---------- ---------- ----------
911 125185 21
687 110212 15
990 77475 11
150 39124 10
63 40360 7
528 49605 7
779 84009 7
986 47578 7
529 90768 7
924 93107 6
49 74533 5
573 73598 5
867 88027 5
884 39341 5
717 67423 5
704 91489 5
678 99729 5
278 116910 5
379 84139 5
23 86193 4
20 rows selected
--該latch管理的資料塊:
--如下3個數值相等:
--v$session.p1raw(event='latch: cache buffers chains')
--x$bh.hladdr
--v$latch_children.addr(name='cache buffers chains')
SQL> SELECT obj, COUNT(*), SUM(tch)
2 FROM x$bh
3 WHERE hladdr = ANY (SELECT addr
4 FROM v$latch_children
5 WHERE child# IN (687, 911)
6 AND NAME = 'cache buffers chains')
7 GROUP BY obj
8 ORDER BY 3;
OBJ COUNT(*) SUM(TCH)
---------- ---------- ----------
63016 3 0
63017 3 0
75 1 1
73 1 1
4294967295 4 4
55 1 13
29 2 33
181 3 39
8 2 146
63024 2 242 --idx_cbc_test_id
2 2 274
63023 6 732 --cbc_test
18 3 1373
13 rows selected
--該表/索引上的資料塊訪問次數分佈，是否有hot block：
SQL> SELECT obj, tch, COUNT(*)
2 FROM x$bh
3 WHERE obj IN (63023, 63024)
4 GROUP BY obj, tch
5 ORDER BY 1, 2;
OBJ TCH COUNT(*)
---------- ---------- ----------
63023 2 1
63023 5 1
63023 122 716
63023 124 70 ==>大部分資料塊tch為122-124
63024 5 1
63024 120 107 ==>大部分資料塊tch為120-123
63024 122 6
63024 123 1
8 rows selected
1.2,相同的併發訪問量,不使用索引提示
create or replace procedure do_select is
begin
for z in 1..50 loop
for x in(select *
from cbc_test where id>0) loop
null;
end loop;
end loop;
end;
/
declare
i int;
begin
for x in 1..20 loop
dbms_job.submit(i,'do_select;');
end loop;
commit;
end;
/
set serveroutput on
exec session_pkg.get_snap;
exec do_select;
exec session_pkg.rpt_stat_event(0);
----v$session_wait_history---
# Event p1 p2 p3 wcnt wtim
1 SQL*Net message from 1413697536 1 0 1 0
2 SQL*Net message to c 1413697536 1 0 1 0
3 SQL*Net message from 1413697536 1 0 1 2
4 SQL*Net message to c 1413697536 1 0 1 0
5 SQL*Net message from 1413697536 1 0 1 0
6 SQL*Net message to c 1413697536 1 0 1 0
7 SQL*Net message from 1413697536 1 0 1 3
8 SQL*Net message to c 1413697536 1 0 1 0
9 SQL*Net message from 1413697536 1 0 1 0
10 SQL*Net message to c 1413697536 1 0 1 0
----session stats---
Name Value
table scan rows gotten 4,122,161
session pga memory 1,703,936
session uga memory 1,642,256
session pga memory max 1,441,792
session uga memory max 1,387,876
session logical reads 63,593 ==>logical reads,減少了接近一半
consistent gets 63,585
consistent gets from cache 63,585
no work - consistent read gets 63,325
table scan blocks gotten 63,320
recursive calls 25,666
workarea memory allocated 6,358
bytes received via SQL*Net fro 1,703
DB time 1,575 ==>1.5sec
redo size 1,140
bytes sent via SQL*Net to clie 1,055
undo change vector size 908
CPU used when call started 748
CPU used by this session 748
recursive cpu usage 652
sorts (rows) 363
calls to get snapshot scn: kcm 312
buffer is not pinned count 70
concurrency wait time 68
execute count 61
table scans (short tables) 51
user calls 15
opened cursors cumulative 13
parse count (total) 13
db block changes 9
db block gets from cache 8
db block gets 8
SQL*Net roundtrips to/from cli 8
consistent changes 6
enqueue releases 6
session cursor cache hits 4
opened cursors current 4
redo entries 3
enqueue requests 3
parse count (hard) 2
commit cleanouts 2
commit cleanout failures: bloc 2
sorts (memory) 1
parse time elapsed 1
index scans kdiixs1 1
calls to kcmgas 1
shared hash latch upgrades - n 1
free buffer requested 1
consistent gets - examination 1
messages sent 1
user commits 1
redo synch writes 1
----session events---
Wait_Class Event Total_waits Time_waited
Commit log file sync 1 0
Concurrency latch: cache buffers chains 3 69 ==>減少,但沒有消失
Idle SQL*Net message from client 8 9
Network SQL*Net message to client 8 0
PL/SQL procedure successfully completed
2.hot block引起的cache buffers chains latch等待
drop table cbc_test;
create table cbc_test(id number,name char(100));
insert into cbc_test
select object_id,object_name from dba_objects;
commit;
select count(*) from cbc_test;
COUNT(*)
----------
51212
create index idx_cbc_test_id on cbc_test(id);
select object_name,object_id
from dba_objects
where object_name in('CBC_TEST','IDX_CBC_TEST_ID');
OBJECT_NAME OBJECT_ID
----------------- ----------
CBC_TEST 63025
IDX_CBC_TEST_ID 63026
create or replace procedure do_select(p_from in number,p_to in number) is
begin
--集中掃描特定塊
for z in 1..50000 loop
for x in(select id
from cbc_test where id between p_from and p_to) loop
null;
end loop;
end loop;
end;
/
declare
i int;
begin
for x in 1..20 loop
dbms_job.submit(i,'do_select(1000,1010);');
end loop;
commit;
end;
/
set serveroutput on
exec session_pkg.get_snap;
exec do_select(1000,1010);
exec session_pkg.rpt_stat_event(0);
----v$session_wait_history---
# Event p1 p2 p3 wcnt wtim
1 SQL*Net message to c 1413697536 1 0 1 0
2 SQL*Net message from 1413697536 1 0 1 3
3 SQL*Net message to c 1413697536 1 0 1 0
4 SQL*Net message from 1413697536 1 0 1 19
5 latch: library cache 1708708748 216 0 1 0
6 SQL*Net message from 1413697536 1 0 1 0
7 SQL*Net message to c 1413697536 1 0 1 0
8 SQL*Net message from 1413697536 1 0 1 1
9 SQL*Net message to c 1413697536 1 0 1 0
10 SQL*Net message from 1413697536 1 0 1 0
----session stats---
Name Value
session pga memory 1,638,400
session uga memory 1,576,792
session logical reads 150,083
consistent gets from cache 150,080
consistent gets 150,080
no work - consistent read gets 50,071
recursive calls 50,019
calls to get snapshot scn: kcm 50,012
execute count 50,011
consistent gets - examination 50,001
index scans kdiixs1 50,001
shared hash latch upgrades - n 49,581
physical read total bytes 16,384
physical read bytes 16,384
workarea memory allocated 6,318
table scan rows gotten 4,561
bytes received via SQL*Net fro 1,714
redo size 1,148
bytes sent via SQL*Net to clie 1,055
undo change vector size 916
DB time 898
CPU used when call started 661
CPU used by this session 661
recursive cpu usage 546
shared hash latch upgrades - w 420
sorts (rows) 363
concurrency wait time 96
buffer is not pinned count 70
table scan blocks gotten 70
user calls 15
enqueue releases 10
opened cursors cumulative 10
parse count (total) 10
SQL*Net roundtrips to/from cli 8
enqueue requests 7
parse count (hard) 6
db block changes 5
session cursor cache hits 4
db block gets 3
db block gets from cache 3
redo entries 3
physical read total IO request 2
physical reads 2
physical reads cache 2
physical read IO requests 2
consistent changes 2
commit cleanouts 2
commit cleanout failures: bloc 2
free buffer requested 2
sorts (memory) 1
table scans (short tables) 1
calls to kcmgas 1
redo synch writes 1
messages sent 1
user commits 1
opened cursors current 1
----session events---
Wait_Class Event Total_waits Time_waited
Commit log file sync 1 1
Concurrency latch: library cache pin 1 0
Concurrency latch: library cache 4 12
Concurrency latch: cache buffers chains 16 83 ==>hot block導致latch: cache buffers chains
Idle SQL*Net message from client 8 26
Network SQL*Net message to client 8 0
User I/O db file sequential read 2 0
PL/SQL procedure successfully completed
SQL>
SQL> SELECT obj, tch,state, COUNT(*)
2 FROM x$bh
3 WHERE obj IN (63025, 63026)
4 GROUP BY obj, tch,state
5 ORDER BY 1, 2;
OBJ TCH STATE COUNT(*)
---------- ---------- ---------- ----------
63025 2 1 717
63025 3 1 70
63025 4 1 1
63026 2 1 1
63026 13 1 2 ==>索引上hot block,並不是期望的上萬次
63026 14 1 1
6 rows selected
SQL> SELECT hladdr, obj, dbarfil, dbablk, state, CLASS, tch
2 FROM x$bh
3 WHERE obj IN (63025, 63026)
4 AND tch > 10;
HLADDR OBJ DBARFIL DBABLK STATE CLASS TCH
-------- ---------- ---------- ---------- ---------- ---------- ----------
678D5908 63026 1 64850 1 1 14
67955E40 63026 1 64852 1 1 13
67AA58DC 63026 1 64853 1 1 13
SQL> SELECT *
2 FROM (SELECT child#, gets, sleeps
3 FROM v$latch_children
4 WHERE NAME = 'cache buffers chains'
5 ORDER BY sleeps DESC)
6 WHERE rownum <= 20;
ADDR CHILD# GETS SLEEPS
-------- ---------- ---------- ----------
67955E40 278 6502739 634 -->block:64852
67AA58DC 911 6440917 192 -->block:64853
67A2EC5C 687 125943 15
67981E74 361 61453 12
67ACF720 990 95402 11
67912040 150 50612 10
678E3E1C 63 52108 7
678F1AB4 89 100036 7
679DAFD4 529 102266 7
679DA758 528 63214 7
67A5F8EC 779 97658 7
67ACD530 986 61168 7
67AAC728 924 104640 6
678DC754 49 84159 5
6798B72C 379 106365 5
67A2A000 678 119672 5
67A37C98 704 103010 5
67A973C8 884 52930 5
67A995B8 888 34773 5
67A8E38C 867 101686 5
20 rows selected
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/18922393/viewspace-712720/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【等待事件】ORACLE常見等待事件事件Oracle
- 【等待事件】等待事件系列（5.1）--Enqueue(佇列等待)事件ENQ佇列
- 等待事件事件
- Solidity事件，等待事件Solid事件
- 【TUNE_ORACLE】等待事件之等待事件類別Oracle事件
- 等待事件分析事件
- oracle等待事件Oracle事件
- Oracle 等待事件Oracle事件
- px等待事件事件
- 等待事件 二事件
- 【等待事件】等待事件系列（1）--User I/O型別事件型別
- 【效能調整】等待事件(三) 常見等待事件（一）事件
- 【效能調整】等待事件(四) 常見等待事件（二）事件
- Oracle Mutex 等待事件OracleMutex事件
- 等待事件指令碼事件指令碼
- oracle等待事件一Oracle事件
- ASH, AWR , 等待事件事件
- latch free等待事件事件
- 【Oracle概念】-等待事件Oracle事件
- 頂級等待事件：事件
- Oracle 等待事件 一Oracle事件
- Oracle的等待事件Oracle事件
- 常見等待事件事件
- oracle等待事件3構造一個Direct Path write等待事件和構造一個Log File Sync等待事件Oracle事件
- 【等待事件之二】log 相關的等待事件
- Latch free等待事件(轉)事件
- gc cr request等待事件GC事件
- latch等待事件彙總事件
- log file sync等待事件事件
- 【等待事件】log file sync事件
- ORACLE 常見等待事件Oracle事件
- cursor: mutex S等待事件Mutex事件
- latch 相關等待事件事件
- 【等待事件】Buffer Exterminate事件
- oracle常見等待事件Oracle事件
- Latch free等待事件三事件
- Latch free等待事件四事件
- Latch free等待事件二事件