Re: CBO & different execution plans

On Mar 7, 5:07 pm, "Matthias Hoys" <a..._at_spam.com> wrote:
> "Anurag Varma" <avora..._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.

Same reason Bind Variable Peeking. Bind Variable Peeking happens when you use binds. So one user parsed the sql using a value which occurs frequently and its explain plan changed to something like a full table scan
for ALL subsequent variations of the query.

Try this test yourself:

create table peekt (a number, b number); create index peekt_idx on peekt (a);
insert into peekt select rownum, rownum from dba_objects where rownum < 100;
insert into peekt select 100, rownum from dba_objects where rownum < 1000;

So we have a value "100" which occurs very frequently.

Now run the following test in sqlplus (Note; You have to use sql_trace or 10046 event to find out
the real execution plan):

alter session set sql_trace=true;
exec :v := 1
select /* PLAN_1 */ * from peekt where a = :v; exec :v := 100
select /* PLAN_1 */ * from peekt where a = :v; exec :v := 100
select /* PLAN_2 */ * from peekt where a = :v; exec :v := 1
select /* PLAN_2 */ * from peekt where a = :v;

Now grep the trace file to fid what the execution plans were: In my test database (Oracle 9.2.0.8, optimizer_method choose, cursor_sharing exact):

-> egrep '(BEGIN :v|select |STAT )' mysid_ora_26754.trc BEGIN :v := 1; END;
select /* PLAN_1 */ * from peekt where a = :v STAT #1 id=1 cnt=1 pid=0 pos=1 obj=1034252 op='TABLE ACCESS BY INDEX ROWID PEEKT '
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=1034253 op='INDEX RANGE SCAN PEEKT_IDX '
BEGIN :v := 100; END;
select /* PLAN_1 */ * from peekt where a = :v STAT #1 id=1 cnt=1098 pid=0 pos=1 obj=1034252 op='TABLE ACCESS BY INDEX ROWID PEEKT '
STAT #1 id=2 cnt=1098 pid=1 pos=1 obj=1034253 op='INDEX RANGE SCAN PEEKT_IDX ' BEGIN :v := 100; END;
select /* PLAN_2 */ * from peekt where a = :v STAT #1 id=1 cnt=1098 pid=0 pos=1 obj=1034252 op='TABLE ACCESS FULL PEEKT '
BEGIN :v := 1; END;
select /* PLAN_2 */ * from peekt where a = :v STAT #1 id=1 cnt=1 pid=0 pos=1 obj=1034252 op='TABLE ACCESS FULL PEEKT '

As you see, the peeking happened on the first hard parse and execution plan did not change subsequently. The plan itself varied upon the first value used
when the peeking happened.

So in your database if you do not gather histograms, then the queries should
perform consistently from user to user. If your app uses binds exclusively, then
gathering histograms should be done in rare case when fully justified. Or .. you might want to use literals for query which operate on skewed columns and require histograms.
 ... there might be other options too..

Anurag Received on Wed Mar 07 2007 - 18:50:55 CST