Re: Cost vs rule based optimizer

In article <6dh859$f60_at_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_at_purdue.edu
>
>
>
Received on Tue Mar 03 1998 - 00:00:00 CST