Re: Is sqlplus too slow to unload data?

Oracle has always annoyed me about not having an equivalent to INFORMIX's dbunload.

Depending on whether you are doing replace or merge/update stuff between the instances, you may find that (if you are replacing entire tables and their relatives) a TRUNCATE ot the target tables and EXPORTing from the source instance and IMPORTing to the target instance might be faster and easier, particularly if you make use of the options to bypass the conventional path, and don't forget, always say NO to COMPRESS.

Are these instances on the same server? I am presuming not.

What operating system is involved ?

Anyhow, if the two machines are isolated, exp/imp could help a lot, providing a million provisos of course (nothing good is ever free of some curse); using SQLPLUS to dump out tables in a SQL*LOADER form than you ship somehow over to the target system would seem to be inefficient; sqlplus does all the work reparsing and outputting the data and then sqlldr turns around, of course not knowing the data's origin is Oracle, and reparses and loads it. At least EXP/IMP trust each other, and as long as you can do complete clean replaces (this means considering referential links, PK's and the rest), it certainly should be faster than what you are presently doing.

As far as SQL*PLUS goes, as was said, it wasn't REALLY meant as a data extraction/transport tool for things like moving stuff between instances, but it could be tuned a bit, perhaps, if that's what you want to do.

As a completely gross kludge (hoping everyone's digested their breakfast / lunch / dinners, etc) something else you could try is ... using Microsoft Access to suck the data out of the origin database and push it into the target database. I suggest this with great embarassment, but only because we've done it sometimes, out of desparation. You incur at least double hop SQLNET link delays, but depending on how much data needs to be moved, how often, and other circumstances, it can often end up being more expedient than writing lots of code.

As far as networking goes, you indicated the machines are isolated from each other, so I don't understand the relevance of the tep/ip parameter issue. Is SQL*PLUS natively running on the source machine, or elsewhere?

Just trying to help...

RSH.
"wangbin" <wangbin_at_start.com.au> wrote in message news:2d15bd69.0204231909.75f677b_at_posting.google.com...
> 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.
>
> Thank you,
> Bin
Received on Thu Apr 25 2002 - 02:37:49 CDT