Re: ** query over db link behaving strange

"ajoshi" <ajoshi9777_at_yahoo.com> wrote in message news:1130432008.176769.276870_at_g14g2000cwa.googlegroups.com...
> Hi,
> I have a query selecting from a local table and a view which selects
>
> from remote table. The query is doing a full table scan at remote site
> and taking very long. This remote database is replicated. If I change
> the view to point this replicated database of remote database the query
>
> chooses index there and runs very fast. The query sent to the to this
> replicated copy of remote database has this column with index specified
>
> in where clause. so it chooses to use index there. I get this query
> using set autotrace traceonly explain. The query sent to the (original)
>
> remote database does not have this column specifed in where clause. The
>
> database and table are identical along with init parameters. Only
> difference is that table on remote database was re-orged using alter
> table .. move. so it could have different statistics. I tried exp/imp
> of statistics from this problem database to another database but could
> not reproduce the problem. Well...
> So when query executes does it look at statistics or something else
>
> on remote database to determine the access path? Answer from Oracle
> support is that remote database statistics are not looked at when
> determining execution plan. So what is happening here? Wy send
> different query to two databases for the same initial query. The
> version is 8.1.7.4
> The same query when run on remote database (it has view pointing to
> my local table) runs fine using index. The same query with driving_site
>
> hint on my database runs fine using the index. I cannot change the
> query to put in the hint. it is generated so i need to fix this issue
> some other way. Thanks a lot of your help. Thanks
>

Presumably the execution path has changed, and the cost reported by autotrace has changed. Is there any reason why you couldn't post the two execution plans ?

Since the view is local,. the optimiser should have resolved it and then acquired the statistics of the remote table and its indexes. The optimiser can acquire most statistics from a remote database, even in 8.1.7.4, but does not acquire the remote histogram data.

Given a re-org on one of databases it is possible, though a little unlikely, that the subsequent rebuild of the index has managed to reduce the size of the index just enough to decrease the cost of using the index a little, with the result that a nested loop is more desirable than a hash join or merge join.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals
Now available to pre-order.

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005