DBMS_STATS包（一）


DBMS_STAS包不仅能够对表进行分析，它还可以对数据库分析进行管理。 按照功能可以分一下几类：

（1）       性能数据的收集

（2）       性能数据的设置

（3）       性能数据的删除

（4）       性能数据的备份和恢

更多信息参考Oracle 联机文档：

11g DBMS_STATS

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10577/d_stats.htm#ARPLS68486

10g DBMS_STATS

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036461


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的性能等。


（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);


在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自己决定。

 

（a）estimate_percent 参数

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

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

 

（b）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.

 

（c）degree 参数

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

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

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

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

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

 

（d）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一句数据的分布情况进行考虑，关键是视数据的倾斜程度而定。