Improve Spool Performance [message #663128] |
Mon, 22 May 2017 23:11 |
|
abhi_orcl
Messages: 40 Registered: December 2016
|
Member |
|
|
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.
Regards
|
|
|
|
Re: Improve Spool Performance [message #663134 is a reply to message #663131] |
Tue, 23 May 2017 03:19 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
To see how long the query itself takes run this:
set timing on
BEGIN
FOR rec in (your query here) LOOP
NULL;
END LOOP;
END;
/
Also make sure you're running sqlplus on the DB server itself, that'll take network out of the equation.
|
|
|