Re: Is sqlplus too slow to unload data?

It's SQL*Plus. Love it to pieces, but it just dawdles when used as an "unloader". It must spend a tremendous amount of processing just formatting or something...

Never bother blaming the network or Net8 unless you are just trying to get someone off your back to delay for time. DBAs will say "it must be the network" to cause users to go hounding off after the network administrator or systems administrator. While they're baying at that poor person, you should have at least an hour to find out what's really going on...

If you are working on UNIX, one thing you can do to speed up unloading/loading is put a UNIX "pipe" or FIFO between the "unloader" process (i.e. SQL*Plus or other) and SQL*Loader, instead of a "flat file". Saves disk space but most importantly saves time -- you don't have to wait for unloading to complete before loading. In fact, loading must start with the unloading, so you can't help but save both time and space...

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, April 23, 2002 10:53 PM

> 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: 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: Tim Gorman
  INET: Tim_at_SageLogix.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).