[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