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

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: Thu, 5 Jun 2008 06:36:09 -0700 (PDT)
Message-ID: <953d9854-2b43-4d7d-a252-da89771aac1e@b1g2000hsg.googlegroups.com>


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? Received on Thu Jun 05 2008 - 08:36:09 CDT

Original text of this message