Oracle Client Server Performance – Arraysize Tuning

The ARRAYSIZE parameter controls the number of rows that can be returned from disk in a single network transfer. If you set ARRAYSIZE correctly, you can substantially reduce the number of network packets. An adequate value for this parameter allows multiple SELECT, INSERT, and UPDATE operations to be performed in a single database server access.

To figure out how many packets need to be transferred for a particular SQL statement or set of statements (for example, the series of network round-trips described in the previous section), divide the number of required fetches by the ARRAYSIZE in effect for your current database interface tool.

Note: Our benchmarks indicate that increasing ARRAYSIZE when you are running in a unitary environment has little or no effect on performance.

You can increase ARRAYSIZE, or its equivalent parameter, in a number of Oracle tools:

• SQL*Forms: Uses array processing by default.

• Precompiler 3GL languages (e.g., Pro*C): Support array processing implicitly. For example:

  BEGIN DECLARE
  FLOAT emp_no[100];
  CHAR emp_name[30,100];
  END   DECLARE
  
  EXEC SQL
       INSERT INTO emp ( emp_no, emp_name )
                VALUES (:emp_no, :emp_name );

  If the values are part of an array of 200, 200 rows will be inserted with the one execution of the statement. Array selects, updates, and deletes are also supported.

• Oracle Call Interface (OCI): Uses the OFEN parameter to specify the number of rows to be returned via each OEXN fetch. This is equivalent to ARRAYSIZE.

• SQL*Plus: You can set ARRAYSIZE in the SET command. The default is 20. If you set it to 200 or more, you will improve performance over the network. (MAXDATA must be large enough to contain that many rows.)

• EXPORT and IMPORT: Try to avoid executing these utilities across network nodes. If it is absolutely necessary, you can improve response time by setting the BUFFER parameter to an appropriate size. If the row length is 100 and BUFFER is 32,768, the effective array size is approximately 327. Most sites set their buffers on exports and imports to at least 32 kilobytes. Some UNIX sites set buffers as high as 1 megabyte. The maximum value is operating system dependent.

• SQL*Loader: Set the ROWS parameter to allocate a buffer or array size. The actual buffer size is set by multiplying the size of each row by the value you specify for the parameter. Set this value as high as possible for your site. The maximum is system dependent.