Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

 
 

Automating Statistics Sample Size with dbms_stats 

The higher the quality of the schema statistics will result in higher probability that CBO will choose the optimal execution plan. Unfortunately, doing a complete analysis of every rows of every table in a schema could take days and most shops must sample their database to get CBO statistics.

 

The goal of estimating the sample size is to take a large enough sample of the database to provide top quality data for the CBO while not adversely impacting server resources.  Now that how the dbms_stats option works has been introduced, it will be useful see how to specify an adequate sample size for dbms_stats.

 

In earlier releases, the DBA had to guess what percentage of the database provided the best sample size and sometimes under analyzed the schema. Starting with Oracle9i Database, the estimate_percent argument was added to dbms_stats to allow Oracle to automatically estimate the best percentage of a segment to sample when gathering statistics.  A sample invocation follows below:

 

estimate_percent => dbms_stats.auto_sample_size

 

After collecting statistics with an automatic sample size, the accuracy of the automatic statistics sampling can be verified by looking at the sample_size column on any of these data dictionary views:

§       dba_object_tables   

§       dba_tab_col_statistics   

§       dba_tab_partitions  

§       dba_tab_subpartitions   

§       dba_part_col_statistics  

§       dba_subpart_col_statistics   

§       dba_tables 

§       dba_tab_cols

§       dba_tab_columns   

§       dba_all_tables  

§       dba_indexes  

§       dba_ind_partitions   

§       dba_ind_subpartitions   

In practice, the auto_sample_size  option of dbms_stats generally chooses a sample_size from five to 20 percent when using automatic sampling, depending on the size of the tables and the distribution of column values.

 

Too small a sample can impact the CBO, so one should always make sure to take a statistically significant sample size for every object in the schema.   The next section will introduce some methods DBA’s use to ensure that their SQL optimizer always has great schema statistics.

 

SEE CODE DEPOT FOR FULL SCRIPTS


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks