测试:模拟Cache Buffers Chains Latch竞用
1:创建表test,总共一条记录,共1个Block。
SCOTT@ prod>select * from test;
ID
----------
1
SCOTT@ prod>select dbms_rowid.rowid_relative_fno(rowid) file#,
2 dbms_rowid.rowid_block_number(rowid) block# from test;
FILE# BLOCK#
---------- ----------
4 523
2:创建存储过程,模拟对块不断查询
create or replace procedure latch is
i number;
begin
loop
select id into i from test;
end loop;
end;
/
3:session 47上执行
SCOTT@ prod>select userenv('sid') from dual;
USERENV('SID')
--------------
47
查看会话event
SYS@ prod>select sid,event,p1,p1raw,p1text,p2,p2text from v$session where sid in (47,33);
SID EVENT P1 P1RAW P1TEXT P2 P2TEXT
---------- ---------------------------------------- ---------- ---------------- ---------- ---------- ----------
33 SQL*Net message from client 1650815232 0000000062657100 driver id 1 #bytes
47 SQL*Net message from client 1650815232 0000000062657100 driver id 1 #bytes
在执行期间可见没有miss
SYS@ prod>select gets,misses,sleeps,spin_gets,wait_time from v$latch where name ='cache buffers chains';
GETS MISSES SLEEPS SPIN_GETS WAIT_TIME
---------- ---------- ---------- ---------- ----------
156829087 0 0 0 0
4:在session 33上在执行存储过程latch
再次查看
SYS@ prod>select sid,event,p1,p1raw,p1text,p2,p2text from v$session where sid in (47,33);
SID EVENT P1 P1RAW P1TEXT P2 P2TEXT
---------- ---------------------------------------- ---------- ---------------- ---------- ---------- ----------
33 latch: cache buffers chains 1042883468 000000003E29238C address 150 number
47 latch: cache buffers chains 1043032304 000000003E2B68F0 address 150 number
SYS@ prod>/
SID EVENT P1 P1RAW P1TEXT P2 P2TEXT
---------- ---------------------------------------- ---------- ---------------- ---------- ---------- ----------
33 cursor: pin S 3777061920 00000000E1216420 idn 3080192 value
47 latch: cache buffers chains 1042883468 000000003E29238C address 150 number
查看
SYS@ prod>select gets,misses,sleeps,spin_gets,wait_time from v$latch where name ='cache buffers chains';
GETS MISSES SLEEPS SPIN_GETS WAIT_TIME
---------- ---------- ---------- ---------- ----------
241954233 157 70 89 7021403
可以看到出现latch: cache buffers chains等待事件。这就是2个session要同时访问同一个Block,这个时候在一个会话持有Latch的时候,另一个会话必须Spin等待获得Latch。
也会看到latch: library cache等待事件。这是由于在共享池进行软解析的时候需要获得library cache latch来扫描library cache中相应Bucket的Chain来获得执行计划来执行SQL。因为并发性高,导致library cache latch的争用。
Buffer cache学习(四)--实验Cache Buffers Chains Latch
最新推荐文章于 2021-03-25 09:01:50 发布