Re: Is sqlplus too slow to unload data?

On 23 Apr 2002 20:09:42 -0700, wangbin_at_start.com.au (wangbin) wrote:

>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

Make sure you run this as a cron job, spooling to a file, *termout off*. Terminal display is a *HUGE* delay factor in sql*plus. Your arrysize is also probably just TOO big: arraysize * <the number of bytes per record> shouldn't exceed SDU (default 2048), or it will also run much much slower (tested!). The benefits of a big arraysize are yet another myth to be addressed by this group (anyone willing to do this? Howard? Jonathan?)

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue Apr 23 2002 - 23:51:38 CDT