ORA-00096

The rule based optimizer is no longer supported. This example, tested in Oracle 11.2, shows that you can still use it. For now, if you use the rule based optimizer with set autotrace on, Oracle just displays a message suggesting you should use the CBO (cost based optimizer) instead. Oracle have said that, in some future release, you will not be allowed to use the rule based optimizer at all. If you try to use it then, you will get an ORA-00096 as shown at the end of this example:

SQL> alter session set optimizer_mode=rule

  2  /

Session altered.

SQL> set autotrace on

SQL> select sysdate from dual

  2  /

SYSDATE

---------

16-MAY-12

Execution Plan

-------------------------------------------------------

Plan hash value: 1546270724

---------------------------------

| Id  | Operation        | Name |

---------------------------------

|   0 | SELECT STATEMENT |      |

|   1 |  FAST DUAL       |      |

---------------------------------

Note

-----

   - rule based optimizer used (consider using cbo)

Statistics

-------------------------------------------------------

          0  recursive calls

          0  db block gets

          0  consistent gets

          0  physical reads

          0  redo size

        530  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> alter session set optimizer_mode=blah

  2  /

ERROR:

ORA-00096: invalid value BLAH for parameter

optimizer_mode, must be from among first_rows_1000,

first_rows_100, first_rows_10, first_rows_1,

first_rows, all_rows, choose, rule

SQL>