Re: Is sqlplus too slow to unload data?

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;

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 Received on Mon Apr 29 2002 - 02:07:53 CDT