Understanding Optimizer Statistics With Oracle Database 12c Release 2 (White Paper)

Oracle White Paper
Understanding Optimizer Statistics With Oracle Database 12c Release 2

Introduction
When the Oracle database was first introduced, the decision of how to execute a SQL statement was determined by a Rule Based Optimizer (RBO). The Rule Based Optimizer, as the name implies, followed a set of rules to determine the execution plan for a SQL statement.

In Oracle Database 7, the Cost Based Optimizer (CBO) was introduced to deal with the enhanced functionality being added to the Oracle Database at this time, including parallel execution and partitioning, and to take the actual data content and distribution into account. The Cost Based 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. The lower the cost, the more efficient an execution plan is expected to be. In order for the Cost Based Optimizer to accurately determine the cost for an execution plan, it must have information about all of the objects (tables and indexes) accessed in the SQL statement, and 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 critical for achieving optimal SQL execution. This whitepaper is the first in a two part series on optimizer statistics and describes the core concepts of what statistics are and what types are statistics are used by the Oracle Optimizer. The second paper in the series (Best Practices for Gathering Optimizer Statistics with Oracle Database 12c) covers how to keep optimizer statistics up-to-date so that they accurately represent the data that’s stored in the database.

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-statistics-concepts-12c-1963871.pdf