1,概况
获取cache buffers lru chain latch的情况如下:
*需要装载块到内存的进程,需要查询lru获取空闲区时
*DBWR进程在将lruw上的脏块写入文件后,需要将相应缓冲区放入lru时,包括如下情况:
----需要装载块到内存的进程,在查询lru列时,查询超过特定块数还没有获取时,向DBWR请求写dirty block
----parallel query时,或truncate,drop,tablespace backup时,向DBWR请求写相关对象的dirty block
----检查点导致的脏块写入,包括:为保障FAST_START_MTTR_TARGET/LOG_CHECKPOINT_TIMEOUT周期性执行的检查点,日志切换,管理员检查点命令
通过以上分析,cache buffers lru chain latch争用主要由于过多空闲缓冲区请求导致,如下情况可能导致该latch竞争:
*低效SQL
*过小的buffer cache
*过多的检查点
cache buffers chains latch与cache buffers lru chain latch区别:
*前者在多个会话同时访问同一个表或索引,甚至集中在某个热块时发生,后者发生在多个会话分别访问不同表导致空闲缓冲区争用而引发
*后者一般伴随物理IO(索引扫描时db file sequential read,全表扫描时db file scattered read)
*在典型系统上,由于混合负载,二者同时发生的概率较高
1,测试环境
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> select * from v$sgainfo;
NAME BYTES RESIZEABLE
-------------------------------- ---------- ----------
Fixed SGA Size 1219088 No
Redo Buffers 15556608 No
Buffer Cache Size 973078528 Yes
Shared Pool Size 184549376 Yes
Large Pool Size 16777216 Yes
Java Pool Size 16777216 Yes
Streams Pool Size 0 Yes
Granule Size 16777216 No
Maximum SGA Size 1207959552 No
Startup overhead in Shared Pool 50331648 No
Free SGA Memory Available 0
11 rows selected
SQL> SELECT child#, gets, sleeps, immediate_gets, immediate_misses
2 FROM v$latch_children
3 WHERE NAME LIKE 'cache buffers lru chain';
CHILD# GETS SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ---------- ---------- -------------- ----------------
1 28 0 0 0
2 28 0 0 0
3 28 0 0 0
4 28 0 0 0
5 12641 0 33939 11
6 13474 0 34231 9
7 28 0 0 0
8 28 0 0 0
9 28 0 0 0
10 28 0 0 0
11 28 0 0 0
12 28 0 0 0
13 28 0 0 0
14 28 0 0 0
15 28 0 0 0
16 28 0 0 0
说明:总共有16个'cache buffers lru chain'latch,分别用于不同的buffer pool;缺省buffer pool使用2个。
2,测试用表(每个会话访问不同表,被访问表空间超过buffer cache)
2.1所有会话只做查询(全表扫描),没有出现cache buffers lru chain latch事件
create table cbl_test1 nologging
as
select rownum id,rpad('x',100,'x') txt from dual
connect by level<640000;
select bytes from user_segments where segment_name like 'CBL_TEST%';
BYTES
----------
83886080
declare
sqltext varchar2(100);
begin
for x in 2..20 loop
sqltext := 'create table cbl_test'||x||' as select * from cbl_test1';
execute immediate sqltext;
end loop;
end;
/
create or replace procedure do_select(p_idx in number) is
l_cursor sys_refcursor;
l_value number;
begin
for x in 1..100 loop
--全表扫描
open l_cursor for 'select min(id) from cbl_test'||p_idx||
' where id=:id' using x;
fetch l_cursor into l_value;
close l_cursor;
end loop;
end;
/
declare
l_job number;
begin
for x in 1..19 loop
dbms_job.submit(l_job,'do_select('||x||');');
end loop;
--commit;
end;
/
commit;
set serveroutput on
exec session_pkg.get_snap;
exec do_select(20);
exec session_pkg.rpt_stat_event(0);
==>
Session id:142 Serial#:271 sampid:7630036
----session stats---
Name Value
physical read total bytes 80,904,192
physical read bytes 80,904,192
table scan rows gotten 63,999,900
session pga memory 1,703,936
session uga memory 1,642,256
session logical reads 985,803
consistent gets from cache 985,800
consistent gets 985,800
no work - consistent read gets 984,700
table scan blocks gotten 984,700
free buffer inspected 14,510
hot buffers moved to head of L 10,244
physical reads 9,876
free buffer requested 9,876
physical reads cache 9,876
physical reads cache prefetch 9,223
workarea memory allocated 6,364
bytes received via SQL*Net fro 1,707
DB time 1,578
calls to get snapshot scn: kcm 1,202
redo size 1,132
CPU used when call started 1,128
recursive cpu usage 1,121
CPU used by this session 1,121
bytes sent via SQL*Net to clie 1,055
undo change vector size 916
physical read IO requests 653
physical read total IO request 653
physical read total multi bloc 624
recursive calls 404
sorts (rows) 363
concurrency wait time 162
execute count 108
parse count (total) 107
opened cursors cumulative 107
session cursor cache hits 103
table scans (short tables) 99
user I/O wait time 19
user calls 15
SQL*Net roundtrips to/from cli 8
db block changes 5
enqueue releases 4
db block gets from cache 3
redo entries 3
db block gets 3
commit cleanouts 2
commit cleanout failures: bloc 2
consistent changes 2
sorts (memory) 1
parse count (hard) 1
parse time elapsed 1
heap block compress 1
table scans (long tables) 1
calls to kcmgas 1
redo synch writes 1
enqueue requests 1
messages sent 1
user commits 1
----session events---
Wait_Class Event Total_waits Time_waited
Commit log file sync 1 1
Concurrency latch: cache buffers chains 19 162
Idle SQL*Net message from client 8 5
Network SQL*Net message to client 8 0
User I/O db file sequential read 34 0
User I/O db file scattered read 624 19
----v$active_session_history---
Wait_class Event count(*) Event Event
Concurrency latch: cache buffers chains 2 7630044 7630045
PL/SQL procedure successfully completed
2.2一个会话修改,其他会话只做查询(全表扫描),出现cache buffers lru chain latch事件
说明:测试表明,一般在出现latch: cache buffers lru chain竞争以前先出现latch: cache buffers chains等待,在特定会话捕获该事件较难,下面比较测试前后系统级统计/事件差异.
create or replace procedure do_update(p_idx in number) is
sqltext varchar2(100);
begin
sqltext := 'update cbl_test'||p_idx||' set id=id';
execute immediate sqltext;
execute immediate 'commit';
end;
/
create or replace procedure do_select(p_idx in number) is
l_cursor sys_refcursor;
l_value number;
begin
for x in 1..300 loop
--全表扫描
open l_cursor for 'select min(id) from cbl_test'||p_idx||
' where id=:id' using x;
fetch l_cursor into l_value;
close l_cursor;
end loop;
end;
/
declare
l_job number;
begin
for x in 1..1 loop
dbms_job.submit(l_job,'do_update('||x||');');
end loop;
--commit;
for x in 2..19 loop
dbms_job.submit(l_job,'do_select('||x||');');
end loop;
--commit;
end;
/
commit;
set serveroutput on
exec system_pkg.get_snap;
exec do_select(20);
--注意:可能部分提交任务还没有完成
exec system_pkg.rpt_stat_event(0);
==>
Last sampid:7637575
----system stats---
Name Value
physical read total bytes ############
physical read bytes ############
table scan rows gotten ############
process last non-idle time ############
session connect time ############
physical write total bytes 230,109,184
redo size 105,822,360
physical write bytes 47,169,536
undo change vector size 44,145,652
session logical reads 22,729,752
table scan blocks gotten 22,566,404
no work - consistent read gets 22,435,637
consistent gets from cache 22,330,528
consistent gets 22,330,475
session pga memory 11,706,000
session pga memory max 10,395,280
session uga memory 5,283,600
session uga memory max 4,434,400
hot buffers moved to head of L 842,052
db block changes 768,802
free buffer inspected 599,294
db block gets from cache 401,680
db block gets 401,675
redo entries 384,089
buffer is pinned count 374,711
free buffer requested 337,149
physical reads 324,575 -->大量物理io
physical reads cache 324,575
physical reads cache prefetch 272,419
redo blocks written 206,028
bytes sent via SQL*Net to clie 98,227
user I/O wait time 90,819
consistent gets - examination 63,918
data blocks consistent reads - 63,096
consistent changes 61,930
physical read total IO request 52,774
physical read IO requests 52,282
bytes received via SQL*Net fro 51,733
redo wastage 36,992
CPU used by this session 35,290
calls to get snapshot scn: kcm 30,524
recursive calls 22,278
physical read total multi bloc 20,476
concurrency wait time 16,114
dirty buffers inspected 14,863
summed dirty queue length 13,328
calls to kcmgas 12,477
DB time 9,827
redo write time 6,126
workarea memory allocated 5,960
physical writes 5,758
physical writes from cache 5,758
redo ordering marks 5,510
physical write IO requests 5,415
enqueue requests 4,928
enqueue releases 4,902
change write time 4,363
recursive cpu usage 4,341
CPU used when call started 4,333
deferred (CURRENT) block clean 3,938
switch current to new buffer 3,888
execute count 3,875
parse count (total) 3,809
opened cursors cumulative 3,765
sorts (rows) 3,725
table scans (short tables) 2,898
redo log space wait time 2,667
write clones created in foregr 2,029
physical write total IO reques 1,833
physical writes non checkpoint 1,652
session cursor cache hits 1,624
shared hash latch upgrades - n 1,495
DBWR undo block writes 1,493
index scans kdiixs1 1,484
buffer is not pinned count 1,416
sorts (memory) 1,348
pinned buffers inspected 1,187
immediate (CR) block cleanout 972
cleanout - number of ktugct ca 972
active txn count during cleano 971
cleanouts and rollbacks - cons 970
CR blocks created 953
workarea executions - optimal 860
table fetch by rowid 628
messages sent 598
messages received 595
enqueue conversions 532
calls to kcmgcs 496
physical write total multi blo 453
user calls 435
session cursor cache count 308
SQL*Net roundtrips to/from cli 287
background timeouts 234
parse time elapsed 195
redo writer latching time 182
opened cursors current 122
index fetch by key 104
redo writes 86
rows fetched via callback 64
redo synch writes 43
commit cleanouts 38
redo log space requests 36
commit cleanouts successfully 36
user commits 23
logons cumulative 20
logons current 20
table scans (long tables) 18
parse time cpu 18
enqueue waits 14
prefetched blocks aged out bef 14
space was found by tune down 13
steps of tune down ret. in spa 13
tune down retentions in space 13
drop segment calls in space pr 13
local undo segment hints helpe 13
auto extends on undo tablespac 11
redo buffer allocation retries 8
parse count (hard) 4
commit cleanout failures: bloc 2
background checkpoints started 2
cursor authentications 2
heap block compress 2
enqueue timeouts 2
DBWR checkpoints 2
commit txn count during cleano 1
immediate (CURRENT) block clea 1
cleanouts only - consistent re 1
write clones created in backgr 1
----system events---
Wait_Class Event Total_waits Time_waited
Application SQL*Net break/reset to client 4 0
Commit log file sync 1 0
Concurrency latch: library cache 1 7
Concurrency os thread startup 20 201
Concurrency buffer busy waits 109 770
Concurrency latch: cache buffers chains 1,419 15,544 *
Configuration log file switch completion 44 3,059
Configuration undo segment extension 131 7
Idle dispatcher timer 1 6,067
Idle virtual circuit status 3 9,035
Idle Streams AQ: qmn slave idle wai 3 9,695
Idle jobq slave wait 8 2,244
Idle Streams AQ: qmn coordinator id 12 9,695
Idle pmon timer 36 9,231
Idle SQL*Net message from client 288 150,726
Idle rdbms ipc message 777 62,492
Network SQL*Net more data from client 3 0
Network SQL*Net more data to client 22 0
Network SQL*Net message to client 287 0
Other latch: redo allocation 1 34
Other job scheduler coordinator slav 2 3,129
Other latch: checkpoint queue latch 3 11
Other enq: US - contention 8 122
Other enq: CF - contention 13 2,220
Other latch: cache buffers lru chain 35 78 *
Other LGWR wait for redo copy 93 175
Other rdbms ipc reply 240 2,565
Other latch free 497 6,943
System I/O log file single write 4 64
System I/O Log archive I/O 73 2,264
System I/O log file sequential read 74 248
System I/O log file parallel write 87 6,113
System I/O control file parallel write 95 7,036
System I/O control file sequential read 411 116
User I/O db file scattered read 20,970 81,183
User I/O db file sequential read 32,420 11,724
----v$active_session_history---
Wait_class Event count(*) min_samp max_samp
Concurrency buffer busy waits 8 7637639 7637673
Concurrency latch: cache buffers chains 146 7637608 7637696
Concurrency os thread startup 2 7637606 7637607
Configuration log file switch completion 33 7637609 7637697
Other LGWR wait for redo copy 1 7637644 7637644
Other enq: CF - contention 24 7637621 7637697
Other enq: US - contention 1 7637636 7637636
Other job scheduler coordinator slav47 7637649 7637697
Other latch free 75 7637630 7637681
Other null event 4 7637606 7637607
Other rdbms ipc reply 25 7637620 7637679
System I/O Log archive I/O 21 7637673 7637696
System I/O control file parallel write 73 7637608 7637697
System I/O control file sequential read 1 7637652 7637652
System I/O log file parallel write 63 7637607 7637696
System I/O log file sequential read 4 7637679 7637697
User I/O db file scattered read 812 7637606 7637697
User I/O db file sequential read 118 7637607 7637697
PL/SQL procedure successfully completed
2.3与2.2相同负载,创建索引后进行对比测试:
declare
sqltext varchar2(100);
begin
for x in 1..20 loop
sqltext := 'create index idx_cbl_test'||x||'_id on cbl_test'||x||'(id)';
execute immediate sqltext;
end loop;
end;
/
SQL> select segment_name,bytes from user_segments where segment_name like 'IDX_CBL_TEST%';
SEGMENT_NAME BYTES
------------------ ----------
IDX_CBL_TEST4_ID 12582912
IDX_CBL_TEST5_ID 12582912
IDX_CBL_TEST6_ID 12582912
IDX_CBL_TEST7_ID 12582912
IDX_CBL_TEST8_ID 12582912
IDX_CBL_TEST9_ID 12582912
IDX_CBL_TEST10_ID 12582912
IDX_CBL_TEST11_ID 12582912
IDX_CBL_TEST12_ID 12582912
IDX_CBL_TEST13_ID 12582912
IDX_CBL_TEST1_ID 12582912
IDX_CBL_TEST14_ID 12582912
IDX_CBL_TEST15_ID 12582912
IDX_CBL_TEST16_ID 12582912
IDX_CBL_TEST17_ID 12582912
IDX_CBL_TEST18_ID 12582912
IDX_CBL_TEST19_ID 12582912
IDX_CBL_TEST20_ID 12582912
IDX_CBL_TEST2_ID 12582912
IDX_CBL_TEST3_ID 12582912
20 rows selected
==>所有索引数据可以缓存在buffer cache,引起空闲块请求竞争的可能性很小
create or replace procedure do_update(p_idx in number) is
sqltext varchar2(100);
begin
sqltext := 'update cbl_test'||p_idx||' set id=id';
execute immediate sqltext;
execute immediate 'commit';
end;
/
create or replace procedure do_select(p_idx in number) is
l_cursor sys_refcursor;
l_value number;
begin
for x in 1..300 loop
--全表扫描
open l_cursor for 'select min(id) from cbl_test'||p_idx||
' where id=:id' using x;
fetch l_cursor into l_value;
close l_cursor;
end loop;
end;
/
declare
l_job number;
begin
for x in 1..1 loop
dbms_job.submit(l_job,'do_update('||x||');');
end loop;
--commit;
for x in 2..19 loop
dbms_job.submit(l_job,'do_select('||x||');');
end loop;
--commit;
end;
/
commit;
set serveroutput on
exec system_pkg.get_snap;
exec do_select(20);
--注意:可能部分提交任务还没有完成
exec system_pkg.rpt_stat_event(0);
==>
Last sampid:7639315
----system stats---
Name Value
physical read total bytes 3,457,024
physical read bytes 3,457,024
session uga memory 1,380,400
session pga memory 1,376,256
physical write total bytes 103,936
workarea memory allocated 6,290
table scan rows gotten 3,965
bytes received via SQL*Net fro 1,706
recursive calls 1,217
redo size 1,176
bytes sent via SQL*Net to clie 1,055
session logical reads 980
consistent gets 977
consistent gets from cache 977
undo change vector size 916
consistent gets - examination 602
redo wastage 428
free buffer requested 422
physical reads cache 422
physical reads 422
physical reads cache prefetch 369
sorts (rows) 363
calls to get snapshot scn: kcm 317
execute count 310
opened cursors cumulative 309
parse count (total) 309
shared hash latch upgrades - n 302
index scans kdiixs1 301
session cursor cache hits 301
redo blocks written 203
buffer is not pinned count 61
DB time 61
no work - consistent read gets 61
table scan blocks gotten 61
physical read IO requests 53
physical read total IO request 53
user I/O wait time 35
physical read total multi bloc 25
CPU used when call started 19
user calls 15
enqueue releases 9
SQL*Net roundtrips to/from cli 8
enqueue requests 6
parse count (hard) 5
recursive cpu usage 5
CPU used by this session 5
db block changes 5
db block gets from cache 3
redo entries 3
db block gets 3
consistent changes 2
commit cleanouts 2
commit cleanout failures: bloc 2
parse time cpu 2
sorts (memory) 1
user commits 1
cursor authentications 1
messages sent 1
table scans (short tables) 1
messages received 1
background timeouts 1
physical write total multi blo 1
calls to kcmgas 1
redo synch writes 1
physical write total IO reques 1
redo writes 1
----system events---
Wait_Class Event Total_waits Time_waited
Commit log file sync 2 0
Idle jobq slave wait 1 293
Idle rdbms ipc message 2 100
Idle SQL*Net message from client 8 3
Network SQL*Net message to client 8 0
System I/O log file parallel write 1 1
User I/O db file scattered read 25 23
User I/O db file sequential read 30 12
----v$active_session_history---
Wait_class Event count(*) min_samp max_samp
User I/O db file scattered read 1 7639316 7639316
PL/SQL procedure successfully completed
2.4缩小buffer cache,与2.3同样测试案例:
从测试结果可以看到,buffer_cache为108MByte,比所有索引存储空间小。
实际上,通过索引访问时,并不需要在内存中加载所有索引块,虽然buffer cache较小,仍能满足查询需求,并没有出现buffer cache相关等待事件。
SQL> alter system set sga_target=150M;
alter system set sga_target=150M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00827: could not shrink sga_target to specified value
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
sga_target big integer 176M
SQL> select * from v$sgainfo;
NAME BYTES RESIZEABL
-------------------------------- ---------- ---------
Fixed SGA Size 1218388 No
Redo Buffers 7168000 No
Buffer Cache Size 109051904 Yes
Shared Pool Size 58720256 Yes
Large Pool Size 4194304 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 184549376 No
Startup overhead in Shared Pool 37748736 No
Free SGA Memory Available 0
11 rows selected.
-----------
create or replace procedure do_update(p_idx in number) is
sqltext varchar2(100);
begin
sqltext := 'update cbl_test'||p_idx||' set id=id';
execute immediate sqltext;
execute immediate 'commit';
end;
/
create or replace procedure do_select(p_idx in number) is
l_cursor sys_refcursor;
l_value number;
begin
for x in 1..300 loop
--全表扫描
open l_cursor for 'select min(id) from cbl_test'||p_idx||
' where id=:id' using x;
fetch l_cursor into l_value;
close l_cursor;
end loop;
end;
/
declare
l_job number;
begin
for x in 1..1 loop
dbms_job.submit(l_job,'do_update('||x||');');
end loop;
--commit;
for x in 2..19 loop
dbms_job.submit(l_job,'do_select('||x||');');
end loop;
--commit;
end;
/
commit;
set serveroutput on
exec system_pkg.get_snap;
exec do_select(20);
--注意:可能部分提交任务还没有完成
exec system_pkg.rpt_stat_event(0);
Last sampid:7867648
----system stats---
Name Value
physical read total bytes 3,899,392
physical read bytes 3,899,392
session pga memory 1,245,184
session uga memory 1,184,008
session pga memory max 983,040
session uga memory max 740,712
table scan rows gotten 8,656
workarea memory allocated 5,993
physical write total bytes 2,048
bytes received via SQL*Net fro 1,706
recursive calls 1,343
redo size 1,140
session logical reads 1,109
consistent gets from cache 1,101
consistent gets 1,101
sorts (rows) 1,089
bytes sent via SQL*Net to clie 1,055
undo change vector size 908
consistent gets - examination 611
free buffer inspected 515
free buffer requested 477
physical reads cache 476
physical reads 476
physical reads cache prefetch 405
calls to get snapshot scn: kcm 351
hot buffers moved to head of L 341
parse count (total) 326
execute count 325
opened cursors cumulative 320
session cursor cache hits 312
shared hash latch upgrades - n 311
index scans kdiixs1 309
redo wastage 288
buffer is not pinned count 166
no work - consistent read gets 147
DB time 131
table scan blocks gotten 124
parse time elapsed 76
physical read IO requests 71
physical read total IO request 71
user I/O wait time 65
CPU used when call started 31
physical read total multi bloc 27
table fetch by rowid 20
enqueue releases 16
user calls 15
enqueue requests 13
parse count (hard) 12
dirty buffers inspected 10
CPU used by this session 9
db block changes 9
db block gets from cache 8
db block gets 8
SQL*Net roundtrips to/from cli 8
consistent changes 7
recursive cpu usage 7
cursor authentications 5
redo blocks written 4
parse time cpu 4
redo entries 3
table scans (short tables) 3
sorts (memory) 3
commit cleanouts 2
opened cursors current 2
commit cleanout failures: bloc 2
immediate (CR) block cleanout 1
cleanouts only - consistent re 1
cleanout - number of ktugct ca 1
redo write time 1
table scans (long tables) 1
redo writes 1
calls to kcmgas 1
redo synch time 1
redo synch writes 1
physical write total multi blo 1
physical write total IO reques 1
background timeouts 1
messages received 1
messages sent 1
user commits 1
commit txn count during cleano 1
----system events---
Wait_Class Event Total_waits Time_waited
Commit log file sync 1 0
Idle pmon timer 1 293
Idle rdbms ipc message 2 146
Idle SQL*Net message from client 8 8
Network SQL*Net message to client 8 0
System I/O log file parallel write 1 0
User I/O db file scattered read 27 41
User I/O db file sequential read 46 25
----v$active_session_history---
Wait_class Event count(*) min_samp max_samp
User I/O db file sequential read 1 7867649 7867649
PL/SQL procedure successfully completed
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-712721/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18922393/viewspace-712721/