Improved Statistics Gathering in Oracle 11g

Page: 1 2

dbms_stats:

Oracle uses dbms_stats to manage optimizer statistics. The dbms_stats estimates statistics in a better way especially for large partitioned tables and thus results in faster SQL execution plans.  dbms_stats package provides several pl/sql procedures to gather statistics for a table, schema, or a database.

gather_table_statistics:

gather_table_statistics is used to gather statistics on a table The estimate_percent parameter of gather_table_statistics specifies the sampling percentage of the statistics gathering. The users can specify any number 0 to 100 for this parameter. Below command specifies a 1% sampling percentage for this parameter.

exec dbms_stats.gather_table_stats(null, 'MYTABLE', estimate_percent => 1);

Appropriate Sampling Percentage:

Estimating appropriate sampling percentage is a critical task. It may take longer to gather statistics if the specified sampling percentage is too high. On the other hand you may get inaccurate statistics if the data is skewed and the specified sampling percentage is too low.

auto_sample_size:

You can set auto value for the estimate_percent parameter by using below command.

exec dbms_stats.gather_table_stats(null, 'MYTABLE', estimate_percent => dbms_stats.auto_sample_size);

Specifying auto sample size makes the system automatically determine appropriate sampling percentage. Auto sample size is more flexible sampling percentage and makes Oracle adjust the sample size when the data distribution changes. When auto is used Oracle picks a sample size where the statistics quality is good enough.

Auto Sampling in Oracle 11g:

Oracle 11g improves the behavior of auto sampling by generating deterministic statistics. The statistics generated by auto sampling in Oracle 11g are almost as accurate as 100% sampling but takes much less time than 100% sampling. To illustrate these merits, we compare the performance of using a fixed sampling percentage, auto sample size in Oracle 10g and auto sample size in Oracle 11g. Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   The Nuances of regular expressions in Oracle!!
   Tricky Oracle Recovery for missing Archive Log!!
   Beware of over-creating Indexes!!
   Efficient Recovery by Skipping Tablespaces!!