a picture from the rear of a mclaren sports car

SQL*Plus shortcut for fast data unload

Posted by

I published a video a few days ago on the various tools you can use to unload data out of the Oracle Database to a flat file format (CSV in this particular case). I compared SQLcl, SQL*Plus, UTL_FILE and even Pro*C to look at getting the data spooled out as efficiently as possible.

You can check out that video below, but read on to get another cool shortcut.

Spoiler Alert: You more work you put in, the better results you get.

For the SQL*Plus demo, you’ll notice that I had to nominate the markup settings, and also tweaked some SQL*Plus settings a little to get the best query fetch performance. If this all seems too complicated, then here’s a shortcut for you. When you start SQL*Plus, if you know that the purpose of running this SQL*Plus session is to unload some data, you can nominate the MARKUP options right there on the command line at startup.

C:\>sqlplus -MARKUP "CSV ON" scott/tiger@db19_pdb1

SQL*Plus: Release - Production on Wed Dec 1 14:42:01 2021

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Wed Dec 01 2021 14:39:57 +08:00

Not only does that immediately get the output ready for unloading to CSV, behind the scenes, we will also alter the settings to ensure that we get good fetching performance as well. Compare a standard session settings

SQL> show arraysize
arraysize 15
SQL>  show lobprefetch
lobprefetch 0
SQL>  show rowprefetch
rowprefetch 1
SQL>  show statementc
statementcache is 0

with that of a SQL*Plus session launched with the markup option:

SQL> show arraysize
arraysize 100
SQL> show lobprefetch
lobprefetch 16384
SQL> show rowprefetch
rowprefetch 2
SQL> show statementc
statementcache is 20

All of this is covered in the docs as well.


Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.