Performance of many Distributed Queries joining over link suddenly much poorer

How does Oracle determine the driving site for a distributed query if no hint is given and both databases are running the rule based optimizer? Are there any parameters, settings, etc., that would suddenly cause a change in many execution plans.  

We have been using distributed queries for some time. A typical query may join a 10000 row table on the calling site to a million row table on the remote site. Although performance has not been ideal we have found it to be adequate and in general the query is effectively moving the rows to the correct location, minimizing network traffic. Over the last few months it seems a number of queries have gotten dramatically worse over a very short period of time. We are using RULE based optimizing on both sites. The calling site is still 7.3.4 and the called site is Oracle 8.1.7. To the best of our knowledge there has been no change in indexes or any other specific schema changes.

I know we could use the driving site hint but it seems to us that suddenly many of our queries are much worse then below. We don't want to have to tune each individually.

A typical query might look like this.... (I know distributed is not ideal but this is our limitation right now).

select a.c1, a.c2, a.c3, b.c4, b.c5
from tablocal a,
tabremote_at_ourlink b
where a.c1 = b.c1

and a.c2 = b.c2
and a.c6='ACTIVE' 
and b.c9='I'

thanks,
Pete Merks Received on Thu Nov 15 2001 - 20:06:40 CST