Re: CBO & different execution plans

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 ...
>
> 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?

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. Received on Wed Mar 07 2007 - 16:34:24 CST