Re: ORA 8 optimizer really bad!

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 Thu Jan 27 2000 - 11:23:31 CST