oracle statistics – what are collected – for the new oracle dba

you are a new oracle dba and you know that collecting statistics on tables and indexes are important for the sql statements to have a good plan.when the sql statements have a good plan then they can read or write to the database in an efficient way.Below article explains the difference between analyze and dbms_stats packages. Both these packages can be used to gather statistics.However from 10G onwards oracle recommends to use the dbms_stats package. The article also explains about the different things that are collected by oracle during statics gathering.

How to Move from ANALYZE to DBMS_STATS – Introduction [ID 237293.1]

	Modified 26-SEP-2008 Type FAQ Status PUBLISHED

PURPOSE

=======

This note is an introduction to a set of notes which helps DBA through examples

to move gathering statistics from ANALYZE or DBMS_UTILITY to DBMS_STATS.

SCOPE & APPLICATION

===================

This set of Notes is aimed at anyone wishing to gather statistics for use by the Cost Based Optimizer (CBO).

How to Move From ANALYZE/DBMS_UTILITY.ANALYZE_* to DBMS_STATS

=============================================================

Cost-Based optimization relies on accurate statistics to determine the relative

cost of execution plans. In Oracle 7, there was two methods to gather statistics:

        o ANALYZE

        o DBMS_UTILITY

Since the Oracle Version 8.1.5, a single package has been provided to gather

statistics:

        o DBMS_STATS package

It is strongly recommended to use the DBMS_STATS package and all the different

functions that are defined by it to handle the collect of statistics.

Using DBMS_STATS is the only way to access the new sampling functionality

provided in later releases since ANALYZE is not being enhanced.

 - Note:236935.1 Global statistics - DBMS_STATS versus ANALYZE

 - estimate_percent => dbms_stats.auto_sample_size

 - method_opt => 'FOR ALL COLUMNS SIZE AUTO', -

 - degree => DBMS_STATS.DEFAULT_DEGREE, -

 - options => 'GATHER AUTO', -

For an overview of sampling recommendations see:

Note:114671.1 Gathering Statistics for the Cost Based Optimizer

For specific package related detail, consult the Oracle9i Supplied PL/SQL

Packages and Types Reference documentation

Moving from Legacy sampling (ANALYZE and DBMS_UTILITY) to DBMS_STATS

--------------------------------------------------------------------

Examples of various sampling scenarios are provided in the following aricles:

Note:237537.1 How to Move from ANALYZE to DBMS_STATS on Non-Partitioned Tables - Some Examples

Note:237538.1 How to Move from ANALYZE to DBMS_STATS on Partitioned Tables - Some Examples

Note:237539.1 How to Move from ANALYZE to DBMS_STATS on Composite-Partitioned Tables - Some Examples

Note:237397.1 How to Move from ANALYZE (using DBMS_UTILITY) to DBMS_STATS:

Difference in statistics collection between ANALYZE and DBMS_STATS ?

--------------------------------------------------------------------

The DBMS_STATS package only gathers statistics for use by the CBO wheras the

legacy ANALYZE command gathered statistics for other purposes. The following

statistics are gathered:

 - Table statistics -

   ****************

 . Number of rows         : NUM_ROWS

 . Number of blocks       : BLOCKS

 . The Average row length : AVG_ROW_LEN

 . Sample size            : SAMPLE_SIZE

 . Last analyzed          : LAST_ANALYZED

 . Global statistics      : GLOBAL_STATS

 . User statistics        : USER_STATS

 . These are visible in the following Dictionary Views :

   - DBA_TABLES, DBA_TAB_PARTITIONS, DBA_TAB_SUBPARTITIONS

 - Column statistics -

   *****************

 . Number of distinct values (NDV) in column : NUM_DISTINCT

 . Number of null values in column           : NUM_NULLS

 . Average column length                     : AVG_COL_LEN

 . Number of Buckets                         : NUM_BUCKETS

 . Low Value                                 : LOW_VALUE

 . High Value                                : HIGH_VALUE

 . Sample size                               : SAMPLE_SIZE

 . Last analyzed                             : LAST_ANALYZED

 . Global statistics                         : GLOBAL_STATS

 . User statistics                           : USER_STATS

 . Data distribution                         : See note below

 . Standard Column statistics are visible in the following Dictionary Views :

   - DBA_TAB_COL_STATISTICS, DBA_PART_COL_STATISTICS, DBA_SUBPART_COL_STATISTICS

 . Column Data distribution statistics are stored separately in the DBA_*_HISTOGRAMS views:

   - DBA_HISTOGRAMS, DBA_PART_HISTOGRAMS, DBA_SUBPART_HISTOGRAMS

 - Index statistics -

   ****************

 . Number of leaf blocks                     : LEAF_BLOCKS

 . Number of Levels                          : BLEVEL

 . Clustering factor                         : CLUSTERING_FACTOR

 . Number of Distinct Keys                   : DISTINCT_KEYS

 . The Average Leaf Index Blocks per Key     : AVG_LEAF_BLOCKS_PER_KEY

 . The Average Data Blocks per Key           : AVG_DATA_BLOCKS_PER_KEY

 . Sample size                               : SAMPLE_SIZE

 . Last analyzed                             : LAST_ANALYZED

 . Global statistics                         : GLOBAL_STATS

 . User statistics                           : USER_STATS

 . These are visible in the following Dictionary Views :

   - DBA_INDEXES, DBA_IND_PARTITIONS, DBA_IND_SUBPARTITIONS

If statistics unrelated to the cost based optimizer are required, then these

must still be collected using the the ANALYZE command. These statistics include:

- Space Usage information :

 . EMPTY_BLOCKS,

 . AVG_SPACE,

 . CHAIN_CNT

- Information on freelist blocks

 . AVG_SPACE_FREELIST_BLOCKS,

 . NUM_FREELIST_BLOCKS

RELATED DOCUMENTS

=================

Note.236935.1 Global statistics - DBMS_STATS versus ANALYZE

Note:237537.1 How to Move from ANALYZE to DBMS_STATS on Non-Partitioned Tables - Some Examples

Note:237538.1 How to Move from ANALYZE to DBMS_STATS on Partitioned Tables - Some Examples

Note:237539.1 How to Move from ANALYZE to DBMS_STATS on Sub-Partitioned Tables - Some Examples

Note:237397.1 How to Move from ANALYZE (using DBMS_UTILITY) to DBMS_STATS

Note:31412.1 SCRIPT - Select to show Optimizer Statistics for CBO

Note:210673.1 Different Values For EMPTY_BLOCKS and AVG_SPACE Using DBMS_STATS and ANALYZE

Bug:1874139 STATISTICS (EMPTY_BLOCKS & AVG_SPACE) ARE DIFFERENT AFTER EXPORT/IMPORT:

Bug:1759544 DBMS_STATS PACKAGE REPORTS '0' FOR EMPTY_BLOCKS:

Bug:2932639 WRONG TABLE STATISTICS CALCULATED IN AUTO SEGMENT SPACE MANAGED TABLESPACE:

Note:35272.1  Is ANALYZE on the Data Dictionary Supported (TABLES OWNED BY SYS)?

Note:245051.1 Gathering Data Dictionary Statistics

Bug:2222368 DBMS_UTILITY.ANALYZE_DATABASE ALSO ANALYZES SYS

Bug:1422285 DBMS_STATS.GATHER_DATABASE_STATISTICS COLLECTS STATISTICS FOR SYS OBJECTS

Bug:2453682 DBMS_STATS.GATHER_SCHEMA_STATS DOES NOT GATHER STATISTICS FOR OBJECT TABLES

Related

Products

Oracle Database Products > Oracle Database > Oracle Database > Oracle Server – Enterprise Edition

Keywords

CBO; DBMS_STATS

Related

Categories