什么是global statistics?

大家都知道,dbms_stats是Oracle 9i及后续的版本中用于收集优化器统计信息的包,虽然analyze命令也一直可用,但是现在已经不推荐使用analyze来收集统计信息,而是使用dbms_stats。二者之间一个很大的不同,也是dbms_stats一个很突出的优点就是能够正确收集分区表的统计信息,换言之就是global statistics。而analyze命令只会收集最低层次对象的统计信息,然后推导和汇总出高一级的统计信息,如分区表只会收集分区信息,然后再汇总所有分区的统计信息,得到表一级的统计信息。

那什么是global statistics?简单地说global statistics就是指直接从对象本身这一级收集到的统计信息,而不是从下一级对象“推导”或“汇总”出来的统计信息。比如,表的global statistics指直接通过表收集到的统计信息,而不是从分区收集的统计信息进行汇总或推导出的。同样,分区的global statistics是指直接通过分区收集到的统计信息,而不是从子分区收集的统计信息进行汇总或推导出的。global statistics对优化器来说是非常重要的,一个SQL,除非其查询条件限定了数据只在部分分区上,否则在大多数情况下需要global statistics才能得到正确的执行计划。

有的统计值可以从对象的下一级对象进行汇总后得到,比如表的总行数,可以通过各分区的行数相加得到。但有的统计值则不能从下一级对象得到,比如列上的唯一值数量(distinct value)以及密度值(density)。

怎样收集global statistics?

global statistics只能通过dbms_stats包来收集。注意,用analyze命令得到的统计信息,虽然也会有表一级的统计值,但是,那些值是从分区或子分区推导和汇总出来的,是不精确的。后面的实验中,将会验证这一点。

使用dbms_stats收集统计信息时,参数granularity(比如gather_table_stats过程) 指定了哪个级别上的统计信息会被收集:

  • Global -- Table (表)
  • Partition -- Partition (分区)
  • SubPartition -- SubPartition (子分区)
  • Default -- Table + Partition (表+分区)
  • All -- Table + Partition + Subpartition (表+分区+子分区)
  • Auto -- Table + Partition + Subpartition (10g,表+分区,当子分区是list分区时还包括子分区)

比如,要在一个做为子分区的表上,以'ALL'级别收集统计信息时,会收集”表+分区+子分区“上的统计信息,相当于需要执行下面的三类SQL:

  • select .... from table
  • select .... from table (partition P1) -- 在每个分区上都重复一次
  • select .... from table (subpartition SP1) -- 在每个子分区上重复一次

可以看到,dbms_stats需要比analyze命令更多的时间来进行统计信息的收集。对于有子分区的表,dbms_stats至少要多花三倍的时间。

怎样查看表是否收集了global statistics?

统计信息(下面主要描述表和分区,不涉及索引)一般是通过查看下列视图来检查的:

  • 表的统计信息:
  • [DBA|ALL|USER]_TABLES
    [DBA|ALL|USER]_TAB_COL_STATISTICS

  • 分区的统计信息:
  • [DBA|ALL|USER]_TAB_PARTITIONS
    [DBA|ALL|USER]_PART_COL_STATISTICS

  • 子分区的统计信息:
  • [DBA|ALL|USER]_TAB_SUBPARTITIONS
    [DBA|ALL|USER]_SUBPART_COL_STATISTICS

在以上的视图中,如果GLOBAL_STATS列值为”YES“,表示该级对象上收集了global statistics,而值为”NO“则表示没有收集global statistics。

下面用一些实验来更深入地验证dbms_stats,analyze以及global statistics三者之间的联系。测试环境为Linux AS4上的Oracle 10.2.0.3。注意不同的Oracle可能有不同的行为。

我们先用下面的SQL创建一个测试表,这个表有2个分区,每个分区有2个子分区:

CREATE TABLE T3(i number,  p number,sp number)
PARTITION BY RANGE(p)
SUBPARTITION BY HASH(sp) SUBPARTITIONS 2 
(
  PARTITION q1 VALUES LESS THAN(3) TABLESPACE USERS,
   PARTITION q2 VALUES LESS THAN(MAXVALUE) TABLESPACE USERS
);

declare 
  i number; 
begin 
  for i in 1..100000 loop 
    insert into T3 values(i,mod(i,7), mod(i,8)); 
    if( mod(i, 1000) = 0) then commit; end if; 
  end loop; 
for i in 1..50000 loop 
    insert into T3 values(i,mod(i,7), mod(i,8)+5); 
    if( mod(i, 1000) = 0) then commit; end if; 
  end loop; 
end; 
/

我们向测试表中插入15W行数据。I列有100,000个不同的值,P列有7个不同的值,SP列有13个不同的值。为了能够方便地查看收集的统计信息,我们使用了一段来自Metalink的sql代码,点击此处下载此代码文件sosi.sql

首先用@sosi可以看到,T3没有任何统计信息存在。
使用analyze table t3 compute statistics;收集T3表的统计信息,可以看到如下的统计信息(由于版面原因,去除了一些与本文无关的信息):

SQL> analyze table t3 compute statistics;
SQL> @sosi
SQL> set echo off

Tables owned by TEST
------------------------------
T3

Please enter Name of Table Owner (Null = TEST):
Please enter Table Name to show Statistics for: T3

***********
Table Level
***********

Table  Number           Empty Average Chain Average Global User   Sample 
Name  of Rows   Blocks Blocks   Space Count Row Len Stats  Stats    Size 
------------- -------- ------ ------- ----- ------- ------ ------ ------ 
T3    150,000      328     32     921     0      13 NO     NO          0

Column Column    Distinct          Number Number Global User   Sample 
Name   Details     Values Density Buckets  Nulls Stats  Stats    Size 
------ ------------------ ------- ------- ------ ------ ------ ------ 
I      NUMBER(22)  59,845       0       1      0 NO     NO            
P      NUMBER(22)       7       0       1      0 NO     NO            
SP     NUMBER(22)       8       0       1      0 NO     NO            

***************
Partition Level
***************
Partition  Number           Empty Average Chain Average Global User   Sample 
Name      of Rows   Blocks Blocks   Space Count Row Len Stats  Stats    Size 
--------- ------- -------- ------ ------- ----- ------- ------ ------ ------ 
Q1         64,285      138     14     878     0      13 NO     NO            
Q2         85,715      190     18     954     0      14 NO     NO            

Partition Column Distinct          Number Number Global User   Sample 
Name      Name     Values Density Buckets  Nulls Stats  Stats    Size 
--------- ------ -------- ------- ------- ------ ------ ------ ------ 
Q1        I        44,878       0       1      0 NO     NO            
          P             3       0       1      0 NO     NO            
          SP            8       0       1      0 NO     NO            
Q2        I        59,844       0       1      0 NO     NO            
          P             4       0       1      0 NO     NO            
          SP            8       0       1      0 NO     NO       
***************
SubPartition Level
***************

Partition SubPartition  Number           Empty Average Average Global User   Sample 
Name      Name         of Rows   Blocks Blocks   Space Row Len Stats  Stats    Size 
--------- ------------ ------- -------- ------ ------- ------- ------ ------ ------ 
Q2        SYS_SUBP31    35,715       80      8     992      14 NO     NO     35,715 
Q1        SYS_SUBP29    26,785       58      6     902      14 NO     NO     26,785 
Q2        SYS_SUBP32    50,000      110     10     928      14 NO     NO     50,000 
Q1        SYS_SUBP30    37,500       80      8     861      13 NO     NO     37,500 

Partition SubPartition    Column  Distinct          Number Number Global User   Sample 
Name      Name            Name      Values Density Buckets  Nulls Stats  Stats    Size 
--------- --------------- ------- -------- ------- ------- ------ ------ ------ ------ 
Q1        SYS_SUBP29      I         24,106       0       1      0 NO     NO     26,785 
          SYS_SUBP29      P              3       0       1      0 NO     NO     26,785 
          SYS_SUBP29      SP             5       0       1      0 NO     NO     26,785 
          SYS_SUBP30      I         32,142       0       1      0 NO     NO     37,500 
          SYS_SUBP30      P              3       0       1      0 NO     NO     37,500 
          SYS_SUBP30      SP             8       0       1      0 NO     NO     37,500 
Q2        SYS_SUBP31      I         32,144       0       1      0 NO     NO     35,715 
          SYS_SUBP31      P              4       0       1      0 NO     NO     35,715 
          SYS_SUBP31      SP             5       0       1      0 NO     NO     35,715 
          SYS_SUBP32      I         42,858       0       1      0 NO     NO     50,000 
          SYS_SUBP32      P              4       0       1      0 NO     NO     50,000 
          SYS_SUBP32      SP             8       0       1      0 NO     NO     50,000

由上面的表可以看到,analyze只是收集了sub partition的统计信息,然后经过汇总和推导得到了partition和table级别的统计信息。global_stats列值均为"NO"。在表级的I字段的统计信息上看到,I的唯一值个数为59,845,而实际的值应该为100,000,这里差距是比较大的;SP的唯一值个数为8,而实际的值应该为13。

下面再将统计信息删除,然后使用dbms_stats重新收集统计信息:

SQL> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T3',granularity=>'ALL');
SQL> @sosi
SQL> set echo off

Tables owned by TEST
------------------------------
T3

Please enter Name of Table Owner (Null = TEST):
Please enter Table Name to show Statistics for: T3

***********
Table Level
***********

Table   Number           Empty Average Chain Average Global User    Sample 
Name   of Rows   Blocks Blocks   Space Count Row Len Stats  Stats     Size 
------ ------- -------- ------ ------- ----- ------- ------ ------ ------- 
T3     150,000      328      0       0     0      10 YES    NO     150,000 

Column Column      Distinct          Number Number Global User    Sample Date
Name   Details       Values Density Buckets  Nulls Stats  Stats     Size MM-DD-YYYY
------ ----------- -------- ------- ------- ------ ------ ------ ------- ----------
I      NUMBER(22)   100,000       0       1      0 YES    NO     150,000 12-15-2008
P      NUMBER(22)         7       0       1      0 YES    NO       5,497 12-15-2008
SP     NUMBER(22)        13       0       1      0 YES    NO       5,497 12-15-2008
***************
Partition Level
***************

Partition  Number           Empty Average Chain Average Global User   Sample 
Name      of Rows   Blocks Blocks   Space Count Row Len Stats  Stats    Size 
--------- ------- -------- ------ ------- ----- ------- ------ ------ ------ 
Q1         64,285      138      0       0     0      10 YES    NO     64,285 
Q2         85,715      190      0       0     0      10 YES    NO     85,715 

Partition Column  Distinct          Number Number Global User   Sample 
Name      Name      Values Density Buckets  Nulls Stats  Stats    Size 
--------- ------- -------- ------- ------- ------ ------ ------ ------ 
Q1        I         42,857       0       1      0 YES    NO     64,285 
          P              3       0       1      0 YES    NO      5,584 
          SP            13       0       1      0 YES    NO      5,584 
Q2        I         57,143       0       1      0 YES    NO     85,715 
          P              4       0       1      0 YES    NO      5,443 
          SP            13       0       1      0 YES    NO      5,443

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

Partition SubPartition   Number           Empty Average Average Global User  Sample 
Name      Name          of Rows   Blocks Blocks   Space Row Len Stats  Stats   Size 
--------- ------------- ------- -------- ------ ------- ------- ------ ----- ------ 
Q2        SYS_SUBP31     35,715       80      0       0      10 YES    NO    35,715 
Q1        SYS_SUBP29     26,595       58      0       0      10 YES    NO     5,461 
Q2        SYS_SUBP32     50,000      110      0       0      10 YES    NO    50,000 
Q1        SYS_SUBP30     37,500       80      0       0      10 YES    NO    37,500

Partition  SubPartition    Column  Distinct          Number Number Global User   Sample 
Name       Name            Name      Values Density Buckets  Nulls Stats  Stats    Size 
---------- --------------- ------- -------- ------- ------- ------ ------ ------ ------ 
Q1         SYS_SUBP29      I         26,084       0       1      0 YES    NO      5,461 
           SYS_SUBP29      P              3       0       1      0 YES    NO      5,461 
           SYS_SUBP29      SP             5       0       1      0 YES    NO      5,461 
           SYS_SUBP30      I         32,142       0       1      0 YES    NO     37,500 
           SYS_SUBP30      P              3       0       1      0 YES    NO      5,459 
           SYS_SUBP30      SP             8       0       1      0 YES    NO      5,459 
Q2         SYS_SUBP31      I         32,144       0       1      0 YES    NO     35,715 
           SYS_SUBP31      P              4       0       1      0 YES    NO      5,605 
           SYS_SUBP31      SP             5       0       1      0 YES    NO      5,605 
           SYS_SUBP32      I         42,858       0       1      0 YES    NO     50,000 
           SYS_SUBP32      P              4       0       1      0 YES    NO      5,500 
           SYS_SUBP32      SP             8       0       1      0 YES    NO      5,500

从上面的统计信息可以看到,不管是在partition级,还是在table级,I列和SP列的唯一值个数都得到了正确的值。在三种级别(table,partition,subpartition)上,global_stats列值都为“YES”,表示三个级别收集的都是global statistics。

将统计信息删除,使用exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T3',granularity=>'SUBPARTITION');
收集子分区级别上的统计信息(此处不再列出具体的统计信息数据),则子分区统计信息中的global_stats值为"YES",而分区和表级统计信息中的global_stats值为”NO",这两级的统计数据则是由子分区级统计信息汇总和推导得出。
而使用exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T3',granularity=>'PARTITION');收集分区级上的统计信息,则子分区没有统计信息,表级的统计信息也是由分区级统计信息推导和汇总而来。
而在缺省情况下,即granularity参数为default值时,分区和表都有global statistics,而子分区级没有任何统计信息 。

在以上的测试中,在进行新的统计信息收集之前,都是先删除了原来的统计信息。但是如果我们在收集统计信息之前没有删除原来的统计数据,并且不同的统计策略,会有不同的影响。但大部分都是很不好的影响,容易造成统计信息的不准确。比如:

  • 使用dbms_stats以granularity='ALL'收集统计信息,删除一部分数据之后,再以更低级别的粒度收集统计数据:

SQL> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T3',granularity=>'ALL');
SQL> delete from t3 where i<3000;
SQL> commit;
SQL> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T3',granularity=>'PARTITION');
SQL>

然后对比统计信息,可以发现,表级和子分区级仍然保留着原来的过时的统计信息。综合其他的几个测试(此处不再列出),我们可以得到这个结论:使用dbms_stats收集统计信息,粒度(granularity)为较低级别时,更低级别的统计信息保持不变,而高级别的统计信息由收集的这一级别的统计信息推导和汇总得出,但不能跨级别推导和汇总。但如果高级别的统计信息之前是global statistics,则那一级别的统计信息保持不变。analyze始终是在子分区级别收集统计信息,其高级别统计信息的推导和汇总的行为与dbms_stats一样。比如,现在表级的统计信息global_stats为NO,分区级的统计信息global_stats为YES,使用SUBPARTITION级别或使用analyze命令分析后,分区级的统计信息仍然是原来的统计信息,而表级的统计信息其global_stats为NO,但由于不能跨级推导和汇总,所以表这一级的统计信息仍然是原来的统计信息。

  • 使用dbms_stats以granularity='PARTITION'收集统计信息,然后使用analyze删除统计信息:

SQL> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T3',granularity=>'PARTITION');
SQL> analyze table t3 delete statistics;
SQL> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T3',granularity=>'ALL');
SQL> analyze table t3 delete statistics;

执行上面的命令序列之后,我们可以发现,analyze ..delete statistics命令总是会删除子分区的统计信息,但分区级和表级,如果有global statistics统计信息,则不会删除。并且不能跨级删除,比如分区有global statistics,由表级没有global statistics,表级的统计信息也不会被删除。综合其他几个测试,也可以得到:如果某一级(partition,subpartition)上有统计信息,那么比它更高的所有级别上都会有统计信息。

  • 各种情况下使用dbms_stats.delete_table_stats删除统计信息,可以发现:

dbms_stats.delete_table_stats总是可以删除表上所有的三个级别的统计信息。

  • 使用analyze命令收集的统计信息,有如下特点:
  • analyze命令收集的统计信息,global_stats总是为"NO“。

    综合以上测试和得到的结果,总结出我们在收集优化统计信息需要注意以下几个方面:

  • 分区表的统计信息使用dbms_stats包中的过程,这样才能收集global statistics。
  • 删除表的统计信息使用dbms_stats包中的过程,analyze命令删除统计信息,可能不是你想要的结果,因为有可能部分统计信息并没有被删除。
  • 收集统计信息时,使用一致的granularity参数,比如开始用ALL,过一段时间用DEFAULT或PARTITION,这样得出的结果,在某些级别上的统计信息是过时的,不准确的。
  • 由于analyze命令只在最低一级收集统计信息,而高一级的统计信息是下级统计信息汇总和推导而来。因此不要将analyze命令与dbms_stats混合使用于同一张表上,其原因如同上一条。

本文涉及到的内容,只有表(并且是普通的HEAP表),不包括索引。也没有涉及到直方图。本文部分内容来源于Metalink Note ID:236935.1 ”Global statistics - An Explanation“

如果需要深入研究analyze和dbms_stats,感兴趣的可以使用sql trace或10046事件来跟踪二者的行为。

Trackback

9 comments untill now

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

  2. 弱弱的问下:
    1. 由上面的表可以看到,analyze只是收集了sub partition的统计信息,然后经过汇总和推导得到了partition和table级别的统计信息–>这个怎么看出来的呢?
    很懒的问下:
    2. 如果不是分区表, analyze 和 dbms_stats 两种方法收集信息的准确度应该没啥区别吧?

    老熊 Reply:

    @zhongsr,
    1.从表和分区的统计信息中的global stats=NO可以看出来
    2.analyze和dbms_stats之间的准确度对比,各版本不尽相同,但总的来说,dbms_stats越来越完善。

  3. 大師級人物﹐實驗確實是詳細啊

  4. 请教下:文中提到“使用dbms_stats收集统计信息,粒度(granularity)为较低级别时,更低级别的统计信息保持不变,而高级别的统计信息由收集的这一级别的统计信息推导和汇总得出,但不能跨级别推导和汇总”和“dbms_stats.gather_table_stats(ownname=>user,tabname=>’T3′,granularity=>’SUBPARTITION’);
    收集子分区级别上的统计信息(此处不再列出具体的统计信息数据),则子分区统计信息中的global_stats值为”YES”,而分区和表级统计信息中的global_stats值为”NO”,这两级的统计数据则是由子分区级统计信息汇总和推导得出。”是否矛盾,前者说不能跨级推导,后者说分区和表级都可以推出。

    老熊 Reply:

    这里跨级的意思是中间隔了一级。比如从SUB PARTITION直接推导出TABLE级的统计信息。
    在执行dbms_stats.gather_table_stats(ownname=>user,tabname=>’T3′,granularity=>’SUBPARTITION’);这条语句时,如果T3表的partition级已经有了global的统计信息,那么表级的统计信息就不能由sub partition级推导而出。

  5. 老熊,问一下,如果一个分区表,已经有部分分区收集了统计信息,而现在想收集表和剩下的分区的,改用那个参数?用AUTO吗?

    老熊 Reply:

    如果只想收集指定分区的统计信息,那么你需要指定partname为指定的分区名,granularity参数设置为partition。对于表一级统计信息,你需要将granularity参数指定为global。

  6. lianjie1229 @ 2012-09-08 11:49

    老熊你好,遇到如下问题,很困惑
    用户表在月份字段上分区,每个分区的数据量在1500万左右,现在总共有100个分区 oracle 10G
    问题一:oracle自带的收集统计信息的job不分析本表
    问题二:使用dbms_stats包收集单分区统计信息是很慢,一般在2个小时左右,不知道咋回事,语句如下:dbms_stats.gather_table_stats(ownname=>’DWV’,tabname=>’T_USERS′,partname=>’P20120906’granularity=>’PARTITION’);

Add your comment now