Query Optimizer 11g – What’s new? by Christian Antognini

- when it comes to optimizer there is always new stuff with new releases

- invisible indexes : alter index indx invisible; 
cbo does not use it after setting invisible, also hints are not honored,
create index indx .. invisible; 
alter session optimizer_use_invisible_indexes=TRUE;

- 10g could not use LIKE with Linguistic search, 11g can

- full outer join : _optimizer_native_full_outer_join and no_native_full_outer_join hints

- join filter partition pruning

- composite part. with all combinations in 11g supports pruning

- predicates based on expressions :
dbms_stats.create_extended_stat(.. extension=>'(trunc(n))' ..)
user_stat_extensions view, a virtual colum created
but better to create it by yourself because of Oracle naming

- correlated data : again with extensions
dbms_stats.create_extended_stat(.. extension => (n1,n2) ..)
sys_op_combined_hash(n1,n2) result is applied to virtual column here
only with equality predicates

- default preferences for dbms_stats : 
the set_param procedure is depreciated, set_global/schema/database_prefs procedures for 11g
dbms_stats.set_table_prefs(.. 

- statistics collection for partitioned objects :
incremental gathering can be used now, pname =>'incremental', pvalue=>'true'
synopsis addition to global stats, they are stored in the dict.

- pending statistics : pname => 'publish', pvalue=>'false'
init.ora optimizer_use_pending_statistics
test and if you are satisfied with these than publish them :)

- stored outlines are replaced by execution plan baselines : only in Ent.Ed.
optimizer maintains a history of SQL statements
a. to capture; alter session set optimizer_capture_sql_plan_baselines=TRUE
b. run several times your query
c. query dba_sql_plan_baseline, "origin auto_capture" and note part of dbms_xplan gives the information that baseline is used
if you run several times after a new index creation to accept the new plan dbms_spm.evolve_sql_base_line a stats report created also, old one is not dropped
if the new index droped baseline is not used and if you re-create the same index second baseline automatically used since this baseline information was not dropped with the index
dbms_spm.load_plans_from_Sqlset/cursor_cache

- bind variable peeking : at the 4th time in the demo optimizer understood that for the bind used in the skewed table is not the best one and changed the plan
v$sql, is_bind_sensitive - is_bind_aware - is_sharable columns
v$sql_cs_selectivity for detailed stats