Oracle Database - Optimizer Statistics

About

Optimizer statistic in Oracle

You can collect exact or estimated statistics about physical storage characteristics and data distribution in these schema objects by using the DBMS_STATS package.

Categories

Database Objects

System

  • System statistics: (of the operating System) during a regular workload (otherwise non appropriate values for the CPU costing for instance)
    • I/O performance and utilization
    • CPU performance and utilization

Others

  • Database
  • Fixed objects (dynamic performance tables)
  • Dictionary
  • Schema

What if

Bad statistics

For example, if a table has not been analyzed since it was created, and if it has less than DB_FILE_MULTIBLOCK_READ_COUNT blocks under the high water mark, then the optimizer thinks that the table is small and uses a full table scan. (Review the LAST_ANALYZED and BLOCKS columns in the ALL_TABLES table to examine the statistics)

No statistics

  • If no statistics are available when using query optimization, the optimizer will do dynamic sampling.

This may cause slower parse times so for best performance, the optimizer should have representative optimizer statistics.

  • If the optimizer uses the cost-based approach for a SQL statement, and if some tables accessed by the statement have no statistics, then the optimizer uses internal information, such as the number of data blocks allocated to these tables, to estimate other statistics for these tables.

Stale

statistics are stale when 10% or more of the rows in the table have changed since the last time statistics were gathered.

Management

List

Optimizer Statistics on Database Objects (tables, indexes, columns) are stored in the data dictionary. To view statistics in the data dictionary, query the appropriate data dictionary view (USER, ALL, or DBA). These DBA_* views include the following:

  • DBA_TABLES
  • DBA_OBJECT_TABLES
  • DBA_TAB_STATISTICS
  • DBA_TAB_COL_STATISTICS
  • DBA_TAB_COLS
  • DBA_COL_GROUP_COLUMNS

Collecting

Hint

GATHER_OPTIMIZER_STATISTICS

The GATHER_OPTIMIZER_STATISTICS hint instructs the optimizer to enable statistics gathering during the following types of bulk loads:

  • CREATE TABLE … AS SELECT
  • INSERT INTO … SELECT into an empty table using a direct-path insert
And then not on SELECT !

GATHER_DATABASE_STATS_JOB_PROC

The GATHER_DATABASE_STATS_JOB_PROC procedure collects statistics on database objects when:

  • the object has no previously gathered statistics
  • or the existing statistics are stale because the underlying object has been modified significantly (more than 10% of the rows).

The DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC:

  • is an internal procedure (You don't see it in the DBMS_STATS package)
  • it operates in a very similar fashion to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option. The primary difference is that the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure prioritizes the database objects that require statistics, so that those objects which most need updated statistics are processed first. This ensures that the most-needed statistics are gathered before the maintenance window closes.

Reference

Task Runner