Re: CBO & different execution plans


"Matthias Hoys" <anti@xxxxxxxx> wrote in message
news:45ef1abf$0$2940$ba620e4c@xxxxxxxxxxxxxxxxx
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



By accident, I found the solution for this problem on MetaLink :

Bug 4652274 - Explain Plan Differs With Different Users

It has to do with the init parameter secure_view_merging, which is new since
10gR2. Setting it to FALSE in the spfile and boucing the instance resolved
all my problems ... Now queries on view from another schema have the same
exection plans when executed by the view owner compared to another user.

Matthias






.

Relevant Pages

  • Optimizer index considerations.
    ... I have conducted an index tuning session for a small set of medium sized ... consolidate the results in the end, I analyzed the queries and created about ... I extracted all the indexes that were used from the plans and found out that ... I conducted a benchmark of the new index set and to my great surprise, ...
    (microsoft.public.sqlserver.programming)
  • Re: Optimizer index considerations.
    ... > columns, taking into account all aspects of the queries (select columns, ... > I extracted all the indexes that were used from the plans and found out ... > was chosen by the optimizer. ... > I conducted a benchmark of the new index set and to my great surprise, ...
    (microsoft.public.sqlserver.programming)
  • Re: CBO & different execution plans
    ... Today I noticed the following strange CBO behaviour: ... The problem is that certain queries run fast with user A (the schema ... Considering you have gathered histograms ... ... Yes, the application uses bind variables, but cursor_sharing is not set ...
    (comp.databases.oracle.server)
  • Re: Different access plans for exists and in predicates
    ... I get different access plans for these queries: ... in case of query 1 I can't get rid of table scan of a and response time ... Why these queries are optimized different? ... didnt write tohe optimiser so I cannot tell. ...
    (comp.sys.ibm.as400.misc)
  • Re: CBO & different execution plans
    ... Today I noticed the following strange CBO behaviour: ... The problem is that certain queries run fast with user A, ... Considering you have gathered histograms ... ... Are you using bind variables or cursor_sharing=SIMILAR/FORCE? ...
    (comp.databases.oracle.server)