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
Comment