| How dblink works? [message #169536] |
Thu, 27 April 2006 09:02  |
srhl
Messages: 24
Registered: December 2005
|
Junior Member |
|
|
Hi,
I just want to know whats happening internally, when we migrate data from source DB to target DB using dblink.
Please let me know if there is any material for the same.
any explanation would be more helpful.
i just want to understand how it affects the performance of the database while migrating the data.
Thanks in advance.
|
|
|
|
|
|
|
|
| Re: How dblink works? [message #169563 is a reply to message #169550] |
Thu, 27 April 2006 10:22  |
srhl
Messages: 24
Registered: December 2005
|
Junior Member |
|
|
Hi Mahesh,
Thanks for your reply
suppose if a table contains 3 lakhs record and i want to pull the data from production to my development environment using dblink.
whats the performance of the production db?
how long will it take to pull the data?
for ex,
we will be doing
insert into table1 as select * from table1@dblinkname;
how many rows it will fetch at a time?
how often will it commit?
|
|
|
|
| Re: How dblink works? [message #169566 is a reply to message #169563] |
Thu, 27 April 2006 10:41 |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
>>if a table contains 3 lakhs record
In most cases, this is piece of cake. My PC,which is a SunBlade-100 did this in a few minutes.
>>whats the performance of the production db?
>>how long will it take to pull the data?
I cannot comment anything without ***knowing*** your environment.
Your hardware configuration in source/destination, server load, network etc. One important factor is your HWM.
If the source table goes through a lot of inserts/updates/deletes and if HWM is never been reset, your process may take a little more time (because oracle needs to scan every block, even if the block holds no data).
>>we will be doing
>>insert into table1 as select * from table1@dblinkname;
You may want to add an append hint.
or
even do CTAS with nologging.
scott@9i > create table new_emp /*+ APPEND */ as select * from emp nologging;
Table created.
>>how many rows it will fetch at a time?
Depends on your ARRAYSIZE
scott@9i > show arraysize
arraysize 15
>>how often will it commit?
You mean, how often you would like to commit?
Once. Never often.
Issue a Commit once after everything is done.
[Updated on: Thu, 27 April 2006 10:44] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
