No question at this time
The DBA-Village forum
as RSS feed
Site StatisticsEver registered users | 48667 | Total active users | 1334 | Act. users last 24h | 0 | Act. users last hour | 1 | Registered user hits last week | 21 | Registered user hits last month | 191 |
|
Go up
How to speed up the loading process
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...... |
     |
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 ...... |
     |
Ales Kavsek |
May 05, 2005, 12:04 |
Hi Ales,
It;s really interesting but how come i...... |
     |
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...... |
     |
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...... |
     |
Ales Kavsek |
May 05, 2005, 14:49 |
Hi,
In addition to setting the arrarysize, tr...... |
     |
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...... |
     |
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 |
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: |
       |
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: |
       |
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: |
       |
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: |
       |
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: |
       |
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: |
       |
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: |
       |
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
|