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

On Jun 5, 8:36 am, "stephen O'D" <stephen.odonn..._at_gmail.com> wrote:
> Oracle 10.2.0.2 on HPUX.
>
> I have a fairly simple query that queries from a table T1 and a view
> V2 (which is just a straight select * from T2).  All objects are owned
> by the same user.
>
> If I explain/run my query as the same user that owns the tables and
> view, it picks a 'good' plan.
>
> If I parse/explain it as another user that has select on all three
> views and tables and it consistently picks a different 'bad' plan.
>
> As a further wrinkle, if I change the query to exclude the view and
> use the underlying table directly (no other change to the query except
> the view name) it always picks the correct plan.
>
> Can anyone offer an explanation for this sort of behaviour?

Not without seeing the plans and possibly 10053 traces for both. Also, you're running 10.2.0.2 and some optimizer-related issues were fixed in 10.2.0.3 so possibly you should consider patching?

David Fitzjarrell Received on Thu Jun 05 2008 - 08:49:39 CDT