Re: ORA 8 optimizer really bad!

The analyze is definitely the key to getting the optimizer to work properly. You should also consider and try the various approaches to doing stats. You can analyze (compute or estimate) for table, indexes, indexed columns and all columns or any combination thereof. How you choose to do it can have a huge impace on what the optimizer chooses.

Geoffrey Bray

In article <86ps46$8uh_at_journal.concentric.net>,   "Daniel" <delj_at_flash.net> wrote:
> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 27 2000 - 14:16:15 CST