Re: Cost vs rule based optimizer

Since the cost based optimizer uses a hash join in this example, have you tuned the hash join parameters in your init.ora?

HASH_AREA_SIZE
Parameter type: integer
Parameter class: dynamic, scope= ALTER SESSION Default value: 2 * SORT_AREA_SIZE
Range of values: 0 - system-dependent value

HASH_AREA_SIZE specifies the maximum amount of memory, in bytes, to be used for hash joins. If this parameter is not set, its value defaults to twice the
value of the SORT_AREA_SIZE parameter.

HASH_MULTIBLOCK_IO_COUNT
Parameter type: integer
Parameter class: dynamic, scope= ALTER SESSION, ALTER SYSTEM Default value: 1
Range of values: operating system dependent HASH_MULTIBLOCK_IO_COUNT specifies how many sequential blocks a hash join reads and writes in one IO. When operating in multi-threaded server
mode, however, this parameter is ignored (a value of 1 is used even if you set
the parameter to another value).
The maximum value for HASH_MULTIBLOCK_IO_COUNT varies by operating system. It is always less than the operating system’s maximum I/O
size expressed as Oracle blocks (max_IO_size/DB_BLOCK_SIZE). This parameter strongly affects performance because it controls the number of
partitions into which the input is divided. If you change the parameter value,
try to make sure that the following formula remains true: R / M <= Po2(M/C)
where:
R = sizeof(left input to the join)
M = HASH_AREA_SIZE * 0.9
Po2(n) = largest power of 2 that is smaller than n C = HASH_MULTIBLOCK_IO_COUNT * DB_BLOCK_SIZE Go Purdue! (BSIM 65)

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 Tue Mar 03 1998 - 00:00:00 CST