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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

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
where

   credit_rating * extended_credit > .07
and
   (qty_in_stock * velocity) /.075 < 30
or
   (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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster