Is sqlplus too slow to unload data?
wangbin
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
Sybrand Bakker
Make sure you run this as a cron job, spooling to a file, *termout
off*. Terminal display is a *HUGE* delay factor in sql*plus.
Your arrysize is also probably just TOO big: arraysize * <the number
of bytes per record> shouldn't exceed SDU (default 2048), or it will
also run much much slower (tested!). The benefits of a big arraysize
are yet another myth to be addressed by this group (anyone willing to
do this? Howard? Jonathan?)
Hth
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address
Katherine
1) what sqlloader path are you using? (conventional or direct)
2) do you have any integrity constraints or triggers active?
3) what is your commit point or data saves?
4) are there indexes?
5) are you pre-sorting before insert?
6) did you run an explain plan on the sql statement --- how optimized is it?
a good reference for sqlloader is the utilities manual.
"wangbin" <wan...@start.com.au> wrote in message
news:2d15bd69.0204...@posting.google.com...
Herman de Boer
sqlplus has not been designed to extract large amounts of data.
That's why we created sqlout - the common counterpart for sqlloader.
Drop me an email, if you're interested.
Kind Regards,
Herman de Boer (h.de bo...@itcg.nl)
sr consultant
IT Consultancy Group bv
the Netherlands.
RSH
dbunload.
Depending on whether you are doing replace or merge/update stuff between the
instances, you may find that (if you are replacing entire tables and their
relatives) a TRUNCATE ot the target tables and EXPORTing from the source
instance and IMPORTing to the target instance might be faster and easier,
particularly if you make use of the options to bypass the conventional path,
and don't forget, always say NO to COMPRESS.
Are these instances on the same server? I am presuming not.
What operating system is involved ?
Anyhow, if the two machines are isolated, exp/imp could help a lot,
providing a million provisos of course (nothing good is ever free of some
curse); using SQLPLUS to dump out tables in a SQL*LOADER form than you ship
somehow over to the target system would seem to be inefficient; sqlplus does
all the work reparsing and outputting the data and then sqlldr turns around,
of course not knowing the data's origin is Oracle, and reparses and loads
it. At least EXP/IMP trust each other, and as long as you can do complete
clean replaces (this means considering referential links, PK's and the
rest), it certainly should be faster than what you are presently doing.
As far as SQL*PLUS goes, as was said, it wasn't REALLY meant as a data
extraction/transport tool for things like moving stuff between instances,
but it could be tuned a bit, perhaps, if that's what you want to do.
As a completely gross kludge (hoping everyone's digested their breakfast /
lunch / dinners, etc) something else you could try is ... using Microsoft
Access to suck the data out of the origin database and push it into the
target database. I suggest this with great embarassment, but only because
we've done it sometimes, out of desparation. You incur at least double hop
SQLNET link delays, but depending on how much data needs to be moved, how
often, and other circumstances, it can often end up being more expedient
than writing lots of code.
As far as networking goes, you indicated the machines are isolated from each
other, so I don't understand the relevance of the tep/ip parameter issue. Is
SQL*PLUS natively running on the source machine, or elsewhere?
Just trying to help...
RSH.
"wangbin" <wan...@start.com.au> wrote in message
news:2d15bd69.0204...@posting.google.com...
wangbin
Sybrand,
The following is my set parameters in sqlplus. The result is the same.
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 20;
From the definition of arrysize, it looks the same as array fetch.
In Guy Harrison' book, it shows that it could improve the performance.
Katherine,
I didn't complain any thing about sqlloader.
RSH,
> Oracle has always annoyed me about not having an equivalent to INFORMIX's
> dbunload.
Totally agree. The logic may be if you ever load the data into oracle
DB, you shouldn't have any requirement of unloading them. Our Sybase
DBA laughs at me many times because of it. There are some 3rd party
tools around,
PDQOUT, sqlout, even code:
http://asktom.oracle.com/pls/ask/f?p=4950:8:646297::NO::F4950_P8_DISPLAYID,F
4950_P8_CRITERIA:459020243348,%7BSQLDA%7D
Wouldn't it be nice if Oracle offically issues one?
> Depending on whether you are doing replace or merge/update stuff between the
> instances,
In our site, the process has two tasks. One is moving data from OLTP
to report server,both on solaris. A little bit like replica. The
archives will be kept for audit. I know one solution is set up
temporary tables on both instances->
insert as select-> exp(direct path)/imp -> insert again.
However, what's data extraction/transport tool in oracle? Offically
exp/imp isn't. Sqlplus definitely is reporting tool for DBA. exp
tablespace sounds beautiful, but it replies on the same version and
platform. So you cannot use the result as an archive.
> but it could be tuned a bit, perhaps, if that's what you want to
It is exactly what I want. I'm a production DBA. The whole application
is provided by a third party. Our operators do the process by using
browser. Any change to the code involves a lot of processes. Therefore
I intend to change as small as possible. Now the speed of sqlplus is
only 5M/minute, and it took
four hours. If it is increased to 20M/minute, I achieve the goal.
Someone told me that he inceased sqlplus speed to 60M/minute by seting
tcp.nodelay=yes on protocol.ora. However, it doesn't happen in my site
although I restart both server and listener after I change it, and it
also doesn't make sence to me, since PDQOUT can achieve 60M/minute on
my site.
> using Microsoft
> Access to suck the data out of the origin database and push it into the
> target database.
It's the last thing I will do. However, I did see it happen somewhere.
Kind Regards,
Bin
Christop...@oracle.comx
> Thanks for all reply.
>
> Sybrand,
> The following is my set parameters in sqlplus. The result is the same.
> 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 20;
>
If you do end up using SQL*Plus for this task you might want to
benchmark these settings as well:
SET SERVEROUTPUT OFF
SET APPINFO OFF
SET DEFINE OFF
SET AUTOPRINT OFF
Play with SET TAB and SET FLUSH. Tune LONGCHUNKSIZE if LONGS or LOBs
are being fetched. Keep LINESIZE as small as possible to avoid extra
memory allocations/copying.
Chris
--
Christopher Jones, Oracle Corporation, Australia.
wangbin
I test all settings. Only LINESIZE has improvement, which is huge.
When I change it to 500, the speed is about 30M/m. However, the
avarage of the line is about 2100. So I have to increase it to 2500,
then the speed drops to 11M/m. It appears that it does reach the
limitation of sqlplus.
Thanks,
Bin
Christop...@oracle.comX wrote in message news:<uy9f52...@oracle.comX>...
> wan...@start.com.au (wangbin) writes:
>
> > Thanks for all reply.
> >
> > Sybrand,
> > The following is my set parameters in sqlplus. The result is the same.
> > set pages 0;
> > set lines 10000;
>
Christop...@oracle.comx
> Hi Chris,
>
> I test all settings. Only LINESIZE has improvement, which is huge.
> When I change it to 500, the speed is about 30M/m. However, the
> avarage of the line is about 2100. So I have to increase it to 2500,
> then the speed drops to 11M/m. It appears that it does reach the
> limitation of sqlplus.
Thanks for the feedback.
My seat of the pants guess with LINESIZE would be that 2500 crosses a
blocksize boundary somewhere.
Chris
>
> Thanks,
> Bin
>
> Christop...@oracle.comX wrote in message news:<uy9f52...@oracle.comX>...
> > wan...@start.com.au (wangbin) writes:
> >
> > > Thanks for all reply.
> > >
> > > Sybrand,
> > > The following is my set parameters in sqlplus. The result is the same.
> > > set pages 0;
> > > set lines 10000;
> >
> > If you do end up using SQL*Plus for this task you might want to
> > benchmark these settings as well:
> >
> > SET SERVEROUTPUT OFF
> > SET APPINFO OFF
> > SET DEFINE OFF
> > SET AUTOPRINT OFF
> >
> > Play with SET TAB and SET FLUSH. Tune LONGCHUNKSIZE if LONGS or LOBs
> > are being fetched. Keep LINESIZE as small as possible to avoid extra
> > memory allocations/copying.
> >
> > Chris
--