ARRAY[SIZE] {n}
指示SQL*Plus一次从服务器获取多少行的数据。n的默认值是15取值范围是1至5000。当查询返回的数据较多时设置为较大的值可提高效率,但需占用更多的内存。假设ARRAYSIZE设为5,而某条SQL查询将返回100行数据,则SQL*Plus需分20次才能从服务器获取到所有的数据行。如下所示:
创建一个具有100行数据的表,并设置ARRAYSIZE为5:
SQL
>
set
autotrace
on
--
-使SQL*Plus显示统计信息
SQL > create table t_temp as select * from all_objects where rownum < 101 ;
SQL > set arraysize 5
SQL > create table t_temp as select * from all_objects where rownum < 101 ;
SQL > set arraysize 5
查询表中所有行
SQL
>
select
*
from
t_temp;
显示的统计信息如下:
![ContractedBlock.gif](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
0
recursive calls
9 db block gets
26 consistent gets
0 physical reads
0 redo size
15561 bytes sent via SQL * Net to client
2992 bytes received via SQL * Net from client
21 SQL * Net roundtrips to / from client
0 sorts (memory)
0 sorts ( disk )
100 rows processed
9 db block gets
26 consistent gets
0 physical reads
0 redo size
15561 bytes sent via SQL * Net to client
2992 bytes received via SQL * Net from client
21 SQL * Net roundtrips to / from client
0 sorts (memory)
0 sorts ( disk )
100 rows processed
参看统计信息中“SQL*Net roundtrips to/from client”这行数据。它说明了SQL*Plus需分20次从服务器获取所有数据行(加上发起查询的一次共21次)。
SQL
>
set
arraysize
100
修改ARRAYSIZE为100
SQL > select * from t_temp;
SQL > select * from t_temp;
显示统计信息如下:
![ContractedBlock.gif](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
0
recursive calls
9 db block gets
7 consistent gets
0 physical reads
0 redo size
13262 bytes sent via SQL * Net to client
503 bytes received via SQL * Net from client
2 SQL * Net roundtrips to / from client
0 sorts (memory)
0 sorts ( disk )
100 rows processed
9 db block gets
7 consistent gets
0 physical reads
0 redo size
13262 bytes sent via SQL * Net to client
503 bytes received via SQL * Net from client
2 SQL * Net roundtrips to / from client
0 sorts (memory)
0 sorts ( disk )
100 rows processed
再参看统计信息中的“SQL*Net roundtrips to/from client”这一行,说明SQL*Plus只需1次就可以从服务器获取到所有数据行(加上发起查询的一次共2次)。