Re: question about analyze / cost-based optimizer

IMO, this is about what you can expect.
If you have developed an application and you have fine-tuned it using the RBO, it will almost always have disastrous performance using the CBO. One of the reasons is you could 'fool' or 'tric' the RBO in a certain access path you knew or thought was optimal, by writing the sql-statement in an appropiate fashion.
The RBO is using heuristics, and if that doesn't enable it to get a good access plan, the *rightmost* table is being used as driving. The CBO uses statistics only, no heuristics. It will generally choose the smallest table as driving. Also the CBO works from *left to right*. You have written your application for RBO. You should instruct your customer *NOT* to analyze your tables, if he has also analyzed SYS (the dictionary), he did something *extreemly stupid*, as the same applies to the dictionary. This could easily explain bad performance for your app and for the database per se.
Now, as the RBO did become obsolete with Oracle 7 (which is soon desupported), you should really start using it. You always have the escape of using the /*+first_rows*/ hint to get about the same access path as with RBO, and all new features have not been implemented in RBO, so I see many reasons why you should drop RBO. Once you know how it works, you are soon discovering it will many times actually provide better access paths.

Regards,

Sybrand Bakker, Oracle DBA

"Stephan Born" <stephan.born_at_beusen.de> wrote in message news:3976A026.E526A366_at_beusen.de...
> Hello group,
>
> my company is developing applications for the oracle-database since
> oracle version 5.
> Despite the long time we did not use the analyze-tool, therefor the
> optimizer does
> its optimization by RULE.
>
> Now a customer of our product does every night analyze all
> application-schemas
> with the following command
>
> begin
> dbms_utility.analyze_schema (user, 'COMPUTE');
> end;
>
> and complains about the bad performance of our product. Other
> applications
> (they didn't tell us which one) shall run at least with the same
> speed..if not better!!!
>
> We did the same on our test-systems.... with Oracle 7.3.4 and Oracle
> 8.0.5.
>
> This is what we get with explain plan AFTER we analyzed the schema
> (Oracle uses the cost-based optimizer now)
>
> SELECT STATEMENT Optimizer=CHOOSE (Cost=52653 Card=73679 Bytes=50101720)
>
> NESTED LOOPS (Cost=52653 Card=73679 Bytes=50101720)
> HASH JOIN (Cost=17237 Card=17708 Bytes=10996668)
> TABLE ACCESS (FULL) OF KOMNED$NETZKNOTEN (Cost=12 Card=4427
> Bytes=305463)
> HASH JOIN (Cost=15650 Card=2236 Bytes=1234272)
> MERGE JOIN (CARTESIAN) (Cost=15635 Card=44 Bytes=22176)
> NESTED LOOPS (Cost=15631 Card=4 Bytes=1876)
> NESTED LOOPS (Cost=15629 Card=1 Bytes=410)
> NESTED LOOPS (Cost=15628 Card=1 Bytes=354)
> NESTED LOOPS (Cost=15627 Card=1 Bytes=298)
> HASH JOIN (OUTER) (Cost=15626 Card=1 Bytes=268)
> HASH JOIN (Cost=14969 Card=8854 Bytes=2107252)
> TABLE ACCESS (FULL) OF KOMNED$NETZWEGE (Cost=5
> Card=3171 Bytes=177576)
> MERGE JOIN (CARTESIAN) (Cost=13399 Card=8854
> Bytes=1611428)
> HASH JOIN (Cost=118 Card=4427 Bytes=672904)
> TABLE ACCESS (FULL) OF KOMNED$INSTORTKLASSE
> (Cost=1 Card=11 Bytes=385)
> HASH JOIN (Cost=108 Card=4427 Bytes=517959)
> TABLE ACCESS (FULL) OF KOMNED$INSTORT
> (Cost=1 Card=559 Bytes=26832)
> TABLE ACCESS (FULL) OF KOMNED$NETZKNOTEN
> (Cost=12 Card=4427 Bytes=305463)
> SORT (JOIN) (Cost=13398 Card=2 Bytes=60)
> TABLE ACCESS (BY INDEX ROWID) OF
> KOMNED$NETZWEGNAMEN (Cost=3 Card=2 Bytes=60)
> INDEX (RANGE SCAN) OF
> KOMNED$NETZWEGNAMEN_KEY2 (UNIQUE) (Cost=2 Card=2)
> TABLE ACCESS (FULL) OF KOMNED$STATUS (Cost=1 Card=3
> Bytes=90)
> TABLE ACCESS (BY INDEX ROWID) OF KOMNED$RICHTUNG
> (Cost=1 Card=3 Bytes=90)
> INDEX (UNIQUE SCAN) OF KOMNED$RICHTUNG_KEY1 (UNIQUE)
>
> TABLE ACCESS (BY INDEX ROWID) OF KOMNED$ABSCHNITTE
> (Cost=1 Card=7810 Bytes=437360)
> INDEX (UNIQUE SCAN) OF KOMNED$ABSCHNITTE_KEY1 (UNIQUE)
>
> TABLE ACCESS (BY INDEX ROWID) OF KOMNED$ABSCHNITTE (Cost=1
> Card=7810 Bytes=437360)
> INDEX (UNIQUE SCAN) OF KOMNED$ABSCHNITTE_KEY1 (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF KOMNED$VERBINDUNGEN (Cost=2
> Card=41608 Bytes=2454872)
> INDEX (UNIQUE SCAN) OF KOMNED$VERBINDUNGEN_KEY1 (UNIQUE)
> (Cost=1 Card=41608)
> SORT (JOIN) (Cost=15633 Card=11 Bytes=385)
> TABLE ACCESS (FULL) OF KOMNED$INSTORTKLASSE (Cost=1 Card=11
> Bytes=385)
> TABLE ACCESS (FULL) OF KOMNED$INSTORT (Cost=1 Card=559
> Bytes=26832)
> TABLE ACCESS (BY INDEX ROWID) OF KOMNED$VERBINDUNGEN (Cost=2
> Card=41608 Bytes=2454872)
> INDEX (UNIQUE SCAN) OF KOMNED$VERBINDUNGEN_KEY1 (UNIQUE) (Cost=1
> Card=41608)
>
> There are many full-scans in it. The statemt is very slow....
>
> This is the result of explain plan when the schema is not analyzed:
> (Oracle uses the rule-based optimizer now....the statement is very fast)
>
> SELECT STATEMENT Optimizer=CHOOSE
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS (OUTER)
> NESTED LOOPS
> TABLE ACCESS (BY INDEX ROWID) OF
> KOMNED$NETZWEGNAMEN
> INDEX (RANGE SCAN) OF
> KOMNED$NETZWEGNAMEN_KEY2 (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF
> KOMNED$NETZWEGE
> INDEX (UNIQUE SCAN) OF
> KOMNED$NETZWEGE_KEY2 (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF KOMNED$STATUS
>
> INDEX (UNIQUE SCAN) OF KOMNED$STATUS_KEY1
> (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF
> KOMNED$ABSCHNITTE
> INDEX (UNIQUE SCAN) OF KOMNED$ABSCHNITTE_KEY1
> (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF
> KOMNED$VERBINDUNGEN
> INDEX (UNIQUE SCAN) OF KOMNED$VERBINDUNGEN_KEY1
> (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF KOMNED$NETZKNOTEN
> INDEX (UNIQUE SCAN) OF PK_KN_NETZKNOTEN (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF KOMNED$INSTORT
> INDEX (UNIQUE SCAN) OF PK_KN_INSTORT (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF KOMNED$INSTORTKLASSE
> INDEX (UNIQUE SCAN) OF PK_KN_IOKLASSE (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF KOMNED$ABSCHNITTE
> INDEX (UNIQUE SCAN) OF KOMNED$ABSCHNITTE_KEY1 (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF KOMNED$VERBINDUNGEN
> INDEX (UNIQUE SCAN) OF KOMNED$VERBINDUNGEN_KEY1 (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF KOMNED$NETZKNOTEN
> INDEX (UNIQUE SCAN) OF PK_KN_NETZKNOTEN (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF KOMNED$INSTORT
> INDEX (UNIQUE SCAN) OF PK_KN_INSTORT (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF KOMNED$INSTORTKLASSE
> INDEX (UNIQUE SCAN) OF PK_KN_IOKLASSE (UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF KOMNED$RICHTUNG
> INDEX (UNIQUE SCAN) OF KOMNED$RICHTUNG_KEY1 (UNIQUE)
>
>
> Why does the cost-based optimizer slow down our application?
> Why do the other applications of our customer (don't know which)
> run as fast as before or even faster?
>
> I would be glad if someone could tell me what we are doing wrong
>
> Regards, Stephan Born
>
> --
> ---------------------------------------------------------------
> Dipl.-Inf. (FH) Stephan Born | beusen Consulting GmbH
> fon: +49 30 549932-0 | Landsberger Allee 392
> fax: +49 30 549932-21 | 12681 Berlin
> mailto:stephan.born_at_beusen.de | Germany
> ---------------------------------------------------------------
> PGP-Key verfügbar | PGP-Key available
> ---------------------------------------------------------------
>
>
Received on Thu Jul 20 2000 - 00:00:00 CDT