Re: same query, different parsing users, different plans ...

On Jun 5, 8:16 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Jun 5, 10:10 am, "stephen O'D" <stephen.odonn..._at_gmail.com> wrote:
>
> > I have 10053's for each scenario - the telling thing in them is that
> > Oracle doesn't even consider the index on V2/T2 when it chooses the
> > bad plan - search the entire file for it, and all you get is the part
> > that lists its stats.
>
> > On the good plan, you can see it trying the join scenarios and costing
> > a full scan of T2 and then an Index probe (which wins by a mile - cost
> > of 3/4 instead of 20000 odd).
>
> > If you think you will have time to look I can post the full query and
> > 10053's - its a lot of stuff to post to here though.
>
> You might check the second to last post in this thread to see if it
> applies:http://groups.google.com/group/comp.databases.oracle.server/browse_th...
>
> It seems like I have seen a couple blog posts that describe this
> problem, but I can't find those at the moment.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

That might just be it - I will be testing it out first thing tomorrow:

Quoting from metalink:

If the Text query is being parsed by a query-user not being the owner the optimizer might choose a wrong execution plan in the 10gR2 release, because of lack of privileges.
The user who does NOT own the objects requires the "merge any view" privilege.

This problem is only seen in the 10.2 release and relates to the database parameter optimizer_secure_view_merging which is new in 10.2 as well.

Thanks for that - as this has been turning into one of those "you have to restart the database twice on a Monday, but only if its raining" type of problems (obscure reference from the talk Tom Kyte gives to explain bind variable peeking), as the instance just seems to pick the wrong plan every now and then.

I will update again if this fixes it. Received on Thu Jun 05 2008 - 15:22:11 CDT