Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: optimizer for oracle 8
On 13 Jul 1999 19:51:10 PDT, "David P. Melton" <dpm133_at_concentric.com>
wrote:
I forgot to include that - yes rule based hint will do it, but the problem may crop up somewhere else. we git it in many places. Setting optimzer_mode to rule will also do it. Hope your code is written for rule based..........
Also - Turn multi_block_read_count down - May help.
>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 Wed Jul 14 1999 - 22:01:03 CDT
![]() |
![]() |