| DB Link, transactions and performance [message #20442] |
Fri, 24 May 2002 04:05  |
Mike Nibeck
Messages: 49
Registered: May 2002
|
Member |
|
|
Environment: Multiple 8.1 DBs across multiple servers
We were looking at creating an application architecture that would have a central database (Hub), that would attach to multiple external databaases using database links. Our front-end application would perform all transaction using a library of stored procs in the Hub database. These stored procs would create the appropriate links to the remote DBS, and performs the reads/writes/updates. There are many reasons (political and otherwise) that we are looking at this architecture.
I'm new to Oracle, and specifically DB Links.
Will Oracle handle the transaction integrity if the transaction spanned multiple DBs via links?
Are there any performance issues with this architecture?
Anything else I should know about links?
Note: I have tested the current configuration, and am able to establish the links without problems.
- Mike
|
|
|
|
| Re: DB Link, transactions and performance [message #20444 is a reply to message #20442] |
Fri, 24 May 2002 08:18  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Oracle will use the two-phase commit mechanism to insure transaction integrity in a distributed environment.
Remember that a commit or rollback will only be allowed on the database that is initiating the transaction. You cannot call a remote procedure that contains any transaction management statements.
|
|
|
|
| Re: DB Link, transactions and performance [message #20447 is a reply to message #20442] |
Fri, 24 May 2002 17:13 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
As Todd says, two phase commit will be handled by Oracle for Transactional Integrity. Referential Integrity however isn't supported across DB links.
Consider the fact that with a distributed database like that, you have a backup and restoration problem if you are trying to maintain data integrity. If one of the DB needs restoring - what will you do with the others? Having a distributed DB gives you multiple points of failure and you need to consider the impact when maintenance need to be done on one or more DB.
DB links by their nature usually have substantially lower bandwidth when they are across WANs or LANs and both LAN or WAN DB links will be much slower than no DB link. Not only the bandwidth but the network latency will be issues affecting performance across DB links. I'd imagine the back and forth communication associated with a query is serialised.
Joining tables across DB links can also be an issue because you could need to fetch an entire table across the network to join to a local table. See the driving site hint if you are interested in controlling that stuff:
SELECT /*+DRIVING_SITE(dept)*/ *
FROM emp, dept@rsite
WHERE emp.deptno = dept.deptno;
You could have code residing on each of the remote DB's (packages) which only return final results. Creating views on the remote DBs can also help overcome unnecessary network traffic.
Look at Oracle's data replication which allows you to have a master DB and then you replicate selected tables (and or code) to the slave DBs. You can also have multi master where updates done on any of the DBs will be replicated to the others. It's all GUI driven these days.
Sometimes you need multiple DB links from one schema to multiple other schemas on the same DB. This lesser known functionality can be useful...
create database link boston_db.world@scott connect to scott identifed by tiger using 'boston_db';
create database link boston_db.world@bill connect to bill identifed by billpass using 'boston_db';
select sysdate from dual@boston_db.world@scott;
select sysdate from dual@boston_db.world@bill;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
