晶晶实验十六 详述逻辑读

晶晶实验十六 详述逻辑读ITPUB个人空间B*OLI9t@J
ITPUB个人空间*ayahUF
    这篇实验讨论下数据的读写过程.
f:o"o6e"} b'@ s j&]0    我们都知道,数据块是oracle最基本的读写单位,但用户所需要的数据,并不是整个块,而是块中的行,或列.当用户发出SQL语句时,此语句被解析执行完毕，就开始了数据的抓取阶段,在此阶段,服务器进程会先将行所在的数据块从数据文件中读入buffer cache,这个过程叫做物理读.物理读,每读取一个块,就算一次物理读.当块被送进buffer cache后,并不能立即将块传给用户,因为用户所需要的并不整个块,而是块中的行.从buffer cache的块中读取行的过程,就是逻辑读.为了完成一次逻辑读,服务器进程先要在hash表中查找块所在的cache buffer 链.找到之后,需要在这个链上加一个cache buffer chains 闩,加闩成功之后,就在这个链中寻找指定的块,并在块上加一个pin锁.并释放cache buffer chains闩.然后就可以访问块中的行了.服务器进程不会将块中所有满足条件的行一次取出,而是根据你的抓取命令,每次取一定数量的行.这些行取出之后,会经由PGA传给客户端用户.行一旦从buffer cache中取出,会话要释放掉在块上所加的PIN.本次逻辑读就算结束.如果还要再抓取块中剩余的行,服务器进程要再次申请获得cache bufffer链闩.再次在块上加PIN.这就算是另外一次逻辑读咯.也就是说,服务器进程每申请一次cache buffer链闩,就是一次逻辑读.而每次逻辑读所读取的行的数量,可以在抓取命令中进行设置.ITPUB个人空间M"k1O abVWA,H)Pfb
    逻辑读和Cache buffer chains闩关系密切，TOM曾有文章提到，进程每申请一次Cache buffer chains闩，就是一次逻辑读。但是，逻辑读并不等同于Cache buffer chains闩，每次逻辑读，在9i中至少需要获得两Cache buffer chains闩。逻辑读是指在Hash表中定位块的这个过程。
9X3f,XK?[0
.yE7~E](|@0下面是我的测试：
AeZ~4I(`#S0步1：建立测试表：ITPUB个人空间:E7bxJ}
create table jj_one(id number(5),name char(40));ITPUB个人空间BN"Iicb},`b
ITPUB个人空间y o;lvg-zZ
步2：插入100行
)zzgB%m1R_0begin
;eeQ2|4Pd`0   for i in 1..100 loopITPUB个人空间Pi+`V.z?re
     insert into jj_one values(i,'aaa');
CBc"Fi!EFs0   end loop;
V7^WKuAzi0e4R-bl0end;
b)N#L;q9N.JYy0/
9d,fn(TT I0N0ITPUB个人空间qB6?o2U5N VR~*~[7S
或：insert into jj_one select rownum,'aaa' from dba_objects where rownum<=100;ITPUB个人空间h5XHt"j
ITPUB个人空间7`.TE-I{,hZ#Gyy
步3：显示一下表中行的分布ITPUB个人空间5{2x@:h!Aq ]]
sid=10 pid=11> select bk,max(id),min(id) from (select dbms_rowid.rowid_block_number(rowid) bk,id from jj_one) group by bk;ITPUB个人空间ZgTP_4O

"f)~?C p0        BK    MAX(ID)    MIN(ID)ITPUB个人空间0iW~"f*V
---------- ---------- ----------
(o2T%?g5];u2j4o0     42594         81          1
m9J
j'R|3[0     42595        100         82ITPUB个人空间 No"wgCE
ITPUB个人空间4Bx#}%D:zZ)z
可以看到，表共占两个块，ID从1到81的行在块42594中，ID从82到100的行在42595中。
!JEB(N,vG$n0
Fq jz?0步4：设备批量读取参数为15ITPUB个人空间E eu4B"g w
sid=10 pid=11> set arraysize 15ITPUB个人空间fX3A iZxy
因为9i或10g中的默认值都是15，如果并没有更改过这个设置，此步也可省去。ITPUB个人空间}7n^M$b5nKaJ_+Wn2|
ITPUB个人空间:OUT9bNG&`9l.g
步5：查看1行：ITPUB个人空间[8N3PS5R2N|
sid=11 pid=12> set autot trace stat
P'p }7i
cSg-S0sid=11 pid=12> select * from jj_one where id<=1;
K1NSJT d`Q0ITPUB个人空间 Rk Kqk6t;KXC

4I_7vFKE+L LG}6L0统计信息
p(`$Xf3Tz7_^ilT0----------------------------------------------------------ITPUB个人空间m[e z&jb}f
          0  recursive calls
c3K6W&I3QJ;X Z3]0          6  consistent getsITPUB个人空间)EGm8v5\/}q-w5m;t$V
          0  physical readsITPUB个人空间;gIM2D_"[
        458  bytes sent via SQL*Net to clientITPUB个人空间n#r3x/t
J"C
        372  bytes received via SQL*Net from clientITPUB个人空间|8|E\/hX8P7U
          2  SQL*Net roundtrips to/from clientITPUB个人空间J9ns$}P)wF%g_'T
          1  rows processedITPUB个人空间Cqt4U/Mp_
（省略无关行）
@{~ a@0ITPUB个人空间
JU+`
J!L.v
逻辑读为6ITPUB个人空间ls)vF7E Z

E}X%J I)Qj#d!h6b G0步6：查询15行以内：ITPUB个人空间i~a8o*QH-[u1`8_
sid=11 pid=12> select * from jj_one where id<=2;
v^ ]4{%MM!P{0ITPUB个人空间-y zq^2VS%L!E
统计信息
9Z S4A\F&UR
b5b:k|E0----------------------------------------------------------ITPUB个人空间NpK.KH
          0  recursive calls
|s~;r%O s\;z5T0          6  consistent gets
hy%kF6N5u8@/~0          0  physical reads
"Ol|k;d+g UB8n0        493  bytes sent via SQL*Net to clientITPUB个人空间 W1o-}&\8k
        372  bytes received via SQL*Net from clientITPUB个人空间KwHLj[ d0l&O
          2  SQL*Net roundtrips to/from clientITPUB个人空间+epZ2Y6Z4dH&jQL
          2  rows processed
D5fH {1}e
Ea4h0ITPUB个人空间:\S4Yvx@:y#i
^
在抓取行数小于15的情况下，逻辑读始终为6。
_b c7@H0ITPUB个人空间+Ug&^~ x-NX
步7：查询16行以上：ITPUB个人空间/|:\Elc5H
sid=11 pid=12> select * from jj_one where id<=16;
+fE4gCBw0
J6qcS/C W1zz0已选择16行。
QT?t6H0
/U_"Y,QQ}$Hbt"f0统计信息
)s YS V)cO9y0----------------------------------------------------------
Y%|/G?*]a*Vi:Y0          0  recursive calls
hp4mI$vQ1aV3f0          7  consistent gets
Iu F)K"bq&_0          0  physical reads
)eW_z3sxyWx0        699  bytes sent via SQL*Net to clientITPUB个人空间Qy-K U?Fy"O
        383  bytes received via SQL*Net from client
t$o%F;{1iS\j0~&uO0          3  SQL*Net roundtrips to/from clientITPUB个人空间!J Fk-Gp
         16  rows processed
:@$^T6@
@R0ITPUB个人空间.[2jkC ^*^+v
逻辑读已经变成7次。
o;_@9x4V;lSdP d0ITPUB个人空间1s
l\ M7lJ8H\/u/D
注意，在10G中，对块读的算法有改进。以同样的谓词条件，访问同样的行时，第一次访问时的逻辑读要比以后再行访问时多的多。因此，在10G中，同样的命令，多执行几次，这样看到的结果比较全面。
P$m!yPxcD0
(D[\;E:~QIq^0还有一点，访问15行以内时，为什么会有6次逻辑读？不应该是1次吗？这里，我相信Set autot trace stat命令本身有一定的原因，如果用下面的静态游标：
!cY}N.v0[r0ITPUB个人空间"GQzk}
sid=10 pid=11> alter session set events '10046 trace name context forever ,level 14';
JEhjW+?0会话已更改。ITPUB个人空间+ai2`m9CO&[,Y3n
ITPUB个人空间L1]-b(b5V0Kf"} z
declare
.^f*i{[ n_^1WMQ0  type mid is table of jj_one.id%type;
:}#TcuyoMK0  mid1 mid;
8p2T R%X;`m x0  cursor c is select id from jj_one where id>=1 and id<=15;
a ].G p?8F2q0beginITPUB个人空间+_}+g8V8o |0b~eh
  open c;ITPUB个人空间H6h(^n~d3C?
  fetch c bulk collect into mid1 limit 15;ITPUB个人空间0qK L!V6kw
  dbms_output.put_line(c%rowcount);
"w8j6qY(_7A0  close c;ITPUB个人空间Jo7RJ+Dy
end;ITPUB个人空间$Je{e:iE0VL$^
/
)Ix@"@"eu0ITPUB个人空间7r.{p(Mg0bm? Y&H
sid=10 pid=11> alter session set events '10046 trace name context off';
-k#H-M9G+C&EAA0会话已更改。ITPUB个人空间'] Bnb@O*MP YB
ITPUB个人空间Cq%Z}h/d
用Tkprof格式化跟踪结果：ITPUB个人空间*oQp6_gA4xSX
E:\oracle\admin\mytwo\udump>tkprof mytwo_ora_756.trc m3.txt
rhbka U[0ITPUB个人空间;oK-bDRjV
查看M3.txt文件：ITPUB个人空间
v6Cv*Ow
call     count       cpu    elapsed       disk      query    current        rows
Js*` }W cm0------- ------  -------- ---------- ---------- ---------- ----------  ----------
U-DV5V._$\ ^
u0Parse        1      0.00       0.00          0          0          0           0ITPUB个人空间1H+WGg"{@G
Execute      1      0.00       0.00          0          0          0           0
X$a&\(Z
wn#ZSg(U0Fetch        1      0.00       0.00          0          3          0          15ITPUB个人空间3|.}0{/j
~{ Vp
------- ------  -------- ---------- ---------- ---------- ----------  ----------ITPUB个人空间
A.sH]9T9n]
total        3      0.00       0.00          0          3          0          15ITPUB个人空间A#o@}$T7e[0F4|
ITPUB个人空间 E.pB&O#S.^8{
逻辑读只有3次。这3次逻辑读，有一次是针对行所在块的，其余两次是针对段头的。ITPUB个人空间8T\'@'~i4B

-W4k Wi?s
_ j0
XI1Y"g
{
eACtb'y0实验完毕ITPUB个人空间;N {u!jp4vq

*p5w#]/o,U,W'EpO(~:r0从上面的实验中可以看出，“成批读取”中，批大小的设定，可以影响逻辑读的数量。批大小越大，读相同数量的行，逻辑读就越少。而且服务端和客户端交互的次数也越少，由网络传输的数据也可以减少，下面看一下测试：
k"]XL"yfY0批大小为1：
,uoCbP/d"f0sid=11 pid=12> set arraysize 1
h kw0T2jXt\ x O0sid=11 pid=12> select * from jj_one;ITPUB个人空间B@Q kt+y/Qu SJl
已选择100行。
X%u&vQ LQ0统计信息ITPUB个人空间m%w*OX p
----------------------------------------------------------
:K+G(eT\)u:U0         54  consistent gets
xKf&f(h,wa6KG!f0       7206  bytes sent via SQL*Net to client
| s,B[2Ms5^6c f?0        911  bytes received via SQL*Net from client
H}(mgmITKje0         51  SQL*Net roundtrips to/from clientITPUB个人空间 iv#^!v/vF`1?
        100  rows processedITPUB个人空间{1H+B+~%B?
批大小为100：ITPUB个人空间L^ km/V)j
sid=11 pid=12> set arraysize 100ITPUB个人空间dY m0tW,Ia ?r
sid=11 pid=12> select * from jj_one;ITPUB个人空间x2A#QoO
已选择100行。ITPUB个人空间(G.mC9u0G`o6c"?}
统计信息ITPUB个人空间xjY"gAU)YF
----------------------------------------------------------
h4w D1C*q[-Yg.RG0          6  consistent gets
(aW)zPk,lg0       1277  bytes sent via SQL*Net to client
+[&n2Xr:F4Db(yJL}0        372  bytes received via SQL*Net from clientITPUB个人空间6?bO_0cwQ+s
          2  SQL*Net roundtrips to/from clientITPUB个人空间EtER*z4m
        100  rows processedITPUB个人空间 a|V,PX1f1@n,^

;ML
g_]W0差别是很明显的，bytes sent via SQL*Net to client的数值，相差了6倍左右。
Y9g
CY9PO0
7Etb0DS)}Y w?q0但这并不代表将批大小设置的越高，速度就越快，否则，Oracle直接将它设置为一个最大的值，不就行了，干吗还要让我们去自己调节呢！ITPUB个人空间MV7KIi}+n
行从Buffer cache中读出来后，会先缓存在PGA中（具体是在游标的运行时区），然后再传给客户端。如果批大小过大，在PGA、客户端占用的内存也会增大。而且，如果渐歇性的在网络上传输大量数据，对网络也会有一定影响。下面来观察一下批大小对PGA的影响：
@GB1Nj~0在会话11中执行如下过程：ITPUB个人空间w5A9XB\ N1bOK/o
declareITPUB个人空间 Vpqb
o:s0bc+@6\G'm
  type mid is table of t1.id%type;
W:F.p'v;cGx0  mid1 mid;
+y9EqJ x+D(A6V,A@0  cursor c is select id from t1;ITPUB个人空间_l#d,Wz
begin
&[ MA/tS"]Trh4U`0  open c;ITPUB个人空间T+bT*L5X/J!g0Ix
  loopITPUB个人空间C!DS ~E:N%v
    fetch c bulk collect into mid1 limit 5000;ITPUB个人空间*GF6p
H(s!k)c
  exit when c%notfound;ITPUB个人空间"T3b#Z^ G)M;?N
  end loop;ITPUB个人空间&ej-T,i Zf5WNM
  dbms_output.put_line(c%rowcount);
,U)]'i!B9jS8Nt0  close c;
h0r*{(J)xW)X0end;ITPUB个人空间%C y']n4N
/ITPUB个人空间9G9LYJ4O@&x
ITPUB个人空间U!@ HWlP
在另一会话中观察会话11的内存占用情况：
"v
TSp!H `0sid=10 pid=11> @pga   --此脚本下面有说明ITPUB个人空间C-G~HH7D
输入 user 的值:  11ITPUB个人空间#h5qHXav0M}s
原值    7: and b.sid= &user
?)gh:fZ0新值    7: and b.sid= 11ITPUB个人空间9qQ$YO^#P'N
ITPUB个人空间?Ni#t$N\J%IZ|"h
  PGA Used  PGA Alloc    PGA Max
X_*i0w]:?.DR0---------- ---------- ----------
ypo|6I(u.}0    561508     779492     779492
:K@w n1T,R?0ITPUB个人空间&CC-|[^Hqf
ITPUB个人空间|(H'eY k6@R
然后将会话11中过程的批大小改为1：fetch c bulk collect into mid1 limit 5000; 再试一次
}eHG"?9i6^'Q0
qtHZ)ZU0在另一会话观察会话11的PGA占用情况：
5\.fM#t |
{ t-KQ~X(C0sid=10 pid=11> @pgaITPUB个人空间_w!z[#h_
输入 user 的值:  11ITPUB个人空间bG?,z:XJ
原值    7: and b.sid= &userITPUB个人空间 ]buF3H3~gt
新值    7: and b.sid= 11ITPUB个人空间]"{_{w+V*ai6t

VsH2JP/[0  PGA Used  PGA Alloc    PGA Max
9J+dg9PX5[|0---------- ---------- ----------
| W'UK M`!@%h_Bw0    184388     250668     250668
}V?.zZH#Y6M R0
ekI i0YqA5g%L(T0批大小为5000时的内存占用，是批大小为1时的3倍左右。另外，测试表一定要大一些，我的测试表是1000000行，否则不容易看到结果。在10G中，可以得到基本相同的结果。
SZ;_.sB@ ~{0
K|5z&o&s+^VeP0PGA.SQL脚本如下：
\4e eR'?3~"O6Wr0--pga_by_process.sql：ITPUB个人空间 mQ| p
Vyi~
SELECTITPUB个人空间8t|(p0c-u
a.pga_used_mem "PGA Used",
&Y&o'R6uCM2{^0a.pga_alloc_mem "PGA Alloc",
Xp+H{E8?1Vr0a.pga_max_mem "PGA Max"ITPUB个人空间js'q ~6Qx
FROM v$process a,v$session bITPUB个人空间 B;oB@
AB
where a.addr = b.paddrITPUB个人空间H:z}AIL
and b.sid= &user
:uV$E[c~+j3d0/