Re: Fasted Way to Unload Data - Straight SQL query vs. PL/SQL and dbms_output vs. PL/SQL and utl_file
- From: rjamya <rjamya@xxxxxxxxx>
- To: strickland.mark@xxxxxxxxx
- Date: Wed, 29 Sep 2010 21:01:30 -0400
Mark,
Straight SQL output via sqlplus will be affected by ARRAYSIZE. The drawback
with dbms_output is it outputs once block is complete. However I have two
(more) ideas for you.
1. do all you need to in a pipelined function, which outputs data in a
format you want, preferable connect using bequeth or ipc connection rather
than SQLNET. If you must use SQLNET see if you can use large sdu/tdu and set
larger arraysize (in sqlplus) and let it rip. I have not tried using
parallel pipelined but it may help too.
2. if you can instead of dbms_output, try using
dbms_system.ksdwrt(1,'whatever'); This uses buildin c functions to write to
session trace file. Once tracefile is written, a bit of trimming might be
needed to remove headers and periodic date-timestamps oracle tends to write
there.
Raj
On Wed, Sep 29, 2010 at 12:49 PM, Mark Strickland <strickland.mark@xxxxxxxxx
> wrote:
> Oracle 10gR2 on Windows 2008R2 with Cygwin and Bash Scripts, 4 CPUs and
> 4-GB RAM
>
>
Other related posts:
