Re: OPTIMIZER

The problem here is that so many things *can* be involved. I just spent weeks resolving a similar problem where the app all of a sudden started to perform full table scans on a 31 million row table involved in a join.
After weeks it appeared the original driving table became bigger (47 million rows) than the affected table.
Statistics were refreshed every week.

A few things to check obviously:
- the presence of histograms on those tables (as you don't mention a version I can't provide an exact table name on 7.3.4 it is user_histograms, on 8i it seems to be user_tab_histograms) - the number of rows on both sides
- *VERY IMPORTANT*: the clustering factor of the affected indexes. The clustering factor determines how well the table is organized in terms of the index. If the records are in index order, one I/O will fetch multiple records, so total I/O will be less.
If you have access to Metalink, I can post a few notes id's to check.

You may also want to set event 10053 to see *why* the optimizer chooses a specific access path.
Issue
alter session set events='10053 trace name context forever' before running the affected statement.
You can also set that in a different session either by running the oradebug module in server manager or by calling dbms_system.set_ev.

If you want to share more details, feel free to e-mail me privately. My current assignment is focusing on troubleshooting a badly performing application, and to determine whether it is the application (very likely) or the CBO making stupid decisions.

Hth,

Sybrand Bakker, Senior Oracle DBA

"Anthony Millard" <Anthony.Millard_at_Ramesys.Com> wrote in message news:wxTc7.23935$e%3.2640654_at_news2-win.server.ntlworld.com...
> Thanks for that,
> the problem that I have is the following.
>
> A customer running a test script through our application takes 9seconds,
> where on our database takes 200m/seconds.
> We are using the saty database structure with the same indexes.
>
> I looked into this further - ignoring the application side and went to
> SQLPLUS.
> With explain on, running the same test statment, the customers database
> chose INDEX A.
> Our databse chose INDEX B.
>
> What I'm trying to do is to get our environment here to mimic theirs so
 that
> I can then look into speeding it up.
>
> By altering the optimiz session variable between RULE, ALL_RECORDS,
> FIRST_RECORD, I can get SQL_PLUS to return index results of A, B, and C.
>
> So I thought maybe the customer had a different optermizing mode to us,
> hence the difference.
> I now have thier int.ora files and the are no optermizing modes being set.
>
> The only other noticable difference in environments as we use 7.3.4.0.0
 and
> they have 7.3.4.0.1.
>
> Assuming that COSTing by Throughtput with ANALZE run every night, what
 would
> govern which index was used?
>
> Any help would be greatfully appriciated.
>
> Regards
> Tony Millard
>
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> news:997456342.21856.0.pluto.d4ee154e_at_news.demon.nl...
> >
> > "Anthony Millard" <Anthony.Millard_at_Ramesys.Com> wrote in message
> > news:euRc7.23422$e%3.2537773_at_news2-win.server.ntlworld.com...
> > > Is it possible to find out which optimizer goal has been decided in
> > > SQL_PLUS.
> > > I can use the
> > > alter session set optimizer_goal = FIRST_ROWS;
> > > to set it to a neww value, however, how do I find out the original
 value.
> > >
> > > Regards
> > > Anthony.Millard_at_Ramesys.Com
> > >
> > >
> >
> > The original value is whatever your DBA has set in the optimizer_mode
> > parameter in init<sid>.ora
> > Usually that's choose which means use RBO, if you have no statistics and
> > ALL_ROWS if you have statistics.
> > PL/SQL runs in all_rows by design.
> >
> > Hth,
> >
> > Sybrand Bakker, Senior Oracle DBA
> >
> >
> >
>
>
Received on Fri Aug 10 2001 - 12:47:28 CDT