请教：全表扫描时为什么逻辑读的量是表大小的N多倍？如下

hanghangyang

10:12:39 >SELECT segment_name, segment_type, bytes FROM user_segments WHERE segment_name = 'T';

SEGMENT_NA SEGMENT_TYPE            BYTES
---------- ------------------ ----------
T          TABLE                38797312

10:10:49 >SELECT * FROM t;

365824 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 11432 |   948K|    44   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    | 11432 |   948K|    44   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      28733  consistent gets
       4641  physical reads
          0  redo size
   37710983  bytes sent via SQL*Net to client
     268737  bytes received via SQL*Net from client
      24390  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     365824  rows processed

10:10:56 >SELECT * FROM t;

365824 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 11432 |   948K|    44   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    | 11432 |   948K|    44   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      28733  consistent gets
          0  physical reads
          0  redo size
   37710983  bytes sent via SQL*Net to client
     268737  bytes received via SQL*Net from client
      24390  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     365824  rows processed
================================================

请教一下：这张T表只有37M的大小，第一次全表扫描时物理读36M左右，没有问题，但是逻辑读为什么确实200多M，第二次物理读为零，逻辑读依然为200多M，按理来说T表只有37M，那么逻辑读也应该只有37M左右才对啊，多出来的逻辑读是出在哪里啊？

谢谢！

hanghangyang

顶一下，别沉了！

hanghangyang

show arraysize

Maclean Liu(刘相兵

FYI

2. Arraysize
If the application is using large amount of data, consider increasing the arraysize in the application.  If small arraysize is used to fetch the data, then the query will use multiple fetch calls, each of these will wait for the ‘SQL*net message to client’ event. With a small arraysize and a large amount of data, the number of waits can become significant.
If running SQL from sqlplus, the arraysize can be increased using the sqlplus “set” command:
set arraysize 1000
From the raw 10046 tracefile, the fetch buffer size or the arraysize can be seen from the r (rows) of the fetch line:
FETCH #18446744071490060104:c=0,e=17086,p=2,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=872636971,tim=28473178755694
Here the r=1 is indicating arraysize of 1.  1 may be too low; so try increasing it if the wait for ‘SQL*net message to client’ events is large.
There is more information on arraysize and how to increase it in different applications in the following document:
Document 1419023.1 Row Prefetching and its impact on logical reads and fetch calls