No question at this time
DBA Top 10
1 M. Cadot 5800
2 B. Vroman 3800
3 J. PĂ©ran 2200
4 A. Kavsek 1400
5 P. Tsongayinwe 800
6 D. Johnson 700
7 T. Boles 600
8 D. Walgude 400
9 J. Alcroft 200
9 S. Spb 200
9 P. Wisse 200
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48667
Total active users1334
Act. users last 24h0
Act. users last hour1
Registered user hits last week21
Registered user hits last month191
Go up

How to speed up the loading process
Next thread: DBMS_JOB EXECUTION
Prev thread: a message to REDA: Get a life!

Message Score Author Date
Hi, I our data warehouse,about 3 million rows...... neeraj bhatia May 05, 2005, 09:46
Hi neeraj, Why dont you try using sql loader to...... malay maru May 05, 2005, 10:18
Dear Mamay, Ya, Its a nice suggestion. I will c...... neeraj bhatia May 05, 2005, 10:49
check this link: http://www.lc.leidenuniv.nl/aw...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts malay maru May 05, 2005, 11:04
Dear Malay, I'm reviewing the link . I think dis...... neeraj bhatia May 05, 2005, 11:30
Neeraj, Not on production server, it should be ...... malay maru May 05, 2005, 11:35
Hi neeraj, first check that you don't have any ...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Ales Kavsek May 05, 2005, 12:04
Hi Ales, It;s really interesting but how come i...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts malay maru May 05, 2005, 12:14
Dear Malay, I want to say that In the same ti...... neeraj bhatia May 05, 2005, 12:26
Anyways you are rebuilding those indexes after loa...... malay maru May 05, 2005, 12:31
It didn't increase the speed over dblink since SQL...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Ales Kavsek May 05, 2005, 12:35
Hi Ales, This means that a bunch of records are...... malay maru May 05, 2005, 12:47
Nope. It would be nice but it doesn't work this wa...... Ales Kavsek May 05, 2005, 13:07
It means we neet to set ARRAYSIZE where db1 reside...... malay maru May 05, 2005, 13:20
Hi malay, for example if you use client PC to c...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Ales Kavsek May 05, 2005, 14:49
Hi, In addition to setting the arrarysize, tr...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Kathamuthu Raj May 05, 2005, 16:23
Hi Raj, speaking for my case, yes, I did consid...... Ales Kavsek May 05, 2005, 23:03
There is the way to speed up the unload - load pro...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Bob White May 10, 2005, 20:59
Hi, Ales, You have an interesting question. I c...... Yong Huang May 11, 2005, 15:35
Hi Yong, I'm 100% sure that arraysize set in SQ...... Ales Kavsek May 11, 2005, 16:09

Follow up by mail Click here


Subject: How to speed up the loading process
Author: neeraj bhatia, India
Date: May 05, 2005, 09:46, 6227 days ago
Oracle info: 9.2
Message: Hi,

I our data warehouse,about 3 million rows are inserted into a table from the another Production database through a db link daily.
This process is so slow.
Please suggest me how to speed up this process.
How can I use parallel option.
Tables are not in parallel mode,then whether parallel hint in the select statement will make the change.

There are following queries in the script.

for i in ( select * from tab@dblink where dated>=sysdate-1 and dated<=sysdate)



parallel_max_servers integer 5
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_server boolean FALSE



With Regards,
Neeraj


Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to speed up the loading process
Author: malay maru, India
Date: May 05, 2005, 10:18, 6227 days ago
Message: Hi neeraj,

Why dont you try using sql loader to load the data. Make some arrangement to unload data from production on a daily basis and load that data using SQL loader. You need to receive that flat file using ftp from primary server to dwh server.

It will be much faster.

Malay
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to speed up the loading process
Author: neeraj bhatia, India
Date: May 05, 2005, 10:49, 6227 days ago
Message: Dear Mamay,

Ya, Its a nice suggestion. I will consider on that.
Can you please tell me something about parallelize the insert and select operations.
Both the tables are on noparallel mode,can we stiil provide a parallel hint.

If you have any good link for parallel query,let me know.



Thanks and regards,
Neeraj


Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to speed up the loading process
Author: malay maru, India
Date: May 05, 2005, 11:04, 6227 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: check this link:

http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96524/c21dlins.htm

One more thing if you have created indexes on target table then disable those indexes, fire insert /*+ append */ into table_name select * from t1@something, and then rebuild those indexes.

Malay
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to speed up the loading process
Author: neeraj bhatia, India
Date: May 05, 2005, 11:30, 6227 days ago
Message: Dear Malay,
I'm reviewing the link .
I think disabling the indexes will be much costly as during the loading process ,the data from target tables are selected. If I disble the index,then queries will go for full table scan.



Neeraj
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to speed up the loading process
Author: malay maru, India
Date: May 05, 2005, 11:35, 6227 days ago
Message: Neeraj,

Not on production server, it should be at DWH server where you are loading into table.

insert /*+ append */ into dwh_table
select * from t1@something; ... I am talking about disabling indexes at dwh_table not of t1.

I hope you got it.

Malay
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to speed up the loading process
Author: Ales Kavsek, Slovenia
Date: May 05, 2005, 12:04, 6227 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Hi neeraj,

first check that you don't have any bitmap index on the destination table, then try to use sql*plus COPY with arraysize set to its max. value of 5000 (I'm sure you'll find this approach several times faster than dblink!):

SET ARRAYSIZE 5000
COPY FROM .... INSERT .... USING .....;

Why? I was in similar situation some months ago replicating data between two hosts (one was OS/390, the other one W2K, both with Oracle 9.2.0.5) connected over good WAN connection (ATM, 20Mbps, fiber optics). We needed about 5-6h to transfer cca. 2.5 mio rows (1GB) of data in the off-peak hours - this was bad, really bad.
Analyzing wait events for the session I realized that most time was wasted on more data from dblink wait event, analyzing further the distribution of waits (with simple python script parsing trace file) I found that we had severe number of waits in 100-200ms range, and some even in 1-2s range (on distance of a couple of km between two sites, with usual latency of less than 10ms). As far as we could check other data transfer between the two sites was working smoothly, without any performance degradation.
The problem was in my opinion partly on OS/390 side (lack of CPU) and partly on Oracle Bug 2375503 "Fetch from remote row source may be slow". I couldn't find documented or undocumented way that I could force Oracle to use larger arraysize for fetching data over dblink, so I replaced snapshots with tables that are filled with the data via SQL*Plus COPY and ARRAYSIZE 5000. Time needed to transfer the same amount of data droped from 5-6h to 15".

Regards,
ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to speed up the loading process
Author: malay maru, India
Date: May 05, 2005, 12:14, 6227 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi Ales,

It;s really interesting but how come it increase the speed of operation over the use of dblink?

Can you explain a bit more.

Thanks.

Malay
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to speed up the loading process
Author: neeraj bhatia, India
Date: May 05, 2005, 12:26, 6227 days ago
Message: Dear Malay,

I want to say that In the same time when data is being loading on the DWH table,data from the table is fetched. So it is not possible for me to set the indexes unusable.
Its not a purely DWH. Its hybrid database.



Neeraj
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to speed up the loading process
Author: malay maru, India
Date: May 05, 2005, 12:31, 6227 days ago
Message: Anyways you are rebuilding those indexes after loading the data. Isn't it? So I think it should not be a problem in your case. This is just to increase the speed of data load that's it.

Malay
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to speed up the loading process
Author: Ales Kavsek, Slovenia
Date: May 05, 2005, 12:35, 6227 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: It didn't increase the speed over dblink since SQL*plus copy doesn't use dblink. The point is that you want to lower the number of roundtrips over the network, one way to do it is by increasing arraysize (to 5000) in sql*plus session (or you can write your own script in C, perl, python...that use OCI library), as said before I couldn't find a trick to 'influence' the server to increase arraysize for dblink and thus lower the roundtrips - on Metalink I found some forum replies that suggest that Oracle is dynamically adjusting this parameter but it doesn't seem to work very well, at least not in my environment.

So basically what I had done is replacing solution A with B:
A) db1 -> dblink -> db2
B) db1 -> Copy (Client PC) -> db2

Of course, you can execute sql script with copy statements on server (db2) and eliminate round trip to/crom client (but this was not possible in my case).

regards,
ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to speed up the loading process
Author: malay maru, India
Date: May 05, 2005, 12:47, 6227 days ago
Message: Hi Ales,

This means that a bunch of records are copied out from one server to another, right? And this will decrease the number of round trips.

Is it not possible if I set ARRAYSIZE and use dblink in this case?

Malay
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to speed up the loading process
Author: Ales Kavsek, Slovenia
Date: May 05, 2005, 13:07, 6227 days ago
Message: Nope. It would be nice but it doesn't work this way.

SQL*Plus
PC <--Arraysize=5000--> DB1 <--dblink--> DB2

When you set ARRAYSIZE, it's for the connection between your client and DB1, if you use dblink then DB1 (not you!) talks to DB2, and here is that I couldn't find a fix. How to change arraysize for dblink between DB1 and DB2.
If anyone knows the answer, please let me know!

Regards,
ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to speed up the loading process
Author: malay maru, India
Date: May 05, 2005, 13:20, 6227 days ago
Message: It means we neet to set ARRAYSIZE where db1 resides.. From where we are copying the data. Am I right?

Malay
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to speed up the loading process
Author: Ales Kavsek, Slovenia
Date: May 05, 2005, 14:49, 6226 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi malay,

for example if you use client PC to copy data *from* DB1 *to* DB2 with SQL*Plus and set arraysize to 5000 then what you have is:

DB1 <---arraysize=5000--> PC (SQL*Plus) <-- arraysize=5000 --> DB2

You have two sessions opened at the same time, one with DB1 and one with DB2, both with arraysize 5000. There is no such parameter that you can setup on db1 or db2 that'll force *dblink* arraysize (or at least I don't know one).

If you choose to run SQL script with COPY on db1 (push) or db2 (pull) then it's the same story, two sessions:

DB1 <----arraysize=5000--> SQL*Plus on DB2 host <--array=5000--> DB2


regards,
ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to speed up the loading process
Author: Kathamuthu Raj, United States
Date: May 05, 2005, 16:23, 6226 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi,
In addition to setting the arrarysize, try to increase the default sql*net packet size (SDU and TDU - I think the default size 2k).
In your tnsnames.ora
(ADDRESS_LIST =
(SDU=16384)
(TDU=16384)
(ADDRESS = (PROTOCOL = TCP)(HOST = yourhost)(PORT = 1521))
)


HTH

Regards,
RajK
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to speed up the loading process
Author: Ales Kavsek, Slovenia
Date: May 05, 2005, 23:03, 6226 days ago
Message: Hi Raj,

speaking for my case, yes, I did consider these two parameters back then (there isn't much more to tweak on Net anyway ;). You're right, default size for SDU is 2048 bytes (32767 bytes for TDU).
The problem is that I'm not in a control of listener on OS/390 where listener is listening for several instances (and frankly mainframers are special;-), so the idea of changing SDU size for listener was not an option for me.
However, I have some doubts about increasing SDU size without also increasing arraysize - in case of dblink where we're not in a control of arraysize (I really hope someone knows better and prove me wrong!), we could start sending the same number of frames (controlled by arraysize), just larger frames and mostly empty. I would definitely try this on some special port (1526) and truly test it before changing anything on standard ('production') ports.

Regards,
ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to speed up the loading process
Author: Bob White, United States
Date: May 10, 2005, 20:59, 6221 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: There is the way to speed up the unload - load process without affecting production database.:

1) Extract tables into flat file on local machine
2) compress it
3) ftp or other way to copy to destination
4) load data with SQL loader.

Network roundtrip will be minimal

If tables that you are unloading are very large, you may try using Fastreader for automating of entire process.

Fastreader www.wisdomforce.com is exporting data into flat csv files 7 - 10 time faster than native oracle export does into binary format, and automatically creates loaders to destination. However Fastreader is commercial product
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to speed up the loading process
Author: Yong Huang, United States
Date: May 11, 2005, 15:35, 6220 days ago
Message: Hi, Ales,

You have an interesting question. I checked 9i SQL*Plus User's Guide and Reference. It says "The SQL*Plus SET ARRAYSIZE variable determines the size of a batch." I wonder if the arraysize you set on your client PC side also determines the arraysize between DB1 and DB2. Can you do a test by varying arraysize and and check consistent gets when you run a COPY between DB1 and DB2?

Reference also warns several times that COPY is deprecated but it doesn't say what is its future replacement.

Yong Huang
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to speed up the loading process
Author: Ales Kavsek, Slovenia
Date: May 11, 2005, 16:09, 6220 days ago
Message: Hi Yong,

I'm 100% sure that arraysize set in SQL*Plus doesn't determine arraysize OF THE DBLINK between db1 <-dblink-> db2. During my test (and also later on Metalink forum) I confirmed that.

Of course, since during COPY you have TWO separate sessions with both servers, arraysize with PC <-> DB1 and PC <-> DB2 is 5000 - so this is a nice workaround, but would prefer if I could influence arraysize for the dblink as well.

Imho, COPY statement will stay in SQL*Plus for quite some time (without support of more modern type, such as BLOBs, but with current functionallity).

Regards,
ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here