CBO & different execution plans

From: Matthias Hoys <anti_at_spam.com>
Date: Wed, 7 Mar 2007 21:04:18 +0100
Message-ID: <45ef1abf$0$2940$ba620e4c@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 Received on Wed Mar 07 2007 - 14:04:18 CST