2 WHITE PAPER / Best Practices for Gathering Optimizer Statistics with Oracle Database 19c
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 part one of this series, Understanding
Optimizer Statistics with Oracle Database 19c, 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 quality of statistics
Gathering statistics more quickly
When not to gather statistics
Gathering other types of statistics
This document in any form, software or printed matter, contains proprietary information that is the
exclusive property of Oracle. Your access to and use of this confidential material is subject to the
terms and conditions of your Oracle software license and service agreement, which has been
executed and with which you agree to comply. This document and information contained herein may
not be disclosed, copied, reproduced or distributed to anyone outside Oracle without prior written
consent of Oracle. This document is not part of your license agreement nor can it be incorporated
into any contractual agreement with Oracle or its subsidiaries or affiliates.
This document is for informational purposes only and is intended solely to assist you in planning for
the implementation and upgrade of the product features described. It is not a commitment to deliver
any material, code, or functionality, and should not be relied upon in making purchasing decisions.
The development, release, and timing of any features or functionality described in this document
remains at the sole discretion of Oracle.
Due to the nature of the product architecture, it may not be possible to safely include all features
described in this document without risking significant destabilization of the code.