Bruce, SQLPlus: set pages 0; set lines 10000; set termout off; set trimspool on; set trimout on; set echo off; set feedback off; set verify off; set recsep off; set arraysize 2000;
PDQOut is 3rd party product I test. I also test the PL/SQL from Thomas Kyte's book. I call it from sqlplus, and the speed is only 1.5 time faster than sqlplus one. exp can achieve 500M/minute. However, I intend to change it as small as possible. I wonder if about 5M/minute is max speed for sqlplus. Regards, Bin "Reardon, Bruce (CALBBAY)" wrote: > 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bin Wang INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
