Method R Discussion GroupMethod R helps developers, DBAs, and decision-makers optimize Oracle-based software in every phase of the software life cycle. |
||
Discuss Method R products, techniques, and events. Ask new questions or post your own responses. |
What should be the percent for gathering statistics?
When (how often) do you need to collect stats?
This is one of the most frequently-asked questions about Oracle performance, and it's one of the most difficult to answer succinctly. Some excellent places to begin, in my opinion, are:
http://wedonotuse.blogspot.com/2005/11/how-often-should-stats-be-collected.html - Mogens Noergaard's overview of the subject. http://www.jlcomp.demon.co.uk/stats_i.html - A few pages by Jonathan Lewis to get you off on the right foot.
The approach I have taken for statistics is the following.
1.) Use the default stats gathering process as the baseline. 2.) You will notice that there are some one off's especially around the calculation of number of distinct values and date columns that you will have problems with. 3.) Gather them with a higher percentage for the one off's. In addition, you could the following. 1.) Read Jonathan Lewis's book on Cost Based Fundamentals. 2.) Run stats gathering process and look around the dba_tables, dba_indexes and related tables to see how the stats are updated. 3.) Dump the 10053 trace and understand how the optimizer goes about generating "optimal" execution plans with the statistics it has available at its disposal. |
Powered by FogBugz