Re: ** outline change
- From: A Joshi <ajoshi977@xxxxxxxxx>
- To: Nigel Thomas <nigel.thomas@xxxxxxxxxxxxxx>, riyaj.shamsudeen@xxxxxxxxx
- Date: Thu, 19 Mar 2009 07:14:21 -0700 (PDT)
Riyaj,
The sql being executed is simple MV refresh like :
exec dbms_mview.refresh('ACTIVITY_MV', 'F')
Then this internally kicks off sql like
SELECT /*+ remote_mapped(ACT_SOURCE
This sql I tried to capture in full and execute at sqlplus level but it
has a bind variable :1 and that I am not able to simulate.
Can you give more detailed steps on how I can do dbms_xplan.display_cursor
So far : I have not been able to capture much.
Yes, Nigel is right. I contacted the source DBA but he says he cannot
make any change at his database level : the usual : that all other MVs
are refreshing fine and no changes made at his database level recently.
He suggested that MV refresh part can be changed so it sends the right
sql to his database. With right join and hint etc. Thanks
I do have a outline for the sql : SELECT /*+ remote_mapped(ACT_SOURCE and
wanted to change that.
Nigel,
About changing the sql for MV : It is like a select .. from
activity@act_source
Are you suggesting this be changed? I do not know if it will help but I
can try it. I did not know if giving hint at this level will work and
if any issue with it. I also see that other MVs are similar and they
are executing OK : meaning doing index scan on source. Thanks
--- On Thu, 3/19/09, Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx> wrote:
From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
Subject: Re: ** outline change
To: "Nigel Thomas" <nigel.thomas@xxxxxxxxxxxxxx>
Cc: ajoshi977@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
Date: Thursday, March 19, 2009, 8:11 AM
HiJoshi
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@xxxxxxxxxxxxxx>
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@xxxxxxxxx>
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
Other related posts:
