Re: Database Link Question (For Guru)!

In article <8nqtj7$rre$1_at_nnrp1.deja.com>,   japs1_at_my-deja.com wrote:
> I am considering creating a database link between two production
> databases (Db1 and Db2).
>
> The link will allow Db1 to interrogate certain tables on Db2.
>
> Both Db1 and Db2 reside on different servers running Oracle 7.3 on
 Unix.
>
> The question I have is, where does the loading occur when a query is
> executed from Db1 on Db2.

The query is parsed on DB1. Some stat info is obtained from db2, but it appears minimal. We have seen the exact same query generate very different query plan when DBLINKS are in the picture.

>
> Both Db1 and Db2 are supporting business critical applications, and
> this is where my concerns lie.
>
> I am keen to understand the impact on both databases and both servers
> if a query (or multiple simultaneous queries) are run from Db1 on Db2.
>
> Will there be an impact on the Db2 database if queries are run on it
> from Db1?

DB2 impact will be mostly table locks and file contention. Due to the update comming across the wire, there will be lock latency. Also if you are doing a join (NOT RECOMMENDED) all the data us usually returned to DB1 and the join performed there. AVOID JOINS. Create a view on DB2 instead.

>
> What processor takes the biggest hit. Is it server1 or server2, or do
> both servers processors get used?

DB1 for parse. DB2 is minimal CPU but perhaps a lot of IO

>
> Are Unix processes kicked off on both servers if a query is executed
> from Db1 on Db2?

Yes. DB1 decomes a client and DB2 acts just like it was any other user connecting except: the query is not passed through to DB2 as q whole. The pieces are split up and passed and DB1 does all the join, filtering, etc....

>
> Finally, I would appreciate if anyone could advise me of any other
> drawbacks to creating a database link between two production
databases.

• Security for DBLINKS is not strong. Passwords for the link are stored in plain text in a SYS table
• Don't expect all to work just fine. DBlinks tend to have issues whene you get beyond the basics. Bitmap indexes, partitions, referential integrity, update trigger bug/issues/fatures tend to start creeping in.

Bottom line. Keep DBlink use down. Keep it simple. Be prepared with a backup plan if they do not pan out.

>
> Eternal thanks in advance for any help,
>
> Japs1.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

HTH

--
Doug Coan
Oracle Certified Professional DBA


Sent via Deja.com http://www.deja.com/
Before you buy.