The default ARRAYSIZE in SQL*PLus is 15. This will show up
during the analysis of the trace files by dividing the number of
rows returned by the number of SQLNet roundtrips as the default
size being using in the application. The ARRAYSIZE specifies the
number of rows to return when returning a set of values to the
application. Since this specifies the array size to return, it
directly impacts the number of round trips required to satisfy a
request for data.
I ran a series of select statements using the SQL*Plus interface
to a development environment and varied the ARRAYSIZE starting
at 5 and working up to 200 in increments of five. The following
chart shows the results for the example select statement:
SELECT * FROM dba_objects;
I utilized the SET AUTOTRACE ON STATISTICS and SET TIMING ON
commands to gather statistics on net round trips and time
required to satisfy the select statement.
As you can see the benefits for this select statement fall off
after an ARRAYSIZE of 50 is reached. A plot of the roundtrips
verses ARRAYSIZE tells why. In the following chart of roundtrips
verses ARRAYSIZE setting you see that at 50 the value for
roundtrips is 147 having reduced from the high of 1606 by 90%,
from array sizes 50 to 200 it only decreases to 42 a change of
only 10% (based on the original 1606) fewer roundtrips.
This indicates we need to adjust the ARRAYSIZE being used to
retrieve data from the Oracle database, by doing so we can
dramatically reduce the number of network roundtrips. In this
environment where our latency is very small, you can see the
change of ARRAYSIZE from a default value of 15 to a size of 50
reduced the time it took to retrieve the data from the select
statement from 6.03 seconds to 3.07 seconds, nearly a 50 percent
decrease in elapsed time and a reduction in network roundtrips
of over 90 percent. This was on a local database with 4 ms
latency imagine what a difference it would make when latency is
50 to 100 times this latency?
Where this can be of real benefit is in a Java situation. To set
the array size, use the setDefaultRowPrefetch method of the
OracleConnection class. However, the size should be determined
empirically by setting the size and running test runs because
this is a case of diminishing returns as the array size gets
larger. Also, the size of network buffers and packets need to be
factored in when sizing the buffers in SQL.
