模拟Latch:cache buffers chains的两种情况。
Oracle Latch:cache buffers chains的出现一般情况下是由于低效的SQL(并发的、大范围的的索引扫描和全表扫描)和Hot Block引起。下面就模拟这两种情况。
并发的全表扫描
1. 首先建立测试Table,数据量在25W左右.
sajet@FDK> drop table cbc_full_scan_table;
表已删除。
sajet@FDK> create table cbc_full_scan_table (no int,object_name varchar2(50));
表已创建。
sajet@FDK> declare i int;
2 begin
3 for i in 1..5 loop
4 insert into cbc_full_scan_table select rownum as no,object_name from dba_objects;
5 end loop;
6 end;
7 /
PL/SQL 过程已成功完成。
sajet@FDK> commit;
提交完成。
sajet@FDK> select count(*) from cbc_full_scan_table;
COUNT(*)
----------
253881
2. 模拟并发全表扫描cbc_full_scan_table。
sajet@FDK> create or replace procedure cbc_full_scan_test is
2 i int;
3 icount int;
4 begin
5 for i in 1..1000 loop
6 select count(*) into icount from cbc_full_scan_table;
7 end loop;
8 end;
9 /
过程已创建。
3.模拟20个session执行以上的procedure;
sajet@FDK> var job_no number;
sajet@FDK> begin
2 for idx in 1..20 loop
3 dbms_job.submit(:job_no,'cbc_full_scan_test;');
4 commit;
5 end loop ;
6 end;
7 /
PL/SQL 过程已成功完成。
4.查看争用情况
sys@FDK> select sid,event,p1text,p1raw from v$session_wait where event='latch: cache buffers chains';
SID EVENT P1TEXT P1RAW
---------- ---------------------------------------- ---------- --------
129 latch: cache buffers chains address 6C1E8A80
130 latch: cache buffers chains address 6C1BBA80
133 latch: cache buffers chains address 6C1E8A80
134 latch: cache buffers chains address 6C18C380
137 latch: cache buffers chains address 6C1D9480
139 latch: cache buffers chains address 6C1E8A80
140 latch: cache buffers chains address 6C1CDC00
141 latch: cache buffers chains address 6C1E8A80
142 latch: cache buffers chains address 6C1B1B80
144 latch: cache buffers chains address 6C1D3F00
sys@FDK> col name for a30;
sys@FDK> select ADDR,LATCH#,CHILD#,NAME,GETS,MISSES,SLEEPS from
2 (select * from v$latch_children
3 where name ='cache buffers chains'
4 order by sleeps desc)
5 where rownum<=20;
ADDR LATCH# CHILD# NAME GETS MISSES SLEEPS
-------- ---------- ---------- -------------------- ---------- ---------- ----------
6C197000 122 142 cache buffers chains 324019 31005 28
6C1C8C80 122 673 cache buffers chains 243026 23509 28
6C1A0780 122 243 cache buffers chains 323927 26931 27
6C1AD980 122 383 cache buffers chains 326615 27496 27
6C1C0A00 122 586 cache buffers chains 242895 16893 27
6C1A6300 122 304 cache buffers chains 650548 58148 27
6C1C6B80 122 651 cache buffers chains 242908 23467 27
6C1B5D80 122 471 cache buffers chains 242838 23343 26
6C196880 122 137 cache buffers chains 242793 19774 25
6C1AA500 122 348 cache buffers chains 323657 26331 25
6C1ABA00 122 362 cache buffers chains 242891 16885 25
6C1B1580 122 423 cache buffers chains 243322 24847 25
6C1E5300 122 976 cache buffers chains 163967 15485 25
6C1A9900 122 340 cache buffers chains 243391 23500 24
6C1CEF80 122 739 cache buffers chains 323591 27873 24
6C1E2D80 122 951 cache buffers chains 162042 16985 24
6C1D5880 122 809 cache buffers chains 161924 16069 24
6C198380 122 155 cache buffers chains 404665 34464 23
6C1D6D80 122 823 cache buffers chains 404445 33667 23
6C1AB280 122 357 cache buffers chains 243117 22308 23
已选择20行。
根据以上的查询,基本可以得知,cache buffers chains锁存器并没有集中在一两个锁存器上,可以判定为低效的SQL并发引起。
最后查询低效sql,改进sql。
热块
sys@FDK> create index cbc_full_scan_table_idx on sajet.cbc_full_scan_table(no);
索引已创建。
为了模拟只读一个块,我们保留no=2只有一条记录,确保只读这一个块
sys@FDK> delete cbc_full_scan_table where no=2 and rownum<=4;
创建Procedure
sajet@FDK> create procedure cbc_hot_block_test is
2 v_no int;
3 i int;
4 begin
5 for i in 1..20000 loop
6 select no into v_no from cbc_full_scan_table where no=2;
7 end loop;
8 end;
9 /
过程已创建。
20session同时执行以上procedure
sajet@FDK> var job_no number;
sajet@FDK> begin
2 for idx in 1..20 loop
3 dbms_job.submit(:job_no,'cbc_hot_block_test;');
4 commit;
5 end loop;
6 end;
7 /
PL/SQL 过程已成功完成。
此时查看latch争用情况。
sys@FDK> select sid,event,p1text,p1raw from v$session_wait where event='latch: cache buffers chains'
SID EVENT P1TEXT P1RAW
---------- ------------------------------ ---------- --------
131 latch: cache buffers chains address 6C1D3300
140 latch: cache buffers chains address 6C1D3300
141 latch: cache buffers chains address 6C1D3300
143 latch: cache buffers chains address 6C1D3300
147 latch: cache buffers chains address 6C1D3300
158 latch: cache buffers chains address 6C1D3300
已选择6行。
Latch的地址都是一样,表明Hot Block的存在。
sys@FDK> col name for a30;
sys@FDK> select ADDR,LATCH#,CHILD#,NAME,GETS,MISSES,SLEEPS from
2 (select * from v$latch_children
3 where name ='cache buffers chains'
4 order by sleeps desc)
5 where rownum<=20;
ADDR LATCH# CHILD# NAME GETS MISSES SLEEPS
-------- ---------- ---------- ------------------------------ ---------- ---------- ----------
6C1D3300 122 784 cache buffers chains 10162666 355639 1663
6C197D80 122 151 cache buffers chains 5080225 428883 7
6C1E5000 122 974 cache buffers chains 17531 126 1
6C189C80 122 1 cache buffers chains 180 0 0
6C189E00 122 2 cache buffers chains 136 0 0
6C189F80 122 3 cache buffers chains 78 0 0
6C18A100 122 4 cache buffers chains 28 0 0
6C18A280 122 5 cache buffers chains 54 0 0
6C18A400 122 6 cache buffers chains 63 0 0
6C18A580 122 7 cache buffers chains 2371 0 0
6C18A700 122 8 cache buffers chains 759 0 0
6C18A880 122 9 cache buffers chains 106 0 0
6C18AA00 122 10 cache buffers chains 340 0 0
6C18AB80 122 11 cache buffers chains 114 0 0
6C18AD00 122 12 cache buffers chains 56 0 0
6C18AE80 122 13 cache buffers chains 91 0 0
6C18B000 122 14 cache buffers chains 101 0 0
6C18B180 122 15 cache buffers chains 957 0 0
6C18B300 122 16 cache buffers chains 47 0 0
6C18B480 122 17 cache buffers chains 90 0 0
已选择20行。
发现Sleep集中在地址为6C1D3300的子latch上,表明产生了热块。
现在我们根据以上提供的信息来查明究竟是那些段有热块。
根据X$BH,和DBA_OBJECTS关联查询
OBJECT_NAME HLADDR TCH DBARFIL DBABLK
---------------------------------------- -------- ---------- ---------- ----------
CBC_FULL_SCAN_TABLE_IDX 6C1D3300 22 1 60963
C_OBJ# 6C1D3300 8 1 56625
I_OBJ2 6C1D3300 5 1 231
ACCESS$ 6C1D3300 3 1 697
COL_USAGE$ 6C1D3300 2 1 4103
SYN$ 6C1D3300 2 1 35401
WRH$_PARAMETER_NAME 6C1D3300 1 3 2938
C_TOID_VERSION# 6C1D3300 1 1 8674
I_DEPENDENCY1 6C1D3300 1 1 8907
IDL_UB2$ 6C1D3300 1 1 9140
C_COBJ# 6C1D3300 1 1 56858
C_TOID_VERSION# 6C1D3300 1 1 13711
CBC_FULL_SCAN_TABLE 6C1D3300 0 5 694544
CBC_FULL_SCAN_TABLE 6C1D3300 0 5 695010
CBC_FULL_SCAN_TABLE 6C1D3300 0 5 694777
CBC_FULL_SCAN_TABLE_IDX 6C1D3300 0 1 60963
已选择16行。
可以很明显的看到CBC_FULL_SCAN_TABLE_IDX这个object的60963块的产生了热块。
以上模拟了Latch:cache buffers chains的两种情况,以及如何去查找引起的原因。