[20151104]Cache Buffers chains与共享模式疑问3.txt

--今天itpub上讨论vage讲11.2.0.4读读模式不会出现cache buffers chains latch，好奇做一个测试：

--链接如下：

1.测试环境：

SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> show parameter cpu_count

NAME       TYPE    VALUE ---------- ------- ------ cpu_count  integer 4 --为了尽快出现相关等待事件，我修改参数cpu_count=4

SCOTT@book> select rowid,empno,'sqlplus scott/book @h2 2e6 '||rowid c60 from emp ;

ROWID              EMPNO C60 ------------------ ----- --------------------------------------------- AAAVREAAEAAAACXAAA  7369 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAA AAAVREAAEAAAACXAAB  7499 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAB AAAVREAAEAAAACXAAC  7521 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAC AAAVREAAEAAAACXAAD  7566 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAD AAAVREAAEAAAACXAAE  7654 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAE AAAVREAAEAAAACXAAF  7698 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAF AAAVREAAEAAAACXAAG  7782 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAG AAAVREAAEAAAACXAAH  7788 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAH AAAVREAAEAAAACXAAI  7839 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAI AAAVREAAEAAAACXAAJ  7844 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAJ AAAVREAAEAAAACXAAK  7876 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAK AAAVREAAEAAAACXAAL  7900 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAL AAAVREAAEAAAACXAAM  7902 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAM AAAVREAAEAAAACXAAN  7934 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAN

14 rows selected.

--看rowid可以确定这些信息在一个数据块中。

SCOTT@book> @ &r/rowid  AAAVREAAEAAAACXAAA     OBJECT       FILE      BLOCK        ROW DBA                  TEXT ---------- ---------- ---------- ---------- -------------------- ----------------------------------------      87108          4        151          0 4,151                alter system dump datafile 4 block 151 ;

$ cat h2.sql

declare m_id number; m_data varchar2(200); begin         for i in 1 .. &&1 loop                 select ename into m_data from emp where rowid='&&2'; end loop; end ; / quit

--拷贝以上内容到bbb.sh,注后面加上&放在后台执行: $ cat bbb.sh sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAA  & sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAB  & sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAC  & sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAD  & sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAE  & sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAF  & sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAG  & sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAH  & sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAI  & sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAJ  & sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAK  & sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAL  & sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAM  & sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAN  &

SYS@book> @ &r/bh 4 151

HLADDR           DBARFIL      DBABLK CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               OBJECT_NAME ---------------- ------- ----------- ----- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- 0000000083BFD3C8       4         151     1 data block         xcur              101          0          0          0          0          0 0000000078848000 EMP

--HLADDR=0000000083BFD3C8

2.执行bbb.sh脚本,这样相当于打开14个会话,访问同一个块的不同记录.

SYS@book> select p1raw,p2raw,sid,serial#,seq#,event,state,wait_time_micro,seconds_in_wait from v$session where wait_class<>'Idle' order by event ;

P1RAW            P2RAW                   SID    SERIAL#       SEQ# EVENT                     STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT ---------------- ---------------- ---------- ---------- ---------- ------------------------- ------------------- --------------- --------------- 0000000062657100 0000000000000001        393         17         79 SQL*Net message to client WAITED SHORT TIME                 0               0 000000006010D7C0 0000000000000150        592         17         31 latch: shared pool        WAITED SHORT TIME               166               7 000000006010D7C0 0000000000000150         14          5         30 latch: shared pool        WAITED SHORT TIME               183               7 000000006010D7C0 0000000000000150        199         21         28 latch: shared pool        WAITED SHORT TIME               217               7 000000006010D7C0 0000000000000150        201         37         36 latch: shared pool        WAITED SHORT TIME               134               7 000000006010D7C0 0000000000000150        202         19         36 latch: shared pool        WAITED SHORT TIME               148               7 000000006010D7C0 0000000000000150        204         25         35 latch: shared pool        WAITED SHORT TIME               128               7 000000006010D7C0 0000000000000150        394         37         33 latch: shared pool        WAITED SHORT TIME               122               7 000000006010D7C0 0000000000000150        395         23         30 latch: shared pool        WAITED SHORT TIME               142               7 000000006010D7C0 0000000000000150        396         15         41 latch: shared pool        WAITED SHORT TIME               158               7 000000006010D7C0 0000000000000150        588         37         27 latch: shared pool        WAITED SHORT TIME               185               7 000000006010D7C0 0000000000000150        589         29         37 latch: shared pool        WAITED SHORT TIME               174               7 000000006010D7C0 0000000000000150        590         39         43 latch: shared pool        WAITED SHORT TIME               205               7 000000006010D7C0 0000000000000150          9         21         34 latch: shared pool        WAITED SHORT TIME               150               7 000000006010D7C0 0000000000000150         10         13         34 latch: shared pool        WAITED SHORT TIME               193               7

15 rows selected.

--我有执行3个bbb.sh，确实没有出现latch: cache buffers chains  等待事件。

SYS@book> select p1raw,p2raw,sid,serial#,seq#,event,state,wait_time_micro,seconds_in_wait from v$session where wait_class<>'Idle' order by event ;

P1RAW            P2RAW                   SID    SERIAL#       SEQ# EVENT                     STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT ---------------- ---------------- ---------- ---------- ---------- ------------------------- ------------------- --------------- --------------- 0000000062657100 0000000000000001        393         17         87 SQL*Net message to client WAITED SHORT TIME                 2               0 000000008CF6D633 000000CF00000000        602          9         48 cursor: pin S             WAITED KNOWN TIME             21428               2 00000000FED04CCB 000000D500000003         13         23        113 cursor: pin S             WAITED KNOWN TIME             10052               1 00000000F5C70456 0000001700000003         14          5        100 cursor: pin S             WAITING                        7690               0 00000000763F6E17 0000019400000003         15         25         90 cursor: pin S             WAITED KNOWN TIME             17990               1 0000000039E57F03 0000019700000000         16          1         91 cursor: pin S             WAITED KNOWN TIME             34634               1 000000000C0459F9 000000CA00000003         17          9         90 cursor: pin S             WAITED KNOWN TIME              9974               1 00000000830A4780 000000CC00000003         19          1         80 cursor: pin S             WAITED KNOWN TIME             12986               2 00000000442BC338 0000024D00000003         20          1         93 cursor: pin S             WAITED KNOWN TIME             21984               0 00000000C3E7115E 0000018D00000002         21          1         66 cursor: pin S             WAITED KNOWN TIME             12791               4 000000000F1DE22E 000000D100000003         22          7         59 cursor: pin S             WAITED KNOWN TIME             12363               0 00000000F5C70456 0000000E00000000         23         11         46 cursor: pin S             WAITED KNOWN TIME             15576               1 00000000077D57C8 0000025400000002         24         27         58 cursor: pin S             WAITED KNOWN TIME             21996               1 00000000763F6E17 0000019400000003        199         21         89 cursor: pin S             WAITED KNOWN TIME             10980               1 000000000F1DE22E 0000018A00000003        200         53         93 cursor: pin S             WAITED SHORT TIME                 9               0 00000000C3E7115E 0000018D00000003        201         37         67 cursor: pin S             WAITED KNOWN TIME             19985               1 000000000C0459F9 0000001100000003        202         19        148 cursor: pin S             WAITED KNOWN TIME             10996               0 00000000763F6E17 000000C700000000        203         29         83 cursor: pin S             WAITED KNOWN TIME             43400               1 00000000830A4780 0000001300000003        204         25         85 cursor: pin S             WAITED KNOWN TIME             11975               0 000000000C0459F9 0000025800000003        205         31         90 cursor: pin S             WAITED KNOWN TIME             13673               1 000000008CF6D633 000000CF00000000        206          5         81 cursor: pin S             WAITED KNOWN TIME             21434               2 000000005ACF2B33 0000019000000002        208          1         64 cursor: pin S             WAITED KNOWN TIME             15993               1 000000000F1DE22E 0000018A00000003        209          1         89 cursor: pin S             WAITED SHORT TIME              4018               0 00000000C3E7115E 0000018D00000003        210         27         45 cursor: pin S             WAITED KNOWN TIME             21980               1 000000005ACF2B33 0000019000000002        212         11         47 cursor: pin S             WAITED KNOWN TIME             12998               1 00000000FED04CCB 0000019300000000        213         13         54 cursor: pin S             WAITED KNOWN TIME             11942               1 0000000039E57F03 0000001000000003          9         21        137 cursor: pin S             WAITED KNOWN TIME             10992               0 00000000077D57C8 0000025400000002         10         13        110 cursor: pin S             WAITED KNOWN TIME             21992               1 000000000F1DE22E 000000D100000003        394         37        140 cursor: pin S             WAITED SHORT TIME              4018               0 00000000FED04CCB 000000D500000004        395         23        126 cursor: pin S             WAITED KNOWN TIME             11763               1 000000008CF6D633 000000CF00000000        396         15        112 cursor: pin S             WAITED KNOWN TIME             21438               2 00000000C3E7115E 000000C900000002        397         21         53 cursor: pin S             WAITED KNOWN TIME             10957               2 00000000EF787C90 0000024C00000003        398         17         47 cursor: pin S             WAITED KNOWN TIME             21992               0 00000000BF44A91D 0000025000000000        399         29         90 cursor: pin S             WAITED KNOWN TIME             20970               1 000000005ACF2B33 000000D400000004        400          3         52 cursor: pin S             WAITED KNOWN TIME              9978               2 0000000039E57F03 0000001000000003        401         13        101 cursor: pin S             WAITED KNOWN TIME              7973               0 00000000F5C70456 0000001700000003        402          1         72 cursor: pin S             WAITING                          72               0 00000000FED04CCB 000000D500000004        403          1         87 cursor: pin S             WAITED KNOWN TIME             10776               1 00000000763F6E17 000000C700000000        404         11         46 cursor: pin S             WAITED KNOWN TIME             43398               1 00000000BF44A91D 0000018F00000002        405         17         51 cursor: pin S             WAITED KNOWN TIME              6979               0 00000000442BC338 0000024D00000003        406         11         53 cursor: pin S             WAITED KNOWN TIME             10984               0 0000000039E57F03 0000001000000003        407          7         58 cursor: pin S             WAITED KNOWN TIME             16984               0 00000000EF787C90 0000025600000002        588         37         67 cursor: pin S             WAITED KNOWN TIME             21991               1 00000000442BC338 0000019600000000        589         29        121 cursor: pin S             WAITED KNOWN TIME             13970               1 000000005ACF2B33 0000019000000002        590         39         72 cursor: pin S             WAITED KNOWN TIME             16997               1 00000000077D57C8 0000000A00000001        591         29        100 cursor: pin S             WAITED KNOWN TIME              8993               0 00000000BF44A91D 0000018F00000002        592         17        117 cursor: pin S             WAITED KNOWN TIME              8986               0 00000000442BC338 0000024D00000003        593         39        107 cursor: pin S             WAITED KNOWN TIME             21998               0 00000000F5C70456 0000000E00000000        594         15         95 cursor: pin S             WAITED KNOWN TIME             10363               1 00000000830A4780 0000001300000003        595         11         58 cursor: pin S             WAITED KNOWN TIME             11995               0 00000000077D57C8 0000000A00000000        596          7         81 cursor: pin S             WAITED KNOWN TIME             12994               1 00000000830A4780 0000001300000003        597         11         46 cursor: pin S             WAITED KNOWN TIME             23993               0 00000000EF787C90 0000024C00000003        598          1         76 cursor: pin S             WAITED KNOWN TIME             22975               0 00000000BF44A91D 0000018F00000002        599          1         86 cursor: pin S             WAITED KNOWN TIME              5988               0 000000000C0459F9 0000001100000003        600         13         63 cursor: pin S             WAITED KNOWN TIME             21995               0 00000000EF787C90 0000024C00000003        601         11         47 cursor: pin S             WAITED KNOWN TIME             21979               0 000000006010D7C0 0000000000000150        207          1         86 latch: shared pool        WAITED KNOWN TIME             10462               2

57 rows selected.

--我最终启动14个 相当于 196个会话，依旧没有出现cache buffers chains latch。

--检查X$KSUPRLAT：

SYS@book> select * from X$KSUPRLAT ;

ADDR                   INDX    INST_ID   KSUPRPID   KSUPRSID   KSUPRLLV   KSUPRLTY KSUPRLAT         KSUPRLNM             KSUPRLMD     KSULAWHY   KSULAWHR   KSULAGTS ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- ------------ -------- ---------- ---------- 00007F8A223E9528          0          1         46        403          1          0 0000000083BFD3C8 cache buffers chains SHARED              0       1810  478952427 00007F8A223E9528          1          1         67        589          1          1 0000000083BFD3C8 cache buffers chains MAYBE-SHARED 16777367       1807  478953012 00007F8A223E9528          2          1         71        593          1          0 0000000083BFD3C8 cache buffers chains SHARED              0       1810  478953019 00007F8A223E9528          3          1         80          9          1          1 0000000083BFD3C8 cache buffers chains MAYBE-SHARED 16777367       1807  478953025 00007F8A223E9528          4          1         87        604          1          1 0000000083BFD3C8 cache buffers chains MAYBE-SHARED 16777367       1807  478953030

-- KSUPRLMD 并没有出现EXCLUSIVE模式. 总之不停的执行select * from X$KSUPRLAT ;依旧无法出现EXCLUSIVE模式.