On Mar 7, 7:50 pm, "Anurag Varma" <avora..._at_gmail.com> wrote:
> 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
A key thing I forgot to mention: I had gathered stats on the table
like this (before running the test):
exec dbms_stats.gather_table_stats(ownname=>user,
tabname=>'PEEKT',cascade=>true, method_opt => 'FOR ALL COLUMNS SIZE
AUTO')
Anurga
Received on Wed Mar 07 2007 - 18:54:51 CST
