Re: ORA 8 optimizer really bad!

On 27 Jan 2000 15:17:37 EST, "Daniel" <delj_at_flash.net> wrote:

I forgot to say that the tables were freshly analyzed, of course.

>I see.... the database is not just supposed to use the indexes you create.
>I understand what you are saying but this is an RDBMS. All of this is
>supposed to be automatic. It has been automatic. If I do a select
>somefield from sometable where somekey = 'somevalue'; the database should
>use the index if it is there. That is the primary purpose of having an
>optimizer. My point is, I never had to do this in Oracle 7.x. I am having
>to do this in 8.x. In addition, some of the optimizer problems were not
>resolved by computing statistics. I had to have the hints to tell Oracle
>that there was an index.... This is the way it is supposed to work?
>
>Daniel
>Austin, TX
>
>"Sybrand Bakker" <postmaster_at_sybrandb.demon.nl> wrote in message
>news:948993931.10723.0.pluto.d4ee154e_at_news.demon.nl...
>> That's the way it is supposed to work.
>> Simply use execute dbms_utility.analyze_schema('<schemaname>','COMPUTE')
>> and you should be set.
>>
>> Hth,
>>
>> --
>> Sybrand Bakker, Oracle DBA
>> Daniel <delj_at_flash.net> wrote in message
>> news:86ps46$8uh_at_journal.concentric.net...
>> > We just went through this with one of our projects. I spent 3 days
>> > optimizing statements for performance. I even had a two table inner
>join
>> > that insisted on using a full table scan for one of the tables even with
>> the
>> > hint. One thing that worked for us was to script a mass "analyze table"
>> and
>> > compute statistics for every table in the schema. This seemed to help
>> most
>> > of the problems.
>> >
>> > Daniel
>> > Austin, TX
>> >
>> > "Franz Mueller" <nospam#####franz.mueller_at_orbis.de> wrote in message
>> > news:389055a1.23592374_at_news.salink.net...
>> > > Hi,
>> > >
>> > > I have seen really disappointing behaviour of the ORACLE 8 optimizer:
>> > >
>> > > in statement that looks like this:
>> > > SELECT .... FROM TABLE1,TABLE2....
>> > > WHERE
>> > > TABLE1.Col1=x AND TABLE1.Col2=y AND
>> > > TABLE1.T2_ID=TABLE2.T2_ID AND TABLE2.Col1=z....
>> > > (i.e. inner join on TABLE1 and TABLE2)
>> > > There is an Index Col1,Col2 on TABLE1 and T2_ID,Col1 on TABLE2.
>> > > Both tables have something like 100000 entries and there are approx 5
>> > > hits.
>> > > EXPLAIN PLAN takes the index on Table1, then performs a full table
>> > > scan on Table2 and finally combines them using a HASH JOIN.. The
>> > > estimated costs are 3500
>> > > I would have expexted the optimizer to do a nested loop on the 2
>> > > tables since the indexes are perfectly suited. EXPLAIN PLAN computes
>> > > costs of 4000 (if I force the it using the /*+ USE_NL (T1 T2)*/ hint),
>> > > and therefore it uses variant 1.
>> > > In fact, if I run the 2 ways, the nested loop is 100 times faster than
>> > > the Hash Join. Why does the optimizer such a bad job?
>> > >
>> > > Franz
>> >
>> >
>>
>>
>
>
Received on Fri Jan 28 2000 - 02:54:56 CST