set arraysize n Arraysize specifies how many rows SQL*Plus will fetch in a call. The number n can be between 1 and 5000. This will be demonstrated here. A table with 20 rows is created: create table t_20_rows as select object_name, object_id from all_objects where rownum < 21; Now, make SQL*Plus display statistics about SQL statements: set autotrace on Setting the arraysize to five: set arraysize 5 Selecting all rows from the table: select * from t_20_rows; Here's what autotrace reports: 0 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 1566 bytes sent via SQL*Net to client 532 bytes received via SQL*Net from client 5 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 20 rows processed We're particularyly interested in SQL*Net roundtrips to/from client. It records 5. This makes sense: there are 20 rows in the table, we get 5 (as specified with arraysize) at a time, so there are 4 roundtrips to get the rows and a 5th roundtrip that initiates the query. Now, let's see what happens if we decrease the arraysize down to 3: set arraysize 3 We'd expect 8 roundtrips: 7 to get the row plus one to initiate the query: 0 recursive calls 0 db block gets 10 consistent gets 0 physical reads 0 redo size 1930 bytes sent via SQL*Net to client 565 bytes received via SQL*Net from client 8 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 20 rows processed
Arraysize specifies how many rows SQL*Plus will fetch in a call. The number n can be between 1 and 5000. This will be demonstrated here. A table with 20 rows is created: create table t_20_rows
set arraysize n
Arraysize specifies how many rows SQL*Plus will fetch in a call. The number n can be between 1 and 5000. This will be demonstrated here. A table with 20 rows is created:
create table t_20_rows
as select object_name, object_id
from
all_objects
where
rownum < 21;
Now, make SQL*Plus display statistics about SQL statements:
set autotrace on
Setting the arraysize to five:
set arraysize 5
Selecting all rows from the table:
select * from t_20_rows;
Here's what autotrace reports:
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1566 bytes sent via SQL*Net to client
532 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
We're particularyly interested in SQL*Net roundtrips to/from client. It records 5. This makes sense: there are 20 rows in the table, we get 5 (as specified with arraysize) at a time, so there are 4 roundtrips to get the rows and a 5th roundtrip that initiates the query. Now, let's see what happens if we decrease the arraysize down to 3:
set arraysize 3
We'd expect 8 roundtrips: 7 to get the row plus one to initiate the query:
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1930 bytes sent via SQL*Net to client
565 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
create table t_20_rows
在sql plus里可以
我用sqlplus的命令模式就OK了