Igor's Blog

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:
sqlfetchsize.png




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.

-i

comments powered by Disqus
Other posts you may like...
Programming, DIY, Games, Hacks, Tech and more.

Recent Blog Posts

Learn How to Add Disclaimer to All Outgoing Emails in Office 365

How to securely host your web site for free with Google Cloud and CloudFlare (part 3)

How to securely host your web site for free with Google Cloud and CloudFlare (part 2)

How to securely host your web site for free with Google Cloud and CloudFlare (part 1)

Beware of the subtle difference in date format strings between MySQL and SQLite

Postman reporting Unexpected 'N' error in response body

Adding extra shelves to the Ikea Detolf display cabinet

Posting array data from a web form to a Jersey REST service

How to avoid double encoding XML entities with Apache Commons StringEscapeUtils

Add your commonly used fonts to macOS Mail font drop down

Recent Galleries

Space Food - Chocolate Ice Cream with Chocolate Chips

Legeod Star Wars AT-DP kit

DIY spare parts computer build with a RAIDMAX Anura case

Fake 'Lepin' brand Lego packaging

Hardwood garden bench with clear resin void filler

Fixing a 3D printer extruder that stopped heating up

Easily increase disk space in a Lenovo Ideapad 100S 14" laptop with an M.2 SSD

Making a multi-piece 3D printed solder spool holder stand

DIY indoor apartment grow light wiring

Good Friday Electronics fun Easter Bunny LED PCB Kit IBEABU-01.0

My Other Web Sites

Igor and Elise's Travels
Riverside Expressway Cam
300 George St Blogumentary

My Online Tools

UUID to OID Converter
Guru JSON-RPC Tester
Extrudifier Object Designer
Travel ┬ÁBlog

Blogs and Friends

Matt Moores Blog
Georgi's FlatPress Guide
Perplexing Permutations
The Security Sleuth
Ilia Rogatchevski
Travelling Fairy

Blog Activity

Blog Activity
Please follow me for more great content!
     
Don't show this again