RE: Re: Is sqlplus too slow to unload data?]
Stephane, pdqout does give me a real good impression, not only the speed but also the interface. I can see it uses parallel query. However, I, as a production DBA, intend to change the application which come from a third party as a package as small as possible. It takes 4hours to extract data at 5M/minute. If the speed of sqlplus can be increased to 20M/minute, I achieve the goal. Just one thing about pdqout confuses me. I have to use DBA account, otherwise I get the following error. ' Oracle oexfet() failure (-907) ORA-00907: missing right parenthesis ' Kind Regards, Bin Original Message To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] As somebody who has insider's knowledge of pdqout and a decent understanding of exp and SQL*Plus, I think that you must have a good number of numbers and dates in your data. Both types (as opposed to strings) require conversion. Which is why exp, which dumps bytes 'as is', can be so fast - there is hardly any conversion from the internal Oracle format (same thing with SELECT ... INSERT ..., which is also lighter on the I/O side). However, the conversion does occur with both SQL*Plus and Pdqout. My interpretation in the difference in waits is that, with your SQL*Plus query, there is a good deal of formatting done on the kernel side - with Pdqout, conversion is let to Oracle but there is formatting on the application side too. This may explain why Pdqout gets its results faster than SQL*Plus. SORRY IF IT LOOKS LIKE A SALES PLUG There are two other factors which probably explain the difference in speed : - Pdqout is multithreaded. While one thread waits for Oracle to return batch n, a second one is busy formatting batch n-1 (this is the thread which is heavier on CPU) while a third one is writing formatted batch n-2 to disk. Although SQL*Plus is fairly efficient and uses arrays as much as Pdqout does (even if default buffer sizes are probably different, you could try to play on this too), it does fetch and writing in sequence, and doesn't get the next batch before having written to disk - vs memory copy with Pdqout, in which threads just wait on mutexes. - Pdqout is very byte-conscious. We had benched it a long long time ago against a competitive product, it generated a 1.2G file against a 2G file with the same data - and the loading speed with SQL*Loader was in the same ratio. It doesn't put separators where they are not strictly required, uses a very compact date format, and by saving a few bytes per row it can make a huge difference on many rows. So, the writing time for SQL*Plus is probably significantly higher than with Pdqout too. /SORRY IF IT LOOKS LIKE A SALES PLUG If you regularly make at wider intervals calls which take longer to answer, a significant time difference is not too surprising. Does it make sense ? -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: Stephane Faroul 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
RE: RE: Re: Is sqlplus too slow to unload data?]
Stephane, pdqout does give me a real good impression, not only the speed but also the interface. I can see it uses parallel query. However, I, as a production DBA, intend to change the application which come from a third party as a package as small as possible. It takes 4hours to extract data at 5M/minute. If the speed of sqlplus can be increased to 20M/minute, I achieve the goal. Just one thing about pdqout confuses me. I have to use DBA account, otherwise I get the following error. ' Oracle oexfet() failure (-907) ORA-00907: missing right parenthesis ' Kind Regards, Bin AFAIK it's a bug who has stayed in some versions, due to an innate tendency to make a distinction between the DBA elite and the scum of the earth ;-). Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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).
RE: Re: Is sqlplus too slow to unload data?
As somebody who has insider's knowledge of pdqout and a decent understanding of exp and SQL*Plus, I think that you must have a good number of numbers and dates in your data. Both types (as opposed to strings) require conversion. Which is why exp, which dumps bytes 'as is', can be so fast - there is hardly any conversion from the internal Oracle format (same thing with SELECT ... INSERT ..., which is also lighter on the I/O side). However, the conversion does occur with both SQL*Plus and Pdqout. My interpretation in the difference in waits is that, with your SQL*Plus query, there is a good deal of formatting done on the kernel side - with Pdqout, conversion is let to Oracle but there is formatting on the application side too. This may explain why Pdqout gets its results faster than SQL*Plus. SORRY IF IT LOOKS LIKE A SALES PLUG There are two other factors which probably explain the difference in speed : - Pdqout is multithreaded. While one thread waits for Oracle to return batch n, a second one is busy formatting batch n-1 (this is the thread which is heavier on CPU) while a third one is writing formatted batch n-2 to disk. Although SQL*Plus is fairly efficient and uses arrays as much as Pdqout does (even if default buffer sizes are probably different, you could try to play on this too), it does fetch and writing in sequence, and doesn't get the next batch before having written to disk - vs memory copy with Pdqout, in which threads just wait on mutexes. - Pdqout is very byte-conscious. We had benched it a long long time ago against a competitive product, it generated a 1.2G file against a 2G file with the same data - and the loading speed with SQL*Loader was in the same ratio. It doesn't put separators where they are not strictly required, uses a very compact date format, and by saving a few bytes per row it can make a huge difference on many rows. So, the writing time for SQL*Plus is probably significantly higher than with Pdqout too. /SORRY IF IT LOOKS LIKE A SALES PLUG If you regularly make at wider intervals calls which take longer to answer, a significant time difference is not too surprising. Does it make sense ? - Original Message - From: Bin Wang [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tue, 23 Apr 2002 23:33:21 Bruce, SQLPlus: set pages 0; set lines 1; 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: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services
