I've been doing a fair amount of work with data extraction from a database to a flat file recently using SQL*Plus and have been looking at ways to speed up the process. This is where the arraysize setting comes in handy. This setting controls how many rows are fetched from the database in a single round trip. I did some testing with various sizes and found that tweaking this value can have huge performance improvements. This is what I found with the data I was extracting.

These results are very data-dependent so the exact values may not apply in all circumstances. My data profile is a single column from a table containing 23 columns and around 5.7 million rows. The table is dense, no null values are permitted.

The results looked like this:




SQL*Plus has a default setting for the fetch size set to 15. Increasing this first to 50 and then further in increments of 50 decreased the amount of time my full extraction was taking. Eventually this plateaued to the point where increasing the fetch size had no significant effect on extraction time.

The overall result was that I was able to cut the extraction time by more than half. What I've learned from this experiment was that the default setting is not adequate for large data extractions. However, I've tried tweaking the same on a more transactional system (via JDBC), in that case there was virtually no difference to a larger fetch size.

To change the fetch size in SQL*Plus, the arraysize parameter is used like so:


An easy setting with quite profound effects.

-i