Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle cardinality feedback tips

Oracle Database Tips by Donald BurlesonApril 29, 2015

Question: What is Oracle cardinality feedback?  I hear that cardinality feedback is new in 11g and helps Oracle make better optimizer decisions.  Is cardinality feedback shared to other SQL statements?  Does cardinality feedback stored between database bounces?  If so, where is the cardinality feedback stored?
Answer:  Like all new features, cardinality feedback is not well documented, but in a nutshell, the cardinality feedback allows the SQL optimizer to learn from its mistakes.  I've not used cardinality feedback, but here is what I have gleaned from credible sources.  Note:  This user determined an issue with cardinality feedback cause poor SQL performance and fix it by setting "_optimizer_use_feedback" to turn-off cardinality feedback.

When Oracle create a SQL execution plan the optimizer "guesses" the number of rows that might be returned by a specific table join or table row fetch.  This row size estimate is called labeled "cardinality" and it greatly effects the optimizers choices of access plans.  The cardinality appears in the execution plan details as an integer.

SQL tuning with cardinality estimates

In complex SQL statements, one of the most important jobs of the SQL optimizer is determining the best table join order.  In an ideal world, the optimizer would be able to accurate guess the result set of a table join so that the first table join produces the smallest rowset, the least "intermediate row baggage" to lug through subsequent joins.

Bad join order (5,000 rows to carry in TEMP)

Better join order (only 2.000 to carry in TEMP)


Also see how to tune SQL with histograms.

The central problem with cardinality estimation is the in cases of complex WHERE clauses the optimizer does not have enough information about inter-join result set sizes to determine the optimal table join order.  Einstein would have trouble figuring out this optimal table join order:

-- ****************************************************************
-- impossible to guess the cardinality without extended statistics:
-- ****************************************************************
select * from stuff

   credit_rating * extended_credit > .07
   (qty_in_stock * velocity) /.075 < 30
   (sku_price / 47) * (qty_in_stock / velocity) > 47;

It's not always Oracle fault that he cannot guess the cardinality of an explain plan step.  In cases with complex or function-based where clauses, it can be very difficult to guess the cardinality accurately.

See these related notes on Oracle join cardinality estimates and how dynamic sampling helps in cardinality estimation.

After query execution, the cardinality feedback mechanism will somehow store the actual returned rowset size, thereby learning from its own mistakes in cardinality estimation.  Oracle does not document how this cardinality feedback is stored, but repeated executions of the "autotrace" command clearly show Oracle learning the actual rowset feedback.

Features and limitations of cardinality feedback:

  • It appears that the cardinality feedback is only stored in the SGA RAM and it currently is not stored for use after the instance is re-started.

  • It also appears that cardinality feedback is not shared between sessions, and the feedback is currently available only within a single session.

  • Oracle does not support cardinality estimates for multiple table joins, and the existing cardinality feedback is only available for single table cardinalities.

  • Cardinality feedback for table functions (i.e. execution plans with collection iterator pickler fetch) started in Oracle 11g release 1.

  • In some early releases (Oracle 10g), cardinality feedback appeared to be tied to the settings for dynamic sampling, and it only works when optimizer_dynamic_sampling to level 4 or higher: 

    select /*+ dynamic_sampling (customer 4) */

  • A 10053 SQL trace shows that Oracle uses opt_estimate hint when invoking cardinality feedback.

  • Cardinality feedback is controlled by a hidden parameter called _optimizer_use_feedback and _optimizer_extended_cursor_sharing_rel.

  •  Cardinality feedback can be enabled and disabled at the system or session level with alter system statements:

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

Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.



Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster