Buffer cache学习（四）--实验Cache Buffers Chains Latch

一时失言乱红尘 2022-08-11 07:28 185阅读 0赞

测试：模拟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的争用。