Home » SQL & PL/SQL » SQL & PL/SQL » Improve Spool Performance (Oracle Database 12c Enterprise Edition Release - 64bit, AIX OS)
Improve Spool Performance [message #663128] Mon, 22 May 2017 23:11 Go to next message
Messages: 40
Registered: December 2016
Hello Everyone,

I have a requirement wherein I need to spool few tables to flat files. The entire table contents needs to be spooled to flat files so I tried the spool method (have applied the pre setting like termout on trimspool etc which worked fine and gave the desired result) first and for extracting around 6 million records from a table, it took around 16 minutes. I am trying to bring this timing as low as possible and below are the options I could find over the forums and the findings I did:

1. Use UTL_FILE - Used the same, could find considerable improvement from 16 minutes down to 12 minutes. Would use this method in case not able to fix the spool performance.

2. Set the spool parameters like termout, trimpsool etc - Have set it all and it works fine.

I read in some forums that spool slowness is not something to do with spool utility etc, it has completely to do with the performance of the underlying query. In the case it is correct, can you all please suggest if I use parallel hint for fetching the records from table (complete table record), will it do the trick for me.
Unfortunately, I am unable to test this scenario in our development environment as the DB setup doesn't allow parallel sessions to be created even though I use parallel hints. Your suggestions are highly appreciated.

Re: Improve Spool Performance [message #663131 is a reply to message #663128] Tue, 23 May 2017 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 67817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

First UTL_FILE (which is a PL/SQL program) writes on the server whereas SPOOL (which is a SQL*Plus command) writes on the client.
To get the best performances with SPOOL you must write the following commands in a script:
set termout off trimspool on echo off verify off autoprint off serveroutput off arraysize 1000 pagesize 0
set linesize <minimum value to fit your lines>
set long <minimum value to fit your LONG/xLOB>
set longchunsize <your linesize>
set numwidth <minimum size to fit your number with default format>
set colsepchar '<the column separator character you want>'
col <col> format <minimum format to fit your column for each column>
spool <your file>
select ...
spool off
Re: Improve Spool Performance [message #663134 is a reply to message #663131] Tue, 23 May 2017 03:19 Go to previous message
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
To see how long the query itself takes run this:
set timing on

  FOR rec in (your query here) LOOP




Also make sure you're running sqlplus on the DB server itself, that'll take network out of the equation.
Previous Topic: Multiple rows based on column
Next Topic: Create Year and week partition automatically
Goto Forum:

Current Time: Fri Apr 23 01:16:29 CDT 2021