SET ARRAYSIZE 5000 for sqlldr spool

Name

SET ARRAYSIZE

Synopsis

The ARRAYSIZE setting controls the number of rows SQL*Plus fetches from the database at one time.

Syntax

SET ARRAY[SIZE] array_size

Parameters

SET ARRAY[SIZE]

Is the command, which may be abbreviated SET ARRAY.

array_size

Is the number of rows fetched at one time. The default value is 15. The allowed range is from 1 to 5000.

Increasing the array size allows SQL*Plus to return more rows in one fetch, thus lessening the required number of network round trips between it and the database server. The tradeoff is that larger array size settings require more memory. Using the default value of 15, SQL*Plus would require 10 fetches to return 150 rows from a query. By increasing the array size to 50, you reduce the number of fetches to three.