Re: Cost vs rule based optimizer

Have you analyzed your tables and indexes before enabling the cost-based optimizer? This may explain why the Cost-based solution takes so long.

The rule-based optimiser uses a set of rules to determine how it will execute a query. Whereas, the cost-based optimiser uses the analyzed table and index information (plus inline hints) to determine how it will execute the queries.

I hope I've been helpful?

Vince

Dagmar Murray <dmurray_at_purdue.edu> wrote in article <6dh859$f60_at_mozo.cc.purdue.edu>...
> 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 Thu Mar 05 1998 - 00:00:00 CST