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]

Modified26-SEP-2008TypeFAQStatusPUBLISHED PURPOSE=======This note is an introduction to a set of notes which helps DBA through examplesto 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 relativecost of execution plans. In Oracle 7, there was two methods to gather statistics:o ANALYZEo DBMS_UTILITYSince the Oracle Version 8.1.5, a single package has been provided to gatherstatistics:o DBMS_STATS packageIt is strongly recommended to use the DBMS_STATS package and all the differentfunctions that are defined by it to handle the collect of statistics.Using DBMS_STATS is the only way to access the new sampling functionalityprovided 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 OptimizerFor specific package related detail, consult the Oracle9i Supplied PL/SQLPackages and Types Reference documentationMoving 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 ExamplesNote:237538.1 How to Move from ANALYZE to DBMS_STATS on Partitioned Tables - Some ExamplesNote:237539.1 How to Move from ANALYZE to DBMS_STATS on Composite-Partitioned Tables - Some ExamplesNote: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 thelegacy ANALYZE command gathered statistics for other purposes. The followingstatistics 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_SUBPARTITIONSIf statistics unrelated to the cost based optimizer are required, then thesemust 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_BLOCKSRELATED DOCUMENTS=================Note.236935.1 Global statistics - DBMS_STATS versus ANALYZENote:237537.1 How to Move from ANALYZE to DBMS_STATS on Non-Partitioned Tables - Some ExamplesNote:237538.1 How to Move from ANALYZE to DBMS_STATS on Partitioned Tables - Some ExamplesNote:237539.1 How to Move from ANALYZE to DBMS_STATS on Sub-Partitioned Tables - Some ExamplesNote:237397.1 How to Move from ANALYZE (using DBMS_UTILITY) to DBMS_STATSNote:31412.1 SCRIPT - Select to show Optimizer Statistics for CBONote:210673.1 Different Values For EMPTY_BLOCKS and AVG_SPACE Using DBMS_STATS and ANALYZEBug: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 StatisticsBug:2222368 DBMS_UTILITY.ANALYZE_DATABASE ALSO ANALYZES SYSBug:1422285 DBMS_STATS.GATHER_DATABASE_STATISTICS COLLECTS STATISTICS FOR SYS OBJECTSBug: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

## Post a Comment