Many of you are aware of the Oracle 11g Database New Features and while some may be generally interested in new features, one area that I focus on is new features that yield gains in performance. Some of these features can be found in the General Server Performance section of the Oracle 11g Database New Features documentation. There is one area (for now…) that didn’t make this list but I feel is worth mentioning - performance enhancements made to DBMS_STATS.

The Necessity of Representative Statistics

Representative statistics are the foundation that the Optimizer relies on to make the best decisions when choosing execution plans. One recent blog post from Don Seiler, with the help of Wolfgang Breitling, is a prefect real-world case. This blog post dealt with out-of-range values, but one other case that often causes headaches is data skew. In the Real-World Performance Roundtable, Part II session at OracleWorld 2006, I discussed a basic stats gathering strategy that dealt with the exception case of data skew. When using the DBMS_STATS default of DBMS_STATS.AUTO_SAMPLE_SIZE in 10g and 9i, the NDV (Number of Distinct Values) may be statistically inaccurate when there is significant data skew. In order to deal with this exception, a fixed percentage of data that yields statistically representative NDV counts should be chosen.

11g DBMS_STATS

In 11g there have been some enhancements made to the DBMS_STATS package. Overall the GATHER_* processes run faster but what stands out to me is the speed and accuracy that DBMS_STATS.AUTO_SAMPLE_SIZE now gives. As a performance person, I often times make reference to letting the numbers tell the story, so lets dive into a comparison between 10.2.0.3 and 11.1.0.5. I’ve chosen the same data set that I used in the “Refining the Stats” section of Real-World Performance Roundtable, Part II session. Stats were serially gathered with ESTIMATE_PERCENT of 10%, 100%, and DBMS_STATS.AUTO_SAMPLE_SIZE.

10.2.0.3

11.1.0.5

11g DBMS_STATS Observations

As you can see by the numbers, 11g pulls a win in each of the three GATHER_TABLE_STATS calls. Take note of the AUTO_SAMPLE_SIZE timings. The 11g AUTO_SAMPLE_SIZE gather takes the same time as the 11g 10% sample. Not bad!

NDV Accuracy

We’ve seen that the 11g gather stats is overall faster and that the 11g AUTO_SAMPLE_SIZE shows a significant improvement in speed compared to 10.2.0.3 AUTO_SAMPLE_SIZE for this table, but how do the NDV calculations compare? Again, let’s look at the numbers. I’ve queried USER_TAB_COL_STATISTICS to get the NDV and SAMPLE_SIZE for our skewed data set.

10.2.0.3

ESTIMATE_PERCENT => 10
COLUMN_NAME     NUM_DISTINCT  NUM_NULLS SAMPLE_SIZE
--------------- ------------ ---------- -----------
C1                     31464          0     2148910
C2                    608544          0     2148910
C3                    359424          0     2148910

ESTIMATE_PERCENT => 100%
COLUMN_NAME     NUM_DISTINCT  NUM_NULLS SAMPLE_SIZE
--------------- ------------ ---------- -----------
C1                     60351          0    21456269
C2                   1289760          0    21456269
C3                    777942          0    21456269

ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE
COLUMN_NAME     NUM_DISTINCT  NUM_NULLS SAMPLE_SIZE
--------------- ------------ ---------- -----------
C1                      1787          0        5823
C2                    367075          0      576909
C3                     52464          0       57431

11.1.0.5

ESTIMATE_PERCENT => 10
COLUMN_NAME     NUM_DISTINCT  NUM_NULLS SAMPLE_SIZE
--------------- ------------ ---------- -----------
C1                     31320          0     2147593
C2                    608814          0     2147593
C3                    359365          0     2147593

ESTIMATE_PERCENT => 100
COLUMN_NAME     NUM_DISTINCT  NUM_NULLS SAMPLE_SIZE
--------------- ------------ ---------- -----------
C1                     60351          0    21456269
C2                   1289760          0    21456269
C3                    777942          0    21456269

ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE
COLUMN_NAME     NUM_DISTINCT  NUM_NULLS SAMPLE_SIZE
--------------- ------------ ---------- -----------
C1                     59852          0    21456269
C2                   1270912          0    21456269
C3                    768384          0    21456269

As expected, the 100% samples are identical and the 10% samples are statistically equivalent. One interesting data point is that the SAMPLE_SIZE for the 11g AUTO_SAMPLE_SIZE run shows the exact SAMPLE_SIZE as the 100% gather - the total number of rows in the table. Also note that the NDV counts for the 11g AUTO_SAMPLE_SIZE gather are statistically equivalent to the 100% sample. What does this mean? It means that the 11g AUTO_SAMPLE_SIZE had been enhanced to provide nearly 100% sample accuracy, even on skewed data sets.

Summary

Overall the 11g DBMS_STATS has been enhanced to gather stats in less time, but in my opinion the significant enhancement is to AUTO_SAMPLE_SIZE which yields near 100% sample accuracy in 10% sample time. As the documentation says:

…Oracle recommends setting the ESTIMATE_PERCENT parameter of the DBMS_STATS gathering procedures to DBMS_STATS.AUTO_SAMPLE_SIZE to maximize performance gains while achieving necessary statistical accuracy.

I couldn’t agree with the documentation more. If you wish to know more about how the new DBMS_STATS.AUTO_SAMPLE_SIZE works, see section 3 of Efficient and scalable statistics gathering for large databases in Oracle 11g.