arraysize对逻辑读的影响

grassbell · 发表于 2004-7-12 13:43

arraysize规定了每次读返回多少rows，但是读是发生在block中的，而不是row上。所以由于arraysize的存在，使得很多block必须重复多读一次。所以调整arraysize的大小，不同程度上影响了逻辑读的性能。

[php]
SQL> create table t as select * from dba_objects;

表已创建。

SQL> exec show_space('T');

PL/SQL 过程已成功完成。

SQL> set serveroutput on;
SQL>  exec show_space('T');
Free Blocks.............................0
Total Blocks............................352
Total Bytes.............................2883584
Unused Blocks...........................13
Unused Bytes............................106496
Last Used Ext FileId....................3
Last Used Ext BlockId...................338
Last Used Block.........................3

PL/SQL 过程已成功完成。

SQL> set autotrace on
SQL> set arraysize 15 '设置为默认值'
SQL> select count(*) from t;

  COUNT(*)
----------
   24642

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1    TABLE ACCESS (FULL) OF 'T'

Statistics
----------------------------------------------------------
      0  recursive calls
      18  db block gets
      344  consistent gets '大小略等于352-13+1=340，Total Blocks-Unused Blocks+ 返回结果数'
      338  physical reads
      0  redo size
      366  bytes sent via SQL*Net to client
      425  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

SQL> set autotrace traceonly
SQL> select * from t;

已选择24642行。

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'

Statistics
----------------------------------------------------------
      0  recursive calls
      18  db block gets
   1963  consistent gets '大小略等于340-1+24642/15=1982,即本来的consistent gets+返回值/arraysize'
      338  physical reads
      0  redo size
2910735  bytes sent via SQL*Net to client
   182687  bytes received via SQL*Net from client
   1644  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   24642  rows processed

SQL> set arraysize 200  '改变arraysize为200'
SQL> select * from t;

已选择24642行。

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'

Statistics
----------------------------------------------------------
      0  recursive calls
      18  db block gets
      466  consistent gets '大小略等于340-1+24642/200=462,即本来的consistent gets+返回值/arraysize'
      314  physical reads
      0  redo size
2739088  bytes sent via SQL*Net to client
   14078  bytes received via SQL*Net from client
      125  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   24642  rows processed

SQL>
[/php]

grassbell · 发表于 2004-7-12 14:03

当然arraysize 也不是说设的越大越好，因为毕竟逻辑读还跟db_file_multiblock_read_count 和extent边界有关系。有时间再作个实验。

biti_rainy · 发表于 2004-7-12 14:29

你这说法是有问题的

因为数据是 buffer cache ----> PGA -------- > sqlnet ------> client

arraysize 决定了通过 sqlnet 传送的次数
至于从 buffer cache 到 PGA 这个过程，并不是一次只搜集 arraysize 条记录，比如游标循环单条 fetch ，但是从 buffer cache ---- > pga 可能是一次多个 block ，然后在 pga 内部做了一定数量的循环

grassbell · 发表于 2004-7-12 14:37

恩，在研究研究。

Yong Huang · 发表于 2004-7-13 12:00

最初由 biti_rainy 发布
[B]你这说法是有问题的

因为数据是 buffer cache ----> PGA -------- > sqlnet ------> client

arraysize 决定了通过 sqlnet 传送的次数
至于从 buffer cache 到 PGA 这个过程，并不是一次只搜集 arraysize 条记录，比如游标循环单条 fetch ，但是从 buffer cache ---- > pga 可能是一次多个 block ，然后在 pga 内部做了一定数量的循环 [/B]

I believe grassbell's test and Tom Kyte's same test show that fetching from buffer cache is indeed controlled by sqlplus array_size (among other things). Consistent gets is a concept related to buffer cache access, not PGA access. Actually I can't think of a way to count the number of times a server process reads from its own PGA.

Unduly small array size in sqlplus (or similar settings in other client environments) causes unnecessary block logical reads. I think asktom's site or his Expert One on One has quite a number of notes on this.

Yong Huang

biti_rainy · 发表于 2004-7-13 13:06

最初由 Yong Huang 发布
[B]

I believe grassbell's test and Tom Kyte's same test show that fetching from buffer cache is indeed controlled by sqlplus array_size (among other things). Consistent gets is a concept related to buffer cache access, not PGA access. Actually I can't think of a way to count the number of times a server process reads from its own PGA.

Unduly small array size in sqlplus (or similar settings in other client environments) causes unnecessary block logical reads. I think asktom's site or his Expert One on One has quite a number of notes on this.

Yong Huang [/B]

我想说明的是，sarraysize 是影响了 process 去buffer  cache 中搜集的记录，但实际上影响的应该是进程一次去搜索的  buffer 的数量，并不是说，如果 arraysize 为1，  一个buffer有5条记录就要重复读这个buffer5次

tom 的书或者站点我很早前都读过了，就 process  去 buffer 中搜集数据这个问题，我问过 tom，他说游标单条 fetch 的时候并不是说只fetch 一条记录（oci中有一个 prefetch呢），而是把少量(也许是2，一个preread,也许是其他数字)的block 中一次性都搜集到 pga 中了

看看我的测试

SQL> show parameters block_size

NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
db_block_size                      integer    8192

SQL> create table t(a char(2000));

Table created.

SQL> insert into t select 'a' from dba_objects where rownum < 101;

100 rows created.

插入100条记录，一个 block是装3条记录，也就是大约有34个block装数据

SQL> set arraysize 200
SQL> select * from  t;

100 rows selected.

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      39  consistent gets
      0  physical reads
      0  redo size
   2901  bytes sent via SQL*Net to client
      499  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      100  rows processed

这是正常的合理的结果

如果设置 arraysize  为1  ，按照楼主的猜想，那逻辑读应该是超过 100 了，因为每个block要读 3次
那我们来看看

SQL> set arraysize 1
SQL> select * from t;

100 rows selected.

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      71  consistent gets
      0  physical reads
      0  redo size
   8830  bytes sent via SQL*Net to client
      918  bytes received via SQL*Net from client
      51  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      100  rows processed

很显然，一个 block 并没有被重复地读多次
至于100条记录却只有 51  SQL*Net roundtrips to/from client
这个应该是 OCI  prefetch 的缘故

也就是说，arraysize 是控制了 process 一次返回给 client 的记录数，也可以认为是一次fetch搜集的记录数，但这个搜集过程是复杂的，并不是简单地说去 buffer  cache 中一次只读 arraysize 这么多条记录，PGA 应该是参与了缓冲的

这个问题最先是 tom 含糊地告诉了我一句就没有深入了，当然，说 arraysize 影响了 logical  reads 是没有错的，只不过这个logical  reads 并不是简单地对 buffer cache 进行 reads ，里面存在 preread 到 PGA 和在 pga 中进行读的问题

我以前曾经做过实验来期望证明类似的一个观点，但是实验现象违背了猜想就去问tom，他是告诉我读实际上是 buffer cache  --->  PGA (这是一个preread or  cache 的作用，直接导致我当时的实验失败)  ---- >  clinet

我也忘记具体的实验了，大体是在一个游标中对少量记录的循环读取，读一条记录sleep  30秒，在另一个session中同时也对这些记录进行处理，结果发现该循环没有受到那个session的影响，而preread的解释则说明了这个现象

biti_rainy · 发表于 2004-7-13 13:25

arraysize 1 和 arraysize 2 看起来是一样的，我认为这是跟 oci  prefetch 有关系
从3  开始就发生了变化

SQL> set arraysize 1
SQL> select * from t;

100 rows selected.

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      71  consistent gets
      0  physical reads
      0  redo size
   8830  bytes sent via SQL*Net to client
      918  bytes received via SQL*Net from client
      51  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      100  rows processed

SQL> set arraysize 2
SQL> select * from t;

100 rows selected.

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      71  consistent gets
      0  physical reads
      0  redo size
   8857  bytes sent via SQL*Net to client
   1038  bytes received via SQL*Net from client
      51  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      100  rows processed

SQL> set arraysize 3
SQL> select * from t;

100 rows selected.

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      67  consistent gets
      0  physical reads
      0  redo size
   6894  bytes sent via SQL*Net to client
      862  bytes received via SQL*Net from client
      35  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      100  rows processed

SQL> set arraysize 4
SQL> select * from t;

100 rows selected.

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      55  consistent gets
      0  physical reads
      0  redo size
   5832  bytes sent via SQL*Net to client
      763  bytes received via SQL*Net from client
      26  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      100  rows processed

SQL> set arraysize 5
SQL> select * from t;

100 rows selected.

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      51  consistent gets
      0  physical reads
      0  redo size
   5227  bytes sent via SQL*Net to client
      708  bytes received via SQL*Net from client
      21  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      100  rows processed

SQL>

实际上，arraysize 确实是控制了去搜集的记录条数，但若说这个是去 buffer  cache中可能重复地读取则是有问题的。只不过我认为 arraysize 真正地是在 pga中计算的， pga 是一个 preread 并缓冲的作用。

Yong Huang · 发表于 2004-7-14 12:40

You proved me wrong on one point. I thought arraysize was strictly the number of rows the server process reads out of buffer cache at a time. Looks like it's more complicated than that. The documentation says "[arraysize] Sets the number of rows--called a batch--that SQL*Plus will fetch from the database at one time". I guess they need to add the word "approximate" to it.

I don't know your Oracle version. On my 9.0.1.3.1 on Windows XP using 8k db_block_size and creating a table like yours (create table t (a char(2000))), I get this result:

arraysize - consistent gets
1 - 72
2 - 72
3 - 72
4 - 56
5 - 52
6 - 56
7 - 49
8 - 48
9 - 50
10 - 46
11 - 45
12 - 48

Yours apparently is different:

1 - 71
2 - 71
3 - 67
4 - 55
5 - 51

By the way, my old thinking was 100 consistent reads for 100 rows, not 300, regardless how many times the same block is read. I still believe with improper array size, most blocks are read more than once. Consistent gets are the count of reads on buffer cache, not PGA. There must be some prefetch in sqlplus behind the scenes, as you said. But that doesn't change the definition of consistent gets.

Yong Huang

biti_rainy · 发表于 2004-7-14 14:18

恩，想来想去，我也觉得 consistent gets 应该是计算的 buffer cache 中的

但实际上,arraysize 也好， prefetch /preread 也好，这里面还应该有更复杂的内容，只是我们难以去了解。虽然这并不影响我们对sql tuning 的理解

shiri512003 · 发表于 2009-9-7 22:11

这帖子要顶一下
很多方面oracle的实现并非资料上描述的那么简单，真正理解其中的东西，一方面要靠自己去试验，另外也要依赖于自己的知识背景，计算机方面和非计算机方面的东西

arraysize对逻辑读的影响

浏览过的版块