Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Two DBLINK or not two DBLINK?

RE: Two DBLINK or not two DBLINK?

From: David Kurtz <>
Date: Fri, 15 Dec 2006 19:15:07 -0000
Message-ID: <>

A question you should ask is what processes will use these database links and how many concurrent sessions will be involved.

A while ago I went to a PeopleSoft site with separate HR and Finance database. They had database links going in both directions. BTW - this was a customisation, the vanilla product is not delivered like this.

i) A database link from HR to Financials was used by the Payroll process to post to the GL system. So a single batch process inserted a batch of data into a remote table - that worked fine.

ii) Both HR and Finance hold employee name data. In Financials it is need for employee expense processing to search for employee by name, and to display the name associated with an employee ID. They had replaced the NAMES table in Financials with a view across the database link to the NAMES table in HR. This meant that lots of process where using the link concurrently. I found this in a statspack report.

Top 5 Wait Events

~~~~~~~~~~~~~~~~~                                              Wait     %
Event                                               Waits   Time (cs)   Wt
-------------------------------------------- ------------ ------------- ----
SQL*Net message from dblink                    10,128,485 6,812,419,011
db file scattered read                          4,733,976 6,811,718,066
db file sequential read                         2,744,015 3,405,871,418

The number of sessions that can concurrently use database links is limited by the OPEN_LINKS and OPEN_LINKS_PER_INSTANCE see .

So the number of open links was severely impacting the performance of the finanacials system.
(In this case I suggested that they replicate the table to financials rather than read it across a link every time)


David Kurtz
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
Book: PeopleSoft for the Oracle DBA: The PeopleSoft DBA Blog: PeopleSoft DBA Forum:

> -----Original Message-----
> From:
> []On Behalf Of Jesse, Rich
> Sent: 15 December 2006 18:40
> Subject: Two DBLINK or not two DBLINK?


> Hey all,

> I've finally succumbed to DBLINKs with the hope that it's the lesser of
> evils for our new ERP system. Since I won't have the time to test these
> out as much as I think they warrant, I'm trying to plan for the future.
> One of the things I'm considering is having two DBLINKs for each schema
> in the database -- one for updating, one for read-only queries. Both
> local and destination DBs are and will be on the same server,

> My kneejerk for separating out the links is that I think it will help in
> troubleshooting performance issues from the destination database by
> making the connection easy to identify as well as some security
> isolation, since each DBLINK would connect to a different schema in the
> destination DB. One downside I can forsee is that I would increase the
> DBLINK connections as any update would most likely also have read from
> the DB as well.

> Thoughts? I'm just getting to the "play" stage now, but thought I'd ask
> in advance of testing to try and get as many of the "gotchas" out of the
> way as possible.

> TIA!
> Rich
> --

Received on Fri Dec 15 2006 - 13:15:07 CST

Original text of this message