Re: OPTIMIZER

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 - 10:58:35 CDT