Re: optimizer for oracle 8

Tell the query to ignore the stats with a SQL hint like this;

select /*+ RULE */ rest of query......

Theres's white space around the word RULE.

The 8.0 optimizer makes some bad choices - the RULE hint informs SQL engine to use only rule based heuristics to generate the access plan.

dpm.

<canglin_at_anglingroup.com> wrote in message news:7mgi2e$1ui$1_at_nnrp1.deja.com...
> I too am seeing the same type of weird behavior with upgrading to Oracle
> 8.0.5 from Oracle 7.3.2. Our tables have been analyzed and we have
> played around with the optimizer_mode. In our situation, the proper
> index is not used even though the optimizer knows about it. It has
> taken our query execution time from seconds to over 8 hours. I
> contacted Oracle support and they would not release any information on
> the differences between the optimizers. Does anyone have suggestions on
> what we could try or information about the new optimizer init parameters
> in 8.0.5?
>
> Thanks
>
> In article <uP7a3.3168$ll5.22082_at_server1.news.adelphia.net>,
> "Ying" <ying_at_spellchecker.com> wrote:
> > We are using Oracle 8 an we have a large table with several indexes.
> When
> > we query the table, we use hints to try to force the use of the proper
> > index. But sometime some queries are very slow anyway. We have
> checked
> > them
> > with 'explain plan' and find the indexes we requested are not always
> being
> > used. For example, with the query below
> >
> > select /*+ INDEX(mytable i2_index) */ ....
> > from mytable
> > where name like 'SMITH%'
> > and file_date between
> > to_date('01/01/1999 000000', 'mm/dd/yyyy hh24miss') and
> > to_date('01/02/1999 235959', 'mm/dd/yyyy hh24miss')
> > order by name, other fields ...
> >
> > i2_idex is based on name and other fields. The order by is indentical
> to
> > the
> > index.
> >
> > If the date range is small, Oracle will use another index based on
> date
> > range
> > and then sort the data. This generates a slow query. If we change
> the date
> > range to include more days, Oracle will use the correct index.
> >
> > ALso, we have found some queries work correctly if the optimizer goal
> is set
> > to FIRST_ROWS. But still others don't work with that setting and do
> work
> > with the goal of CHOOSE. Do we have to examine every query in the
> program
> > and set the optimizer goal for each one?
> >
> > Also, we have one query that has a "group by" clause. Different
> results are
> > returned depending on the optimizer goal setting.
> >
> > We would like to know if others are having problems with the Oralce 8
> > optimizer. This problem does not happen with oracle 7. It only
> happens
> > with oracle 8.
> >
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Tue Jul 13 1999 - 21:51:10 CDT