Re: CBO & different execution plans

"Matthias Hoys" <anti_at_spam.com> wrote in message news:45ef1abf$0$2940$ba620e4c_at_news.skynet.be...
> 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 Received on Sat Apr 07 2007 - 09:33:29 CDT