oracle 对象统计信息

对象统计信息的作用是帮助查询优化器选择更好的执行计划。

比如，某一个表的索引clustering_factor非常的高，这个时候如果在where条件中指定column between A and B的限定条件来查询，那么查询优化器有两个选择，一个是通过索引做索引范围扫描，另一个是全表扫描。

因为我们知道clustering_factor的值非常高，这时候做索引范围扫描的开销可能还会比全表扫描更高。所以我们期望是全表扫描。

但是如果数据库中没有关于这个索引数据对象的统计信息，那么查询优化器就不会了解到这个索引的 clustering_factor很高，那么查询优化器就有可能选择索引范围扫描的执行计划，这样就会产生一个低效的执行计划。 所以我们可以看到，对象的统计信息的重要。

下面了解一下对象的统计信息。

1. 对象统计信息有哪几种类型

有三种类型的对象统计信息：表统计，列统计，索引统计。

对象

表/索引级别统计

分区级别统计

子分区级别统计

表

user_tab_statistics

user_tables

user_tab_statistics

user_tables

user_tab_statistics

user_tables

列

user_tab_col_statistics

user_tab_histograms

user_part_col_statistics

user_part_histograms

user_subpart_col_statistics

user_subpart_histograms

索引

user_ind_statistics

user_indexes

user_ind_statistics

user_ind_partitions

user_ind_statistics

user_ind_subpartitions

为了测试，我们创建如下表并手动搜集统计信息。

CREATE TABLE t

AS

SELECT ROWNUM AS id,

       ROUND(DBMS_RANDOM.normal*1000) AS val1,

       100+ROUND(LN(ROWNUM/3.25+2)) AS val2,

       100+ROUND(LN(ROWNUM/3.25+2)) AS val3,

       DBMS_RANDOM.string('p',250) AS pad

FROM all_objects

WHERE ROWNUM<=1000

ORDER BY dbms_random.value;

 

UPDATE t SET val1=NULL WHERE val1<0;

ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY(id);

CREATE INDEX t_val1_i ON t(val1);

CREATE INDEX t_val2_i ON t(val2);

 

 

 

BEGIN

  dbms_stats.gather_table_stats(

  ownname => 'SYS',

  tabname =>'T',

  method_opt => 'for all columns size skewonly',

  cascade => TRUE);

END;

 

下面详细说一下表，列，索引的统计信息。

1.1   表统计信息

表统计信息从user_tab_statistics中可以得到。

       SELECT           

       num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len

       FROM

       user_tab_statistics

       WHERE

       table_name = 'T';

这里要注意的是empty_blocks表示高水位以上的数据块，这里为0，因为dbms_stat不计算其值。Avg_space表示数据块平均空闲空间（字节），这里也不计算，设置为0。Chain_cnt 行链接和航迁移总数，也不计算设置为零。Avg_row_len table中平均行的长度（字节）。

1.2   列统计信息

列统计信息有两种，普通列统计信息和直方图。先看一下普通列统计信息。

普通列统计信息

SELECT column_name,

       num_distinct,

       low_value,

       high_value,

       density,

       num_nulls,

       avg_col_len,

       histogram,num_buckets

FROM   user_tab_col_statistics

WHERE  table_name ='T';

        Num_distinct 该列中唯一值的数量。

        Low_value 该列的最小值，存储为oracle内部格式，对字符串至存储前32字节。

        High_value 该列的最大值，存储为oracle内部格式，对字符串至存储前32字节。

        Density 0到1之间的一个小数。接近0表示对该列的过滤可以过滤掉大多数行（选择性较强），接近1表示选择性较弱。没有直方图的话其值为1/num_distinct。如果有直方图，则根据直方图计算。

        Histograms 是否有直方图，如果有是那种类型。

        Num_buckets 直方图里桶数。没有直方图则桶数为1，最大桶数为254。

直方图

  查询优化器原则上认为数据是均匀分布的，就如表t的主键列id。这一列的值分布很均匀，在1到1000上平均分布。然而有时数据的分布并不均匀。那么这时查询优化器需要额外的信息才能做出正确的判断。比如我们已经知道了val2列的数据分布如下：

SQL> SELECT val2 as val2,count(*) FROM t GROUP BY val2 ORDER BY val2;

 

      VAL2   COUNT(*)

---------- ----------

       101          8

       102         25

       103         68

       104        185

       105        502

       106        212

 

6 rows selected

如果有一条查询语句以val2=105做过滤条件，这时查询优化器如何选择访问路径？很可能查询优化器会选择索引扫描，因为它并不知道这一列值为105的占了一半还多。如果查询优化器知道了这个信息，那么它就可以果断的选择全表扫描，因为在返回大量数据的情况下，全表扫描比索引开销更小。（当然，本例中t表一共才有1000条数据，所以无论是全表还是索引扫描都会很快返回结果）

 

  要获得这些额外信息，查询优化器可以查询直方图。直方图有两种，频率直方图和等高直方图。他们的区别主要在于桶数和唯一值的个数。直方图的桶数最大为254。如果唯一值的个数小于等于254就可以建立频率直方图，每一个唯一值一个桶。如果唯一值的个数大于254则要建立等高直方图。

 

  频率直方图，下面的查询查询了表t val2列上的频率直方图。

SQL> SELECT endpoint_value,

  2         endpoint_number,

  3         endpoint_number - lag(endpoint_number,1,0) OVER (ORDER BY endpoint_number) AS frequency

  4  FROM   user_tab_histograms

  5  WHERE table_name='T' AND column_name='VAL2'

  6  ORDER BY endpoint_number

  7  /

 

 

 

ENDPOINT_VALUE ENDPOINT_NUMBER  FREQUENCY

-------------- --------------- ----------

           101               8          8

           102              33         25

           103             101         68

           104             286        185

           105             788        502

           106            1000        212

 

6 rows selected

从上面的查询可以了解到，endpoint_value代表的是直方图中桶内唯一值的内容。Endpoint_number存储的是频率。不过这个频率是累加频率。

 

等高直方图，下面的语句手动建立了一个等高直方图。

SQL> SELECT count(*) , max(val2) AS endpoint_value , endpoint_number

  2  FROM (SELECT val2, ntile(5) over(order by val2) as endpoint_number FROM t)

  3  GROUP BY endpoint_number

  4  ORDER BY endpoint_number

  5  /

 

  COUNT(*) ENDPOINT_VALUE ENDPOINT_NUMBER

---------- -------------- ---------------

       200            104               1

       200            105               2

       200            105               3

       200            106               4

       200            106               5

 

关于直方图的信息这里讲述的很不完全，需要查看额外的文档具体了解。

1.3   扩展列的统计信息

        如果我们在where条件中这样使用一个查询条件upper（val2） = 105，那么查询优化器是无法使用统计信息的。而相应的我们可以这样处理，创建一个upper(val2)的虚列，然后在这个虚列上收集统计信息。不过我们要了解的下面的这个函数可以帮我们完成从创建虚列到收集虚列统计信息的全过程。

SELECT dbms_stats.create_extended_stats(ownname => 'SYS',tabname => 'T',extension => '(UPPER(PAD))') FROM DUAL;

1.4   索引统计信息

SQL> SELECT INDEX_NAME,

  2  BLEVEL,

  3  LEAF_BLOCKS,

  4  DISTINCT_KEYS,

  5  NUM_ROWS,

  6  CLUSTERING_FACTOR

  7  FROM DBA_IND_STATISTICS WHERE TABLE_NAME='IND_GOOD';

 

上面主要讲解了各种类型的统计信息，以及如何查询这些统计信息，下面讲解一下怎样生成这些统计信息。

2.      收集对象统计信息

收集统计信息要用dbms_stats工具包来收集。里面提供了多个过程：

Gater_database_stats

Gather_dictionary_stats

Gather_fixed_objects_stats

Gather_schema_stats

Gather_table_stats

Gather_index_stats

这些存储过程每个都需要多个参数，这些参数可以分成三大组，第一组，指明对象，第二组，指明收集选项，第三组指明覆盖当前的统计信息之前是否备份。

参数

数据库

数据字典

固定对象

模式

表

索引

目标对象

 

 

 

 

 

 

Owner

 

 

 

√

√

√

Indname

 

 

 

 

 

√

tabname

 

 

 

 

√

 

partname

 

 

 

 

√

√

Comp_id

 

√

 

 

 

 

granularity

√

√

 

√

√

√

cascade

√

√

 

√

√

 

Gather_sys

√

 

 

 

 

 

Gather_temp

√

 

 

√

 

 

options

√

√

 

√

 

 

objlist

√

√

 

√

 

 

force

 

 

 

√

√

√

Obj_filter_list

√

√

 

√

 

 

收集选项

 

 

 

 

 

 

Estimate_percent

√

√

 

√

√

√

Block_sample

√

√

 

√

√

 

Method_opt

√

√

 

√

√

 

 No_invalidate

√

√

√

√

√

√

备份表

 

 

 

 

 

 

Stattab

√

√

√

√

√

√

Statid

√

√

√

√

√

√

statown

√

√

√

√

√

√

下面介绍一下这些参数的意义。

目标对象参数：

Ownname      指定schema名称。

Indname       指定索引名称。

Tabname      指定表的名称

Partname      指定分区或者子分区的名称。如果不指定，则收集所有分区的对象统计信息。

Comp_id       这个不明确。

Granularity     指定要处理的分区对象的统计级别。

Cascade      指明是否手机索引统计信息。

Gather_sys    指明是否手机sys用户统计信息

Gather_temp  指明是否收集临时表的统计信息。但要注意的是，dbms_stats运行时，会先commit，所以只有指定了on commit preserve rows的临时表才可以被处理。

Options           包含7个选项

                     Gather:           处理所有对象

                      Gatherauto     由系统决定那些对象要处理以及如何处理

                      Gatherstale    只处理信息失效的对象，注意，没有统计信息的不算信失效。

                      Gatherempty  只处理没有统计信息的对象

                      List auto         列出将用gather auto处理的对象

                      Liststale         列出将用gather stale处理的对象

                      Listempty      列出将用gather empty处理的对象

Objlist             该参数根据options的不同值返回被处理的对象列表。

Obj_filter_list   在目标对象满足该参数指定的过滤条件时才会收集统计信息

收集选项参数：

Estimate_percent指明是否采样收集统计信息。值可以为0.0000001到100。一百表示不采样。常量dbms_stats.auto_sample_size算作0，由系统决定采样大小。

Block_sample                指明采样是采用数据行采样还是数据块采样。行采样精确，块采样快捷。

Method_opt               指明是否收集直方图统计信息还指明采样的最大桶数。

                                    值的格式为columns clause + size clause

                                    例如：for all columns size skewonly

                                    Columnsclause可取值有：

for all [indexed / hidden ] columns

                                    Sizeclause      可取值有：

size 1-254

                                                 Size skewonly 只收集skew列，桶数由系统决定

                                                 Size auto 同上，并且要加上where引用的列

                                                 Size repat 只是刷新现有直方图

No_invalidate                收集了统计信息，很有可能有一些游标就不适用了。那么这时这个参数可以指定成三个值。

                                    True游标不失效，这样重新收集统计信息就没意义了。

                                    False游标立即失效，可能会造成集中大量解析游标

                                    Dbms_stats.auto_invalidate游标不立即失效，这样可以避免集中解析问题。

备份表参数

Stattab                         指定存储统计信息的备份表

Statid                           

Statown                      指定备份表的用户名，默认是当前用户。