Lies, damned lies, and statistics Part 2

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!


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


How does CONCURRENT global preference work alongside DEGREE preference? Should I set DEGREE false when CONCURRENT is true?

Posted by guest on April 10, 2012 at 02:32 AM PDT #

Thank you for this nice collection of Best Practices! Very useful!

Posted by Uwe Hesse on April 10, 2012 at 02:45 AM PDT #

Very Informative. Thanks Maria

Posted by Raheel Syed on April 11, 2012 at 02:10 AM PDT #

Thanks Maria for this great works.
Can you explainto us the usuful of sql_profie and interaction with:
xact_matching_signature and force_matching_signature.
In other word: when we create sql_profile or baseline for one sql_id.
IS tthis depend of exact text body or depend on the signature of the text(SQL).

Thanks in advance.

Posted by guest on April 28, 2012 at 12:39 PM PDT #

Very Informative.Thanks Maria

Posted by Durga on June 13, 2012 at 03:24 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed

The Oracle Optimizer blog is written by members of the Optimizer development team. The goal of this blog is to provide an insight into the workings of the Optimizer and the statistics it relies on. The views expressed on this blog are our own and do not necessarily reflect the views of Oracle and its affiliates. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect ours.


« June 2016