Logical I/O(consistent get) and Arraysize relation with SQL*PLUS

“Set Arraysize” sets the number of rows that SQL*Plus will fetch from the database at one time. Valid values are 1 to 5000. The effectiveness of setting ARRAYSIZE depends on how well Oracle Database fills network packets and your network latency and throughput. Here is a small test case;

conn hr/hr

drop table test_arraysize purge ;
create table test_arraysize nologging as select * from all_source ;
set serveroutput on
exec show_space(upper(’test_arraysize’));

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 ………………… 9,976
Total Blocks………………………. 10,240
Total Bytes……………………….. 83,886,080
Total MBytes………………………. 80
Unused Blocks……………………… 121
Unused Bytes………………………. 991,232
Last Used Ext FileId……………….. 4
Last Used Ext BlockId………………. 11,529
Last Used Block……………………. 903

PL/SQL procedure successfully completed.

set timing on
set autotrace traceonly statistics
show arraysize

arraysize 15 <– default value

select text from test_arraysize where owner = ‘SYS’ ;

set arraysize 5
select text from test_arraysize where owner = ‘SYS’ ;

set arraysize 50
select text from test_arraysize where owner = ‘SYS’ ;

set arraysize 100
select text from test_arraysize where owner = ‘SYS’ ;

Here are the information given;

1- “select text from test_arraysize where owner = ‘SYS’” query returns 93963 rows for my case,
2- test_arraysize table Full Blocks 9976 and Unused Blocks 121, returned from show_space procedure
3- Formula is : Consistent Gets ~= (number of rows / arraysize) + (Full Blocks – Unused Blocks)

Lets test the formula from these information and above test scripts results;

** arraysize 15(default)
16155 consistent gets
Elapsed: 00:00:04.51

93963 rows / 15 arraysize = 6264,2
9976 Full Blocks – 121 Unused Blocks = 9855
6264,2 + 9855 = 16119,2

** arraysize 5
28233 consistent gets
Elapsed: 00:00:05.34

93963 rows / 5 arraysize = 18792,6
9976 Full Blocks – 121 Unused Blocks = 9855
18792,6 + 9855 = 28647,6

** arraysize 50
11819 consistent gets
Elapsed: 00:00:03.71

93963 rows / 50 arraysize = 1879,26
9976 Full Blocks – 121 Unused Blocks = 9855
1879,26 + 9855 = 11734,26

** arraysize 100
10904 consistent gets
Elapsed: 00:00:03.84

93963 rows / 100 arraysize = 939,63
9976 Full Blocks – 121 Unused Blocks = 9855
939,63 + 9855 = 10794,63

Responce time changes with the arraysize since logical I/O changes. Ok what about increasing arraysize to 500, what will be the impact;

set arraysize 15
select count(*) from test_arraysize where owner = ‘SYS’ ;

Elapsed: 00:00:02.82

set arraysize 500
select count(*) from test_arraysize where owner = ‘SYS’ ;

Elapsed: 00:00:02.96

Responce time got worser, overlarge sizes can easily take more SQL*Plus memory which may decrease overall performance. So optimum arraysize for your query is a test subject, no silver bullet again :)

Also in recent versions of SQL*Plus and Oracle Database, ARRAYSIZE may have little effect.

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.2.0

Refences Used : “consistent gets — Very puzzling” askTom thread
show_space procedure on asktom