New Optimizer in 11g

OPTIMIZER_USE_INVISIBLE_INDEXES

NAME                                 TYPE        VALUE

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

optimizer_use_invisible_indexes      boolean     FALSE

Invisible indexes is a Oracle 11g new feature.  This allows you to create an index on a table without affecting any execution plan and so will not affect the performance of the application.  If you want to test a potential index, you need to set OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE within a session before executing SQL statements.  If the index is useful, you can mark it visible.

OPTIMIZER_USE_PENDING_STATISTICS

SQL> show parameter optimizer_use_pending_statistics

NAME                                 TYPE        VALUE

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

optimizer_use_pending_statistics     boolean     FALSE

It’s possible to gather optimizer statistics but not published immediately in Oracle 11g.  You can test these pending statistics by alter session command to set OPTIMIZER_USE_PENDING_STATISTICS=TRUE.

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

NAME                                 TYPE        VALUE

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

optimizer_use_sql_plan_baselines     boolean     FALSE

When this parameter is set to TRUE, Oracle will automatically capture a SQL plan baseline for every repeatable SQL statement on the system

OPTIMIZER_SQL_PLAN_BASELINES

NAME                                 TYPE        VALUE

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

optimizer_use_sql_plan_baselines     boolean     TRUE

The optimizer will only use one of these known plans even if a different plan is found during SQL-comilation. This gurantees that any plan change is verified to have a better performance before using it.