Skip to content

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

 

 

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.