Re: ORA 8 optimizer really bad!

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 Thu Jan 27 2000 - 14:17:37 CST