New Whitepaper: Best Practices for Gathering EBS Database Statistics
- by Elke Phelps (Oracle Development)
Most Oracle Applications DBAs and E-Business Suite users understand the importance of accurate database statistics. Missing, stale or skewed statistics can adversely affect performance.
Oracle E-Business Suite statistics should only be gathered using FND_STATS or the Gather Statistics concurrent request. Gathering statistics with DBMS_STATS or the desupported ANALYZE command may result in suboptimal executions plans for E-Business Suite.
Our E-Business Suite Performance Team has been busy implementing and testing new features for gathering statistics using FND_STATS in Oracle E-Business Suite databases. The new features and guidelines for when and how to gather statistics are published in the following whitepaper:
Best Practices for Gathering Statistics with Oracle E-Business Suite (Note 1586374.1)
The new white paper details the following options for gathering statistics using FND_STATS and the Gather Statistics concurrent request::
History Mode - backup existing statistics prior to gather new statistics
GATHER_AUTO Option - gather statistics for tables based upon % change
Histograms - collect statistics for histograms
AUTO Sampling - use the new FND_STATS feature that supports the AUTO option for using AUTO sample size
Extended Statistics - use the new FND_STATS feature that supports the creation of column groups and automatic statistics collection on the column groups when table statistics are gathered
Incremental Statistics - gather incremental statistics for partitioned tables
The new white paper also includes examples and performance test cases for the following:
Extended Optimizer Statistics
Incremental Statistics Gathering
Concurrent Statistics Gathering
This white paper includes details about the standalone Oracle E-Business Suite Release 11i and 12 patches that are required to take advantage of this new functionality.
Your feedback is welcome
We would be very interested in hearing about your experiences with these new options for gathering statistics. Please feel free to post your comments here or drop us a line privately.Related Oracle OpenWorld 2013 Session
Getting Optimal Performance from Oracle E-Business Suite (CON8485)
Related My Oracle Support Notes
Collecting Statistics with Oracle EBS 11i and R12 (Note 368252.1)
Non-EBS Related Blogs, White Papers and My Oracle Support Notes
Oracle Optimizer Blog
Understanding Optimizer Statistic (white paper)
Fixed Objects Statistics(GATHER_FIXED_OBJECTS_STATS) Considerations (Note 798257.1)
