DBMS_STATS包的几个常用功能分别是什么

今天就跟大家聊聊有关DBMS_STATS包的几个常用功能分别是什么，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。

3.1 DBMS_STATS包的几个常用功能

    性能数据的收集包含这样几个存储过程：

GATHER_DATABASE_STATS Procedures

GATHER_DICTIONARY_STATS Procedure

GATHER_FIXED_OBJECTS_STATS Procedure

GATHER_INDEX_STATS Procedure

GATHER_SCHEMA_STATS Procedures

GATHER_SYSTEM_STATS Procedure

GATHER_TABLE_STATS Procedure

从名字也可以看出各自的作用，这些存储过程用来收集数据库不同级别对象的性能数据，包括：数据库，数据字典，表，索引，SCHEMA的性能等。

3.1.1 GATHER_TABLE_STATS Procedure存储过程

在10g中, GATHER_TABLE_STATS的参数如下：

DBMS_STATS.GATHER_TABLE_STATS (

ownname VARCHAR2,

tabname VARCHAR2,

partname VARCHAR2 DEFAULT NULL,

estimate_percent NUMBER DEFAULT to_estimate_percent_type

(get_param('ESTIMATE_PERCENT')),

block_sample BOOLEAN DEFAULT FALSE,

method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),

degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),

granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),

cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),

stattab VARCHAR2 DEFAULT NULL,

statid VARCHAR2 DEFAULT NULL,

statown VARCHAR2 DEFAULT NULL,

no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (

get_param('NO_INVALIDATE')),

force BOOLEAN DEFAULT FALSE);

到了11g，对参数做了调整：

    DBMS_STATS.GATHER_TABLE_STATS (

ownname VARCHAR2,

tabname VARCHAR2,

partname VARCHAR2 DEFAULT NULL,

estimate_percent NUMBER DEFAULT to_estimate_percent_type

(get_param('ESTIMATE_PERCENT')),

block_sample BOOLEAN DEFAULT FALSE,

method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),

degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),

granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),

cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),

stattab VARCHAR2 DEFAULT NULL,

statid VARCHAR2 DEFAULT NULL,

statown VARCHAR2 DEFAULT NULL,

no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (

get_param('NO_INVALIDATE')),

force BOOLEAN DEFAULT FALSE);

对参数的说明：

Parameter

Description

ownname

Schema of table to analyze

tabname

Name of table

partname

Name of partition

estimate_percent

Percentage of rows to estimate (NULL means compute) The  valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE  to have Oracle determine the appropriate sample size for good statistics.  This is the default.The default value can be changed using the SET_PARAM Procedure.

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

Accepts:

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

FOR COLUMNS [size clause] column|attribute  [size_clause] [,column|attribute [size_clause]…]

size_clause is defined as size_clause := SIZE {integer  | REPEAT | AUTO | SKEWONLY}

 – integer : Number of histogram buckets. Must be in the range [1,254].

 – REPEAT : Collects histograms only on the columns that already have  histograms.

 – AUTO : Oracle determines the columns to collect histograms based on data  distribution and the workload of the columns.

 – SKEWONLY : Oracle determines the columns to collect histograms based on the  data distribution of the columns.

The default is FOR ALL COLUMNS SIZE AUTO.The default  value can be changed using the SET_PARAM Procedure.

degree

Degree of parallelism. The default for degree is NULL.  The default value can be changed using the SET_PARAM Procedure NULL means use the table  default value specified by the DEGREE clause in the CREATE TABLE or ALTER  TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the  default value based on the initialization parameters. The AUTO_DEGREE value  determines the degree of parallelism automatically. This is either 1 (serial  execution) or DEFAULT_DEGREE (the system default value based on number of  CPUs and initialization parameters) according to size of the object.

granularity

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

'ALL' – gathers all (subpartition, partition, and  global) statistics

'AUTO'- determines the granularity based on the  partitioning type. This is the default value.

'DEFAULT' – gathers global and partition-level statistics.  This option is obsolete, and while currently supported, it is included in the  documentation for legacy reasons only. You should use the 'GLOBAL AND  PARTITION' for this functionality. Note that the default value is now 'AUTO'.

'GLOBAL' – gathers global statistics

'GLOBAL AND PARTITION' – gathers the global and  partition level statistics. No subpartition level statistics are gathered  even if it is a composite partitioned object.

'PARTITION '- gathers partition-level statistics

'SUBPARTITION' – gathers subpartition-level statistics.

cascade

Gather statistics on the indexes for this table. Index  statistics gathering is not parallelized. Using this option is equivalent to  running the GATHER_INDEX_STATS Procedure on each of the  table's indexes. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle  determine whether index statistics to be collected or not. This is the  default. The default value can be changed using theSET_PARAM Procedure.

stattab

User statistics table identifier describing where to  save the current statistics

statid

Identifier (optional) to associate with these  statistics within stattab

statown

Schema containing stattab (if different than ownname)

no_invalidate

Does not invalidate the dependent cursors if set to  TRUE. The procedure invalidates the dependent cursors immediately if set to  FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to  invalidate dependent cursors. This is the default. The default can be changed  using the SET_PARAM Procedure.

force

Gather statistics of table even if it is locked

在gather_table_stats 存储过程的所有参数中，除了ownname和tabname，其他的参数都有默认值。所以我们在调用这个存储过程时，Oracle 会使用参数的默认值对表进行分析。如：

SQL> exec dbms_stats.gather_table_STATS('SYS','T');

PL/SQL 过程已成功完成。

    如果想查看当前的默认值，可以使用dbms_stats.get_param函数来获取：

SQL> select dbms_stats.get_param('method_opt') from dual;

DBMS_STATS.GET_PARAM('METHOD_OPT')

————————————————————

FOR ALL COLUMNS SIZE AUTO

结合上面对参数的说明：

  – AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.

我们可以看出，就是对所有的列做直方图分析，直方图设置的bucket值由Oracle自己决定。

3.1.1.1 estimate_percent 参数

    这个参数是一个百分比值，它告诉分析包需要使用表中数据的多大比例来做分析。

理论上来讲，采样的数据越多，得到的信息就越接近于实际，CBO做出的执行计划就越优化，但是，采样越多，消耗的系统资源必然越多。对系统的影响也越大。所以对于这个值的设置，要根据业务情况来。如果数据的直方图分布比较均匀，就可以使用默认值：AUTO_SAMPLE_SIZE，即让Oracle 自己来判断采样的比例。有时，特别是对于批量加载的表，我们可以预估表中的数据量，可以人工地设置一个合理的值。一般，对于一个有1000万数据的表分区，可以把这个参数设置为0.000001.

3.1. 1.2 Method_option 参数

    这个参数用来定义直方图分析的一些值。

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]…]

    这里给出了4种指定哪些列进行分析的方式：

（1） 所有列：for all column

（2） 索引列：只对有索引的列进行分析，for all indexed columns

（3） 影藏列：只对影藏的列进行分析，for all hidden columns

（4） 显示指定列：显示的指定那些列进行分析，for columns columns_name

该参数默认值：for all columns size auto.

3.1. 1.3 degree 参数

用来指定分析时使用的并行度。有以下这些设置：

(1) Null：如果设置为null，Oracle 将使用被分析表属性的并行度，比如表在创建时指定的并行度，或者后者使用alter table 重新设置的并行度。

(2) 一个数值：可以显示地指定分析时使用的并行度。

(3) Default_degree: 如果设置为default，Oracle 将根据初始化参数中相关参数的设置来决定使用的并行度。

这个参数的默认值是Null，即通过表上的并行度属性来决定分析使用的并行度。当需要分析的表或表分区非常大，并且系统资源比较充分的时候，就可以考虑使用并行的方式来做分析，这样就会大大提高分析的速度。相反，如果你的系统资源比较吃紧，那么启用并行可能会适得其反。

3.1. 1.4 Granularity

分析的粒度，有以下几个配置：

（1） ALL : 将会对表的全局（global），分区，子分区的数据都做分析

（2） AUTO: Oracle 根据分区的类型，自动决定做哪一种粒度的分析。

（3） GLOBAL：只做全局级别的分析。

（4） GLOBAL AND PARTITION: 只对全局和分区级别做分析，对子分区不做分析，这是和ALL的一个区别。

（5） PARTITION: 只在分区级别做分析。

（6） SUBPARTITION: 只在子分区做分析。

在生产环境中，特别是OLAP 或者数据仓库的环境中，这个参数的设置会直接影响到CBO的执行计划选择。

在OLAP或者数据仓库系统中，经常有这样的事情，新创建一个分区，将批量的数据（通常是很大的数据）加载到分区中，对分区做分析，然后做报表或者数据挖掘。在理想的情况下，对表的全局，分区都做分析，这样才能得到最充足的数据，但是通常这样的表都非常大，如果每增加一个分区都需要做一次全局分析，那么会消耗极大的系统资源。但是如果只对新加入的分区进行分区而不做全局分析，oracle 在全局范围内的信息就会不准确。

    该参数在默认情况下，DBMS_STATS 包会对表级（全局），分区级（对应参数partition）都会进行分析。如果把cascade 设置为true，相应索引的全局和分区级别也都会被分析。如果只对分区级进行分析，而全局没有分析，那么全局信息没有更新，依然会导致CBO 作出错误的执行计划。

所以当一些新的数据插入到表中时，如果对这些新的数据进行分析，是一个非常重要的问题。一般参考如下原则：

（1） 看一下新插入的数据在全表中所占的比例，如果所占比例不是很大，那么可以考虑不做全局分析，否则就需要考虑，一句是业务的实际运行情况。

（2） 采样比例。如果载入的数据量非常大，比如上千万或者更大，就要把采样比例压缩的尽可能地小，但底线是不能影响CBO做出正确的执行计划，采样比例的上线是不能消耗太多的资源而影响到业务的正常运行。

（3） 新加载的数据应该要做分区级的数据分析。至于是否需要直方图分析，以及设置多少个buckets（size参数指定），需要DBA一句数据的分布情况进行考虑，关键是视数据的倾斜程度而定。

3.1.2 GATHER_SCHEMA_STATS 存储过程

    这个存储过程用于对某个用户下所有的对象进行分析。如果你的数据用户对象非常多，单独对每个对象进行分析设定会非常不方便，这个存储过程就很方便。它的好处在于如果需要分析的对象非常多，将可以大大降低DBA的工作量，不足之处是所有分析使用相同的分析策略，可能会导致分析不是最优。所以要根据实际情况来决定。

    该存储过程参数如下：

    DBMS_STATS.GATHER_SCHEMA_STATS (

ownname VARCHAR2,

estimate_percent NUMBER DEFAULT to_estimate_percent_type

(get_param('ESTIMATE_PERCENT')),

block_sample BOOLEAN DEFAULT FALSE,

method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),

degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),

granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),

cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),

stattab VARCHAR2 DEFAULT NULL,

statid VARCHAR2 DEFAULT NULL,

options VARCHAR2 DEFAULT 'GATHER',

objlist OUT ObjectTab,

statown VARCHAR2 DEFAULT NULL,

no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (

get_param('NO_INVALIDATE')),

force BOOLEAN DEFAULT FALSE,

obj_filter_list ObjectTab DEFAULT NULL);

参数说明如下：

Parameter

Description

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]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size  for good statistics. This is the default.The default value can be changed  using the SET_DATABASE_PREFS Procedure,  SET_GLOBAL_PREFS Procedure,  SET_SCHEMA_PREFS Procedure  and SET_TABLE_PREFS Procedure.

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

Accepts:

FOR ALL [INDEXED | HIDDEN] COLUMNS[size_clause]

size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

 – integer : Number of histogram buckets. Must be in the range [1,254].

 – REPEAT : Collects histograms only on the columns that already have histograms.

 – AUTO : Oracle determines the columns to collect histograms based on data  distribution and the workload of the columns.

 – SKEWONLY : Oracle determines the columns to collect histograms based on the data  distribution of the columns.

The default is FOR ALL COLUMNS SIZE AUTO.The default value can be changed using the SET_DATABASE_PREFS Procedure,  SET_GLOBAL_PREFS Procedure,  SET_SCHEMA_PREFS Procedure  and SET_TABLE_PREFS Procedure.

degree

Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_DATABASE_PREFS Procedure,  SET_GLOBAL_PREFS Procedure,  SET_SCHEMA_PREFS Procedure  and SET_TABLE_PREFS Procedure.  NULL means use the table default value specified by the DEGREE clause in the CREATE  TABLE or ALTER TABLE  statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the  initialization parameters.The AUTO_DEGREEvalue  determines the degree of parallelism automatically. This is either 1 (serial  execution) or DEFAULT_DEGREE (the system  default value based on number of CPUs and initialization parameters)  according to size of the object.

granularity

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

'ALL' – gathers all (subpartition, partition, and global) statistics

'AUTO'- determines the granularity based on the partitioning type. This is the  default value.

'DEFAULT' – gathers global and partition-level statistics. This option is  obsolete, and while currently supported, it is included in the documentation  for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.

'GLOBAL' – gathers global statistics

'GLOBAL  AND PARTITION' – gathers the global and  partition level statistics. No subpartition level statistics are gathered  even if it is a composite partitioned object.

'PARTITION '- gathers partition-level statistics

'SUBPARTITION' – gathers subpartition-level statistics.

cascade

Gather statistics on the indexes as well. 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. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle  determine whether index statistics to be collected or not. This is the  default. The default value can be changed using the SET_DATABASE_PREFS Procedure,  SET_GLOBAL_PREFS Procedure,  SET_SCHEMA_PREFS Procedure  and SET_TABLE_PREFS Procedure.

stattab

User statistics 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: Gathers statistics on all objects in the schema.

GATHERAUTO: Gathers all necessary statistics automatically.  Oracle implicitly determines which objects need new statistics, and  determines how to gather those statistics. When GATHER AUTO is  specified, the only additional valid parameters are ownname, stattab, statid, objlist and statown; all other parameter settings are ignored. Returns a  list of processed objects.

GATHERSTALE: Gathers statistics on stale objects as determined by  looking at the *_tab_modifications views. Also, return a list of objects found to be  stale.

GATHEREMPTY: Gathers statistics on objects which currently have no  statistics. also, return a list of objects found to have no statistics.

LIST AUTO: Returns a list of objects to be processed with GATHER AUTO.

LISTSTALE: Returns list of stale objects as determined by  looking at the *_tab_modifications views.

LISTEMPTY: Returns 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)

no_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent  cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure,  SET_GLOBAL_PREFS Procedure,  SET_SCHEMA_PREFS Procedure  and SET_TABLE_PREFS Procedure.

force

Gather statistics on objects even if they are locked

obj_filter_list

A list of object filters. When provided, GATHER_SCHEMA_STATS will gather statistics only on objects which satisfy at least one  object filter in the list as needed. In a single object filter, we can  specify the constraints on the object attributes. The attribute values  specified in the object filter are case- insensitive unless double-quoted.  Wildcard is allowed in the attribute values. Suppose non-NULL values s1, s2, … are specified for attributes a1, a2, … in one  object filter. An object o is said to satisfy this object filter if (o.a1  like s1) and (o.a2 like s2) and … is true. See Applying an Object Filter List.

3.1.3 DBMS_STATS.GATHER_INDEX_STATS 存储过程

    该存储过程用于对索引的分析，如果我们在使用DBMS_STATS.GATHER_TABLES_STATS的分析时设置参数cascade=>true。那么Oracle会同时执行这个存储过程来对索引进行分析。

存储过程参数：

DBMS_STATS.GATHER_INDEX_STATS (

ownname VARCHAR2,

indname VARCHAR2,

partname VARCHAR2 DEFAULT NULL,

estimate_percent NUMBER DEFAULT to_estimate_percent_type

(GET_PARAM('ESTIMATE_PERCENT')),

stattab VARCHAR2 DEFAULT NULL,

statid VARCHAR2 DEFAULT NULL,

statown VARCHAR2 DEFAULT NULL,

degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),

granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),

no_invalidate BOOLEAN DEFAULT to_no_invalidate_type

(GET_PARAM('NO_INVALIDATE')),

force BOOLEAN DEFAULT FALSE);

Parameter

Description

ownname

Schema of index to analyze

indname

Name of index

partname

Name of partition

estimate_percent

Percentage of rows to estimate (NULL means compute). The valid range is [0.000001,100]. Use the  constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size  for good statistics. This is the default.The default value can be changed  using the SET_DATABASE_PREFS Procedure,  SET_GLOBAL_PREFS Procedure,  SET_SCHEMA_PREFS Procedure  and SET_TABLE_PREFS Procedure.

stattab

User statistics table identifier describing where to  save the current statistics

statid

Identifier (optional) to associate with these  statistics within stattab

statown

Schema containing stattab (if different  than ownname)

degree

Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_DATABASE_PREFS Procedure,  SET_GLOBAL_PREFS Procedure,  SET_SCHEMA_PREFS Procedure  and SET_TABLE_PREFS Procedure.  NULL means use of table default value that was specified by the DEGREE clause in the CREATE/ALTER  INDEX statement. Use the constant DBMS_STATS.DEFAULT_DEGREE for the default value based on the initialization  parameters. The AUTO_DEGREE value determines the degree of parallelism  automatically. This is either 1 (serial execution) or DEFAULT_DEGREE (the system default value  based on number of CPUs and initialization parameters) according to size of  the object.

granularity

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

'ALL' – gathers all (subpartition, partition, and global) statistics

'AUTO'- determines the granularity based on the partitioning type. This is the  default value.

'DEFAULT' – gathers global and partition-level statistics. This option is  obsolete, and while currently supported, it is included in the documentation  for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.

'GLOBAL' – gathers global statistics

'GLOBAL  AND PARTITION' – gathers the global and  partition level statistics. No subpartition level statistics are gathered  even if it is a composite partitioned object.

'PARTITION '- gathers partition-level statistics

'SUBPARTITION' – gathers subpartition-level statistics.

no_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent  cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure,  SET_GLOBAL_PREFS Procedure,  SET_SCHEMA_PREFS Procedure  and SET_TABLE_PREFS Procedure.

force

Gather statistics on object even if it is locked

上面讨论了三个常用的存储过程。分析对CBO 来说非常重要，如果不能按照自己的系统指定出切合实际的数据分析方案，可能会导致如下问题的发生：

（1） 分析信息不充分导致CBO 产生错误的执行计划，导致SQL执行效率低下。

（2） 过多的分析工具带来系统性能的严重下降。

3.2 DBMS_STATS包管理功能

3.2.1 获取分析数据

GET_COLUMN_STATS Procedures

GET_INDEX_STATS Procedures

GET_SYSTEM_STATS Procedure

GET_TABLE_STATS Procedure

这四个存储过程分别为用户获取字段，索引，表和系统的统计信息。它的用法是首先定义要获取性能指标的变量，然后使用存储过程将性能指标的值赋给变量，最后将变量的值输出。如：

SQL> set serveroutput on

SQL> declare

2 dist number;

3 dens number;

4 ncnt number;

5 orec dbms_stats.statrec;

6 avgc number;

7 begin

8 dbms_stats.get_column_stats('SYS','T','object_ID',distcnt=>dist,density=>dens,nullcnt=>ncnt,srec=>orec,avgclen=>avgc);

9 dbms_output.put_line('the distcnt is:' ||to_char(dist));

10 dbms_output.put_line('the density is:' ||to_char(dens));

11 dbms_output.put_line('the nullcnt is:' ||to_char(ncnt));

12 dbms_output.put_line('the srec is:' ||to_char(ncnt));

13 dbms_output.put_line('the avgclen is:' ||to_char(avgc));

14 end;

15 /

the distcnt is:72926

the density is:.0000137125305103804

the nullcnt is:0

the srec is:0

the avgclen is:5

PL/SQL 过程已成功完成。

3.2.2 设置分析数据

SET_COLUMN_STATS Procedures

SET_INDEX_STATS Procedures

SET_SYSTEM_STATS Procedure

SET_TABLE_STATS Procedure

这几个存储过程允许我们手工地为字段，索引，表和系统性能数据赋值。它的一个用处是当相应的指标不准确导致执行计划失败时，可以使用这种方法手工地来为这些性能数据赋值。在极端情况下，这也不失为一个解决问题的方法。

3.2.3 删除分析数据

DELETE_COLUMN_STATS Procedure

DELETE_DATABASE_STATS Procedure

DELETE_DICTIONARY_STATS Procedure

DELETE_FIXED_OBJECTS_STATS Procedure

DELETE_INDEX_STATS Procedure

DELETE_SCHEMA_STATS Procedure

DELETE_SYSTEM_STATS Procedure

DELETE_TABLE_STATS Procedure

当性能数据出现异常导致CBO判断错误时，为了立刻修正这个错误，删除性能数据也是一种补救的方法，比如删除了表的数据，让CBO重新对表做动态采样分析，得到一个正确的结果。

    它可以删除字段，数据库，数据字典，基表，索引，表等级别的性能数据。

3.2.4 保存分析数据

CREATE_STAT_TABLE Procedure

DROP_STAT_TABLE Procedure

    可以用这两个存储过程创建一个表，用于存放性能数据，这样有利于对性能数据的管理，也可以删除这个表。

3.2.5 导入和导出分析数据

EXPORT_COLUMN_STATS Procedure

EXPORT_DATABASE_STATS Procedure

EXPORT_DICTIONARY_STATS Procedure

EXPORT_FIXED_OBJECTS_STATS Procedure

EXPORT_INDEX_STATS Procedure

EXPORT_SCHEMA_STATS Procedure

EXPORT_SYSTEM_STATS Procedure

EXPORT_TABLE_STATS Procedure

IMPORT_COLUMN_STATS Procedure

IMPORT_DATABASE_STATS Procedure

IMPORT_DICTIONARY_STATS Procedure

IMPORT_FIXED_OBJECTS_STATS Procedure

IMPORT_INDEX_STATS Procedure

IMPORT_SCHEMA_STATS Procedure

IMPORT_SYSTEM_STATS Procedure

IMPORT_TABLE_STATS Procedure

这些存储过程可以将已经有的性能指标导入到用户创建好的表中存放，需要时，可以从表中倒回来。

3.2.6 锁定分析数据

LOCK_SCHEMA_STATS Procedure

LOCK_TABLE_STATS Procedure

UNLOCK_SCHEMA_STATS Procedure

UNLOCK_TABLE_STATS Procedure

The LOCK_* procedures either freeze the current set of the statistics or to keep the statistics empty (uncollected).When statistics on a table are locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.

可能在某些时候，我们觉得当前的统计信息非常好，执行计划很准确，并且表中数据几乎不变化，那么可以使用LOCK_TABLE_STATS Procedure 来锁定表的统计信息，不允许对表做分析或者设定分析数据。 当表的分析数据被锁定之后，相关的所有分析数据，包括表级，列级，直方图，索引的分析数据都将被锁定，不允许被更新。

看完上述内容，你们对DBMS_STATS包的几个常用功能分别是什么有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注云行业资讯频道，感谢大家的支持。