Re: Is sqlplus too slow to unload data?
Bin, You're absolutely correct that we had mentioned the use of SQL*Plus as a cheap homegrown SQL*Unloader tool in our book, and I've posted Gary's unload.sql scripts on my website (http://www.EvDBT.com/library.htm) as well. But for high-volumes of data, SQL*Plus simply does too much work (it seems) and cannot compete with a stripped-down tool or utility (such as a simple PRO*C or OCI program). This is not unique to Oracle and SQL*Plus. I recently had the task of converting a Sybase data warehouse to Oracle, and I fell into the same trap with the Sybase command-line interpreter, called ISQL. Not only is ISQL quite slow, but it has the disconcerting habit of rounding numeric data values to 2 digits, just because it feels happens to like that form of rounding. I thought, OK. Better to use the 'bulk-copy' utility named BCP, anyway. BCP is faster but also rounds to 2 digits! Astounding! So, I ended up writing korn-shell scripts that use ISQL to describe tables (using SP_HELP), and then take that metadata to generate DBLIB C programs to do all of the unloading from Sybase. For all scenarios, I piped the output through a UNIX pipe and used SQL*Loader to load into Oracle. Allowed us to unload/load about 250Gb of data in about 8-9 hours elapsed, in the end... -Tim - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, April 30, 2002 5:43 AM Tim, Thank you for your reply. There are two issues that I cannot use pipe. One is two databases are in two different isolated network. Second is we need the flat files for archives. BTW, whenever we can use PIPE, we can use database link. Is it right? I understand that SQLPlus is just a command line interface to interactively query Oracle databases. It's also a report writer widely used by DBA. It isn't a proper tool for data extraction. However, it has been treated as unload tool in some places. It is introduced as unload tool in your book -- Essential Oracle8i Data Warehousing. Using unload, sqlplus, Thomas Kyte to search in groups.google.com, I also find that he uses it as a suggestion several times. Anyway, what is a proper tool for data extraction in Oracle? exp/imp isn't. Although there are quite a few third party tools, wouldn't it be nice if Oracle offically issues one? Kind Regards, Bin Original message from: Tim Gorman [EMAIL PROTECTED] It's SQL*Plus. Love it to pieces, but it just dawdles when used as an unloader. It must spend a tremendous amount of processing just formatting or something... Never bother blaming the network or Net8 unless you are just trying to get someone off your back to delay for time. DBAs will say it must be the network to cause users to go hounding off after the network administrator or systems administrator. While they're baying at that poor person, you should have at least an hour to find out what's really going on... If you are working on UNIX, one thing you can do to speed up unloading/loading is put a UNIX pipe or FIFO between the unloader process (i.e. SQL*Plus or other) and SQL*Loader, instead of a flat file. Saves disk space but most importantly saves time -- you don't have to wait for unloading to complete before loading. In fact, loading must start with the unloading, so you can't help but save both time and space... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, April 23, 2002 10:53 PM 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
Re: Is sqlplus too slow to unload data?
Tim, Thank you for your reply. There are two issues that I cannot use pipe. One is two databases are in two different isolated network. Second is we need the flat files for archives. BTW, whenever we can use PIPE, we can use database link. Is it right? I understand that SQLPlus is just a command line interface to interactively query Oracle databases. It's also a report writer widely used by DBA. It isn't a proper tool for data extraction. However, it has been treated as unload tool in some places. It is introduced as unload tool in your book -- Essential Oracle8i Data Warehousing. Using unload, sqlplus, Thomas Kyte to search in groups.google.com, I also find that he uses it as a suggestion several times. Anyway, what is a proper tool for data extraction in Oracle? exp/imp isn't. Although there are quite a few third party tools, wouldn't it be nice if Oracle offically issues one? Kind Regards, Bin Original message from: Tim Gorman [EMAIL PROTECTED] It's SQL*Plus. Love it to pieces, but it just dawdles when used as an unloader. It must spend a tremendous amount of processing just formatting or something... Never bother blaming the network or Net8 unless you are just trying to get someone off your back to delay for time. DBAs will say it must be the network to cause users to go hounding off after the network administrator or systems administrator. While they're baying at that poor person, you should have at least an hour to find out what's really going on... If you are working on UNIX, one thing you can do to speed up unloading/loading is put a UNIX pipe or FIFO between the unloader process (i.e. SQL*Plus or other) and SQL*Loader, instead of a flat file. Saves disk space but most importantly saves time -- you don't have to wait for unloading to complete before loading. In fact, loading must start with the unloading, so you can't help but save both time and space... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, April 23, 2002 10:53 PM 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman 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). . __ Get your free Australian email
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: Is sqlplus too slow to unload data?
On Tuesday 23 April 2002 21:53, Bin Wang wrote:
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.
How about using Perl?
Below is a script I just used to unload a table. Not very big, but this
is just from one of my test databases at home on a Linux box.
It unloaded about 12,000 rows in less than 2 seconds. This include
writing them to a file. This script creates a file of TABLENAME.dmp.
$ time ul.pl -database ts01 -username orades -password orades \
-table 'I$RM_PROPERTY_MAPS'
1.22s real 1.07s user 0.04s system
Doing the same thing with SQL*plus took 4.46 seconds.
You must have DBI and DBD::Oracle installed to use this.
Jared
#!/usr/bin/perl
# ul.pl - unload a table
use warnings;
use FileHandle;
use DBI;
use strict;
use Getopt::Long;
my %optctl = ();
Getopt::Long::GetOptions(
\%optctl,
database=s,
username=s,
password=s,
table=s,
sysdba!,
sysoper!,
z,h,help);
#setup environment - homegrown package
my($db, $username, $password, $connectionMode);
$connectionMode = 0;
if ( $optctl{sysoper} ) { $connectionMode = 4 }
if ( $optctl{sysdba} ) { $connectionMode = 2 }
if ( ! defined($optctl{database}) ) {
Usage();
die database required\n;
}
$db=$optctl{database};
if ( ! defined($optctl{username}) ) {
Usage();
die username required\n;
}
if ( ! defined($optctl{table}) ) {
Usage();
die table required\n;
}
$username=$optctl{username};
$password = $optctl{password};
my $dbh = DBI-connect(
'dbi:Oracle:' . $db,
$username, $password,
{
RaiseError = 1,
AutoCommit = 0,
ora_session_mode = $connectionMode
}
);
die Connect to $db failed \n unless $dbh;
# time and adjust row cache size
$dbh-{RowCacheSize} = 5000;
my $MySql=select * from $optctl{table};
my $sth = $dbh-prepare($MySql);
$sth-execute;
open(OUT,$optctl{table}.dmp) || die cannot create $optctl{table}.dmp -
$!\n;
my $delimiter = '~';
no warnings; # don't raise warnings on null columns
while( my $ary = $sth-fetchrow_arrayref ) {
print OUT join($delimiter,@{$ary}), \n;
}
use warnings;
$sth-finish;
$dbh-disconnect;
sub Usage {
print \n;
print usage: ul.pl\n;
print ul.pl -database dv07 -username scott -password tiger -table emp
[-sysdba || -sysoper]\n;
print \n;
}
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
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: Is sqlplus too slow to unload data?
Hey Jared,
I got quite a few folks waiting for your book now. I am really pushing
Perl at the office:-) When is the scheduled release again?
-Original Message-
Sent: Thursday, April 25, 2002 12:03 AM
To: Multiple recipients of list ORACLE-L
On Tuesday 23 April 2002 21:53, Bin Wang wrote:
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.
How about using Perl?
Below is a script I just used to unload a table. Not very big, but this
is just from one of my test databases at home on a Linux box.
It unloaded about 12,000 rows in less than 2 seconds. This include
writing them to a file. This script creates a file of TABLENAME.dmp.
$ time ul.pl -database ts01 -username orades -password orades \
-table 'I$RM_PROPERTY_MAPS'
1.22s real 1.07s user 0.04s system
Doing the same thing with SQL*plus took 4.46 seconds.
You must have DBI and DBD::Oracle installed to use this.
Jared
#!/usr/bin/perl
# ul.pl - unload a table
use warnings;
use FileHandle;
use DBI;
use strict;
use Getopt::Long;
my %optctl = ();
Getopt::Long::GetOptions(
\%optctl,
database=s,
username=s,
password=s,
table=s,
sysdba!,
sysoper!,
z,h,help);
#setup environment - homegrown package
my($db, $username, $password, $connectionMode);
$connectionMode = 0;
if ( $optctl{sysoper} ) { $connectionMode = 4 }
if ( $optctl{sysdba} ) { $connectionMode = 2 }
if ( ! defined($optctl{database}) ) {
Usage();
die database required\n;
}
$db=$optctl{database};
if ( ! defined($optctl{username}) ) {
Usage();
die username required\n;
}
if ( ! defined($optctl{table}) ) {
Usage();
die table required\n;
}
$username=$optctl{username};
$password = $optctl{password};
my $dbh = DBI-connect(
'dbi:Oracle:' . $db,
$username, $password,
{
RaiseError = 1,
AutoCommit = 0,
ora_session_mode = $connectionMode
}
);
die Connect to $db failed \n unless $dbh;
# time and adjust row cache size
$dbh-{RowCacheSize} = 5000;
my $MySql=select * from $optctl{table};
my $sth = $dbh-prepare($MySql);
$sth-execute;
open(OUT,$optctl{table}.dmp) || die cannot create $optctl{table}.dmp -
$!\n;
my $delimiter = '~';
no warnings; # don't raise warnings on null columns
while( my $ary = $sth-fetchrow_arrayref ) {
print OUT join($delimiter,@{$ary}), \n;
}
use warnings;
$sth-finish;
$dbh-disconnect;
sub Usage {
print \n;
print usage: ul.pl\n;
print ul.pl -database dv07 -username scott -password tiger -table emp
[-sysdba || -sysoper]\n;
print \n;
}
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
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: Kimberly Smith
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: Is sqlplus too slow to unload data?
I believe the scheduled date is August.
Of this year. :)
Jared
Kimberly Smith [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
04/25/2002 07:08 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: Is sqlplus too slow to unload data?
Hey Jared,
I got quite a few folks waiting for your book now. I am really pushing
Perl at the office:-) When is the scheduled release again?
-Original Message-
Sent: Thursday, April 25, 2002 12:03 AM
To: Multiple recipients of list ORACLE-L
On Tuesday 23 April 2002 21:53, Bin Wang wrote:
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.
How about using Perl?
Below is a script I just used to unload a table. Not very big, but this
is just from one of my test databases at home on a Linux box.
It unloaded about 12,000 rows in less than 2 seconds. This include
writing them to a file. This script creates a file of TABLENAME.dmp.
$ time ul.pl -database ts01 -username orades -password orades \
-table 'I$RM_PROPERTY_MAPS'
1.22s real 1.07s user 0.04s system
Doing the same thing with SQL*plus took 4.46 seconds.
You must have DBI and DBD::Oracle installed to use this.
Jared
#!/usr/bin/perl
# ul.pl - unload a table
use warnings;
use FileHandle;
use DBI;
use strict;
use Getopt::Long;
my %optctl = ();
Getopt::Long::GetOptions(
\%optctl,
database=s,
username=s,
password=s,
table=s,
sysdba!,
sysoper!,
z,h,help);
#setup environment - homegrown package
my($db, $username, $password, $connectionMode);
$connectionMode = 0;
if ( $optctl{sysoper} ) { $connectionMode = 4 }
if ( $optctl{sysdba} ) { $connectionMode = 2 }
if ( ! defined($optctl{database}) ) {
Usage();
die database required\n;
}
$db=$optctl{database};
if ( ! defined($optctl{username}) ) {
Usage();
die username required\n;
}
if ( ! defined($optctl{table}) ) {
Usage();
die table required\n;
}
$username=$optctl{username};
$password = $optctl{password};
my $dbh = DBI-connect(
'dbi:Oracle:' . $db,
$username, $password,
{
RaiseError = 1,
AutoCommit = 0,
ora_session_mode = $connectionMode
}
);
die Connect to $db failed \n unless $dbh;
# time and adjust row cache size
$dbh-{RowCacheSize} = 5000;
my $MySql=select * from $optctl{table};
my $sth = $dbh-prepare($MySql);
$sth-execute;
open(OUT,$optctl{table}.dmp) || die cannot create $optctl{table}.dmp -
$!\n;
my $delimiter = '~';
no warnings; # don't raise warnings on null columns
while( my $ary = $sth-fetchrow_arrayref ) {
print OUT join($delimiter,@{$ary}), \n;
}
use warnings;
$sth-finish;
$dbh-disconnect;
sub Usage {
print \n;
print usage: ul.pl\n;
print ul.pl -database dv07 -username scott -password
tiger -table emp
[-sysdba || -sysoper]\n;
print \n;
}
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
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: Kimberly Smith
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:
INET: [EMAIL PROTECTED]
Fat City Network
Re: Is sqlplus too slow to unload data?
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: 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).
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
RE: Is sqlplus too slow to unload data?
If you have PRO*C and a c-compiler, you can follow this link (you need both lines) to download a basic sqlunldr tool. It is almost as fast as exp. http://asktom.oracle.com/pls/ask/f?p=4950:8:646297::NO::F4950_P8_DISPLAYID,F 4950_P8_CRITERIA:459020243348,%7BSQLDA%7D Caver -Original Message- Sent: Wednesday, April 24, 2002 3:33 AM To: Multiple recipients of list ORACLE-L 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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: Is sqlplus too slow to unload data?
It's SQL*Plus. Love it to pieces, but it just dawdles when used as an unloader. It must spend a tremendous amount of processing just formatting or something... Never bother blaming the network or Net8 unless you are just trying to get someone off your back to delay for time. DBAs will say it must be the network to cause users to go hounding off after the network administrator or systems administrator. While they're baying at that poor person, you should have at least an hour to find out what's really going on... If you are working on UNIX, one thing you can do to speed up unloading/loading is put a UNIX pipe or FIFO between the unloader process (i.e. SQL*Plus or other) and SQL*Loader, instead of a flat file. Saves disk space but most importantly saves time -- you don't have to wait for unloading to complete before loading. In fact, loading must start with the unloading, so you can't help but save both time and space... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, April 23, 2002 10:53 PM 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman 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: 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: [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).
