[20150309]邏輯讀產生CBC Latch的解析.txt

[20150309]邏輯讀產生Cache Buffer Chain(簡稱CBC) Latch的解析.txt

--參考連結http://blog.csdn.net/guoyjoe/article/details/8585391，自己也做1次。

邏輯讀的過程
1、Oracle以每個塊的檔案號、塊號和型別做HASH運算,得到HASH值。根據HASH值，到HASH表中取出指定塊的記憶體地址
2、獲取CBC Latch(實驗的重點測試部分)
3、根據HASH值,搜尋CBC連結串列
4、根據DBA找到BH(Buffer Header)加Buffer Pin
5、加完Buffer Pin馬上釋放CBC Latch
6、訪問Buffer開始fetch資料
7、獲取CBC Latch
8、釋放Buffer Pin
9、釋放CBC Latch

--可以發現讀取塊需要獲得2次CBC LATCH。

1.建立測試環境:

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

SCOTT@test> select /*+ full(dept) */ rowid,dept.* from dept where deptno=10;
ROWID                    DEPTNO DNAME          LOC
------------------ ------------ -------------- -------------
AABBrlAAEAAAAWDAAB           10 ACCOUNTING     NEW YORK

SCOTT@test> @lookup_rowid AABBrlAAEAAAAWDAAB
      OBJECT         FILE        BLOCK          ROW DBA                  TEXT
------------ ------------ ------------ ------------ -------------------- ----------------------------------------
      269029            4         1411            1 4,1411               alter system dump datafile 4 block 1411

2.根據檔案號塊號獲取CBC Latch的地址:

SCOTT@test> select hladdr from x$bh where file#=4 and dbablk=1411;
HLADDR
----------------
00000000BC852DE0

3.根據CBC Latch的地址可以查出這個CBC Latch被獲得的次數:
SCOTT@test> select addr,name,gets from v$latch_children  where addr='00000000BC852DE0';
ADDR             NAME                                                       GETS
---------------- -------------------------------------------------- ------------
00000000BC852DE0 cache buffers chains                                       1784

SCOTT@test> select * from dept where rowid='AABBrlAAEAAAAWDAAB';
      DEPTNO DNAME          LOC
------------ -------------- -------------
          10 ACCOUNTING     NEW YORK

SCOTT@test> select addr,name,gets from v$latch_children  where addr='00000000BC852DE0';
ADDR             NAME                                                       GETS
---------------- -------------------------------------------------- ------------
00000000BC852DE0 cache buffers chains                                       1786

--可以發現一次邏輯讀產生2次CBC Latch.
--補充測試，實際上可以測試每次邏輯讀都會獲取2次cbc latch：
SCOTT@test> select addr,name,gets from v$latch_children  where addr='00000000BC852DE0';

ADDR             NAME                       GETS
---------------- -------------------- ----------
00000000BC852DE0 cache buffers chains   80275738

SCOTT@test> select * from dept ;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS1
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SCOTT@test> select addr,name,gets from v$latch_children  where addr='00000000BC852DE0';
ADDR             NAME                       GETS
---------------- -------------------- ----------
00000000BC852DE0 cache buffers chains   80275742
--可以發現80275742-80275738=4,需要4次，可以參考我寫的一篇blog：
http://blog.itpub.net/267265/viewspace-1430902/

--如果我設定array=2,這樣將增加6次。邏輯讀僅僅比前面增加1次。
--注意我的測試設定最小array=2,即使你設定1實際上結果與設定2的邏輯讀是一樣的.

SCOTT@test> set array 2
SCOTT@test> select addr,name,gets from v$latch_children  where addr='00000000BC852DE0';
ADDR             NAME                       GETS
---------------- -------------------- ----------
00000000BC852DE0 cache buffers chains   80275742

SCOTT@test> select * from dept ;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS1
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SCOTT@test> select addr,name,gets from v$latch_children  where addr='00000000BC852DE0';
ADDR             NAME                       GETS
---------------- -------------------- ----------
00000000BC852DE0 cache buffers chains   80275748

-- 80275748-80275742=6

4.使用oradebug來測試看看：

SQL> oradebug setmypid
Statement processed.

SYS@test> oradebug peek 0xBC852DE0 16                        --查0xBC852DE0地址開始的4位元組資訊的值為0
[0BC852DE0, 0BC852DF0) = 00000000 00000000 00000730 0000009B

SYS@test> oradebug poke 0xBC852DE0 8 0x1;                    --修改0xBC852DE0地址開始的4位元組資訊的值為1,相當於獲取了Latch
BEFORE: [0BC852DE0, 0BC852DE8) = 00000000 00000000           --修改前的值
AFTER:  [0BC852DE0, 0BC852DE8) = 00000001 00000000           --修改後的值

SYS@test> oradebug peek 0xBC852DE0 4                         --查0xBC852DE0地址開始的4位元組資訊的值為1
[0BC852DE0, 0BC852DE4) = 00000001

--注意執行select * from dept 沒有沒有問題。我不知道是我的版本問題還是那裡出了問題。

SCOTT@test> update dept set loc=lower(loc) where deptno=10 ;
--才會掛起，不知道是否是版本的問題。

SYS@test> select sid,seq#,event,state,wait_time_micro,seconds_in_wait,p1,p2 from v$session where wait_class<>'Idle' order by event ;
       SID       SEQ# EVENT                                    STATE      WAIT_TIME_MICRO SECONDS_IN_WAIT         P1         P2
---------- ---------- ---------------------------------------- ---------- --------------- --------------- ---------- ----------
       400        557 SQL*Net message to client                WAITED SHO               2               0 1650815232          1
                                                               RT TIME

         5         70 latch: cache buffers chains              WAITING           56594939              57 3162844640        155

--oracle也太複雜了。 3162844640 = 0xbc852de0

SYS@test> oradebug poke 0xBC852DE0 8 0x0
BEFORE: [0BC852DE0, 0BC852DE8) = 00000001 40000000
AFTER:  [0BC852DE0, 0BC852DE8) = 00000000 00000000