Wednesday Oct 19, 2011

It's always the Optimizer, isn't it?! - Part 1

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.

  1. You have captured your plans in Oracle 10.2 pre-upgrade.
  2. You have moved these plans from your SQL Tuning Set into the SQL Plan Baseline in Oracle 11.2.
  3. 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:

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!

About

Mike Dietrich - Oracle Mike Dietrich
Senior Principal Technologist - Database Upgrade Development Group - Oracle Corporation

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite with reference customers. Acting as interlink between customers and the Upgrade Development.

Contact me either via XING or LinkedIn

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
4
5
6
9
10
12
13
15
16
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
Slides Download Center
OOW Slides Download
Visitors since 17-OCT-2011
White Paper and Docs
Oracle Blogs
Workshops
Viewlets and Videos
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers