Re: DBLINK and 2PC

akreienbuhl_at_yahoo.com (alainkr) wrote in message news:<9822b77f.0210010637.74b34b9a_at_posting.google.com>...
> Hello,
>
> I can achieve 2 phase commit by using DBLINK right ? Are there any
> performance aspect I should be aware of before using DBLINK
> extensively between 2 DB ?
>
>
>
> Thanks for your Help.
>
> Alain.

Alain, when you tune an SQL statement involving local tables that has a coordinated subquery or sub-select you often try to convert it into a join to bet better performance. In the case of a distributed join query you may find that Oracle is full table scanning one or more remote tables and the solution is to convert the join into sub-queries that can make use of indexed columns in the remote table to repeatedly select only desired rows from the driving query. In other words the tunning process is sometimes backward to what you might normally want to do.

In general if your remote queries use indexed columns then performance will be good (barring network performance issues), but if your application SQL plans result in repeated full table scans you can expect problems. Someone might be tempted to point out this is probably true for local queries but the point here is that how you approach solving the problem might be slightly different.

If you are new to distributed SQL you might want to check out the cooperative FAQ article: Why does it seem that a SELECT over a db_link requires a commit after execution ?

AT url: http://www.jlcomp.demon.co.uk/faq/dblink_commit.html

HTH -- Mark D Powell -- Received on Tue Oct 01 2002 - 13:54:53 CDT