Re: Is sqlplus too slow to unload data?

Bin,

You're absolutely correct that we had mentioned the use of SQL*Plus as a cheap homegrown "SQL*Unloader" tool in our book, and I've posted Gary's "unload.sql" scripts on my website (http://www.EvDBT.com/library.htm) as well. But for high-volumes of data, SQL*Plus simply does too much work (it seems) and cannot compete with a stripped-down tool or utility (such as a simple PRO*C or OCI program).

This is not unique to Oracle and SQL*Plus. I recently had the task of converting a Sybase data warehouse to Oracle, and I fell into the same trap with the Sybase command-line interpreter, called ISQL. Not only is ISQL quite slow, but it has the disconcerting habit of rounding numeric data values to 2 digits, just because it feels happens to like that form of rounding. I thought, "OK. Better to use the 'bulk-copy' utility named BCP, anyway". BCP is faster but also rounds to 2 digits! Astounding!

So, I ended up writing korn-shell scripts that use ISQL to describe tables (using SP_HELP), and then take that metadata to generate DBLIB "C" programs to do all of the unloading from Sybase. For all scenarios, I piped the output through a UNIX pipe and used SQL*Loader to load into Oracle. Allowed us to unload/load about 250Gb of data in about 8-9 hours elapsed, in the end...

-Tim

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, April 30, 2002 5:43 AM

> Tim,
>
> Thank you for your reply.
>
> There are two issues that I cannot use pipe. One is two databases are in
two
> different isolated network. Second is we need the flat files for archives.
> BTW, whenever we
> can use PIPE, we can use database link. Is it right?
>
> I understand that SQLPlus is just a command line interface to
interactively
> query Oracle databases. It's also a report writer widely used by DBA. It
> isn't a proper tool for data
> extraction. However, it has been treated as unload tool in some places. It
> is introduced as unload tool in your book -- Essential Oracle8i Data
> Warehousing. Using unload, sqlplus, Thomas Kyte to search in
> groups.google.com, I also find that he uses it as a suggestion several
> times. Anyway, what is a proper tool for data extraction in Oracle?
exp/imp
> isn't. Although there are quite a few third party tools, wouldn't it be
nice
> if Oracle offically issues one?
>
> Kind Regards,
> Bin
>
> Original message from: "Tim Gorman" <Tim_at_SageLogix.com>
> >
> >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).
> >.
> >
>
>
> __________________________________________________________________
> Get your free Australian email account at http://www.start.com.au
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: ozemail
> INET: binw_at_ozemail.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).