arraysize in sqlplus

When you access the data remotely in sqlplus, SET ARRAYSIZE can improve the performance of data access. What are the benefits we are getting by changing the default value for ARRAYSIZE? Following are the few benefits, by putting a higher value for arraysize.

I have used a table TEST with 10,36,416 records.

Faster execution: The records were returned faster after setting a higher ARRAYSIZE.

With the default value 15 for the ARRAYSIZE, it took 1.49 minute to complete the execution.

SQL> select * from test;

1036416 rows selected.

Elapsed: 00:01:49.86

But after setting a value to 5000, execution completed in less than 6 seconds

SQL> select * from test;

1036416 rows selected.

Elapsed: 00:00:05.84

So, why ARRAYSIZE making the execution faster? See below,

Consistent gets: When you use ARRAYSIZE 15, the default value, you are instructing sqlplus to to ask for only 15 records at a time. So, the database server will prepare a TCP/IP packet containing 15 records though it can process more. There could be more than 100 records in a block and the server is forced to process only 15 records at a time. This is causing the database re-read the blocks (more Consistent gets) to prepare the packets of 15 records. It is obvious from the ‘consistent gets’ statistics.

SQL> set linesize 132
SQL> col name for a30
SQL> select name,value,username,osuser,se.sql_hash_value
2 from v$mystat st,v$statname sn,v$session se
3 where st.STATISTIC# = sn.STATISTIC#
4 and se.sid=st.sid
5 and sn.name = ‘CPU used by this session’
6 and se.username is not null
7 order by value;

SQL>
SQL> set autotrace off
SQL>
SQL> set linesize 132
SQL> col name for a30
SQL> select name,value,username,osuser,se.sql_hash_value
2 from v$mystat st,v$statname sn,v$session se
3 where st.STATISTIC# = sn.STATISTIC#
4 and se.sid=st.sid
5 and sn.name = ‘CPU used by this session’
6 and se.username is not null
7 order by value;

With ARRAYSZIE 5000 it took 349 CPU cycles to read the table While it took 585 CPU cycles when the ARRAYSIZE is set to 15.

SQL> set linesize 132
SQL> col name for a30
SQL> select name,value,username,osuser,se.sql_hash_value
2 from v$mystat st,v$statname sn,v$session se
3 where st.STATISTIC# = sn.STATISTIC#
4 and se.sid=st.sid
5 and sn.name = ‘CPU used by this session’
6 and se.username is not null
7 order by value;

SQL>
SQL> set autotrace off
SQL>
SQL> set linesize 132
SQL> col name for a30
SQL> select name,value,username,osuser,se.sql_hash_value
2 from v$mystat st,v$statname sn,v$session se
3 where st.STATISTIC# = sn.STATISTIC#
4 and se.sid=st.sid
5 and sn.name = ‘CPU used by this session’
6 and se.username is not null
7 order by value;

So, server is consuming more CPU just because client is set a low value for ARRAYSIZE.

Network impact : Server is preparing a packet of 15 records and sending to the client, while a packet can accomadate more data. This will dramatically increase the SQL*Net roundtrips to/from client. Not a good news, this will not only increase the service time but also clog your network. Below test result shows a big difference in network round trips for the same amount of data.

So, applications can consume more server resources by keeping the default value for ARRAYSIZE in sqlplus. If you are using .Net, JAVA, perl, OCI, Python and PHP there are ways to manage the ARRAYSIZE, read the blog from TOAD experts.