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, written by Deepak Bhatnagar, Mohammed Saleem, and Andy Tremayne, 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 SessionRelated 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)