Re: ORA 8 optimizer really bad!

Franz Mueller wrote:

> 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
> >> >
> >> >
> >>
> >>
> >
> >

Just wondering... did you analyze the indexes as well?

--
Met vriendelijke groet/kind regards,

Frank van Bortel
Technical consultant Oracle