Re: Performance of many Distributed Queries joining over link suddenly much poorer

run explain plan,
check the plan_table's other column

set linesize 160
set pagesize 80
set feedback 100
column explain format a40
--column explain format a80
set long 600
set longchunksize 40
column other word_wrapped
select id||lpad(' ',2*level) || operation ||decode(id,0,'Cost='||position)
||' '||object_name as explain, other
from plan_table
where statement_id='&1'
connect by prior id=parent_id
start with id=0

On 15 Nov 2001 18:06:40 -0800, petermerks_at_hotmail.com (Pete M) wrote:

>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

.......
We use Oracle 8.1.6-8.1.7 on Solaris 2.6, 2.7 boxes

Andrey Dmitriev	 eFax: (978) 383-5892  Daytime: (917) 750-3630
AOL: NetComrade	 ICQ: 11340726 remove NSPAM to email