SQLplus 下行预取特性

通常情况下数据库引擎每访问一个数据块将产生至少一个逻辑读。而行预取与逻辑读息息相关。行预取是指当客户端从数据库获取数据时
可以采用单行也可以采用多行方式返回数据。当采用多行方式时，则会预取多条记录存储在客户端内存中以避免后续多次该数据的请求所致的
各种开销(LIO，PIO，NET IO)。一般预取行数越大，则所产生的开销越小，当达到临界值时其变化不大。
一、演示

[sql] view plain copy print ?

1. 1、创建演示表 
2.     scott@CNMMBO> select * from v$version where rownum<2; 
3.      
4.     BANNER 
5.     ---------------------------------------------------------------- 
6.     Oracle Database 10g Release 10.2.0.3.0 - 64bit Production 
7.     scott@CNMMBO> create table t as select * from dba_objects;   -->创建演示表 
8.      
9.     scott@CNMMBO> analyze table t compute statistics;            -->更新统计信息 
10.      
11.     scott@CNMMBO> exec show_space('T'); 
12.     Unformatted Blocks .....................               0 
13.     FS1 Blocks (0-25) ......................               0 
14.     FS2 Blocks (25-50) .....................               0 
15.     FS3 Blocks (50-75) .....................               0 
16.     FS4 Blocks (75-100).....................               0 
17.     Full Blocks ............................             690 
18.     Total Blocks............................             768    -->表段上的总块数768 
19.     Total Bytes.............................       6，291，456 
20.     Total MBytes............................               6 
21.     Unused Blocks...........................              58    -->未使用的块数58 
22.     Unused Bytes............................         475，136 
23.     Last Used Ext FileId....................              11 
24.     Last Used Ext BlockId...................          39，561 
25.     Last Used Block.........................              70 
26.      
27.     PL/SQL procedure successfully completed. 
28.  
29. 2、arraysize与consistent gets的关系 
30.     scott@CNMMBO> show arraysize                 -->arraysize的大小此时为默认值15 
31.     arraysize 15 
32.     scott@CNMMBO> set arraysize 2                -->修改arraysize的值为2 
33.     scott@CNMMBO> set autotrace traceonly stat;  -->启用autotrace    
34.     scott@CNMMBO> select * from t; 
35.      
36.     Statistics 
37.     ---------------------------------------------------------- 
38.               1  recursive calls 
39.               0  db block gets 
40.           25390  consistent gets                -->此时的consistent gets为25390 
41.               0  physical reads 
42.               0  redo size 
43.         6596152  bytes sent via SQL*Net to client 
44.          275844  bytes received via SQL*Net from client 
45.           25034  SQL*Net roundtrips to/from client 
46.               0  sorts (memory) 
47.               0  sorts (disk) 
48.           50066  rows processed 
49.      
50.     scott@CNMMBO> set arraysize 15              -->修改arraysize的值为15 
51.     scott@CNMMBO> select * from t; 
52.      
53.     Statistics 
54.     ---------------------------------------------------------- 
55.               0  recursive calls 
56.               0  db block gets 
57.            3992  consistent gets                -->此时的consistent gets为3992 
58.               0  physical reads 
59.               0  redo size 
60.         2625967  bytes sent via SQL*Net to client 
61.           37199  bytes received via SQL*Net from client 
62.            3339  SQL*Net roundtrips to/from client 
63.               0  sorts (memory) 
64.               0  sorts (disk) 
65.           50066  rows processed 
66.  
67. --从上面的演示可以看出由于设置了不同的array size值，导致在全表扫描(表上不存在索引)是产生了不同的consistent gets 
68. --当arraysize值变大时，consistent gets的值会减少 
69. --consistent gets 为3992从何而来? 
70.  
71.     scott@CNMMBO> set autotrace off; 
72.     scott@CNMMBO> select 50066/15+768-58 from dual;  -->number_of_rows/arraysize + total_blocks_read  
73.      
74.     50066/15+768-58 
75.     --------------- 
76.          4047.73333 
77.           
78. --从上面的计算可以看出4047若高于3992，因此该consistent gets接近于计算得到的值 
79.  
80.     scott@CNMMBO> set autotrace traceonly; 
81.     scott@CNMMBO> select count(*) from t;           -->聚合计算 
82.      
83.     Execution Plan 
84.     ---------------------------------------------------------- 
85.     Plan hash value: 2966233522 
86.      
87.     ------------------------------------------------------------------- 
88.     | Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     | 
89.     ------------------------------------------------------------------- 
90.     |   0 | SELECT STATEMENT   |      |     1 |   158   (1)| 00:00:02 | 
91.     |   1 |  SORT AGGREGATE    |      |     1 |            |          | 
92.     |   2 |   TABLE ACCESS FULL| T    | 50066 |   158   (1)| 00:00:02 | 
93.     ------------------------------------------------------------------- 
94.      
95.     Statistics 
96.     ---------------------------------------------------------- 
97.               0  recursive calls 
98.               0  db block gets 
99.             695  consistent gets                -->此时的consistent gets为695 
100.               0  physical reads 
101.               0  redo size 
102.             517  bytes sent via SQL*Net to client 
103.             492  bytes received via SQL*Net from client 
104.               2  SQL*Net roundtrips to/from client 
105.               0  sorts (memory) 
106.               0  sorts (disk) 
107.               1  rows processed 
108.      
109.     --此时走的是全表扫描，为什么一致读仅仅只有695?接近于表上的块数. 
110.     --考虑表段上的块数768-58=710 
111.     --注意此时的聚合仅仅是一个SINGLE CALL,SQL引擎内部使用行预取,每个块上将产生一次逻辑读，抽取其中的所有行     
112.      
113.     scott@CNMMBO> set autotrace off; 
114.     scott@CNMMBO> SELECT num_rows， 
115.       2    blocks blks， 
116.       3    empty_blocks em_blks， 
117.       4    avg_space， 
118.       5    chain_cnt， 
119.       6    avg_row_len， 
120.       7    round(num_rows / blocks) AS avg_rows_per_block， 
121.       8    last_analyzed lst_anly， 
122.       9    stale_stats 
123.      10  FROM   dba_tab_statistics 
124.      11  WHERE  table_name = 'T' 
125.      12  AND owner = 'SCOTT'; 
126.      
127.       NUM_ROWS       BLKS    EM_BLKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN AVG_ROWS_PER_BLOCK LST_ANLY  STA 
128.     ---------- ---------- ---------- ---------- ---------- ----------- ------------------ --------- --- 
129.          50066        710         58        860          0          97                 71 12-JAN-12 NO 
130.      
131.     scott@CNMMBO> select 71*710 from dual; 
132.      
133.         71*710 
134.     ----------            -->值50410与50066相接近 
135.          50410 
136.  
137. 3、arraysize与全表扫描     
138.     scott@CNMMBO> set autotrace traceonly stat; 
139.     scott@CNMMBO> set arraysize 100       -->调整arraysize值为100 
140.     scott@CNMMBO> select * from t; 
141.      
142.     Statistics 
143.     ---------------------------------------------------------- 
144.               0  recursive calls 
145.               0  db block gets 
146.            1185  consistent gets 
147.               0  physical reads 
148.               0  redo size 
149.         2106796  bytes sent via SQL*Net to client 
150.            5992  bytes received via SQL*Net from client 
151.             502  SQL*Net roundtrips to/from client 
152.               0  sorts (memory) 
153.               0  sorts (disk) 
154.           50066  rows processed 
155.      
156.     scott@CNMMBO> set arraysize 200       -->调整arraysize值为200   
157.     scott@CNMMBO> select * from t; 
158.      
159.     Statistics 
160.     ---------------------------------------------------------- 
161.               0  recursive calls 
162.               0  db block gets 
163.             940  consistent gets 
164.               0  physical reads 
165.               0  redo size 
166.         2061046  bytes sent via SQL*Net to client 
167.            3242  bytes received via SQL*Net from client 
168.             252  SQL*Net roundtrips to/from client 
169.               0  sorts (memory) 
170.               0  sorts (disk) 
171.           50066  rows processed 
172.      
173.     scott@CNMMBO> set arraysize 2000      -->调整arraysize值为2000,此时访问方式为全表扫描 
174.     scott@CNMMBO> select * from t; 
175.      
176.     Statistics 
177.     ---------------------------------------------------------- 
178.               0  recursive calls 
179.               0  db block gets 
180.             721  consistent gets 
181.               0  physical reads 
182.               0  redo size 
183.         2019871  bytes sent via SQL*Net to client 
184.             767  bytes received via SQL*Net from client 
185.              27  SQL*Net roundtrips to/from client 
186.               0  sorts (memory) 
187.               0  sorts (disk) 
188.           50066  rows processed 
189.      
190.     scott@CNMMBO> set arraysize 4000      -->调整arraysize值为4000,此时访问方式为全表扫描 
191.     scott@CNMMBO> select * from t; 
192.      
193.     Statistics 
194.     ---------------------------------------------------------- 
195.               0  recursive calls 
196.               0  db block gets 
197.             708  consistent gets 
198.               0  physical reads 
199.               0  redo size 
200.         2017492  bytes sent via SQL*Net to client 
201.             624  bytes received via SQL*Net from client 
202.              14  SQL*Net roundtrips to/from client 
203.               0  sorts (memory) 
204.               0  sorts (disk) 
205.           50066  rows processed  
206.      
207.     scott@CNMMBO> set arraysize 20000     -->调整arraysize超出范围 
208.     SP2-0267: arraysize option 20000 out of range (1 through 5000) 
209.     scott@CNMMBO> set arraysize 5000      -->调整arraysize到最大值5000,此时访问方式为全表扫描 
210.     scott@CNMMBO> select * from t; 
211.      
212.     Statistics 
213.     ---------------------------------------------------------- 
214.               0  recursive calls 
215.               0  db block gets 
216.             706  consistent gets 
217.               0  physical reads 
218.               0  redo size 
219.         2017126  bytes sent via SQL*Net to client 
220.             602  bytes received via SQL*Net from client 
221.              12  SQL*Net roundtrips to/from client 
222.               0  sorts (memory) 
223.               0  sorts (disk) 
224.           50066  rows processed  
225.                  
226. --从上面观察可以看出当arraysize不但增大的情形下，其逻辑读相应会减少，当arraysize达到一定值之后逻辑读减少的幅度变化不大 
227.        
228.     scott@CNMMBO> set autotrace traceonly;  
229.     scott@CNMMBO> show arraysize  
230.     arraysize 1 
231.     scott@CNMMBO> select * from emp; 
232.      
233.     Execution Plan 
234.     ---------------------------------------------------------- 
235.     Plan hash value: 3956160932 
236.      
237.     -------------------------------------------------------------------------- 
238.     | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
239.     -------------------------------------------------------------------------- 
240.     |   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 | 
241.     |   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 | 
242.     -------------------------------------------------------------------------- 
243.      
244.     Statistics 
245.     ---------------------------------------------------------- 
246.               0  recursive calls 
247.               0  db block gets 
248.              14  consistent gets 
249.               0  physical reads 
250.               0  redo size 
251.            2633  bytes sent via SQL*Net to client 
252.             558  bytes received via SQL*Net from client 
253.               8  SQL*Net roundtrips to/from client 
254.               0  sorts (memory) 
255.               0  sorts (disk) 
256.              14  rows processed      
257. --从上面的测试可以看出在全表扫描时，当arraysize为1时，表上的一条记录将产生一次consistent gets   
258.               
259. 3、arraysize与索引扫描 
260.     scott@CNMMBO> alter table t add constraint t_pk primary key(object_id);  -->为表添加索引 
261.      
262.     scott@CNMMBO> analyze table t compute statistics;                        -->更新统计信息 
263.      
264.     scott@CNMMBO> set autotrace traceonly;  
265.     scott@CNMMBO> select count(*) from t; 
266.      
267.     Execution Plan 
268.     ---------------------------------------------------------- 
269.     Plan hash value: 454320086 
270.      
271.     ---------------------------------------------------------------------- 
272.     | Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     | 
273.     ---------------------------------------------------------------------- 
274.     |   0 | SELECT STATEMENT      |      |     1 |    24   (0)| 00:00:01 | 
275.     |   1 |  SORT AGGREGATE       |      |     1 |            |          | 
276.     |   2 |   INDEX FAST FULL SCAN| T_PK | 50066 |    24   (0)| 00:00:01 | 
277.     ---------------------------------------------------------------------- 
278.      
279.     Statistics 
280.     ---------------------------------------------------------- 
281.               1  recursive calls 
282.               0  db block gets 
283.             111  consistent gets 
284.               0  physical reads 
285.               0  redo size 
286.             517  bytes sent via SQL*Net to client 
287.             492  bytes received via SQL*Net from client 
288.               2  SQL*Net roundtrips to/from client 
289.               0  sorts (memory) 
290.               0  sorts (disk) 
291.               1  rows processed 
292.                
293.     --从上面的执行计划和buffers可以看出此时走了索引全扫描,且consistent gets的大小为111.即优化器基于索引统计得到总行数,而不是基于表. 
294.      
295.     scott@CNMMBO> exec show_space('T_PK'，'SCOTT'，'INDEX');         -->查看索引段的使用情况 
296.     Unformatted Blocks .....................               0 
297.     FS1 Blocks (0-25) ......................               0 
298.     FS2 Blocks (25-50) .....................               1 
299.     FS3 Blocks (50-75) .....................               0 
300.     FS4 Blocks (75-100).....................               0 
301.     Full Blocks ............................             104 
302.     Total Blocks............................             120       -->索引段总块数120       
303.     Total Bytes.............................         983，040 
304.     Total MBytes............................               0 
305.     Unused Blocks...........................               5       -->空闲块数为5 
306.     Unused Bytes............................          40，960 
307.     Last Used Ext FileId....................              11 
308.     Last Used Ext BlockId...................          39，745 
309.     Last Used Block.........................               3 
310.      
311.     PL/SQL procedure successfully completed. 
312.  
313.     scott@CNMMBO> show arraysize  
314.     arraysize 5000   
315.     scott@CNMMBO> select count(*) from t; 
316.      
317.     Statistics 
318.     ---------------------------------------------------------- 
319.               0  recursive calls 
320.               0  db block gets 
321.             111  consistent gets 
322.               0  physical reads 
323.               0  redo size 
324.             517  bytes sent via SQL*Net to client 
325.             492  bytes received via SQL*Net from client 
326.               2  SQL*Net roundtrips to/from client 
327.               0  sorts (memory) 
328.               0  sorts (disk) 
329.               1  rows processed 
330.      
331.     scott@CNMMBO> set arraysize 2000 
332.     scott@CNMMBO> select count(*) from t; 
333.      
334.     Statistics 
335.     ---------------------------------------------------------- 
336.               0  recursive calls 
337.               0  db block gets 
338.             111  consistent gets 
339.               0  physical reads 
340.               0  redo size 
341.             517  bytes sent via SQL*Net to client 
342.             492  bytes received via SQL*Net from client 
343.               2  SQL*Net roundtrips to/from client 
344.               0  sorts (memory) 
345.               0  sorts (disk) 
346.               1  rows processed 
347.      
348.     scott@CNMMBO> set arraysize 15 
349.     scott@CNMMBO> select count(*) from t; 
350.      
351.     Statistics 
352.     ---------------------------------------------------------- 
353.               0  recursive calls 
354.               0  db block gets 
355.             111  consistent gets 
356.               0  physical reads 
357.               0  redo size 
358.             517  bytes sent via SQL*Net to client 
359.             492  bytes received via SQL*Net from client 
360.               2  SQL*Net roundtrips to/from client 
361.               0  sorts (memory) 
362.               0  sorts (disk) 
363.               1  rows processed 
364.      
365.     scott@CNMMBO> set arraysize 1 
366.     scott@CNMMBO> select count(*) from t; 
367.      
368.     Statistics 
369.     ---------------------------------------------------------- 
370.               0  recursive calls 
371.               0  db block gets 
372.             111  consistent gets 
373.               0  physical reads 
374.               0  redo size 
375.             517  bytes sent via SQL*Net to client 
376.             492  bytes received via SQL*Net from client 
377.               2  SQL*Net roundtrips to/from client 
378.               0  sorts (memory) 
379.               0  sorts (disk) 
380.               1  rows processed 
381.                
382. --从上面的测试可知，走索引扫描之后的聚合与arraysize无关 

1、创建演示表 scott@CNMMBO> select * from v$version where rownum<2; BANNER ---------------------------------------------------------------- Oracle Database 10g Release 10.2.0.3.0 - 64bit Production scott@CNMMBO> create table t as select * from dba_objects; -->创建演示表 scott@CNMMBO> analyze table t compute statistics; -->更新统计信息 scott@CNMMBO> exec show_space('T'); Unformatted Blocks ..................... 0 FS1 Blocks (0-25) ...................... 0 FS2 Blocks (25-50) ..................... 0 FS3 Blocks (50-75) ..................... 0 FS4 Blocks (75-100)..................... 0 Full Blocks ............................ 690 Total Blocks............................ 768 -->表段上的总块数768 Total Bytes............................. 6，291，456 Total MBytes............................ 6 Unused Blocks........................... 58 -->未使用的块数58 Unused Bytes............................ 475，136 Last Used Ext FileId.................... 11 Last Used Ext BlockId................... 39，561 Last Used Block......................... 70 PL/SQL procedure successfully completed. 2、arraysize与consistent gets的关系 scott@CNMMBO> show arraysize -->arraysize的大小此时为默认值15 arraysize 15 scott@CNMMBO> set arraysize 2 -->修改arraysize的值为2 scott@CNMMBO> set autotrace traceonly stat; -->启用autotrace scott@CNMMBO> select * from t; Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 25390 consistent gets -->此时的consistent gets为25390 0 physical reads 0 redo size 6596152 bytes sent via SQL*Net to client 275844 bytes received via SQL*Net from client 25034 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 50066 rows processed scott@CNMMBO> set arraysize 15 -->修改arraysize的值为15 scott@CNMMBO> select * from t; Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3992 consistent gets -->此时的consistent gets为3992 0 physical reads 0 redo size 2625967 bytes sent via SQL*Net to client 37199 bytes received via SQL*Net from client 3339 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 50066 rows processed --从上面的演示可以看出由于设置了不同的array size值，导致在全表扫描(表上不存在索引)是产生了不同的consistent gets --当arraysize值变大时，consistent gets的值会减少 --consistent gets 为3992从何而来? scott@CNMMBO> set autotrace off; scott@CNMMBO> select 50066/15+768-58 from dual; -->number_of_rows/arraysize + total_blocks_read 50066/15+768-58 --------------- 4047.73333 --从上面的计算可以看出4047若高于3992，因此该consistent gets接近于计算得到的值 scott@CNMMBO> set autotrace traceonly; scott@CNMMBO> select count(*) from t; -->聚合计算 Execution Plan ---------------------------------------------------------- Plan hash value: 2966233522 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 158 (1)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T | 50066 | 158 (1)| 00:00:02 | ------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 695 consistent gets -->此时的consistent gets为695 0 physical reads 0 redo size 517 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed --此时走的是全表扫描，为什么一致读仅仅只有695?接近于表上的块数. --考虑表段上的块数768-58=710 --注意此时的聚合仅仅是一个SINGLE CALL,SQL引擎内部使用行预取,每个块上将产生一次逻辑读，抽取其中的所有行 scott@CNMMBO> set autotrace off; scott@CNMMBO> SELECT num_rows， 2 blocks blks， 3 empty_blocks em_blks， 4 avg_space， 5 chain_cnt， 6 avg_row_len， 7 round(num_rows / blocks) AS avg_rows_per_block， 8 last_analyzed lst_anly， 9 stale_stats 10 FROM dba_tab_statistics 11 WHERE table_name = 'T' 12 AND owner = 'SCOTT'; NUM_ROWS BLKS EM_BLKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_ROWS_PER_BLOCK LST_ANLY STA ---------- ---------- ---------- ---------- ---------- ----------- ------------------ --------- --- 50066 710 58 860 0 97 71 12-JAN-12 NO scott@CNMMBO> select 71*710 from dual; 71*710 ---------- -->值50410与50066相接近 50410 3、arraysize与全表扫描 scott@CNMMBO> set autotrace traceonly stat; scott@CNMMBO> set arraysize 100 -->调整arraysize值为100 scott@CNMMBO> select * from t; Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1185 consistent gets 0 physical reads 0 redo size 2106796 bytes sent via SQL*Net to client 5992 bytes received via SQL*Net from client 502 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 50066 rows processed scott@CNMMBO> set arraysize 200 -->调整arraysize值为200 scott@CNMMBO> select * from t; Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 940 consistent gets 0 physical reads 0 redo size 2061046 bytes sent via SQL*Net to client 3242 bytes received via SQL*Net from client 252 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 50066 rows processed scott@CNMMBO> set arraysize 2000 -->调整arraysize值为2000,此时访问方式为全表扫描 scott@CNMMBO> select * from t; Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 721 consistent gets 0 physical reads 0 redo size 2019871 bytes sent via SQL*Net to client 767 bytes received via SQL*Net from client 27 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 50066 rows processed scott@CNMMBO> set arraysize 4000 -->调整arraysize值为4000,此时访问方式为全表扫描 scott@CNMMBO> select * from t; Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 708 consistent gets 0 physical reads 0 redo size 2017492 bytes sent via SQL*Net to client 624 bytes received via SQL*Net from client 14 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 50066 rows processed scott@CNMMBO> set arraysize 20000 -->调整arraysize超出范围 SP2-0267: arraysize option 20000 out of range (1 through 5000) scott@CNMMBO> set arraysize 5000 -->调整arraysize到最大值5000,此时访问方式为全表扫描 scott@CNMMBO> select * from t; Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 706 consistent gets 0 physical reads 0 redo size 2017126 bytes sent via SQL*Net to client 602 bytes received via SQL*Net from client 12 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 50066 rows processed --从上面观察可以看出当arraysize不但增大的情形下，其逻辑读相应会减少，当arraysize达到一定值之后逻辑读减少的幅度变化不大 scott@CNMMBO> set autotrace traceonly; scott@CNMMBO> show arraysize arraysize 1 scott@CNMMBO> select * from emp; Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 14 consistent gets 0 physical reads 0 redo size 2633 bytes sent via SQL*Net to client 558 bytes received via SQL*Net from client 8 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed --从上面的测试可以看出在全表扫描时，当arraysize为1时，表上的一条记录将产生一次consistent gets 3、arraysize与索引扫描 scott@CNMMBO> alter table t add constraint t_pk primary key(object_id); -->为表添加索引 scott@CNMMBO> analyze table t compute statistics; -->更新统计信息 scott@CNMMBO> set autotrace traceonly; scott@CNMMBO> select count(*) from t; Execution Plan ---------------------------------------------------------- Plan hash value: 454320086 ---------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 24 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| T_PK | 50066 | 24 (0)| 00:00:01 | ---------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 111 consistent gets 0 physical reads 0 redo size 517 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed --从上面的执行计划和buffers可以看出此时走了索引全扫描,且consistent gets的大小为111.即优化器基于索引统计得到总行数,而不是基于表. scott@CNMMBO> exec show_space('T_PK'，'SCOTT'，'INDEX'); -->查看索引段的使用情况 Unformatted Blocks ..................... 0 FS1 Blocks (0-25) ...................... 0 FS2 Blocks (25-50) ..................... 1 FS3 Blocks (50-75) ..................... 0 FS4 Blocks (75-100)..................... 0 Full Blocks ............................ 104 Total Blocks............................ 120 -->索引段总块数120 Total Bytes............................. 983，040 Total MBytes............................ 0 Unused Blocks........................... 5 -->空闲块数为5 Unused Bytes............................ 40，960 Last Used Ext FileId.................... 11 Last Used Ext BlockId................... 39，745 Last Used Block......................... 3 PL/SQL procedure successfully completed. scott@CNMMBO> show arraysize arraysize 5000 scott@CNMMBO> select count(*) from t; Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 111 consistent gets 0 physical reads 0 redo size 517 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed scott@CNMMBO> set arraysize 2000 scott@CNMMBO> select count(*) from t; Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 111 consistent gets 0 physical reads 0 redo size 517 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed scott@CNMMBO> set arraysize 15 scott@CNMMBO> select count(*) from t; Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 111 consistent gets 0 physical reads 0 redo size 517 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed scott@CNMMBO> set arraysize 1 scott@CNMMBO> select count(*) from t; Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 111 consistent gets 0 physical reads 0 redo size 517 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed --从上面的测试可知，走索引扫描之后的聚合与arraysize无关

二、分析
  假定使用select * from t发布查询，此时表上每个数据块为16行，且arraysize 的设置为15(缺省值)，
  则
    第一次fetch， 读第一块15行，此时产生第1次consistent gets
    第二次fetch， 读第一块1行，此时产生第2次consistent gets，读第二块14行，此时产生第3次consistent gets
    第三次fetch， 读第二块2行，此时产生第4次consistent gets，读第三块13行，此时产生第5次consistent gets
     依此内推
  假定此时表上每个数据块为10行，即数据块上的行数小于arraysize的情形(10<15)
     第一次fetch，读第一块10行，此时产生第1次consistent gets
           读第二块5行，此时产生第2次consistent gets
     第二次fetch，读第二块5行，此时产生第3次consistent gets
           读第三块10行，此时产生第4次consistent gets
     第三次fetch，读第四块10行，此时产生第5次consistent gets
           读第五块5行，此时产生第6次consistent gets
    依此内推
   
  当arraysize设置为n(15)时，则Oracle从buffer一次fetch n(15)行，然后将数据返回给客户端.接下来Oracle会再次从buffer中fetch第二次
  ，返回下一个n(15)行 
  由上可知，在fetch期间可能会产生多次consistent gets。
 
  当设置了较大的arraysize，从演示中可以看出SQL*Net的sent与received也随之减少。即请求时往返的次数明显降低。  
三、总结
  1、arraysize参数用于控制返回给客户端预取的行数，缺省值为15
  2、逻辑读为consistent gets + db block gets，为简化描述，下面直接使用逻辑读
  3、当行预取值设定为1时，对于全表扫描，每返回一个行需要产生一个逻辑读，如果设定的行预取值大于单块存储的行数，则逻辑读接近于块数
  4、逻辑读的值随arraysize的增大而减小，当达到临界值(即略大于表上单块存储的行束)之后其变化趋势不大
  5、SQL*Net的sent与received的值会随着arraysize的增大而减小
  6、逻辑读通常会多于一次，见第二点的分析
  7、对于聚合运算的情形(无索引)，SQL引擎内部使用行预取。即每次SQL引擎访问一个块的时候，抽取其中的所有行。其逻辑读接近于块数
  8、在使用索引的情形下，consistent gets与arraysize无关