Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Is sqlplus too slow to unload data?

Is sqlplus too slow to unload data?

From: wangbin <wangbin_at_start.com.au>
Date: 23 Apr 2002 20:09:42 -0700
Message-ID: <2d15bd69.0204231909.75f677b@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 Tue Apr 23 2002 - 22:09:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US