From: oracle@tchp2.tcamuk.stratus.com (Neil Chandler)
Subject: Re: Cost vs rule based optimizer
Date: 1998/03/03
Message-ID: <slrn6fogdk.hm3.oracle@tchp2.tcamuk.stratus.com>#1/1
References: <6dh859$f60@mozo.cc.purdue.edu>
Organization: Stratus Computer Inc, Marlboro MA
Newsgroups: comp.databases.oracle.server



In article <6dh859$f60@mozo.cc.purdue.edu>, "Dagmar Murray" wrote:

Which release of Oracle are you using? At 7.2.3 I feel that the RULE based
IS better. At 7.3.3 it is a close thing for most SQL's if they have been 
written with RULE-based in mind. I can't say for Oracle8 yet.

The first thing I would check is the OPTIMIZER MODE in the init{SID}.ora

There are several modes:
RULE   (obvious)
CHOOSE (default - if tables analyzed, run as ALL_ROWS)
FIRST_ROWS (Cost - prefer indexes)
ALL_ROWS   (Cost - prefer Table Scans)

I suspect that you are using CHOOSE and therefore ALL_ROWS.
Try switching to FIRST_ROWS.

You could also investigate using oracle HINTS to force specific access paths.

regs

Neil Chandler


>I am wondering why the performance I get with the cost-based optimizer is so
>poor compared to the performance I get with the rule-based optimizer.
>
>I'm working with 2 tables: student and stu_course
>
>student looks like this:
>CREATE TABLE STUDENT(
> ID                INT NOT NULL,
> CURR              VARCHAR2(3) NOT NULL,
> REG_CLASS         VARCHAR2(2) NULL,
> LNAME              VARCHAR2(20) NOT NULL,
>...and more fields
>There are indexes on id and lname and the table contains about 35,000
>records.
>
>The stu_course table looks like this:
>CREATE TABLE STU_COURSE
>(
> ID              INT NOT NULL,
> COURSE          VARCHAR2(8) NOT NULL,
> CREDIT          NUMBER(3,1) NULL,
>...and more fields
>There are indexes on id and course and the table contains about 220,000
>records
>
>I want to execute the following query:
>
>select student.id, student.curr, student.reg_class, sum(credit)
>from student, stu_course
>where student.id = stu_course.id(+)
>group by student.id, student.curr, student.reg_class;
>
>With the cost-based optimizer on (and statistics have been computed), it
>uses the following plan:
>
>OPERATION            OPTIONS              OBJECT_NAME          POSITION
>-------------------- -------------------- -------------------- --------
>SELECT STATEMENT     [null]               [null]                  #####
>  SORT               GROUP BY             [null]                      1
>    HASH JOIN        OUTER                [null]                      1
>      TABLE ACCESS   FULL                 STUDENT                     1
>      TABLE ACCESS   FULL                 STU_COURSE                  2
>
>Executing this statement takes hours.
>
>Turning off the cost-based optimizer and using the rule-based, this is the
>plan:
>
>SELECT STATEMENT     [null]               [null]               [null]
>  SORT               GROUP BY             [null]                      1
>    NESTED LOOPS     OUTER                [null]                      1
>      TABLE ACCESS   FULL                 STUDENT                     1
>      TABLE ACCESS   BY INDEX ROWID       STU_COURSE                  2
>        INDEX        RANGE SCAN           SC_ID                       1
>
>Results are returned in 2-3 minutes.
>
>I have many other cases where the rule-based optimizer seems to make a more
>sensible decision about how to execute the query and gives results in a
>reasonable amount of time.  This example seemed simple enough to present
>here.
>
>My question is: if Oracle is encouraging us to use the cost-based optimizer
>why am I getting such poor performance using it?  Is there something I'm
>missing here?
>
>----------------------------------------------------------------------------
>-------------------------
>Dagmar C. Murray, Senior Software Engineer
>Space Management & Academic Scheduling
>Purdue University
>W. Lafayette, IN  47907-1128
>E-mail: dmurray@purdue.edu
>
>
>