晶晶实验十六 详述逻辑读
这篇实验讨论下数据的读写过程. 我们都知道,数据块是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链闩,就是一次逻辑读.而每次逻辑读所读取的行的数量,可以在抓取命令中进行设置. 逻辑读和Cache buffer chains闩关系密切,TOM曾有文章提到,进程每申请一次Cache buffer chains闩,就是一次逻辑读。但是,逻辑读并不等同于Cache buffer chains闩,每次逻辑读,在9i中至少需要获得两Cache buffer chains闩。逻辑读是指在Hash表中定位块的这个过程。
下面是我的测试: 步1:建立测试表: create table jj_one(id number(5),name char(40));
步2:插入100行 begin for i in 1..100 loop insert into jj_one values(i,'aaa'); end loop; end; /
或:insert into jj_one select rownum,'aaa' from DBA_objects where rownum<=100;
步3:显示一下表中行的分布 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;
BK MAX(ID) MIN(ID) ---------- ---------- ---------- 42594 81 1 42595 100 82
可以看到,表共占两个块,ID从1到81的行在块42594中,ID从82到100的行在42595中。
步4:设备批量读取参数为15 sid=10 pid=11> set arraysize 15 因为9i或10g中的默认值都是15,如果并没有更改过这个设置,此步也可省去。
步5:查看1行: sid=11 pid=12> set autot trace stat sid=11 pid=12> select * from jj_one where id<=1;
统计信息 ---------------------------------------------------------- 0 recursive calls 6 consistent gets 0 physical reads 458 bytes sent via SQL*Net to client 372 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 rows processed (省略无关行)
逻辑读为6
步6:查询15行以内: sid=11 pid=12> select * from jj_one where id<=2;
统计信息 ---------------------------------------------------------- 0 recursive calls 6 consistent gets 0 physical reads 493 bytes sent via SQL*Net to client 372 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 rows processed
在抓取行数小于15的情况下,逻辑读始终为6。
步7:查询16行以上: sid=11 pid=12> select * from jj_one where id<=16;
已选择16行。
统计信息 ---------------------------------------------------------- 0 recursive calls 7 consistent gets 0 physical reads 699 bytes sent via SQL*Net to client 383 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 16 rows processed
逻辑读已经变成7次。
注意,在10G中,对块读的算法有改进。以同样的谓词条件,访问同样的行时,第一次访问时的逻辑读要比以后再行访问时多的多。因此,在10G中,同样的命令,多执行几次,这样看到的结果比较全面。
还有一点,访问15行以内时,为什么会有6次逻辑读?不应该是1次吗?这里,我相信Set autot trace stat命令本身有一定的原因,如果用下面的静态游标:
sid=10 pid=11> alter session set events '10046 trace name context forever ,level 14'; 会话已更改。
declare type mid is table of jj_one.id%type; mid1 mid; cursor c is select id from jj_one where id>=1 and id<=15; begin open c; fetch c bulk collect into mid1 limit 15; dbms_output.put_line(c%rowcount); close c; end; /
sid=10 pid=11> alter session set events '10046 trace name context off'; 会话已更改。
用Tkprof格式化跟踪结果: E:\oracle\admin\mytwo\udump>tkprof mytwo_ora_756.trc m3.txt
查看M3.txt文件: call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 3 0 15 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 3 0 15
逻辑读只有3次。这3次逻辑读,有一次是针对行所在块的,其余两次是针对段头的。
实验完毕
从上面的实验中可以看出,“成批读取”中,批大小的设定,可以影响逻辑读的数量。批大小越大,读相同数量的行,逻辑读就越少。而且服务端和客户端交互的次数也越少,由网络传输的数据也可以减少,下面看一下测试: 批大小为1: sid=11 pid=12> set arraysize 1 sid=11 pid=12> select * from jj_one; 已选择100行。 统计信息 ---------------------------------------------------------- 54 consistent gets 7206 bytes sent via SQL*Net to client 911 bytes received via SQL*Net from client 51 SQL*Net roundtrips to/from client 100 rows processed 批大小为100: sid=11 pid=12> set arraysize 100 sid=11 pid=12> select * from jj_one; 已选择100行。 统计信息 ---------------------------------------------------------- 6 consistent gets 1277 bytes sent via SQL*Net to client 372 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 100 rows processed
差别是很明显的,bytes sent via SQL*Net to client的数值,相差了6倍左右。
但这并不代表将批大小设置的越高,速度就越快,否则,Oracle直接将它设置为一个最大的值,不就行了,干吗还要让我们去自己调节呢! 行从Buffer cache中读出来后,会先缓存在PGA中(具体是在游标的运行时区),然后再传给客户端。如果批大小过大,在PGA、客户端占用的内存也会增大。而且,如果渐歇性的在网络上传输大量数据,对网络也会有一定影响。下面来观察一下批大小对PGA的影响: 在会话11中执行如下过程: declare type mid is table of t1.id%type; mid1 mid; cursor c is select id from t1; begin open c; loop fetch c bulk collect into mid1 limit 5000; exit when c%notfound; end loop; dbms_output.put_line(c%rowcount); close c; end; /
在另一会话中观察会话11的内存占用情况: sid=10 pid=11> @pga --此脚本下面有说明 输入 user 的值: 11 原值 7: and b.sid= &user 新值 7: and b.sid= 11
PGA Used PGA Alloc PGA Max ---------- ---------- ---------- 561508 779492 779492
然后将会话11中过程的批大小改为1:fetch c bulk collect into mid1 limit 5000; 再试一次
在另一会话观察会话11的PGA占用情况: sid=10 pid=11> @pga 输入 user 的值: 11 原值 7: and b.sid= &user 新值 7: and b.sid= 11
PGA Used PGA Alloc PGA Max ---------- ---------- ---------- 184388 250668 250668
批大小为5000时的内存占用,是批大小为1时的3倍左右。另外,测试表一定要大一些,我的测试表是1000000行,否则不容易看到结果。在10G中,可以得到基本相同的结果。
PGA.SQL脚本如下: --pga_by_process.sql: SELECT a.pga_used_mem "PGA Used", a.pga_alloc_mem "PGA Alloc", a.pga_max_mem "PGA Max" FROM v$process a,v$session b where a.addr = b.paddr and b.sid= &user / |
|