Cardinality Feedback

Topics about Databases, Backups, Tuning, Architecture, Systems Management, etc etc.

Post Reply
bgrenn
Posts: 91
Joined: Mon Mar 16, 2009 11:47 am
Location: Rochester, NY
Contact:

Cardinality Feedback

Post by bgrenn »

Chances are you going "what is that" ?

Cardinality feedback is a new feature with 11g.. If you haven't heard, a query can have multiple plans in 11g.  The most common way this can happen is through the use of bind variables and histograms.. In 10g, there was only one plan. This was decided when the query is parsed, and the bind variables are "peeked".. Now with 11g, oracle "peeks" the bind variable every time, and sees if there is a better plan.. eventually it builds a chart of possible plans based on bind variable distribution.. This is all well documented..

Cardinality feedback is similar.  When oracle creates a plan, it estimates the number of rows returned for each section of the plan.. If when executing the plan, it finds that it's estimates were way off, then it will change the plan.. Oracle will dynamically mutate the plan for better response.  This can be seen when you look at the plan information from  display_cursor.

here is how you work with cardinality feedback.

-- Turn on cardinality feedback
alter session set "_optimizer_use_feedback" = true;

-- Turn off (disable) cardinality feedback
alter session set "_optimizer_use_feedback" = false;
Post Reply