RE: Is sqlplus too slow to unload data?

If you have PRO*C and a c-compiler, you can follow this link (you need both lines) to download a basic "sqlunldr" tool. It is almost as fast as exp.

http://asktom.oracle.com/pls/ask/f?p=4950:8:646297::NO::F4950_P8_DISPLAYID,F 4950_P8_CRITERIA:459020243348,%7BSQLDA%7D Caver

-----Original Message-----
Sent: Wednesday, April 24, 2002 3:33 AM
To: Multiple recipients of list ORACLE-L

Bruce,

SQLPlus:
set pages 0;
set lines 10000;
set termout off;
set trimspool on;
set trimout on;
set echo off;
set feedback off;
set verify off;
set recsep off;
set arraysize 2000;

PDQOut is 3rd party product I test. I also test the PL/SQL from Thomas Kyte's book. I
call it from sqlplus, and the speed is only 1.5 time faster than sqlplus one. exp can
achieve 500M/minute. However, I intend to change it as small as possible. I wonder if
about 5M/minute is max speed for sqlplus.

Regards,
Bin

"Reardon, Bruce (CALBBAY)" wrote:

> Bin,
>
> Have you tried setting term off in your sqlplus session - what effect does
this have?
> I would guess that the Pro*C program also uses Net8 so the problem would
be in SQLPlus.
>
> Which 3rd party product did you try?
> Have you tested PDQOut from http://www.oriole.com - this is written in
OCI.
> Also, I'm sure someone will suggest using Perl.
>
> HTH,
> Bruce Reardon
>
> -----Original Message-----
> Sent: Wednesday, 24 April 2002 14:53
>
> Hi,
> Our application uses sqlplus + sqlloader to transfer data between
> databases. It takes nearly four hours to unload to data to flat
> files(1G), which is far too slow. In the application, the query looks
> like the following. All those &3,&4,&5 are for sqlldr format.
> select ' ' ||
> '&4' || replace( replace ( ltrim(dealerid), '&4', '&4' ||
> '&4' ), CHR(10), CHR(10) || '&5' ) ||'&4'||'&3' ||
> ...
> from table_name f
> where eventdate >= to_date(&1)
> and eventdate <= to_date(&2);
> Firstly, there is nothing wrong with the query, since if I insert into a
table
> it only takes less than 15 minutes. Therefore, there must be problem with
either
> sqlplus or Networking.
> With sqlplus, I increase arraysize from 1 to 2000.
> With Networking, I put tcp.nodelay=yes on protocol.ora.
> Both doesn't work.
>
> I try thrid party software which is writen by Pro*C to download tables to
flat
> file. Its speed is more than 60M/minute. I monitor v$session_event while
it's
> running.The only different is event
> "SQL*Net message from client". In AVERAGE_WAIT and MAX_WAIT, the
> different is huge.
> sqlplus:
> TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT
> 49 0 5998 122.4 1004
> Pro*C:
> TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT
> 351 0 677 1.92 42
> What's the problem sqlplus or net8?
>
> BTW, dblink doesn't work since the two databases on isolated network.
> emp/imp is an option. However, I just try to find out what is wrong
> with sqlplus one.
> I test 8.0.5 and 8.1.7 on solaris 2.6-2.8.
>
> Thanks in advance,
> Bin
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Reardon, Bruce (CALBBAY)
> INET: Bruce.Reardon_at_comalco.riotinto.com.au
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Bin Wang
  INET: binw_at_lasseters.com.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Toepke, Kevin M
  INET: ktoepke_at_Trilegiant.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).