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

From: Mark Strickland <strickland.mark_at_gmail.com>
Date: Wed, 29 Sep 2010 20:26:32 -0700
Message-ID: <AANLkTi=uvPaDJt4is10etc-E1bzOUdy4a4f6uPyPm5eP_at_mail.gmail.com>

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!

Mark

On Wed, Sep 29, 2010 at 6:01 PM, rjamya <rjamya_at_gmail.com> 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 <
> strickland.mark_at_gmail.com> wrote:
>
>> Oracle 10gR2 on Windows 2008R2 with Cygwin and Bash Scripts, 4 CPUs and
>> 4-GB RAM
>>
>>

--
http://www.freelists.org/webpage/oracle-l

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