Trying to measure the effect of setting ARRAYSIZE
set termout off
set pagesize 0
set linesize 32000
set trimspool on
set flush off
set tab off
drop table tq84_export_performance_test;
create table tq84_export_performance_test (one_kb varchar(1024));
insert into tq84_export_performance_test
select
lpad(level, 1024, '*')
from
dual connect by level <= 10000;
commit;
------------------------------------
timing start "export 1 with default array size"
spool tq84_export_performance_test.out
select * from tq84_export_performance_test;
set termout on
timing stop
-- Repeat
set termout off
timing start "export 2 with default array size"
spool tq84_export_performance_test.out
select * from tq84_export_performance_test;
set termout on
timing stop
------------------------------------
set termout off
set arraysize 5000
timing start "export 1 with modified array size"
spool tq84_export_performance_test.out
select * from tq84_export_performance_test;
set termout on
timing stop
-- Repeat
set termout off
timing start "export 2 with modified array size"
spool tq84_export_performance_test.out
select * from tq84_export_performance_test;
set termout on
timing stop
------------------------------------
exit
Setting ARRAYSIZE to 1
Setting the ARRAYSIZE will fetch the first row and the next row in batches of two rows.
As explained by MOS Note 1265916.1, this is because SQL*Plus uses OCI which has a default prefetch value of 1.
Thus, the first fetch call returns 1 row becaue of this prefetch value. The next fetch calls will return 1 row for arraysize=1 and an additional row which is prefetched.
See also Bug 9103343.