RE: Two DBLINK or not two DBLINK?

From: David Kurtz <info_at_go-faster.co.uk>
Date: Fri, 15 Dec 2006 19:15:07 -0000
Message-ID: <CKEAJBMGFEOCDBFILPJDAEIMIGAA.info@go-faster.co.uk>

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     %
Total
Event                                               Waits   Time (cs)   Wt

Time

-------------------------------------------- ------------ ------------- ----
---
SQL*Net message from dblink                    10,128,485 6,812,419,011
40.00
db file scattered read                          4,733,976 6,811,718,066
40.00
db file sequential read                         2,744,015 3,405,871,418

20.00

The number of sessions that can concurrently use database links is limited by the OPEN_LINKS and OPEN_LINKS_PER_INSTANCE see
http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96536/ch1133.htm .

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)

regards

David Kurtz
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
mailto:david.kurtz_at_go-faster.co.uk
web: www.go-faster.co.uk
Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com The PeopleSoft DBA Blog: http://psftdba.blogspot.com PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jesse, Rich
> Sent: 15 December 2006 18:40
> To: ORACLE-L
> 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 10.2.0.2.0 and will be on the same server,
> FWIW.

> 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
> --
> http://www.freelists.org/webpage/oracle-l

>
>
>

--
http://www.freelists.org/webpage/oracle-l

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