Lies, damned lies, and statistics Part 2
- by Maria Colgan
There was huge interest in our OOW session last year on Managing Optimizer Statistics. It seems statistics and the maintenance of them continues to baffle people. In order to help dispel the mysteries surround statistics management we have created a two part white paper series on Optimizer statistics.
Part one of this series was released in November last years and describes in detail, with worked examples, the different concepts of Optimizer statistics. Today we have published part two of the series, which focuses on the best practices for gathering statistics, and examines specific use cases including, the fears that surround histograms and statistics management of volatile tables like Global Temporary Tables.
Here is a quick look at the Introduction and the start of the paper. You can find the full paper here. Happy Reading!
Normal
0
false
false
false
EN-US
X-NONE
X-NONE
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
Introduction
The Oracle Optimizer examines all of the possible
plans for a SQL statement and picks the one with the lowest cost, where cost
represents the estimated resource usage for a given plan. In order for the Optimizer
to accurately determine the cost for an execution plan it must have information
about all of the objects (table and indexes) accessed in the SQL statement as
well as information about the system on which the SQL statement will be run.
This necessary information is commonly referred to
as Optimizer statistics. Understanding and managing Optimizer statistics is key
to optimal SQL execution. Knowing when and how to gather statistics in a timely
manner is critical to maintaining acceptable performance. This whitepaper is
the second of a two part series on Optimizer statistics. The first part of this
series, Understanding
Optimizer Statistics, focuses on the concepts of statistics and will be
referenced several times in this paper as a source of additional information. This
paper will discuss in detail, when and how to gather statistics for the most
common scenarios seen in an Oracle Database. The topics are
· How
to gather statistics
· When
to gather statistics
· Improving
the efficiency of gathering statistics
· When
not to gather statistics
· Gathering
other types of statistics
How to gather statistics
The preferred method for
gathering statistics in Oracle is to use the supplied automatic
statistics-gathering job.
Automatic statistics gathering
job
The job collects statistics
for all database objects, which are missing statistics or have stale statistics
by running an Oracle AutoTask task during a predefined maintenance window. Oracle
internally prioritizes the database objects that require statistics, so that
those objects, which most need updated statistics, are processed first. The
automatic statistics-gathering job uses the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure,
which uses the same default parameter values as the other DBMS_STATS.GATHER_*_STATS procedures.
The defaults are sufficient in most cases. However, it is occasionally necessary to change
the default value of one of the statistics gathering parameters, which can be accomplished
by using the DBMS_STATS.SET_*_PREF procedures.
Parameter values should be changed at the smallest scope possible, ideally on a
per-object bases.
You can find the full paper here. Happy Reading!
+Maria Colgan
