Home > DBA, Oracle > Yes, the Rule-Based Optimizer still lives in Oracle Database 11g

Yes, the Rule-Based Optimizer still lives in Oracle Database 11g


Under the patronage of Charles II. was established the Royal Society. The king sent one day to the learned body for an explanation of the following remarkable phenomenon: “When a live fish is thrown into a basin of water, the basin, water, and fish do not weigh more than the basin and water before the fish is thrown in; whereas, when a dead fish is employed, the weight of the whole is exactly equal to the added weights of the basin, water, and fish.”

This was considered a very curious fact, and the learned Royal Society put their heads together to explain it. Several elaborate papers were read upon the subject. Various theories were broached, but still the question did not seem to be settled, as neither hypothesis was entirely satisfactory. It finally occurred to one of the learned members that it might be well to ascertain whether the fact were so. This raised an angry discussion.

“The fact is notorious,” argued one of the members.

“To doubt it would be an insult to his Majesty, and would amount to constructive treason,” argued others.

The experiment, however, was made, when, lo! it was found that the fish, whether dead or alive, increased the weight of the basin and water by exactly his own weight.

The Wise Men of Gotham. The Monthly Religious Magazine and Independent Journal, Volume XXIII. Edited by Rev. E. H. Sears and Rev. Rufus Ellis. Cambridge: Welch, Bigelow, and Company, 1860.

I was reminded of this story on reading an OTN discussion on whether the rule based optimizer is still available in Oracle Database 11g. The Oracle Database 11g Reference makes no mention of the venerable RULE and CHOOSE options and the demise of the rule based optimizer was therefore treated as fact. However, a simple experiment will prove that the rule based optimizer is alive and well in Oracle Database 11g.

What drew me to investigate the rule based optimizer? While investigating a performance problem in a 11g database, I encountered the warning “rule based optimizer used (consider using cbo)” and rubbed my eyes in disbelief. The unavoidable conclusion was that the rule based optimizer still lived and was being invoked by the application.

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 21 18:22:40 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> set echo on
SQL> @test.sql
SQL> connect / as sysdba
Connected.
SQL>
SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS
SQL>
SQL> alter system set optimizer_mode=RULE;

System altered.

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      RULE
SQL>
SQL> alter system set optimizer_mode=CHOOSE;

System altered.

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      CHOOSE
SQL>
SQL> alter system set optimizer_mode=ALL_ROWS;

System altered.

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS
SQL>
SQL> connect hr
Enter password:
Connected.
SQL>
SQL> set pagesize 1000
SQL>
SQL> column employee_name format a20
SQL> column manager_name format a20
SQL>
SQL> -- Delete statistics on the EMPLOYEES TABLE
SQL> exec dbms_stats.delete_table_stats('HR','EMPLOYEES');

PL/SQL procedure successfully completed.

SQL>
SQL> -- Check if there are statistics on the EMPLOYEES table
SQL> select last_analyzed from user_tables where table_name='EMPLOYEES';

LAST_ANAL
---------

SQL>
SQL> -- Change the optimizer mode to RULE
SQL> alter session set optimizer_mode=RULE;

Session altered.

SQL>
SQL> -- List employees who earn more than their managers
SQL> SELECT emp1.first_name
  2    ||' '
  3    ||emp1.last_name AS employee_name,
  4    emp1.salary      AS employee_salary,
  5    emp2.first_name
  6    ||' '
  7    ||emp2.last_name AS manager_name,
  8    emp2.salary      AS manager_salary
  9  FROM employees emp1
 10  INNER JOIN employees emp2
 11  ON emp1.manager_id = emp2.employee_id
 12  AND emp1.salary    > emp2.salary;

EMPLOYEE_NAME        EMPLOYEE_SALARY MANAGER_NAME         MANAGER_SALARY
-------------------- --------------- -------------------- --------------
Lisa Ozer                      11500 Gerald Cambrault              11000
Ellen Abel                     11000 Eleni Zlotkey                 10500

SQL>
SQL> -- Check the query plan
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  02dn0vsp8sxb8, child number 0
-------------------------------------
SELECT emp1.first_name   ||' '   ||emp1.last_name AS employee_name,
emp1.salary      AS employee_salary,   emp2.first_name   ||' '
||emp2.last_name AS manager_name,   emp2.salary      AS manager_salary
FROM employees emp1 INNER JOIN employees emp2 ON emp1.manager_id =
emp2.employee_id AND emp1.salary    > emp2.salary

Plan hash value: 459917693

-------------------------------------------------------
| Id  | Operation                    | Name           |
-------------------------------------------------------
|   0 | SELECT STATEMENT             |                |
|   1 |  NESTED LOOPS                |                |
|   2 |   NESTED LOOPS               |                |
|   3 |    TABLE ACCESS FULL         | EMPLOYEES      |
|*  4 |    INDEX RANGE SCAN          | EMP_MANAGER_IX |
|*  5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES      |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP1"."MANAGER_ID"="EMP2"."EMPLOYEE_ID")
   5 - filter("EMP1"."SALARY">"EMP2"."SALARY")

Note
-----
   - rule based optimizer used (consider using cbo)

31 rows selected.

SQL>
SQL> -- Change the optimizer mode to CHOOSE
SQL> alter session set optimizer_mode=CHOOSE;

Session altered.

SQL>
SQL> SELECT emp1.first_name
  2    ||' '
  3    ||emp1.last_name AS employee_name,
  4    emp1.salary      AS employee_salary,
  5    emp2.first_name
  6    ||' '
  7    ||emp2.last_name AS manager_name,
  8    emp2.salary      AS manager_salary
  9  FROM employees emp1
 10  INNER JOIN employees emp2
 11  ON emp1.manager_id = emp2.employee_id
 12  AND emp1.salary    > emp2.salary;

EMPLOYEE_NAME        EMPLOYEE_SALARY MANAGER_NAME         MANAGER_SALARY
-------------------- --------------- -------------------- --------------
Lisa Ozer                      11500 Gerald Cambrault              11000
Ellen Abel                     11000 Eleni Zlotkey                 10500

SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  02dn0vsp8sxb8, child number 1
-------------------------------------
SELECT emp1.first_name   ||' '   ||emp1.last_name AS employee_name,
emp1.salary      AS employee_salary,   emp2.first_name   ||' '
||emp2.last_name AS manager_name,   emp2.salary      AS manager_salary
FROM employees emp1 INNER JOIN employees emp2 ON emp1.manager_id =
emp2.employee_id AND emp1.salary    > emp2.salary

Plan hash value: 459917693

-------------------------------------------------------
| Id  | Operation                    | Name           |
-------------------------------------------------------
|   0 | SELECT STATEMENT             |                |
|   1 |  NESTED LOOPS                |                |
|   2 |   NESTED LOOPS               |                |
|   3 |    TABLE ACCESS FULL         | EMPLOYEES      |
|*  4 |    INDEX RANGE SCAN          | EMP_MANAGER_IX |
|*  5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES      |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP1"."MANAGER_ID"="EMP2"."EMPLOYEE_ID")
   5 - filter("EMP1"."SALARY">"EMP2"."SALARY")

Note
-----
   - rule based optimizer used (consider using cbo)

31 rows selected.

SQL>
SQL> -- Change the optimizer mode to COST
SQL> alter session set optimizer_mode=ALL_ROWS;

Session altered.

SQL>
SQL> SELECT emp1.first_name
  2    ||' '
  3    ||emp1.last_name AS employee_name,
  4    emp1.salary      AS employee_salary,
  5    emp2.first_name
  6    ||' '
  7    ||emp2.last_name AS manager_name,
  8    emp2.salary      AS manager_salary
  9  FROM employees emp1
 10  INNER JOIN employees emp2
 11  ON emp1.manager_id = emp2.employee_id
 12  AND emp1.salary    > emp2.salary;

EMPLOYEE_NAME        EMPLOYEE_SALARY MANAGER_NAME         MANAGER_SALARY
-------------------- --------------- -------------------- --------------
Lisa Ozer                      11500 Gerald Cambrault              11000
Ellen Abel                     11000 Eleni Zlotkey                 10500

SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  02dn0vsp8sxb8, child number 2
-------------------------------------
SELECT emp1.first_name   ||' '   ||emp1.last_name AS employee_name,
emp1.salary      AS employee_salary,   emp2.first_name   ||' '
||emp2.last_name AS manager_name,   emp2.salary      AS manager_salary
FROM employees emp1 INNER JOIN employees emp2 ON emp1.manager_id =
emp2.employee_id AND emp1.salary    > emp2.salary

Plan hash value: 468575080

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |       |     7 (100)|          |
|*  1 |  HASH JOIN         |           |     5 |   520 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEES |   107 |  5564 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMPLOYEES |   107 |  5564 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMP1"."MANAGER_ID"="EMP2"."EMPLOYEE_ID")
       filter("EMP1"."SALARY">"EMP2"."SALARY")

Note
-----
   - dynamic sampling used for this statement (level=2)

29 rows selected.

SQL>
SQL> -- Use the RULE hint
SQL> SELECT /*+ RULE */ emp1.first_name
  2    ||' '
  3    ||emp1.last_name AS employee_name,
  4    emp1.salary      AS employee_salary,
  5    emp2.first_name
  6    ||' '
  7    ||emp2.last_name AS manager_name,
  8    emp2.salary      AS manager_salary
  9  FROM employees emp1
 10  INNER JOIN employees emp2
 11  ON emp1.manager_id = emp2.employee_id
 12  AND emp1.salary    > emp2.salary;

EMPLOYEE_NAME        EMPLOYEE_SALARY MANAGER_NAME         MANAGER_SALARY
-------------------- --------------- -------------------- --------------
Lisa Ozer                      11500 Gerald Cambrault              11000
Ellen Abel                     11000 Eleni Zlotkey                 10500

SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  9mapku5nyz31y, child number 0
-------------------------------------
SELECT /*+ RULE */ emp1.first_name   ||' '   ||emp1.last_name AS
employee_name,   emp1.salary      AS employee_salary,   emp2.first_name
  ||' '   ||emp2.last_name AS manager_name,   emp2.salary      AS
manager_salary FROM employees emp1 INNER JOIN employees emp2 ON
emp1.manager_id = emp2.employee_id AND emp1.salary    > emp2.salary

Plan hash value: 459917693

-------------------------------------------------------
| Id  | Operation                    | Name           |
-------------------------------------------------------
|   0 | SELECT STATEMENT             |                |
|   1 |  NESTED LOOPS                |                |
|   2 |   NESTED LOOPS               |                |
|   3 |    TABLE ACCESS FULL         | EMPLOYEES      |
|*  4 |    INDEX RANGE SCAN          | EMP_MANAGER_IX |
|*  5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES      |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP1"."MANAGER_ID"="EMP2"."EMPLOYEE_ID")
   5 - filter("EMP1"."SALARY">"EMP2"."SALARY")

Note
-----
   - rule based optimizer used (consider using cbo)

31 rows selected.

SQL>
SQL> -- Use the CHOOSE hint
SQL> SELECT /*+ CHOOSE */ emp1.first_name
  2    ||' '
  3    ||emp1.last_name AS employee_name,
  4    emp1.salary      AS employee_salary,
  5    emp2.first_name
  6    ||' '
  7    ||emp2.last_name AS manager_name,
  8    emp2.salary      AS manager_salary
  9  FROM employees emp1
 10  INNER JOIN employees emp2
 11  ON emp1.manager_id = emp2.employee_id
 12  AND emp1.salary    > emp2.salary;

EMPLOYEE_NAME        EMPLOYEE_SALARY MANAGER_NAME         MANAGER_SALARY
-------------------- --------------- -------------------- --------------
Lisa Ozer                      11500 Gerald Cambrault              11000
Ellen Abel                     11000 Eleni Zlotkey                 10500

SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  grkrn5nk1xvmc, child number 0
-------------------------------------
SELECT /*+ CHOOSE */ emp1.first_name   ||' '   ||emp1.last_name AS
employee_name,   emp1.salary      AS employee_salary,   emp2.first_name
  ||' '   ||emp2.last_name AS manager_name,   emp2.salary      AS
manager_salary FROM employees emp1 INNER JOIN employees emp2 ON
emp1.manager_id = emp2.employee_id AND emp1.salary    > emp2.salary

Plan hash value: 459917693

-------------------------------------------------------
| Id  | Operation                    | Name           |
-------------------------------------------------------
|   0 | SELECT STATEMENT             |                |
|   1 |  NESTED LOOPS                |                |
|   2 |   NESTED LOOPS               |                |
|   3 |    TABLE ACCESS FULL         | EMPLOYEES      |
|*  4 |    INDEX RANGE SCAN          | EMP_MANAGER_IX |
|*  5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES      |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP1"."MANAGER_ID"="EMP2"."EMPLOYEE_ID")
   5 - filter("EMP1"."SALARY">"EMP2"."SALARY")

Note
-----
   - rule based optimizer used (consider using cbo)

31 rows selected.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

P.S. Do we ever need the rule based optimizer? Never Say Never Again. The Oracle binary contains evidence that the rule based optimizer may still be used for some recursive operations in Oracle Database 11g Release 2.

[oracle@localhost ~]$ cd $ORACLE_HOME/bin
[oracle@localhost bin]$ ls -l oracle
-rwsr-s--x 1 oracle oracle 188960467 Oct  2  2010 oracle
[oracle@localhost bin]$ strings oracle | egrep -i '\/\*\+ rule \*/' 
select /*+ rule */ c.name, u.name from con$ c, cdef$ cd, user$ u  where c.con# = cd.con# and cd.enabled = :1 and c.owner# = u.user#
/*+ RULE */
select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
select /*+ rule */ a.*,   sys.dbms_rcvman.num2displaysize(input_bytes)  input_bytes_display,   sys.dbms_rcvman.num2displaysize(output_bytes)  output_bytes_display,    sys.dbms_rcvman.num2displaysize(input_bytes_per_sec)          input_bytes_per_sec_display,   sys.dbms_rcvman.num2displaysize(output_bytes_per_sec)          output_bytes_per_sec_display,    sys.dbms_rcvman.sec2displaytime(elapsed_seconds) time_taken_display from (select unique a.session_recid session_key, a.*,    decode(autobackup_count, 0, 'NO', 'YES') autobackup_done,    decode(status_weight, 2000, 'FAILED',                          1900, 'RUNNING WITH ERRORS',                          1500, 'RUNNING WITH WARNINGS',                          1001, 'RUNNING',                           900, 'COMPLETED WITH ERRORS',                           500, 'COMPLETED WITH WARNINGS',                           001, 'COMPLETED',                           'FAILED') status,    decode(object_type_weight,9, 'DB FULL',                              8, 'RECVR AREA',                              7, 'DB INCR',                              6, 'DATAFILE FULL',                              5, 'DATAFILE INCR',                              4, 'ARCHIVELOG',                              3, 'CONTROLFILE',                              2, 'SPFILE',                              1, 'BACKUPSET', null) input_type,    decode(optimized_weight, 1, 'YES', 'NO') optimized,   abs(a.end_time-a.start_time)*86400 elapsed_seconds,   case when a.input_bytes/decode(a.output_bytes,0,null, a.output_bytes) > 1   then a.input_bytes/decode(a.output_bytes,0,null, a.output_bytes) else 1 end      compression_ratio,   a.input_bytes/(decode(a.end_time-a.start_time, 0, 1,      abs(a.end_time-a.start_time)*86400)) input_bytes_per_sec,   a.output_bytes/(decode(a.end_time-a.start_time, 0, 1,      abs(a.end_time-a.start_time)*86400)) output_bytes_per_sec        from  (select session_recid, session_stamp, command_id,        min(start_time) over           (partition by session_recid, session_stamp) start_time,        max(end_time) over            (partition by session_recid, session_stamp) end_time,        sum(input_bytes) over            (partition by session_recid, session_stamp) input_bytes,        sum(output_bytes) over            (partition by session_recid, session_stamp) output_bytes,        max(status_weight) over            (partition by session_recid, session_stamp)status_weight,        max(optimized_weight) over            (partition by session_recid, session_stamp)            optimized_weight,        max(object_type_weight) over            (partition by session_recid, session_stamp)            object_type_weight,        decode(count(distinct output_device_type) over                   (partition by session_recid, session_stamp),1,              first_value(output_device_type) over                   (partition by session_recid, session_stamp),0,              null, '*') output_device_type,        sum(autobackup_count) over            (partition by session_recid, session_stamp) autobackup_count,        backed_by_osb   from V$RMAN_BACKUP_SUBJOB_DETAILS) a)a
select /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1
delete /*+ rule */ from %(1)s where rowid in (select column_value from table(:ridlist))
[oracle@localhost bin]$ 
Categories: DBA, Oracle
  1. May 4, 2015 at 6:31 am

    idiot

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: