Re: CBO & different execution plans

On Mar 7, 2:34 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> On Mar 7, 3:04 pm, "Matthias Hoys" <a..._at_spam.com> wrote:
>
>
>
>
>
> > Hello,
>
> > Oracle 10.2.0.3.0 64-bit on RHEL 4
> > optimizer_index_caching = 80
> > optimizer_index_cost_adj = 20
> > optimizer_mode = FIRST_ROWS, statistics have been gathered (with
> > histograms).
>
> > Today I noticed the following strange CBO behaviour :
>
> > User A is the owner of a number of objects of a third-party application.
> > User B has access to the objects of user A through role grants and public
> > synonyms.
>
> > The problem is that certain queries run fast with user A (the schema owner),
> > while they run slow with user B (and all other users with access to the
> > application). However, this only happens when the system optimizer_mode =
> > FIRST_ROWS ! When I change it to CHOOSE, there are no performance
> > differences. It looks like, with an optimizer_mode of FIRST_ROWS, the CBO
> > chooses different execution plans based on the user who is executing the
> > queries ??
> > There are no logon triggers, different user profiles or objects with the
> > same name as the public synonyms ...
>
> > Has anyone else seen this behaviour ? I haven't found the time yet to do a
> > 10053 trace, the application is rather complex and generates a large amount
> > of queries. For now, I changed the optimizer_mode to CHOOSE, but I want to
> > keep FIRST_ROWS for a number of other applications in the same database ...

You can't do an alter session? (If so, I would sympathize, as it is difficult in the app I use.)

>
> > Matthias
>
> I recall reading something that I believe Tom Kyte authored (may have
> been in one of his books) that using synonyms will cause a query to
> execute more slowly than it would using the actual object's schema and
> name (the schema owner would not need to perform a synonym lookup).
> The data dictionary must be checked a couple times to look up the
> actual object name and permissions. A lot of the SQL statements that
> check the data dictionary will contain a RULE hint, but I don't
> believe that has any effect on Oracle 10.2. FIRST_ROWS can make the
> optimizer use a less than ideal index to retrieve the first row
> quickly - I wonder if this has an effect on the dictionary lookups?

Also see http://www.ixora.com.au/q+a/library.htm#10_02_2000

That one made my face red years ago as I realized it was the suddenly obvious reason cpu's were burning on a particular app. Don't know if the details changed for more recent Oracle versions, but at least the concept should hold.

>
> Have executed one of these statements?
> EXEC DBMS_STATS.GATHER_FIXED_OBJECT_STATS
> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS
>
> You can try a 10046 trace at level 8, and look at the wait events
> where dep is 1 or greater to see if it is the additional data
> dictionary lookups that are hurting performance.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc

jg

--
@home.com is bogus.
"Sometimes when working onsite at the client, you need to do the stuff
a little faster and not go into reading some docs or something like
that." - SL