Are you sure the problem is on the spooling/exporting end of things? Maybe your query to the database is just taking longer today. Maybe the blocks needed for the query are not in the cache?
It wasn't a scientific test, but I was the only person in the DB, the query is selecting straight from a table (with minimal functions doing stuff like formatting dates). Both processes were writing to the same directory. I've gone back and repeated both a couple times with similar results. It could be just a crazy coincidence that something else is happening, but I haven't found out what that might be yet.
Figure out where your session is spending its time to understand why it is taking longer. In other words, examine your session's wait events. If you are seeing events like "SQL*Net more data to client", then it can be an indicator the client side is slow and not ready to receive the next rows of the result set. But if your wait events are I/O related, then it more likely that your fast run enjoyed the data already in the cache.
You may be the only person in the DB, but that doesn't mean the cache still didn't come into play. When I'm doing this type of work, I might issue that query a couple of times to ensure I have the data exactly as I want it and then do the export/spool. By the time I am ready to dump the data out of the database, the buffer cache is good and primed for me. Then when I come back the next day, the blocks have aged out of the cache but I don't run my SQL the same way because I already know the statement that will return the results I want. So my query has to wait on I/O the second day.
All of this is just a guess. Without knowing where your session is spending its time, its only a guess.
I bet the difference in speed is due to the effort required to render the output rows in the Script Output tab. In SQL*Plus you can "set termout off" to avoid that overhead, but apparently neither SQL Developer nor SQLcl supports "termout off" in conjunction with spooling.
That is, if you Export but tell SQL Developer to run that Export in the background, there is no Progress Dialog and no Query Results tab to display, and overhead is minimized. On the other hand, if you turn spooling on then "select /*delimited*/ * from <someTableOrView>" (even with "set termout off), you will still have overhead of displaying rows to the screen.
Edit: By the way, if you run the Export in the background, then View > Log displays a convenient tab that show exactly when the Export starts and ends:
Wonderful! So that's the trick -- you cannot type in the commands interactively in the worksheet if you want "termout off" to work.
I did a test using a 15 column table with 2M rows, running a script (@Dump<Table>.sql) containing "set termout off", "set trimspool on", "set timing on", "spool <outputFile>", "select /*delimited*/ * from <Table>", "spool off" from the worksheet against a local 11g XE database. This spooled dump ran almost 5 times faster than an export against the same table from the Connections tree.
This behavior is probably the same as SQL*Plus ("termout off" not working interactively), but it would be good to add a little warning to the "help set termout" text.
Thanks for correcting me!
Edit: Actually the test I mention above was not valid. It used a table having 1,000 "generic" columns, with the 15 ALL_OBJECTS columns mapped into 15 generic columns of the correct type and size. That is fine, of course, but then I made the mistake of letting the Export process all 1,000 columns (with 985 of them as null), whereas my scripted spooled dump query only included the 15 columns actually populated.
A redo of the test using a CTAS clone of the ALL_OBJECTS table, then duplicating rows to increase the row count to 2M, shows these elapsed time results for two repetitions of each:
Scripted Spooled Dump
Export Utility (Elapsed: 00:02:14 and 00:02:16)
So, for whatever reason, it seems that the Export Utility really does run several times faster than even a properly configured scripted spooled dump query.
Thanks for this. When I get a chance, I'll try running with as a script, and from the command line to see how they compare.
1 person found this helpful
By the way, check your Preferences > Database > Advanced > SQL Array Fetch Size. In my prior tests the value was 50. By bumping it up to 200, I got the following elapsed time improvements:
SQL Array Fetch Size: 200 50 50
------------ ------------ ------------
Scripted Spooled Dump: 00:04:31 00:06:15 00:07:11
Export Utility: 00:01:21 00:02:14 00:02:16
I agree with you. Increasing the SQL Fetch Size could be the solution.