请教：全表扫描时为什么逻辑读的量是表大小的N多倍？如下
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左右才对啊，多出来的逻辑读是出在哪里啊？
谢谢！
顶一下，别沉了！ show arraysize 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
页:
[1]