Re: CBO & different execution plans

"Anurag Varma" <avoracle_at_gmail.com> wrote in message news:1173301649.139080.228870_at_j27g2000cwj.googlegroups.com...
> 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
>
>
> Considering you have gathered histograms ...
> Are you using bind variables or cursor_sharing=SIMILAR/FORCE?
> My first impression is that the behavior you are seeing is
> related to bind variable peeking.
>
> Anurag
>

Yes, the application uses bind variables, but cursor_sharing is not set through alter session or after-logon trigger statements. So I can't explain why the same query runs fast with user A and slow with user B. Received on Wed Mar 07 2007 - 16:07:49 CST