[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