RE: Is sqlplus too slow to unload data?

Bin,

Have you tried setting term off in your sqlplus session - what effect does this have? I would guess that the Pro*C program also uses Net8 so the problem would be in SQLPlus.

Which 3rd party product did you try?
Have you tested PDQOut from http://www.oriole.com - this is written in OCI. Also, I'm sure someone will suggest using Perl.

HTH,
Bruce Reardon

-----Original Message-----
Sent: Wednesday, 24 April 2002 14:53

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: Reardon, Bruce (CALBBAY)
  INET: Bruce.Reardon_at_comalco.riotinto.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).