Re: Fasted Way to Unload Data - Straight SQL query vs. PL/SQL and dbms_output vs. PL/SQL and utl_file

From: Mark Strickland <>
Date: Wed, 29 Sep 2010 20:26:32 -0700
Message-ID: <>

Hmm, I'll give that a try. My arraysize is set to 5000. I can certainly try even larger. The connection is local, not over SQL*Net. Thank you for responding!


On Wed, Sep 29, 2010 at 6:01 PM, rjamya <> wrote:

> 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 <
>> wrote:
>> Oracle 10gR2 on Windows 2008R2 with Cygwin and Bash Scripts, 4 CPUs and
>> 4-GB RAM

Received on Wed Sep 29 2010 - 22:26:32 CDT

Original text of this message