How to Move from ANALYZE to DBMS_STATS - Introduction

DBMS_STATSを使うことにより、EMPTY_BLOCKSを0にすることも可能です。

DBMS_STATSはDBA_TABLESの中のEMPTY_BLOCKS列を0にすることができるにもかかわらず，
MetaLinkの中にて記述された内容によりますと，正確にEMPTY_BLOCKSの統計情報を収集
する方法はANALYZE文との一つしかありません。

次に引用されているのはMetaLink(Note#237293.1)の一部です：
---------------------------------------------------------------------------------
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
---------------------------------------------------------------------------------

附：Note#237293.1


Subject: How to Move from ANALYZE to DBMS_STATS - Introduction
Doc ID: Note:237293.1 Type: FAQ
Last Revision Date: 16-FEB-2007 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