Re: ** outline change

From: Riyaj Shamsudeen <>
Date: Thu, 19 Mar 2009 07:11:50 -0500
Message-ID: <>


   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.


On Thu, Mar 19, 2009 at 4:46 AM, Nigel Thomas <>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 <>
> 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

Received on Thu Mar 19 2009 - 07:11:50 CDT

Original text of this message