老熊的三分地-Oracle及数据恢复

在以前的一篇文章《DBMS_STATS、ANALYZE以及Global Statistics》中，提到使用10g数据库dbms_stats收集统计信息时，granularity缺省值为“AUTO”，其含义是“Auto -- Table + Partition + Subpartition （10g，表+分区，当子分区是list分区时还包括子分区）”。本文就这个问题再深入地探讨一下。

大家都知道，子分区有两种，一种是分区为RANGE，子分区为HASH，另一种是分区为RANGE，子分区为LIST。在10g数据库中，如果在使用dbms_stats收集统计信息时，如果没有显式指定granularity（粒度），那么granularity就会取自dbms_stats配置：
而其缺省值是“AUTO"，而不再是9i下的”DEFAULT"：

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

PARAM
------------------------------
AUTO

而10g自带的自动收集统计信息的任务“GATHER_STATS_JOB"，其granularity同样是取自granularity param。当然可以通过下面的SQL来更改其值：

SQL> exec dbms_stats.set_param('granularity','global and partition');

这样更改后，dbms_stats默认就会收集表以及分区级统计信息，不收集子分区级统计信息。

那么，granularity=auto时，到底是怎么样的呢？前面说到了子分区是以list方式分区时，那么就会收集子分区级统计信息，其言外之意就是如果子分区是以hash方式分区时就不会收集子分区统计信息了。到底是不是这样呢？下面做个测试，测试环境是Oracle 10.2.0.4 for Linux AS4：

QL> create table t1
  2  partition by range(object_id)
  3  subpartition by hash(data_object_id)
  4  subpartitions 4
  5  ( partition p1 values less than(10000),
  6    partition p2 values less than(20000),
  7    partition p3 values less than (maxvalue)
  8  )
  9  as select * from dba_objects;  

Table created.

SQL> create table t2
  2  partition by range(object_id)
  3  subpartition by list(object_type)
  4  subpartition template(
  5    subpartition sp1 values ('TABLE'),
  6    subpartition sp2 values ('INDEX'),
  7    subpartition sp3 values ('VIEW'),  
  8    subpartition sp4 values (DEFAULT)
  9  )  
 10  ( partition p1 values less than(10000),
 11    partition p2 values less than(20000),
 12    partition p3 values less than (maxvalue)
 13  )
 14  as select * from dba_objects; 

Table created.

我们先建再从个测试表，表T1是RANGE+HASH方式的复合（组合）分区表，表T2是RANGE+LIST方式的复合分区表。
下面将"granularity" param重新设回为”auto“，然后收集T1和T2的统计信息：

SQL> exec dbms_stats.set_param('granularity','auto');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'t1');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'t2');

PL/SQL procedure successfully completed.

接下来我们看看T1和T2子分区的统计信息，首先是T1表的：

***************
SubPartition Level
***************

Partition       SubPartition            Number              Chain Average
Name            Name                   of Rows   Blocks     Count Row Len
--------------- --------------- -------------- --------  -------- -------
P1              SYS_SUBP57                                               
P2              SYS_SUBP61                                               
P3              SYS_SUBP65                                               
P2              SYS_SUBP62                                               
P3              SYS_SUBP66                                               
P1              SYS_SUBP58                                               
                SYS_SUBP59                                               
P2              SYS_SUBP63                                               
P3              SYS_SUBP67                                               
P1              SYS_SUBP60                                               
P3              SYS_SUBP68                                               
P2              SYS_SUBP64                                               

可以看到子分区没有任何统计信息，再看看T2表的子分区：

***************
SubPartition Level
***************

Partition       SubPartition            Number             Chain Average
Name            Name                   of Rows   Blocks    Count Row Len
--------------- --------------- -------------- -------- -------- -------
P2              P2_SP1                     370        6        0      92
P3              P3_SP1                     346        5        0      86
P1              P1_SP1                     818       11        0      82
P3              P3_SP2                     303        4        0      88
P1              P1_SP2                     916       12        0      85
P2              P2_SP2                     430        6        0      91
                P2_SP3                     252        4        0      86
P3              P3_SP3                     517        7        0      87
P1              P1_SP3                   2,839       34        0      78
P2              P2_SP4                   8,722      124        0      95
P1              P1_SP4                   4,973       64        0      86
P3              P3_SP4                  24,900      352        0      96

可以看到T2表的子分区是有统计信息的。 这也证明了前面说到的，如果granularity为auto，list类型的子分区会收集统计信息，而hash类型的子分区不收集统计信息。

大多数Oracle数据库里面没有使用子分区，就算有，也比较少量。不过下面一个库的一些统计数据，是我写这篇文章的初衷：

SQL> select sum(bytes)/1024/1024 size_mb from dba_data_files where tablespace_name='SYSTEM';

   SIZE_MB
----------
10233.9922

SQL> select /*+ parallel(a,8) */ count(*) from hist_head$ a;

  COUNT(*)
----------
  32643376

SQL> select /*+ parallel(a,8) */ count(*) from  HISTGRM$ a;

  COUNT(*)
----------
  14531284

SQL> select count(*) from dba_tables;

  COUNT(*)
----------
      1097

SQL> select count(*) from dba_tab_partitions;

  COUNT(*)
----------
      5492

SQL> select count(*) from dba_tab_subpartitions;

  COUNT(*)
----------
    908470

SQL> select count(*) from dba_tables where partitioned='YES';

  COUNT(*)
----------
       162
       

上面一组数据来源于某个10g的数据库，SYSTE表空间超过了10g，仅仅只有162个分区表，而子分区数达到了90万个。而与列的统计信息有关的再从个数据字典表，一个其行数达到了3200多万行，另一个其行数达到了1400多万行。终其原因，就在于这个系统太多的子分区，而绝大多数子分区是LIST类型分区的，这样收集统计信息时，会收集子分区一级统计信息，这样导致列统计信息占用了大量的SYSTEM表空间，使SYSTEM表空间暴涨。

sys.hist_head$主要存储包括high value和low value的列的统计信息，而sys.histgrm$表存储"column size > 1"时的直方图数据。看看上面提到的hist_head$表，如果一个表，有100个子分区，共有20列，那么在收集了子分区统计信息而收集统计信息时收集了所有列的统计信息的情况下，hist_head$仅包含子分区一级就会有2000行数据。这不难理解上面提到的库，hist_head$为什么会有3200多万行数据。

另外值得注意的是，如果10g开启了recyclebin特性，表只是被DROP，还未被PURGE的情况下，其统计信息仍然存储在数据字典中。

在数据库中有大量子分区时，是否应该子分区的统计信息，和怎么样收集子分区的统计信息，应结合应用情况，需要仔细考虑。应当避免如上面提到的数据库，过多的统计数据导致SYSTEM表空间暴涨。