Igor Kromin |   Consultant. Coder. Blogger. Tinkerer. Gamer.

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:
set arraysize 200

An easy setting with quite profound effects.


A quick disclaimer...

Although I put in a great effort into researching all the topics I cover, mistakes can happen. Use of any information from my blog posts should be at own risk and I do not hold any liability towards any information misuse or damages caused by following any of my posts.

All content and opinions expressed on this Blog are my own and do not represent the opinions of my employer (Oracle). Use of any information contained in this blog post/article is subject to this disclaimer.
Hi! You can search my blog here ⤵
NOTE: (2022) This Blog is no longer maintained and I will not be answering any emails or comments.

I am now focusing on Atari Gamer.