RE: why rule based optimizer performs better than cost based?
- From: "Goulet, Dick" <DGoulet@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 25 Jun 2004 11:16:49 -0400
Mei,
Take a serious look at two init parameters:
Optimizer_index_caching
Optimizer_index_cost_adj
The default values are 0 and 100 respectively which inform the optimizer =
that a) you will never find an index in the buffer cache and b) scanning =
an index is just as expensive as a table. If any of you are familiar =
with Dan Hotka, he recently presented his "Index Advance Tuning" seminar =
at the NOUG DBA SIG here in Boston. His recommendation is to set these =
to 90 and 30(OLTP) or 50(DSS) respectively. I've tried it, it helps and =
does not appear to have any unpleasant side effects.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: Guang Mei [mailto:gmei@xxxxxxxxxx]
Sent: Friday, June 25, 2004 10:56 AM
To: Oracle-L-freelists
Subject: why rule based optimizer performs better than cost based?
Oracle 8173 on Sun Solaris 2.8
All application tables and indexes have been analyzed using:
execute DBMS_STATS.GATHER_TABLE_STATS('MT','TABLE_NAME',cascade =3D> =
FALSE);
execute DBMS_STATS.GATHER_INDEX_STATS('MT','INDEX_NAME');
I found one query that runs faster when hint /*+ rule */ is added. It
basically uses indexes on table diseasemodels and observationdetails. =
When
cost based (default) optimizer is used, oracle does full table scan on =
these
two tables, which make the query run slower (from Elapsed time =
measurement).
I am curious why here the "rule based" optimizer performas better than =
"cost
based".
TIA.
Guang
Here are the query (with results) and the definition of the tables:
-- fast query:
select /*+ rule */
distinct name, evidence, seeflag, referenceid, category2gene.id =
cid,
d.mnemonic meshid
from diseasemodels, category d,
observationdetails, category2gene where geneid =3D 156005 and
diseasemodels.phenotypedetailsid =3D =
observationdetails.phenotypedetailsid
and category2gene.id =3D category2geneid and name =3D d.string(+) =
and
(d.categorytype =3D 'MESH' or d.categorytype is null)
order by name asc;
NAME
-------------------------------------------------------------------------=
---
----
E S REFERENCEID CID MESHID
- - ----------- ---------- ----------------
Cataract
E N 58814 843529 D002386
Cataract
E N 58814 843532 D002386
Elapsed: 00:00:00.43
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=3DHINT: RULE
1 0 SORT (UNIQUE)
2 1 FILTER
3 2 NESTED LOOPS (OUTER)
4 3 NESTED LOOPS
5 4 NESTED LOOPS
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY2GENE'
7 6 INDEX (RANGE SCAN) OF 'CATEGORY2GENE_GENEID_IN
DEX' (NON-UNIQUE)
8 5 TABLE ACCESS (BY INDEX ROWID) OF 'OBSERVATIONDET
AILS'
9 8 INDEX (RANGE SCAN) OF 'OBSDETAILS_C2GID_INDEX'
(NON-UNIQUE)
10 4 TABLE ACCESS (BY INDEX ROWID) OF 'DISEASEMODELS'
11 10 INDEX (RANGE SCAN) OF 'DMODELS_PDID_INDEX' (NON-
UNIQUE)
12 3 TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY'
13 12 INDEX (RANGE SCAN) OF 'CATEGORY_STRING_INDEX' (NON
-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
78 consistent gets
0 physical reads
0 redo size
752 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
--- slow query:
select
distinct name, evidence, seeflag, referenceid, category2gene.id =
cid,
d.mnemonic meshid
from diseasemodels, category d,
observationdetails, category2gene where geneid =3D 156005 and
diseasemodels.phenotypedetailsid =3D =
observationdetails.phenotypedetailsid
and category2gene.id =3D category2geneid and name =3D d.string(+) =
and
(d.categorytype =3D 'MESH' or d.categorytype is null)
order by name asc;
NAME
-------------------------------------------------------------------------=
---
----
E S REFERENCEID CID MESHID
- - ----------- ---------- ----------------
Cataract
E N 58814 843529 D002386
Cataract
E N 58814 843532 D002386
Elapsed: 00:00:00.64
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D153 Card=3D5 =
Bytes=3D665
)
1 0 SORT (UNIQUE) (Cost=3D100 Card=3D5 Bytes=3D665)
2 1 FILTER
3 2 NESTED LOOPS (OUTER)
4 3 HASH JOIN (Cost=3D32 Card=3D5 Bytes=3D250)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY2GENE' (
Cost=3D11 Card=3D13 Bytes=3D247)
6 5 INDEX (RANGE SCAN) OF 'CATEGORY2GENE_GENEID_INDE
X' (NON-UNIQUE) (Cost=3D3 Card=3D13)
7 4 HASH JOIN (Cost=3D20 Card=3D16107 Bytes=3D499317)
8 7 TABLE ACCESS (FULL) OF 'DISEASEMODELS' (Cost=3D4 C
ard=3D5765 Bytes=3D132595)
9 7 TABLE ACCESS (FULL) OF 'OBSERVATIONDETAILS' (Cos
t=3D14 Card=3D42946 Bytes=3D343568)
10 3 TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY' (Cost=3D3
Card=3D75859 Bytes=3D6296297)
11 10 INDEX (RANGE SCAN) OF 'CATEGORY_STRING_INDEX' (NON
-UNIQUE) (Cost=3D2 Card=3D75859)
Statistics
----------------------------------------------------------
0 recursive calls
8 db block gets
140 consistent gets
0 physical reads
0 redo size
752 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
MT@max-SQL> desc diseasemodels
Name Null? Type
----------------------------------------- -------- =
------------------------
----
PHENOTYPEDETAILSID NOT NULL NUMBER
NAME VARCHAR2(2000)
MT@max-SQL> desc observationdetails;
Name Null? Type
----------------------------------------- -------- =
------------------------
----
CATEGORY2GENEID NOT NULL NUMBER
PHENOTYPEDETAILSID NOT NULL NUMBER
MT@max-SQL> desc category2gene
Name Null? Type
----------------------------------------- -------- =
------------------------
----
ID NOT NULL NUMBER
CATEGORYID NOT NULL NUMBER
GENEID NOT NULL NUMBER
CURID NOT NULL NUMBER
NOTFLAG CHAR(1)
CCOMMENT VARCHAR2(300)
EVIDENCE CHAR(1)
SEEFLAG NOT NULL CHAR(1)
REFERENCEID NUMBER
METHOD VARCHAR2(128)
SEQTABID NUMBER
PROPDATE DATE
MT@max-SQL> desc category;
Name Null? Type
----------------------------------------- -------- =
------------------------
----
ID NOT NULL NUMBER
STATUS NOT NULL VARCHAR2(16)
CATEGORYTYPE NOT NULL VARCHAR2(16)
STRING VARCHAR2(240)
MNEMONIC VARCHAR2(16)
NUMERIC NUMBER
------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
