Re: question about analyze / cost-based optimizer

Check what queries that run extremelly slow and force rulebased on those queries using:

select --+ RULE ....

instead of

select ....

That should solve your problems for now. Then reconstruct your database/queries so that they go fast also for costbased approach.

//Jonas

In article <3976A026.E526A366_at_beusen.de>,   Stephan Born <stephan.born_at_beusen.de> wrote:
> 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
> ---------------------------------------------------------------
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jul 20 2000 - 00:00:00 CDT