Database 12c: RBO (Rule Based Optimizer) is not death


Starting with Oracle Database 10g Release 1 (10.1), the cost-based optimizer (CBO) is now enabled by default. The rule-based optimizer is no longer supported in Oracle Database 10g Release 1 (10.1). As a result, rule and choose are no longer supported as OPTIMIZER_MODE initialization parameter values and a warning is displayed in the alert log if OPTIMIZER_MODE is set to either of these values.

  • Oracle has announced to remove RBO in 11.1 and hasn’t done.
  • Oracle has announced to remove RBO in 11.2 and hasn’t done.
  • Oracle has announced to remove RBO in 12.1 and hasn’t done.

Here the test:

1. Create environment

SQL> conn dhafner/<pwd>
Connected.
SQL> create table tab1 (id number,text varchar2(32767));

Table created.

SQL> desc tab1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
TEXT                                               VARCHAR2(32767)

SQL>

SQL> insert into dhafner.tab1 (select object_id,object_name from dba_objects);

90739 rows created.

SQL> commit;

Commit complete.

SQL> conn dhafner/dhafner
Connected.
SQL> create index tab1_idx on tab1(id);

Index created.

2. Test with CBO without statistics

SQL> explain plan for select text from tab1 where id=1000;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2211052296

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 | 16398 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TAB1 |     1 | 16398 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - filter("ID"=1000 AND INTERNAL_FUNCTION("TEXT"))

13 rows selected.

3. Change to RULE

SQL> alter session set optimizer_mode=rule;

Session altered.

SQL> explain plan for select text from tab1 where id=1000;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 926492477

------------------------------------------------
| Id  | Operation                   | Name     |
------------------------------------------------
|   0 | SELECT STATEMENT            |          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TAB1     |
|*  2 |   INDEX RANGE SCAN          | TAB1_IDX |
------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - filter(INTERNAL_FUNCTION("TEXT"))
2 - access("ID"=1000)

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

19 rows selected.

4. To show the effect test it now with CBO and statistics

SQL> exec dbms_stats.gather_table_stats('DHAFNER','TAB1',cascade=>true);

PL/SQL procedure successfully completed.

SQL> alter session set optimizer_mode=all_rows;

Session altered.

SQL> select * from table(dbms_xplan.display())

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 358219344

------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |     1 |    30 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TAB1     |     1 |    30 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | TAB1_IDX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

1 - filter(INTERNAL_FUNCTION("TEXT"))
2 - access("ID"=1000)

15 rows selected.

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