详述逻辑读与arraysize的关系

原创作品，允许转载，转载时请务必以超链接形式标明文章  原始出处 、作者信息和本声明。否则将追究法律任。

http://5073392.blog.51cto.com/5063392/1308191

我们都知道,数据块是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表中定位块的这个过程。

--创建测试表

SQL> create table t (id int,name varchar2(10));

Table created.

--插入100行数据

SQL> begin

2 for i in 1..100 loop

3 insert into t values (i,'aaa');

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed.

--查看表中行分布

SQL> select bno,max(id),min(id) from (select dbms_rowid.rowid_block_number(rowid) bno, id from t) group by bno;

BNO MAX(ID) MIN(ID)

---------- ---------- ----------

91545 100 1

可以看到这个表的所有数据全部存放在91545块上

--查询一行观察执行计划统计信息

SQL> set autotrace trace stat;

SQL> select * from t where id <= 1;

Statistics

----------------------------------------------------------

28 recursive calls

0 db block gets

8 consistent gets --逻辑读为10

0 physical reads

0 redo size

590 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

--在执行一次

SQL> select * from t where id <= 1;

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

4 consistent gets --逻辑读减小到4

0 physical reads

0 redo size

590 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

--把id换成1到15的值，逻辑读和上面结果一模一样，下面给出了id=12的比较

SQL> select * from t where id <= 12;

12 rows selected.

Statistics

----------------------------------------------------------

5 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

728 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

12 rows processed

SQL> /

12 rows selected.

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

728 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

12 rows processed

--当把id换成16时逻辑读多了1 bytes sent via SQL*Net to client也多了200多

SQL> select * from t where id <=16;

16 rows selected.

Statistics

----------------------------------------------------------

5 recursive calls

0 db block gets

9 consistent gets

0 physical reads

0 redo size

896 bytes sent via SQL*Net to client

535 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

16 rows processed

SQL> /

16 rows selected.

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

5 consistent gets

0 physical reads

0 redo size

896 bytes sent via SQL*Net to client

535 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

16 rows processed

从上面的实验看出，在读取数据的时候，逻辑读的次数与读取的数据量有很大的关系，oracle是通过批读取数据行的，批越大逻辑读就越少，而且服务端和客户端交互的次数也越少，由网络传输的数据也可以减少，下面继续实验

--批默认值

SQL> show arraysize;

arraysize 15

--设置批大小为1

SQL> select * from t;

100 rows selected.

Statistics

----------------------------------------------------------

4 recursive calls

0 db block gets

57 consistent gets

0 physical reads

0 redo size

10946 bytes sent via SQL*Net to client

1063 bytes received via SQL*Net from client

51 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

100 rows processed

--设置批大小为100

SQL> set arraysize 100

SQL> select * from t;

100 rows selected.

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

1832 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

100 rows processed

可以看出逻辑的相差了10多倍，而bytes sent via SQL*Net to client也相差了6倍左右，继续下面的实验

--插入数据

SQL> begin

2 for i in 501..2000 loop

3 insert into t values (i,'aaa');

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed.

--查看表中行分布

SQL> select bno,max(id),min(id),count(*) from (select dbms_rowid.rowid_block_number(rowid) bno, id from t) group by bno;

BNO MAX(ID) MIN(ID) COUNT(*)

---------- ---------- ---------- ----------

91545 567 1 567

91548 2000 1686 315

91546 1126 568 559

91547 1685 1127 559

这时候数据存入了4个快中，默认arraysize为15，那么从91545这个块查询的次数就是567/15=37.8 我们估算约38次，验证下

SQL> select * from t where rownum < 568 --读取91548块的所有行

567 rows selected.

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

41 consistent gets --和我们估算相差很接近

0 physical reads

0 redo size

14781 bytes sent via SQL*Net to client

931 bytes received via SQL*Net from client

39 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

567 rows processed

比我们估算的多了3个逻辑读，如果将批大小改为517，让oracle一次性读出这个块上的所有的行，根据上面的结果推算应该有4个逻辑读，是否是这样呢？

SQL> set arraysize 567

SQL> /

567 rows selected.

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

7899 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

567 rows processed

实验看出刚好为4个，那么这3个逻辑读究竟用在什么地方了呢？继续下面实验

--重建表别且插入数据

SQL> create table t1 (id int, name varchar2(10));

Table created.

SQL> insert into t1 values (1,'aaa');

1 row created.

SQL> commit;

Commit complete.

--查看行分布

SQL> select bno,max(id),min(id),count(*) from (select dbms_rowid.rowid_block_number(rowid) bno, id from t1) group by bno;

BNO MAX(ID) MIN(ID) COUNT(*)

---------- ---------- ---------- ----------

91697 1 1 1

--查看区分布

SQL> select extent_id,file_id,block_id,blocks from dba_extents where segment_name='T1' and owner='SYS';

EXTENT_ID FILE_ID BLOCK_ID BLOCKS

---------- ---------- ---------- ----------

0 1 91696 8

--查看高水位点

SQL> select header_file,header_block from dba_segments where segment_name='T1' and owner='SYS';

HEADER_FILE HEADER_BLOCK

----------- ------------

1 91696

SQL> alter system dump datafile 1 block 91696

在dump文件中找到Highwater:: 0x00416632行，查出高水位点

SQL> select dbms_utility.data_block_address_block(to_number('00416632','xxxxxxxx')) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(TO_NUMBER('00416632','XXXXXXXX'))

-----------------------------------------------------------------------

91698

得出91696为数据段头部分，91697 91698为数据块

--执行查询

SQL> select * from t1 where rownum=1;

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

594 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

逻辑读是3，难道这里刚好读到了这3个块，我只是推测，希望哪位大师帮我解说下，最好能够通过实验证明下！！！！！！！！！！

将批大小设置的越高，速度就越快，否则，Oracle直接将它设置为一个最大的值，我们知道一个session连接到实例会为这个session分配PGA，当行从Buffercache中读出来后，会先缓存在PGA中（具体是在游标的运行时区），然后再传给客户端。如

果批大小过大，在PGA、客户端占用的内存也会增大。而且，如果渐歇性的在网络上传输大量数据，对网

络也会有一定影响。下面来观察一下批大小对PGA的影响：

--创建一个100多万数据的测试表

SQL> create table t2 as select * from dba_objects;

Table created.

SQL> insert into t2 select * from t2;

73884 rows created.

SQL> /

147768 rows created.

SQL> /

295536 rows created.

SQL> /

591072 rows created.

SQL> /

1182144 rows created.

SQL> commit;

Commit complete.

--退出会话重新登录下，查询下sid

SQL> select distinct sid from v$mystat;

SID

----------

140

--在另外一个会话查询sid=140分配的PGA

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= 140

PGA Used PGA Alloc PGA Max

---------- ---------- ----------

711016 1376576 1376576

--返回sid=140的会话执行下面过程

declare

type test is table of t2.object_id%type;

v_test test;

cursor c is select object_id from t2;

begin

open c;

loop

fetch c bulk collect into v_test limit 500; --通过游标将批大小限制到500

exit when c%notfound;

end loop;

dbms_output.put_line(c%rowcount);

close c;

end;

/

我的这个表数据有点大，执行时间比较长，在执行完成后重新查看PGA大小还是一样

--将批大小改为5000

declare

type test is table of t2.object_id%type;

v_test test;

cursor c is select object_id from t2;

begin

open c;

loop

fetch c bulk collect into v_test limit 5000;

exit when c%notfound;

end loop;

dbms_output.put_line(c%rowcount);

close c;

end;

/

第一个执行我花了很久很久。。。这一个大概2秒钟，执行速度非常快。

查看PGA使用如下。可以看到扩大了一倍多。

PGA Used PGA Alloc PGA Max

---------- ---------- ----------

1038872 2031936 2949440

上面的实验很好的证明了设置arrraysize可以降低逻辑读，使用它还需要根据实际情况进行设置。

整理自ITPUB 晶晶小妹的blog