Re: OPTIMIZER

I didn't think histograms were relevant if you used bind variables.

Also the 7.3.4 optimiser was really not very good. It had a tremendous tendency to do very badly performing full table scans if optimiser_goal=all_rows which is effectively the default for analysed tables. You were almost always better specifying a first_rows hint in every query and removing it if that performed badly.

One of the biggest problems with the cbo is the combination of the fact that when it gets things wrong it gets them very wrong and that the access path can change over time. This leads to the situation where a system that was working fine suddenly starts to perform abysmally. Of course, if you have more money than sense you can use plan stability to address the problem, but this is available in the enterprise edition only. I almost believe that you should use the ordered hint to try to fix the access paths.

I wonder how easy it would be to write a translator that takes a sql statement and generates a modified version with hints to "fix" the current access path?

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:997465668.25003.0.pluto.d4ee154e_at_news.demon.nl...
> 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 Sat Aug 11 2001 - 04:53:11 CDT