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.