| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> question about analyze / cost-based optimizer
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
 |
 |