Oracle 统计信息

1. DBMS_STATS.GATHER_TABLE_STATS的语法

DBMS_STATS.GATHER_TABLE_STATS (ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2, estimate_percent NUMBER, block_sample BOOLEAN, method_opt VARCHAR2, degree NUMBER, granularity VARCHAR2, cascade BOOLEAN, stattab VARCHAR2, statid VARCHAR2, statown VARCHAR2, no_invalidate BOOLEAN, force BOOLEAN);

参数说明:

ownname:要分析表的拥有者

tabname:要分析的表名.

partname:分区的名字,只对分区表或分区索引有用.

estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle决定最佳取采样值.

block_sapmple:是否用块采样代替行采样.

method_opt:决定histograms信息是怎样被统计的.method_opt的取值如下:

for all columns:统计所有列的histograms.

for all indexed columns:统计所有indexed列的histograms.

for all hidden columns:统计你看不到列的histograms

for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY:统计指定列的histograms.N的取值范围[1,254]; REPEAT上次统计过的histograms;AUTO由 oracle决定N的大小;SKEWONLY multiple end-points with the same value which is what we define by "there is skew in the data

degree:决定并行度.默认值为null.

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

cascace:是收集索引的信息.默认为falase.

stattab指定要存储统计信息的表,statid如果多个表的统计信息存储在同一个stattab中用于进行区分.statown存储统计信息表的拥有者.以上三个参数若不指定,统计信息会直接更新到数据字典.

no_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.

force:即使表锁住了也收集统计信息.

例子:

execute dbms_stats.gather_table_stats(ownname => 'owner',tabname => 'table_name' ,estimate_percent => null ,method_opt => 'for all indexed columns' ,cascade => true);

例如：

在使用DBMS_STATS分析表的时候，我们经常要保存之前的分析，以防分析后导致系统性能低下然后进行快速恢复。

分析数据库（包括所有的用户对象和系统对象）：gather_database_stats 
分析用户所有的对象（包括表、索引、簇）：gather_schema_stats
分析表：gather_table_stats
分析索引：gather_index_stats 
删除数据库统计信息：delete_database_stats
删除用户方案统计信息：delete_schema_stats
删除表统计信息：delete_table_stats 
删除索引统计信息：delete_index_stats
删除列统计信息：delete_column_stats 
设置表统计信息：set_table_stats
设置索引统计信息：set_index_stats
设置列统计信息：set_column_stats 

这是对命令与工具包的一些总结
1、对于分区表，建议使用DBMS_STATS，而不是使用Analyze语句。
a) 可以并行进行，对多个用户，多个Table
b) 可以得到整个分区表的数据和单个分区的数据。
c) 可以在不同级别上Compute Statistics：单个分区，子分区，全表，所有分区
d) 可以倒出统计信息
e) 可以用户自动收集统计信息
2、DBMS_STATS的缺点
a) 不能Validate Structure
b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，这两个仍旧需要使用Analyze语句。
c) DBMS_STATS 默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True
3、对于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS_STATS来收集信息。
 

Refer tohttp://www.savedba.com/?p=159;

https://blog.csdn.net/Zhengyixiao/article/details/83338711

通过对dbms_stats的概率，加上自己的一点见解，带你走进oracle收集统计信息。

dbms_stats包对段表的分析有3个层次：
1. 表自身的分析：包括表中的行数，数据块数，行长等信息。
2. 列的分析：包括列值的重复数，列上的空值，数据在列上的分布情况(直方图)，分为频率直方图和等高直方图
3. 索引的分析：包括索引叶块的数量，索引的深度，索引的聚合因子等
4. 系统的统计信息：i/o性能和利用，cpu性能和利用

 

当cbo做直方图分析时，会将要分析的列上的数据分成很多数量相同的部分，每一部分称为一个bucket(桶)，这样cbo就可以非常容易知道这个列上的数值的分布情况，这种数据的分布将作为一个非常重要的因素纳入到执行成本的计算当中。

性能收集的包有如下几个存储过程：
1. gather_database_stats
2. gather_dictionary_stats      收集所有系统用户的信息
3. gather_fixed_objects_stats
4. gather_index_stats
5. gather_schema_stats
6. gather_system_stats
7. gather_table_stats

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

dbms_stats.gather_tables_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_cascase_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  ------强制分析，即使表被锁定 )

estimate_percent: 这是一个百分比值，它告诉分析包需要使用表中数据的多大比例来作分析。理论上讲，采样的数量越多，得到的信息将越接近现实，cbo作出的执行计划就越准确，但采样越多，消耗的系统资源越多，对系统的影响就越大，如果数据直方图分布比较均匀，就可以将这个参数设置成AUTO_SAMPLE_SIZE,即让oracle自己来判断采样比例，如果对于一个有1000w数据的表分区，可以设置成0.000001，这只是一个经验值。一般推荐30.
method_opt：这个参数用来定义直方图分析的一些值，参数默认值：FOR ALL COLUMNS SIZE AUTO,包含参数FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause] , FOR COLUMNS [size clause] column|attribute [size_clause] [,column|arrtibute]
            所有列：FOR ALL COLUMNS
    索引列：只对有索引的列进行分析：FOR ALL INDEXED COLUMNS
    隐藏列：只对隐藏列进行分析，比如有函数索引时收集：FOR ALL HIDDEN COLUMNS
    显示指定列：显示的指定哪些列进行分析：FOR COLUMNS columns_name
method_opt=>’for all columns size skewonly’      ORACLE会根据数据分布收集直方图，有些列不必要收集直方图，有可能会导致绑定变量窥探
method_opt=>’for all columns size repeat’          只有以前收集过直方图，才会收集直方图信息，所以一般我们会设置method_opt 为repeat
method_opt=>’for all columns size auto’             ORACLE会根据数据分布以及列的workload来确定是否收集直方图，也就是说会根据查询语句中的where条件来判断列收集直方图
method_opt=>’for all columns size interger’        我们自己指定一个bucket值
degree：用来指定分析时使用的并行度，有如下这些设置
       1. null：设置为null，oracle将使用被分析表属性的并行度，比如表在创建时指定的并行度，或者后来使用alter table 重新设置的并行度，null是默认的参数
       2. 一个数值：可以显示的指定分析时使用的并行度
       3. default_degree：oracle将根据初始化参数中的相关参数的设置来决定使用的并行度。
granularity：分析的粒度，有如下几个设置，当然可以指定partition name，对指定的分区进行收集
       1. ALL：将会对表的全局，分区，子分区的数据都做分析
     2. AUTO：oracle根据分区的定义，自动决定做哪一种粒度的分析，默认设置
     3. GLOBAL：只做全局级别的分析
     4. GLOBAL AND PARTITION：只对全局和分区级别做分析，对于子分区不做分析
       5. PARTITION：只对分区级别作分析
       6. SUBPARTITION：只对子分区作分析
          EG：

1 2 3 4 5 6 7 8

DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',                                  tabname => 'P_TEST',                                  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,                                  method_opt => 'for all columns size repeat',                                  degree => DBMS_STATS.AUTO_DEGREE,                                  granularity => 'ALL',                                  cascade=>TRUE                                  );

在oracle10G中，在安装oracle的时候，就默认创建了一个名为gather_stats_job的job来自动收集统计信息，自动收集过期的统计信息依赖于表监控特征，在oracle10g中表监控默认是开启的，同时它也依赖statistics_level参数的值，10G中默认为typical，只有将statistics_level参数值设置成all或者typical才能让oracle识别过期的统计信息。
关闭自动收集统计信息：dbms_scheduler.disable(‘GATHER_STATS_JOB’);

收集某个schema的信息

1 2 3 4 5 6 7 8 9 10

BEGIN    DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SCOTT',                                   estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,                                   options => 'gather auto',                                   degree  => DBMS_STATS.AUTO_DEGREE,                                   method_opt => 'for all columns size repeat',                                   cascade => TRUE                                  ); END;                                 /

上面的例子收集SCOTT模式下所有对象的统计信息。里面值得注意的一个参数就是options。上面已经讲到过，他与表监控有关。它有四个选项
Options =>’gather’       收集所有对象的统计信息
Options =>’gather empty’ 只收集还没被统计的表
Options =>’gather stale’ 只收集修改量超过10%的表
Options =>’gather auto’  相当于empty+stale ，所以我们一般设置为AUTO。

=======================================================================================================================================
还需要使用Analyze收集统计信息的有：
1)      行迁移/行链接信息。不过使用dbms_stats无法统计这个信息，必须使用analyze table table_name  compute statistics;或者analyze table table_name list chained rows into table_name;来收集。但是在执行analyze table table_name list chained rows into table_name的时候，必须创建CHAINED_ROWS表，然后才能查看

1 2 3 4

SQL> @/opt/oracle/oracle11g/rdbms/admin/utlchain.sql Table created. SQL>  analyze table oldemp list chained rows; Table analyzed.

2)      Cluster table。Cluster table 必须使用analyze命令收集统计信息。
3)      Validate structure。Validate structure这个命令通常用于验证索引结构信息。如果一个表上经常做delete操作，那么就需要经常对这个表上的索引进行结构验证，因为这个表上的索引可能需要重建。可以使用analyze index  index_name validate structure offline;注意一定是要以offline(默认)方式收集统计信息，如果以online方式收集统计信息，那么就无法在index_stats中查找到刚才收集到的统计信息。Online方式收集统计信息不会对表加lock。而以offline方式收集统计信息会对表加lock，可能对性能有影响，一般是在系统处于空闲状态收集统计信息。
有可能需要rebuild的索引：可以查看index_stats.height ,如果高度大于等于4就可能需要rebuild，另外一个就是index_stats.del_lf_rows/index_stats.lf_rows>0.2也就是说有20%的数据被删除了，那么这个索引也需要rebuild。

行迁移：  当一个行上的更新操作(原来的数据存在且没有减少)导致当前的数据不能在容纳在当前块，我们需要进行行迁移。一个行迁移意味着整
              行数据将会移动，仅仅保留的是一个转移地址。因此整行数据都被移动，原始的数据块上仅仅保留的是指向新块的一个地址信息。
              产生：update
行链接：  当一行数据太大而不能在一个单数据块容纳时，行链接由此产生。举例来说，当你使用了4kb的Oracle 数据块大小，而你需要插入一行数据是8k，
              Oracle则需要使用3个数据块分成片来存储。因此，引起行链接的情形通常是，表上行记录的大小超出了数据库Oracle块的大小。
              产生：insert
              表上使用了LONG 或 LONG RAW数据类型的时候容易产生行链接。其次表上多于255列时Oracle会将这些过宽的表分片而产生行链接

迁移行对索引读产生额外的I/O，对全表扫描没什么影响
行链接则影响索引读和全表扫描

在大多数情况下，行链接是无法克服的，特别是在一个表包含象LONGS, LOBs 等这样的列时。当在不同的表中有大量的链接行，并且哪些表的行的长度不是很长时，你可以通过用更大的block size重建数据库的方法来解决它。

例如：当前你的数据库的数据块的大小为4K，但是你的行的平均长度为6k，那么你可以通过用8k大小的数据块来重建数据库的办法解决行链接现象。

行迁移主要是由于设置的PCTFREE参数过小，导致没有给update操作留下足够的空闲空间引起。为了避免行迁移，所有被修改的表应该设置合适的PCTFREE 值，以便在每个数据块内为数据修改保留足够的空间。可以通过增加PCTFREE值的办法来避免行迁移，但这种解决办法是以牺牲更多的空间为代价的，这也就是我们通常所说的以空间换效率。 而且通过增加PCTFREE值的办法只能缓解行迁移现象，而不能完全解决行迁移，所以较好的办法是在设置了合适的PCTFREE值的后，在发现行迁移现象比较严重时，对表的数据进行重组。

 

Part 2 GRANULARITY 的含义

定义：

GRANULARITY

Determines the granularity of statistics to collect. This value is only relevant for partitioned tables.

参数可选项：
GRANULARITY - The value determines 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.