Re: Cost vs rule based optimizer

A customer of ours and they have been having major problems using cost-based optimization.

I suggested running an ANALYZE on the indexes and tables periodically, as this
is required for cost-based analysis to work.

Their DBA insists CBO is a failure and cites second-hand information about
a conference where, when asking a room full of DBA's who used CBO instead of RBO
not one person raised their hand.

My suggestion to use CBO seemed to improve the query in question, but other
problems arose. I think they failed to run the ANALYZE often enough or maybe not
at all.

I'd like to know who refuses to use CBO and why. If it's not too much trouble
forward me any answers you get.

Thanks

Ken Ingram
Navis Corporation  

Dagmar Murray wrote:
>
> 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 Wed Mar 04 1998 - 00:00:00 CST