Re: ORA 8 optimizer really bad!

Daniel wrote:
>
> 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
> > >
> > >
> >
> >

        I suspect that someone at Oracle development made an arbitrary decision not to automatically analyze tables for fear that customers with
billion row tables would come looking for the developers with shotguns in hand.

        I wouldn't mind automatic table analysis for any table with less than a million rows, but I much prefer to choose my own time to anyalze any table
with over a million rows. In part this is because I prefer to do a full analysis and compute the statistics instead of estimating them.

        After all, I can always either use the package to analyze when I want or write multiple sets of scripts to be run at different times and frequency in order to get my tables analyzed when it is convenient for me.

--
Jerry Gitomer
Once I learned how to spell DBA, I became one. Received on Thu Jan 27 2000 - 23:38:50 CST