Managing ORACLE Statistics for Optimal Performance
Karen Morton has published a very well written paper on Managing Statistics for Optimal Query Performance available at Cary Milsap’s MethodR site (http://www.method-r.com). The paper discusses ORACLE’s use of statistics in the query optimizer. Her suggestions for a adopting a statistics collection strategy including dynamic sampling is good complement to an earlier post I wrote titled ORACLE Tuning – When the SQL can’t be modified. This paper also includes a terrific discussion of a “bind peeking” (ORACLE 9i and higher – optimizer behavior as it “peeks” into bind variables in the hard parse phase before query plan decisions are made). This is a topic I don’t see much written about. Her contribution to understanding the complexity of managing this behavior in the ORACLE optimizer is much appreciated.
Very useful and well written, the paper is available when you visit the Method R site (http://www.method-r.com/downloads) on a regular basis and bookmark this important collection of papers and presentations.