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

NOTE: This article is 3 years or older so its information may no longer be relevant. Read on at your own discretion! Comments for this article have automatically been locked, refer to the FAQ for more details.
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.


Hope you found this post useful...

...so please read on! I love writing articles that provide beneficial information, tips and examples to my readers. All information on my blog is provided free of charge and I encourage you to share it as you wish. There is a small favour I ask in return however - engage in comments below, provide feedback, and if you see mistakes let me know.

If you want to show additional support and help me pay for web hosting and domain name registration, donations, no matter how small, are always welcome!

Use of any information contained in this blog post/article is subject to this disclaimer.
comments powered by Disqus
Other posts you may like...