Re: ** outline change

   Yes, Nigel is right on money. I think, I asked for the SQL too. Can you please post the SQL? and also plan from SGA for that SQL using dbms_xplan.display_cursor call?

  If mlog$ full scan is slow, there are few indices that can be added to mlog$ to tune that query.

Cheers
Riyaj

On Thu, Mar 19, 2009 at 4:46 AM, Nigel Thomas <nigel.thomas_at_preferisco.com>wrote:

> Joshi
>
> Isn't the problem here that you are refreshing an mview on database
> T(arget) using a query which is running on database S(ource). The query is
> shipped as is to S, which acts as the driving site (I think). So for any
> query rewrite to happen, you would have to do it on S, the source. Which you
> mentioned you only have read privileges for (not DBA).
>
> Time to talk to the DBA for that database?
>
> Alternatively - and to my mind, a lot simpler: just tune the mview's SQL in
> the normal way, rather than by query rewrite. So start with the SQL and see
> if there is an alternative way of getting the same result without the
> offending FTS. I don't think we've seen the query or the (unsatisfactory)
> plan yet...
>
> Regards Nigel
>
> 2009/3/18 A Joshi <ajoshi977_at_yahoo.com>
> Riyaj,
> Thanks for your help. tkprof shows full table scan on the remote
> database. So even if the mlog has 10 entries : it is doing full table scan
> on source table which is big and fts is taking almost one hour . It does not
> do a simple index search which will take 1 minute. I was able to get the big
> query which uses current$ and mlog$. It has a bind variable 1 (:1) and I am
> not able to create a bind variable with 1
>
>>
>>
>>
>

--
http://www.freelists.org/webpage/oracle-l