By Elke Phelps-EBS Development-Oracle on Oct 23, 2013
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.
- 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 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)