Wouldn’t you agree?
My colleagues from the Optimizer Development might forgive me but a lot of people would say: “Everything went fine after upgrade – except for those tiny 3 queries!”. And I know, you have tested a lot. I’d like to tell you about a few issues we’ve seen post upgrade with 11.2.0.2.
- You have captured your plans in Oracle 10.2 pre-upgrade.
- You have moved these plans from your SQL Tuning Set into the SQL Plan Baseline in Oracle 11.2.
- But the optimizer doesn’t pick them although you have:
(a) ACCEPTED and
(b) FIXED them.
Why?
It might be worth a try to set this underscore parameter and test again:
_optimizer_use_feedback=FALSE
What does _optimizer_use_feedback mean?
You’d find some detailed information in our Upgrade Companion (MOS Note:785351.1):
Cardinality Feedback (Oracle Database 10g to 11g Change)
Cardinality feedback is an enhancement made to the Optimizer in Oracle Database 11g Release
2. Cardinality feedback compares cardinality estimates used to derive the plan with the actual
cardinality seen in the first execute. If the estimate is 2X off, the cursor is marked for hard parse
next time around. The cardinality information seen at first execute is supplied at the next hard
parse thus allowing the Optimizer an opportunity to improve on the plan now that it knows more
about the actual cardinality seen in the query. If cardinality feedback is used, it is displayed in the
section of the execution plan. Cardinality feedback works for predicates on tables, indexes and
group by clauses. It does not help for cardinality mis-estimates for joins. Feedback is not
persistent on disk, it resides in memory only. The Optimizer will need to “relearn” something if the
database is shutdown and restarted. (Related to _optimizer_use_feedback parameter.)
Besides that there were known issue in 11.2.0.1 (Bug 8608703 – SubOptimal Execution Plan created by Cardinality Feedback (Doc ID 8608703.8) – Bug 9342979 – Suboptimal plan change with cardinatilty feedback (Doc ID 9342979.8)) – but they seem to be fixed with patch set 11.2.0.2 and above.
While doing some research in our support portal (MOS) I accidentally found these interesting notes:
- PARAMETERS TO CHANGE 11.2.0.1 TO 10.2.0.4 (Doc ID 1274553.1)
- PARAMETERS TO CHANGE 11.2.0.1 TO 11.1.0.7 (Doc ID 1096377.1)
To be continued …
Please don’t take this as a general recommendation to set this underscore and switch off the functionality as this isn’t a general recommendation!!! Be always careful with underscore/hidden parameters and always check back with Oracle Support – in most cases underscores slow down the upgrade and using underscores from previous releases has often a bad impact on performance!
