Re: Is sqlplus too slow to unload data?

could you clarify a couple of things:

1) what sqlloader path are you using? (conventional or direct)
2) do you have any integrity constraints or triggers active?
3) what is your commit point or data saves?
4) are there indexes?
5) are you pre-sorting before insert?
6) did you run an explain plan on the sql statement --- how optimized is it?

a good reference for sqlloader is the utilities manual.

"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 Wed Apr 24 2002 - 00:05:27 CDT