使用DBMS_STATS来收集统计信息

overview Oracles cost-based optimizer (COB) uses statistics to calculate the selectivity (the fraction of rows in a table that the SQL statements predicate chooses) of predicates and to estimate the cost of each execution plan. The COB wil

overview

Oracle's cost-based optimizer (COB) uses statistics to calculate the selectivity (the fraction of rows in a table that the SQL statement's predicate chooses) of predicates and to estimate the "cost" of each execution plan. The COB will use the selectivity of a predicate to estimate the cost of a particular access method and to determin the optimal join order

ORACLE COB使用统计信息来计算查询谓词的选择性，并借此评估执行计划的成本。然后COB会使用谓词的选择性来评估特定的访问路径的成本并确定最优的连接顺序。

statistics are used to quantify the data distribution and storage characteristics of tables, columns, indexes and partitions. The COB uses these statistics to estimate how much I/O and memory are required to execute a SQL statement using a particular execution plan. Statistics are stored in the data dictionary, and they can be exported from one database and imported into another. Situations in where you would want to perform this, might be to transfer production statistics to a test system to simulate the real environment, even though the test system may only have small samples of the data。

统计信息被用来量化表、列、索引和分区的数据分布特征和存储特征。COB使用统计信息来评估SQL语句采用某特定执行计划时的内存和输入输出量。统计信息存储在数据字典视图中，它们可以被导出和导入，例如，我们可以将生产环境的统计信息导入到测试环境中以便模拟真环境，即使测试环境具有较小的数据采样。

In order to give the Oracle cost-based optimizer the most up-to-date information about schema objects (and the best chance for choosing a good execution plan) all application tables and indexes to be accessed must be analyzed. New statistics should be gathered on schema objects that are out of date. After loading or deleting large amounts of data would obviously change the number of rows. Other changes like updating a large amount of rows would not effect the number of rows, but may effect the average row length.

为了给ORACLE COB提供最新的关于模式对象的信息（从而可以选择最优执行计划），所有被访问的应用表和索引都需要被分析。如果对象的统计信息已经过时，我们需要更新统计信息，例如，在进行大量的装载或者删除数据后，或者对表数据进行了大量的更新操作。

Statistics can be generated with the ANALYZE statement or with the package DBMS_STATS (introduced in Oracle8i). The DBMS_STATS package is great for DBA's in managing database statistics only for use by the COB. The package itself allows the DBA to create, modify, view and delete statistics from a standard, well-defined set of package procedures. The statistics can be gathered on tables, indexes, columns, partitions and schemas, but note that it does not generate statistics for clusters.

统计信息可以通过ANALYZE命令或者DBMS_STATS包来收集。在COB模式下，DBMS_STATS包是DBA管理统计信息的有力工具。DBMS_STATS包允许管理员以调用过程的方式创建，编辑，查看和删除统计信息。它可以收集表、索引、列、分区和模式的统计信息，但是它不可以生成cluster的统计信息；

DBMS_STATS provides a mechanism for you to view and modify optimizer statistics gathered for database objects.The statistics can reside in two different locations:

The dictionary.A table created in the user's schema for this purpose

dbms_stats包为我们提供了查看和编辑统计信息的机制。统计信息可以存储在2个不同的位置：数据字典视图和用户自定义的表中。

Only statistics stored in the dictionary itself have an impact on the cost-based optimizer.

When you generate statistics for a table, column, or index, if the data dictionary already contains statistics for the object, then Oracle updates the existing statistics. Oracle also invalidates any currently parsed SQL statements that access the object.

The next time such a statement executes, the optimizer automatically chooses a new execution plan based on the new statistics. Distributed statements issued on remote databases that access the analyzed objects use the new statistics the next time Oracle parses them.

When you associate a statistics type with a column or domain index, Oracle calls the statistics collection method in the statistics type if you analyze the column or domain index.

只有存储在字典视图中的统计信息，才会被优化器使用。

当我们收集表、列或者索引的统计信息时，如果数据字典中已经包含有统计信息，oracle会将已有信息进行更新。同时oracle也会使当前解析的与更新对象相关的sql语句无效，以便可以使用信息的统计信息。在远程主机中执行的分布式语句，则在oracle下次解析时才会使用心得统计信息。

当我们将某列或者域索引与某一统计类别管理时，oracle会在分析该列或者域索引是调用该统计类别下的统计收集方法。

missing statistics

When statistics do not exist on schema objects, the optimizer uses the following default values.
当统计信息不存在是，oracle会使用如下的默认统计信息。

Tables
Statistic	Default Value Used by Optimizer
Cardinality	100 rows
Avg. row len	20 bytes
No. of blocks	100
Remote cardinality	2000 rows
Remote average row length	100 bytes
Indexes
Statistic	Default Value Used by Optimizer
Levels	1
Leaf blocks	25
Leaf blocks/key	1
Data blocks/key	1
Distinct keys	100
Clustering factor	800 (8*no. of blocks)

Analyze vs DBMS_STATS

The following is a quick overview of the two.

Analyze The only method available for collecting statistics in Oracle 8.0 and lower.(ORACLE 8之前仅有的统计信息收集方式)ANALYZE can only run serially（只可以串行执行）.ANALYZE cannot overwrite or delete certain types of statistics that where generated by DBMS_STATS（不可以覆盖DBMS_STATS生成的部分统计信息）.ANALYZE calculates global statistics for partitioned tables and indexes instead of gathering them directly. This can lead to inaccuracies for some statistics, such as the number of distinct values.（ANALYZE针对分区表和索引计算全局统计信息，而不是直接针对整张表进行统计分析，这可能造成不正确的统计信息，例如distinct value的取值） For partitioned tables and indexes, ANALYZE gathers statistics for the individual partitions and then calculates the global statistics from the partition statistics.（对于分区表，ANALYZE收集每个分区的统计信息，然后根据各个分区的信息计算出全局统计信息）For composite partitioning, ANALYZE gathers statistics for the subpartitions and then calculates the partition statistics and global statistics from the subpartition statistics.（对于组合分区表，ANALYZE收集每个子分区的统计信息，然后据此计算各个分区和全局的统计信息） ANALYZE can gather additional information that is not used by the optimizer, such as information about chained rows and the structural integrity of indexes, tables, and clusters. DBMS_STATS does not gather this information.（ANALYZE 会收集某些与优化器无关的信息，例如chainrow，索引、表和cluster的结构完整性，DBMS_STATS不会收集这些信息）No easy way of knowing which tables or how much data within the tables have changed. The DBA would generally re-analyze all of their tables on a semi-regular basis.（没有办法知道哪些表或者表中的哪些数据发生了变化，dba通常会依据一定的规则重新收集所有标的统计信息）

DBMS_STATS Only available for Oracle 8i and higher.(在oracle8之后才可用)Statistics can be generated to a statistics table and can then be imported or exported between databases and re-loaded into the data dictionary at any time. This allows the DBA to experiment with various statistics.（统计信息可以被导出导入，方便了DBA的使用）DBMS_STATS routines have the option to run via parallel query or operate serially（可以并行或者串行执行）.Can gather statistics for sub-partitions or partitions.（可以收集分区和子分区的统计信息）Certain DDL commands (ie. create index) automatically generate statistics, therefore eliminating the need to generate statistics explicitly after DDL command.（某些DDL语句可以自动收集统计信息）DBMS_STATS does not generate information about chained rows and the structural integrity of segments.（不会收集chainrow和段结构有效性的统计信息）The DBA can set a particular table, a whole schema or the entire database to be automatically monitored when a modification occurs. When enabled, any change (insert, update, delete, direct load, truncate, etc.) that occurs on a table will be tracked in the SGA. This information is incorporated into the data dictionary by the SMON process at a pre-set interval (every 3 hours in Oracle 8.1.x, and every 15 minutes in Oracle 9i). The information collected by this monitoring can be seen in the DBA_TAB_MODIFICATIONS view. Oracle 9i introduced a new function in the DBMS_STATS package called: FLUSH_DATABASE_MONITORING_INFO. The DBA can make use of this function to flush the monitored table data more frequently. Oracle 9i will also automatically call this procedure prior to executing DBMS_STATS for statistics gathering purposes. Note that this function is not included with Oracle 8i.（使用DBMS_STATS，DBA可以指定某张表，或者整个用户，或者这个数据库自动监视数据的变化。当发生任何变化时（增删改查，装载，truncate等），oracle会在sga中自动记录数据的变化，随后SMON进程会将这些变化与已有的统计信息进行合并（oracle8每3个小时合并一次，oracle9之后没15分钟合并一次）。我们可以通过DBA_TAB_MODIFICATIONS视图来查看已经发生的变化。我们也可以直接使用9i引入的新函数FLUSH_DATABASE_MONITORING_INFO来将信息手动合并到已有统计信息中。在9i中，oracle会在每次调用DBMS_STATS时，首先调用FLASH_DATABASE_MONITORING_INFO函数。）DBMS_STATS provides a more efficient, scalable solution for statistics gathering and should be used over the traditional ANALYZE command which does not support features such as parallelism and stale statistics collection.（DBMS_STAS提供了一种更高效，可伸缩的信息统计方式，我们优先使用DBMS_STATS,而不使用ANNLYZE）Use of table monitoring in conjunction with DBMS_STATS stale object statistics generation is highly recommended for environments with large, random and/or sporadic data changes. These features allow the database to more efficiently determine which tables should be re-analyzed versus the DBA having to force statistics collection for all tables. Including those that have not changed enough to merit a re-scan)（优先使用dbms_stats）

What gets collected?

Table Statistics

Oracle collects the following statistics for a table. Statistics marked with an asterisk are always computed exactly. Table statistics, including the status of domain indexes, appear in the data dictionary views USER_TABLES, ALL_TABLES, and DBA_TABLES in the columns shown in parentheses.

oracle可以为表收集如下的统计信息，部分统计信息始终是准确的（带*）。表的统计信息（包括domain index）都可以在 USER_TABLES, ALL_TABLES, and DBA_TABLES等视图的如下字段中可以查看到。

Number of rows (NUM_ROWS)记录数量

 

* Number of data blocks below the high water mark (that is, the number of data blocks that have been formatted to receive data, regardless whether they currently contain data or are empty) (BLOCKS)位于高水位线之下的数据块数量（在mssm中，oracle通过freelist管理段，当段空间不足时，oracle会分配新的数据块到高水位线下，并进行格式化后放到freelist上以备后用，此时高水位线下的块都是格式化的，但可能并没有被使用。在ASSM下，段的管理模式发生了变化，当空间不足时，oracle会分配数据块到高水位线下，但是并不会立即格式化，而是在使用时才格式化，此时引入了另一个概念low 高水位线，lowhwm下的块都是格式化的，lowhwm和hwm之间的数据库可能是格式化也可能并未格式化，当lowhwm和hwm之间的数据块全部格式化时，lowhwm上移到hwm的位置），

 

* Number of data blocks allocated to the table that have never been used (EMPTY_BLOCKS)空闲数据块的数量,HWM之上的数据块

 

Average available free space in each data block in bytes (AVG_SPACE)平均每个数据上的空闲空间，blocks+empty_blocks

 

Number of chained rows. [Not collected by DBMS_STATS] (CHAIN_COUNT)发生chainrow的记录数量

 

Average row length, including the row's overhead, in bytes (AVG_ROW_LEN)平均每行的长度，包含overhead信息

Index Statistics

 

Oracle collects the following statistics for an index. Statistics marked with an asterisk are always computed exactly. For conventional indexes, the statistics appear in the data dictionary views USER_INDEXES, ALL_INDEXES, and DBA_INDEXES in the columns in parentheses.（带*为准确值）

oracle收集如下的索引统计信息。对于常规索引，可以在视图USER_INDEXES, ALL_INDEXES, and DBA_INDEXES中查看到如下的统计信息。

* Depth of the index from its root block to its leaf blocks (BLEVEL)（从0开始）

 

Number of leaf blocks (LEAF_BLOCKS)（叶子块的数量）

 

Number of distinct index values (DISTINCT_KEYS)

 

Average number of leaf blocks per index value (AVG_LEAF_BLOCKS_PER_KEY)（每个索引值存在于几个叶子块，通常为1）

 

Average number of data blocks per index value (for an index on a table) (AVG_DATA_BLOCKS_PER_KEY)（每个索引值对应的记录存在于几个数据块，通常为1）

 

Clustering factor (how well ordered the rows are about the indexed values) (CLUSTERING_FACTOR)（聚簇因子）

Where are the statistics stored?

Statistics are stored into the Oracle Data Dictionary, in tables owned by SYS. Views are created on these tables to retrieve data more easily.

These views are prefixed with DBA_ or ALL_ or USER_. For ease of reading, we will use DBA_% views, but ALL_% views or USER_% views could be used as well.

统计信息存储在数据字典中，在sys用户下的表内。通过视图我们可以非常方便的从这些表中获取信息。视图通常以DBA_ USER_ ALL_开始。为了简便，我们以DBA_开头的视图为例。

Conventions Used

- Statistics available only since 8.0.X rdbms release         : (*)
- Statistics available only since 8.1.X rdbms release         : (**)
- Statistics not available at partition or subpartition level : (G)
- Statistics not available at subpartition level              : (GP)

Table level statistics can be retrieved from:

DBA_ALL_TABLES - (8.X onwards)DBA_OBJECT_TABLES - (8.X onwardsDBA_TABLES - (all versions)DBA_TAB_PARTITIONS - (8.X onwards)DBA_TAB_SUBPARTITIONS - (8.1 onwards)

Columns to look at are:

  NUM_ROWS                         :<strong> Number of rows (always exact even when computed 
                   			 with ESTIMATE method) </strong>
  BLOCKS                           : Number of blocks which have been used even  
                                     if they are empty due to delete statements 
  EMPTY_BLOCKS                     : Number of empty blocks (these blocks have  
                                     never been used) 
  AVG_SPACE                        : <strong>Average amount of FREE space in bytes in blocks  
                                     allocated to the table : Blocks + Empty Blocks</strong> 
  CHAIN_CNT                        : Number of chained or migrated rows     
  AVG_ROW_LEN                      : Average length of rows in bytes 
  AVG_SPACE_FREELIST_BLOCKS (*)(G) : Average free space of blocks in the freelist 
  NUM_FREELIST_BLOCKS       (*)(G) : Number of blocks in the freelist 
  SAMPLE_SIZE                      : Sample defined in ESTIMATE method (0 if COMPUTE) 
  LAST_ANALYZED                    : Timestamp of last analysis 
  GLOBAL_STATS             (**)    : <strong>For partitioned tables, YES means statistics  
                                     are collected for the TABLE as a whole 
                                     NO means statistics are estimated from statistics  
                                     on underlying table partitions or subpartitions </strong>
  USER_STATS               (**)    : YES if statistics entered directly by the user

Index level statistics can be retrieved from:

DBA_INDEXES - (all versions )DBA_IND_PARTITIONS - (8.X onwards)DBA_IND_SUBPARTITIONS - (8.1 onwards )

Columns to look at are:

  BLEVEL                       : B*Tree level : depth of the index from its root  
                                 block to its leaf blocks (从0开始)
  LEAF_BLOCKS                  : Number of leaf blocks 
  DISTINCT_KEYS                : Number of distinct keys 
  AVG_LEAF_BLOCKS_PER_KEY      : Average number of leaf blocks in which each 
                                 distinct key appears (1 for a UNIQUE index) 
  AVG_DATA_BLOCKS_PER_KEY      : Average number of data blocks in the table that  
                                 are pointed to by a distinct key 
  CLUSTERING_FACTOR            : - if near the number of blocks, then the table is  
                                   ordered : index entries in a single leaf block  
                                   tend to point to rows in same data block 
                                 - if near the number of rows, the table is  
                                   randomly ordered : index entries in a single  
                                   leaf block are unlikely to point to rows in  
                                   same data block 
  SAMPLE_SIZE                  : Sample defined in ESTIMATE method (0 if COMPUTE) 
  LAST_ANALYZED                : Timestamp of last analysis 
  GLOBAL_STATS            (**) : For partitioned indexes, YES means statistics  
                                 are collected for the INDEX as a whole 
                                 NO means statistics are estimated from statistics  
                                 on underlying index partitions or subpartitions 
  USER_STATS              (**) : YES if statistics entered directly by the user 
  PCT_DIRECT_ACCESS   (**)(GP) : For secondary indexes on IOTs, percentage of  
                                 rows with VALID guess（可以通过alter index index_name update block references来更新）

Column level statistics can be retrieved from:

DBA_TAB_COLUMNS - (all versions)DBA_TAB_COL_STATISTICS - (Version 8.X onwards)DBA_PART_COL_STATISTICS - (Version 8.X onwards)DBA_SUBPART_COL_STATISTICS - (Version 8.1 onwards)

The last three views extract statistics data from DBA_TAB_COLUMNS.（后三个视图是从DBA_TAB_COLUMNS获取数据）

Columns to look at are:

  NUM_DISTINCT                 : Number of distinct values 
  LOW_VALUE                    : Lowest value  
  LOW_VALUE                    : Highest value  
  DENSITY                      : Density 
  NUM_NULLS                    : Number of columns having a NULL value 
  AVG_COL_LEN                  : Average length in bytes 
  NUM_BUCKETS                  : Number of buckets in histogram for the column    
  SAMPLE_SIZE                  : Sample defined in ESTIMATE method (0 if COMPUTE) 
  LAST_ANALYZED                : Timestamp of last analysis 
  (**)GLOBAL_STATS             : For partitioned tables, YES means statistics  
                                 are collected for the TABLE as a whole 
                                 NO means statistics are estimated from statistics 
                                 on underlying table partitions or subpartitions 
  (**)USER_STATS               : YES if statistics entered directly by the user

Compute statistics vs. Estimate statistics

Both computed and estimated statistics are used by the Oracle optimizer to choose the execution plan for SQL statements that access analyzed objects. These statistics may also be useful to application developers who write such statements.

无论是采用compute还是采用estimat的方式计算统计信息，优化器都会根据这些信息来选择执行计划。程序员也可以根据这些统计信息来编写sql语句。

COMPUTE STATISTICS

COMPUTE STATISTICS instructs Oracle to compute exact statistics about the analyzed object and store them in the data dictionary.

When computing statistics, an entire object is scanned to gather data about the object. This data is used by Oracle to compute exact statistics about the object. Slight variances throughout the object are accounted for in these computed statistics. Because an entire object is scanned to gather information for computed statistics, the larger the size of an object, the more work that is required to gather the necessary information.

To perform an exact computation, Oracle requires enough space to perform a scan and sort of the table. If there is not enough space in memory, then temporary space may be required. For estimations, Oracle requires enough space to perform a scan and sort of only the rows in the requested sample of the table. For indexes, computation does not take up as much time or space, so it is best to perform a full computation.

Some statistics are always computed exactly, such as the number of data blocks currently containing data in a table or the depth of an index from its root block to its leaf blocks.

Use estimation for tables and clusters rather than computation, unless you need exact values. Because estimation rarely sorts, it is often much faster than computation, especially for large tables.

当COMPUTE STATISTICS时，oracle会精确计算被分析对象的统计信息，并将其存储在数据字典中。oracle会扫描整个对象来获取数据，并根据这些数据计算统计信息。对于这种方式，基本是轻微的变化也会被计算在内。因为整个对象都会被扫描，因此对象越大就会需要越多的工作量来完成统计。

为了完成精确统计，oracle需要足够的空间来执行扫描和排序作业。如果在内存中不存在足够的空间，就会占用磁盘的临时空间。对于estimation方式，oracle仅仅需要扫描和排序所采样的内容。如果我们统计的对象是索引，computation方式不会占用太多的时间和空间，因此对于索引我们最好采用compute方式。

某些统计信息总是精确计算的，例如表所占用的数据块数量和索引的深度。

对于表和聚簇，我们建议使用estimation的方式，除非真的需要精确的统计信息。因此estatimation方式通常不会发生排序，速度更快，尤其在分析大表时。

ESTIMATE STATISTICS

ESTIMATE STATISTICS instructs Oracle to estimate statistics about the analyzed object and stores them in the data dictionary.

When estimating statistics, Oracle gathers representative information from portions of an object. This subset of information provides reasonable, estimated statistics about the object. The accuracy of estimated statistics depends upon how representative the sampling used by Oracle is. Only parts of an object are scanned to gather information for estimated statistics, so an object can be analyzed quickly. You can optionally specify the number or percentage of rows that Oracle should use in making the estimate.

estimate statistics 使得oracle评估待分析对象的统计信息并将它们存储在数据字典中。当评估统计信息时，oracle在待分析对象的部分区间内收集信息。这部分信息为分析对象提供了足够的内容。estimate方式的准确程度主要依赖于oracle是如何采样的。由于只有部分内容被扫描，因此速度更快。我们可以指定oracle采样的百分比。

To estimate statistics, Oracle selects a random sample of data. You can specify the sampling percentage and whether sampling should be based on rows or blocks.

对于estimate方式，oracle会随机采样数据。我们可以指定采样的百分比，也可以指定是根据记录还是根据块来采样。

Row sampling reads rows without regard to their physical placement on disk. This provides the most random data for estimates, but it can result in reading more data than necessary. For example, in the worst case a row sample might select one row from each block, requiring a full scan of the table or index.基于记录的采用不会考虑记录的物理存储位置。这种方式提供了更好的随机性，但是可能会造成读取更多的数据。在最坏的情况下，oracle可能会在每个数据块中读取一条记录，从而会全表扫描表或者索引Block sampling reads a random sample of blocks and uses all of the rows in those blocks for estimates. This reduces the amount of I/O activity for a given sample size, but it can reduce the randomness of the sample if rows are not randomly distributed on disk. Block sampling is not available for index statistics.基于块的采样会随机读取数据块，然后利用数据块中的所有记录来进行分析统计工作。这无疑减少了输入输出的数量，但是如果记录在块内的分布不是随机的，这种方式会影响采样的随机性。对于索引，基于块的采样方式是不可用的。

Notes on estimating statistics

The default estimate of the analyze command reads the first approx 1064 rows of the table so the results often leave a lot to be desired.默认情况下，oracle会读取表中的前1064条记录来作为采样数据。

The general consensus is that the default value of 1064 is not sufficient for accurate statistics when dealing with tables of any size. Many claims have shown that estimating statistics on 30 percent produces very accurate results. I personally have been running estimate 35 percent. This seems to produce very accurate numbers. It also saves a lot of time over full scans.通常情况下，默认采样1064条记录是不充分的。多数人认为30%的采样会产生比较准确的结果。我个人常常将采样比例设置为35%

Note that if an estimate does 50% or more of a table Oracle converts the estimate to a full compute statistics.如果采样比超过50%，oracle会将其转换为full compute statiistics

DBMS_STATS functions and variable definitions

Most of the DBMS_STATS procedures include the three parameters statown, stattab, and statid. These parameters allow you to store statistics in your own tables (outside of the dictionary), which does not affect the optimizer. Therefore, you can maintain and experiment with sets of statistics.

大部分DBMS_STAT过程包含三个参数STATOWN,STATTAB和statid。这些参数允许我们将统计信息存放到自己的表中，这些统计信息不回影响优化器。因此，我们可以维护和测试统计信息。

The stattab parameter specifies the name of a table in which to hold statistics, and it is assumed that it resides in the same schema as the object for which statistics are collected (unless the statown parameter is specified). Users may create multiple tables with different stattabidentifiers to hold separate sets of statistics.stattab参数规定了保存统计信息的表明，通常情况下，如果没有指定statown参数，oracle以被统计对象所在的模式用户为stattab的拥有者。我们可以使用不同的stattab来分别存储不同的统计信息。

Additionally, users can maintain different sets of statistics within a single stattab by using the statid parameter, which can help avoid cluttering the user's schema.

灵位，我们也可以指定statid参数，从而在相同的stattab中存储不同的统计信息，这样可以使用户模式显得井井有条。

For all of the SET or GET procedures, if stattab is not provided (i.e., NULL), then the operation works directly on the dictionary statistics; therefore, users do not need to create these statistics tables if they only plan to modify the dictionary directly. However, if stattab is not NULL, then the SET or GET operation works on the specified user statistics table, and not the dictionary.

对于所有的set和get过程，如果我们没有指定stattab，oracle会将统计信息写入数据字典，如果指定了stattab，orcle只会将统计信息写入用户自定义表，而不会更新数据字典。

Create Stats Table

DBMS_STATS.CREATE_STAT_TABLE (
  ownname  VARCHAR2, 
  stattab  VARCHAR2,
  tblspace VARCHAR2 DEFAULT NULL);

ownname : Name of the schema.

stattab : Name of the table to create. This value should be passed as the stattab parameter to other procedures when the user does not want to modify the dictionary statistics directly. tblspace : Tablespace in which to create the stat tables. If none is specified, then they are created in the user's default tablespace.

Drop Stats Table

DBMS_STATS.drop_stat_table (
  ownname VARCHAR2, 
  stattab VARCHAR2);

ownname : Name of the schema.

stattab : User stat table identifier.

Gather Schema Stats (本人在测试过程中，即便指定了stattab，该过程依然更新了数据字典)

DBMS_STATS.gather_schema_stats (
  ownname          VARCHAR2,
  estimate_percent NUMBER   DEFAULT NULL, 
  block_sample     BOOLEAN  DEFAULT FALSE,
  method_opt       VARCHAR2 DEFAULT &#39;FOR ALL COLUMNS SIZE 1&#39;,<strong>（size 1 指在该列上不创建histogram，如果该值大于1，则创建histogram）</strong>
  degree           NUMBER   DEFAULT NULL,
  granularity      VARCHAR2 DEFAULT &#39;DEFAULT&#39;, 
  cascade          BOOLEAN  DEFAULT FALSE,
  stattab          VARCHAR2 DEFAULT NULL, 
  statid           VARCHAR2 DEFAULT NULL,
  options          VARCHAR2 DEFAULT &#39;GATHER&#39;, 
  objlist     OUT  ObjectTab,
  statown          VARCHAR2 DEFAULT NULL);

ownname : Schema to analyze (NULL means current schema).

estimate_percent : Percentage of rows to estimate (NULL means compute): The valid range is [0.000001,100).

block_sample : Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics.

method_opt : Method options of the following format (the phrase 'SIZE 1' is required to ensure gathering statistics in parallel and for use with the phrase hidden):

FOR ALL [INDEXED | HIDDEN] COLUMNS [SIZE integer]

This value is passed to all of the individual tables.

degree : Degree of parallelism (NULL means use table default value).

granularity : Granularity of statistics to collect (only pertinent if the table is partitioned).

DEFAULT: Gather global- and partition-level statistics.SUBPARTITION: Gather subpartition-level statistics.PARTITION: Gather partition-level statistics.GLOBAL: Gather global statistics.ALL: Gather all (subpartition, partition, and global) statistics.

cascade : Gather statistics on the indexes as well.

Index statistics gathering is not parallelized. Using this option is equivalent to running the gather_index_stats procedure on each of the indexes in the schema in addition to gathering table and column statistics.

stattab : User stat table identifier describing where to save the current statistics.

statid : Identifier (optional) to associate with these statistics within stattab.

options : Further specification of which objects to gather statistics for:

GATHER: Gather statistics on all objects in the schema.GATHER STALE: Gather statistics on stale objects as determined by looking at the *_tab_modifications views. Also, return a list of objects found to be stale.GATHER EMPTY: Gather statistics on objects which currently have no statistics. also, return a list of objects found to have no statistics.LIST STALE: Return list of stale objects as determined by looking at the *_tab_modifications views.LIST EMPTY: Return list of objects which currently have no statistics.

objlist : List of objects found to be stale or empty.

statown : Schema containing stattab (if different than ownname).

Export Schema Stats(从数据字典导出到用户表）

DBMS_STATS.export_schema_stats (
  ownname VARCHAR2,
  stattab VARCHAR2, 
  statid  VARCHAR2 DEFAULT NULL,
  statown VARCHAR2 DEFAULT NULL);

ownname : Name of the schema.

stattab : User stat table identifier describing where to store the statistics.

statid : Identifier (optional) to associate with these statistics within stattab.

statown : Schema containing stattab (if different than ownname).

Import Schema Stats（从用户表导入到数据字典）

DBMS_STATS.import_schema_stats (
  ownname VARCHAR2,
  stattab VARCHAR2, 
  statid  VARCHAR2 DEFAULT NULL,
  statown VARCHAR2 DEFAULT NULL);

ownname : Name of the schema.

stattab : User stat table identifier describing from where to retrieve the statistics.

statid : Identifier (optional) to associate with these statistics within stattab.

statown : Schema containing stattab (if different than ownname).

Delete Schema Stats

DBMS_STATS.delete_schema_stats (
  ownname VARCHAR2, 
  stattab VARCHAR2 DEFAULT NULL,
  statid  VARCHAR2 DEFAULT NULL,
  statown VARCHAR2 DEFAULT NULL);

ownname : Name of the schema.

stattab : User stat table identifier describing from where to delete the statistics. If stattab is NULL, then the statistics are deleted directly in the dictionary.

statid : Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL).

statown : Schema containing stattab (if different than ownname).

Set Table Stats

DBMS_STATS.set_table_stats (
  ownname  VARCHAR2, 
  tabname  VARCHAR2, 
  partname VARCHAR2 DEFAULT NULL,
  stattab  VARCHAR2 DEFAULT NULL, 
  statid   VARCHAR2 DEFAULT NULL,
  numrows  NUMBER   DEFAULT NULL, 
  numblks  NUMBER   DEFAULT NULL,
  avgrlen  NUMBER   DEFAULT NULL, 
  flags    NUMBER   DEFAULT NULL,
  statown  VARCHAR2 DEFAULT NULL);

ownname : Name of the schema.

tabname : Name of the table.

partname : Name of the table partition in which to store the statistics. If the table is partitioned and partname is NULL, then the statistics are stored at the global table level.

stattab : User stat table identifier describing where to store the statistics. If stattab is NULL, then the statistics are stored directly in the dictionary.

statid : Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL).

numrows : Number of rows in the table (partition).

numblks : Number of blocks the table (partition) occupies.

avgrlen : Average row length for the table (partition).

flags : For internal Oracle use (should be left as NULL).

statown : Schema containing stattab (if different than ownname).

Get Table Stats

DBMS_STATS.get_table_stats (
  ownname     VARCHAR2, 
  tabname     VARCHAR2, 
  partname    VARCHAR2 DEFAULT NULL,
  stattab     VARCHAR2 DEFAULT NULL, 
  statid      VARCHAR2 DEFAULT NULL,
  numrows OUT NUMBER, 
  numblks OUT NUMBER,
  avgrlen OUT NUMBER,
  statown     VARCHAR2 DEFAULT NULL);

ownname : Name of the schema.

tabname : Name of the table to which this column belongs.

partname : Name of the table partition from which to get the statistics. If the table is partitioned and if partname is NULL, then the statistics are retrieved from the global table level.

stattab : User stat table identifier describing from where to retrieve the statistics. If stattab is NULL, then the statistics are retrieved directly from the dictionary.

statid : Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL).

numrows : Number of rows in the table (partition).

numblks : Number of blocks the table (partition) occupies.

avgrlen : Average row length for the table (partition).

statown : Schema containing stattab (if different than ownname).

Get Index Stats

DBMS_STATS.GET_INDEX_STATS (
  ownname      VARCHAR2, 
  indname      VARCHAR2,
  partname     VARCHAR2 DEFAULT NULL,
  stattab      VARCHAR2 DEFAULT NULL, 
  statid       VARCHAR2 DEFAULT NULL,
  numrows  OUT NUMBER, 
  numlblks OUT NUMBER,
  numdist  OUT NUMBER, 
  avglblk  OUT NUMBER,
  avgdblk  OUT NUMBER, 
  clstfct  OUT NUMBER,
  indlevel OUT NUMBER,
  statown      VARCHAR2 DEFAULT NULL);

ownname : Name of the schema.

indname : Name of the index.

partname : Name of the index partition for which to get the statistics. If the index is partitioned and if partname is NULL, then the statistics are retrieved for the global index level.

stattab : User stat table identifier describing from where to retrieve the statistics. If stattab is NULL, then the statistics are retrieved directly from the dictionary.

statid : Identifier (optional) to associate with these statistics within stattab (Only pertinent if stattab is not NULL).

numrows : Number of rows in the index (partition).

numlblks : Number of leaf blocks in the index (partition).

numdist : Number of distinct keys in the index (partition).

avglblk : Average integral number of leaf blocks in which each distinct key appears for this index (partition).

avgdblk : Average integral number of data blocks in the table pointed to by a distinct key for this index (partition).

clstfct : Clustering factor for the index (partition).

indlevel : Height of the index (partition).

statown : Schema containing stattab (if different than ownname).

Automated table monitoring and stale statistics gathering example

在oracle10g中 statistics_level 初始化参数作为一个全局设置影响对表的监控操作，本文下面涉及的alter_schema_tab_monitoring已经不再被使用，但是到我们调用这些过程时，不会报错，只是没有任何事情发生。

You can automatically gather statistics or create lists of tables that have stale or no statistics.

To automatically gather statistics, run the DBMS_STATS.GATHER_SCHEMA_STATS and DBMS_STATS.GATHER_DATABASE_STATS procedures with the OPTIONS and objlist parameters. Use the following values for the options parameter:

GATHER STALE : Gathers statistics on tables with stale statistics.（通过*_tab_modifications视图）

GATHER : Gathers statistics on all tables. (default)

GATHER EMPTY : Gathers statistics only on tables without statistics.

LIST STALE : Creates a list of tables with stale statistics.（通过*_tab_modifications视图）

LIST EMPTY : Creates a list of tables that do not have statistics.

The objlist parameter identifies an output parameter for the LIST STALE and LIST EMPTY options. The objlist parameter is of type DBMS_STATS.OBJECTTAB.

Step 1 : Perform a quick analyze to load in base statistics

BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS (
  ownname           => 'scott',
  estimate_percent  => null,              -- Small table, lets compute
  block_sample      => false,
  method_opt        => 'FOR ALL COLUMNS',
  degree            => null,              -- No parallelism used in this example
  granularity       => 'ALL',
  cascade           => true,              -- Make sure we include indexes
  options           => 'GATHER'           -- Gather mode
  );
END;
/

PL/SQL procedure successfully completed.

Step 2 : Examine the current statistics

SELECT table_name, num_rows, blocks, avg_row_len    
FROM user_tables
WHERE table_name='EMP';

TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
EMP                                  1500         28          92

Step 3 : Turn on Automatic Monitoring

Now turn on automatic monitoring for the emp table. This can be done using the alter table method. Starting with Oracle 9i, you can also perform this at the "schema", and "entire database" level. I provide the syntax for all three methods below.

通过alter table 语句我们可以设置oracle数据库自动监控某张表的变化，从9i开始，我们还可以在schema或者数据库级别设置是否监控数据变化，监控结构会存储在*_tab_modifications视图中。

Monitor only the EMP table.

<strong>alter table emp monitoring;</strong>

Table altered.

Monitor all of the tables within Scott's schema. (Oracle 9i and higher)

BEGIN
  <strong>DBMS_STATS.alter_schema_tab_monitoring(&#39;scott&#39;, true);</strong>
END;
/

PL/SQL procedure successfully completed.

Monitor all of the tables within the database. (Oracle 9i and higher)

Note: Although the option to collect statistics for SYS tables is available via ALTER_DATABASE_TAB_MONITORING, Oracle continues to recommend against this practice until the next major release after 9i Release 2. Also note that the ALTER_DATABASE_TAB_MONITORING procedure in the DBMS_STATS package only monitors tables; there is an ALTER INDEX...MONITORING statement which can be used to monitor indexes. Thanks to Nabil Nawaz for providing this and pointing out an error I made in the previous version of this article.

BEGIN
 <strong> DBMS_STATS.alter_database_tab_monitoring (
    monitoring => true,
    sysobjs    => false);      -- Don&#39;t set to true, see note above.</strong>
END;
/

PL/SQL procedure successfully completed.

Step 4 : Verify that monitoring is turned on.

Note: The results of the following query are from running the alter table ... statement on the emp table only.

可以通过*_tables视图的monitoring字段来判断某张表是否开启了自动监控

SELECT table_name, monitoring
FROM user_tables
ORDER BY monitoring;

TABLE_NAME                     MONITORING
------------------------------ ----------
DEPT                           NO
EMP                            YES

Step 5 : Delete some rows from the database.

SQL> DELETE FROM emp WHERE rownum < 501;

500 rows deleted.

SQL> commit;

Commit complete.

Step 6 : Wait until the monitered data is flushed.

Data can be flushed in several ways. In Oracle 8i, you can wait it out for 3 hours.In Oracle 9i and higher, you only need to wait 15 minutes.In either version, restart the database.For immediate results in Oracle 9i and higher, use the DBMS_STATS.flush_database_monitoring_info package. OK, I'm impatient...

exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

Step 7 : Check for what it has collected.

As user "scott", check USER_TAB_MODIFICATIONS to see what it was collected.

SELECT * FROM user_tab_modifications;

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED
---------- -------------- ----------------- ------- ------- ------- --------- ---------
EMP                                               0       0     500 18-SEP-02 NO

Step 8 : Execute DBMS_STATS to gather stats on all "stale" tables.

BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname           => 'scott',
    estimate_percent  => null,
    block_sample      => false,
    method_opt        => 'FOR ALL COLUMNS',
    degree            => null,
    granularity       => 'ALL',
    cascade           => true,
    options           => 'GATHER STALE');
END;
/

PL/SQL procedure successfully completed.

Step 9 : Verify that the table is no longer listed in USER_TAB_MODIFICATIONS.

SQL> SELECT * FROM user_tab_modifications;

no rows selected.

Step 10 : Examine some of new statistics collected.

SELECT table_name, num_rows, blocks, avg_row_len    
FROM user_tables where table_name='EMP';

TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
EMP                                  1000         28          92

How to determine if dictionary statistics are RDBMS-generated or user-defined

The following section explains how to determine if your dictionary statistics are RDBMS-generated or set by users through one of the DBMS_STATS.SET_xx_STATS procedures.

This is crucial for development environments that are testing the performance of SQL statements with various sets of statistics. The DBA will need to know if the relying statistics are RDBMS-defined or user-defined.

RDBMS-generated statistics are generated by the following:（我们可以通过如下方式生成统计信息）

ANALYZE SQL commandDBMS_UTILITY.ANALYZE_SCHEMA procedureDBMS_UTILITY.ANALYZE_DATABASE procedureDBMS_DDL.ANALYZE_OBJECT procedure8.1 DBMS_STATS.GATHER_xx_STATS procedures User generated statistics are only done through the use of the DBMS_STATS.SET_xx_STATS procedures（如果我们需要手工设置统计信息，只可以通过dbms_stats包的set_xx_stats过程来实现）

The column USER_STATS from DBA_TABLES, ALL_TABLES, USER_TABLES displays:

YES, when statistics are entered directly by a user.NO, when statistics are generated by RDBMS through an ANALYZE statement（如果USER_STATS字段的值为Yes，则统计信息为手工指定，NO，为通过dbms或者analyze方式系统生成）