Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: optimizer for oracle 8

Re: optimizer for oracle 8

From: <volleyball_at_ntr.net>
Date: Thu, 15 Jul 1999 02:57:48 GMT
Message-ID: <378d4c22.4832997@news.ntr.net>


On Tue, 13 Jul 1999 23:28:28 GMT, canglin_at_anglingroup.com wrote:

i"ve got the scoop for you- We have a 40gb db with some tables 12-15mm rows. Good news - We were able to nail down the problem(s) w/the optimizer. There are two relating to this:

  1. query uses wrong index or will change query plan without the stats changing.
  2. Even the right query plan takes hours to complete where before it took only minutes.

Bad news

The cause is there is a bug in the 8.0 and above optimzer. It is random and hard to id. For some reason it gets the idea that some indexes are MUCH bigger than the are and will thrash memory reading them over and over again. Look in your v$sql for the info on your query and you will see 100X or 1000X 10,000X the number of rows processed as you would expect. We had a single table w about 1000 rows give a rows processed number of over a billion!!!!!! I thought the v$sql had bogus number, but i used a monitoring tool to varify the number.

I had an Oracle guy on site from the architecture and tuning group to work on it and he confirmed with other oracle gurus the problem.

The fix - None. WE tried all kinds of reanalyzing (computes, estimates, histograms, no histograms, etc,.....) no luck. When we see the problem i start analyzing and dropping and rebuilding indexes. At some point it starts working again and we are fine for a while. Problem does not exist prior to 8.0

Hopefully a fix is soon to come.

good luck

Doug Coan
Senior Client Sever System Integrator
dcoan_at_aegonusa.com
AegonUSA

>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 - 21:57:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US