Re: optimizer for oracle 8

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 - 18:28:28 CDT