Recommended Hidden Parameters for 11gR1

Question #1: ========== _optimizer_cost_based_transformation=false Currently set to false, should we keep or remove it for 11g upgrade? It is a workarond for several bugs, including ORA-600 bug 6666870 fixed only in 11,2? ANSWER ======= _OPTIMIZER_COST_BASED_TRANSFORMATION controls whether or not the optimizer tries different transformations against a query using the cost with and without the transformations in order to determine if a transformation is useful or not. The parameter can be set to any of: "exhaustive", "iterative", "linear", "on", "off" giving some control over how much effort is given to costing various transformations. Cost based transformation can add a high overhead at parse time but can yeild considerable benefits by way of a better plan for the statement. Known bugs 6666870 11.2 OERI:qctcte1 from cost based transformation 8541212 11.2 OERI [qctcte1] with function based index and OLD style join push predicate Question #2: _undo_autotune=false Currently set to false, should we remove it for 11g upgrade? Search key: _undo_autotune 11.1.0.7 Bug.8430038/7291739 ORA-1628 MAX # EXTENTS 32765 REACHED FOR ROLLBACK SEGMENT _SYSSMU105_123755639: Fixed in 11.2 Patch available If you leave the _undo_autotune=false in the parameter file in 11.1.0.7, then you will have to manually adjust UNDO_RETENTION, and none of the historical information would be captured in undostats. It is better to remove this parameter and allow AUM to administer the tuned retention for you. However, in 11.1.0.7 there is a bug that can occur for which the workaround is to set it to false. This is Bug 7291739. So my recommendation is to remove the parameter, allowing _undo_autotune to default to true, then install the fix for Bug 7291739 in 11.1.0.7. Question #3: =========== _unnest_subquery=false - Currently NOT set, but recommended by PeopleSoft in note ID 749100.1 "Operating System, RDBMS & Additional Component Patches Required for Installation PeopleTools 8.49" ANSWER ======= _UNNEST_SUBQUERY This parameter controls whether the optimizer attempts to unnest correlated subqueries or not. Known bugs 8245217 11.2 Dump [vopcpl] unnesting subquery