Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> why rule based optimizer performs better than cost based?

why rule based optimizer performs better than cost based?

From: Guang Mei <gmei_at_incyte.com>
Date: Fri, 25 Jun 2004 10:55:49 -0400
Message-ID: <NJEDKDKJDGAKAEKKNEEJAEKADFAA.gmei@incyte.com> 





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 => 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:






   select /*+ rule */

        distinct name, evidence, seeflag, referenceid, category2gene.id cid,
        d.mnemonic meshid




    from diseasemodels, category d,


    observationdetails, category2gene where geneid = 156005  and
    diseasemodels.phenotypedetailsid = observationdetails.phenotypedetailsid
    and category2gene.id = category2geneid and name = d.string(+) and
    (d.categorytype = '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=HINT: 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








    select

        distinct name, evidence, seeflag, referenceid, category2gene.id cid,
        d.mnemonic meshid




    from diseasemodels, category d,


    observationdetails, category2gene where geneid = 156005  and
    diseasemodels.phenotypedetailsid = observationdetails.phenotypedetailsid
    and category2gene.id = category2geneid and name = d.string(+) and
    (d.categorytype = '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=CHOOSE (Cost=153 Card=5 Bytes=665
          )

   1    0   SORT (UNIQUE) (Cost=100 Card=5 Bytes=665)
   2    1     FILTER
   3    2       NESTED LOOPS (OUTER)
   4    3         HASH JOIN (Cost=32 Card=5 Bytes=250)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY2GENE' (
          Cost=11 Card=13 Bytes=247)

   6    5             INDEX (RANGE SCAN) OF 'CATEGORY2GENE_GENEID_INDE
          X' (NON-UNIQUE) (Cost=3 Card=13)

   7    4           HASH JOIN (Cost=20 Card=16107 Bytes=499317)
   8    7             TABLE ACCESS (FULL) OF 'DISEASEMODELS' (Cost=4 C
          ard=5765 Bytes=132595)

   9    7             TABLE ACCESS (FULL) OF 'OBSERVATIONDETAILS' (Cos
          t=14 Card=42946 Bytes=343568)

  10    3         TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY' (Cost=3
          Card=75859 Bytes=6296297)

  11   10           INDEX (RANGE SCAN) OF 'CATEGORY_STRING_INDEX' (NON
          -UNIQUE) (Cost=2 Card=75859)





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_at_max-SQL> desc diseasemodels
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------


----

 PHENOTYPEDETAILSID                        NOT NULL NUMBER
 NAME                                               VARCHAR2(2000)

MT_at_max-SQL> desc observationdetails;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------


----

 CATEGORY2GENEID                           NOT NULL NUMBER
 PHENOTYPEDETAILSID                        NOT NULL NUMBER

MT_at_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_at_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_at_freelists.org
put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------



Received on Fri Jun 25 2004 - 09:53:05 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US