X

Insights into Statistics, Query Optimization and the Oracle Optimizer

Lies, damned lies, and statistics Part 2

Maria Colgan
Master Product Manager

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!

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

Join the discussion

Comments ( 5 )
  • guest Tuesday, April 10, 2012

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


  • Uwe Hesse Tuesday, April 10, 2012

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


  • Raheel Syed Wednesday, April 11, 2012

    Very Informative. Thanks Maria


  • guest Saturday, April 28, 2012

    Hi,

    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.


  • Durga Wednesday, June 13, 2012

    Very Informative.Thanks Maria


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.