ITPub博客

首页 > 数据库 > Oracle > oracle分区表的增量收集统计信息

oracle分区表的增量收集统计信息

Oracle 作者:desert_xu 时间:2018-09-22 23:06:23 0 删除 编辑

oracle分区表的增量收集统计信息

   转至 http://blog.itpub.net/31480688/viewspace-2214249/             作者: 水逸冰                                                                             

分区表的增量统计信息收集
分区表的统计信息存在global和partition级别两种。在收集分区表的全局统计信息的时候,不可避免的要对表的所有分区进行统计,但是由于表分区的初衷,不同分区数据变化情况很可能不一致。那么极容易出现有的分区统计信息是准的,有的分区是不准的,扫描所有分区必然造成资源多余消耗,增量收集统计信息功能就可以解决这个问题了。该功能只针对有变化的分区收集统计信息,然后将其与没有变化的分区的统计信息做合并,得到完整的表的统计信息。

scott下面恰好存在一张以前做实验的分区表
SQL> select table_owner,table_name,partition_name,tablespace_name from dba_tab_partitions where table_owner='SCOTT' and table_name='CMP';

TABLE_OWNER   TABLE_NAME   PARTITION_NAME      TABLESPACE_NAME
------------- ------------ ------------------- -------------------
SCOTT         CMP          YY1_TMP             YY1
SCOTT         CMP          YY2_TMP             YY2
SCOTT         CMP          YY3_TMP             YY3
SCOTT         CMP          T_RANGE_PMAX_TMP    YY4

SQL> select count(*) from cmp;

  COUNT(*)
----------
      4000

查看当前的全局统计信息
SQL> select table_name,num_rows,last_analyzed from dba_tables where table_name='CMP';

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
CMP                                  4000 2017-10-24 22:00:13

当前的分区级别统计信息
SQL> select table_name,partition_name,num_rows,last_analyzed from dba_tab_partitions where table_name='CMP';
TABLE_NAME           PARTITION_NAME                   NUM_ROWS LAST_ANALYZED
-------------------- ------------------------------ ---------- -------------------
CMP                  YY1_TMP                               999 2017-10-24 22:01:17
CMP                  YY2_TMP                              1000 2017-10-24 22:01:17
CMP                  YY3_TMP                              1000 2017-10-24 22:01:17
CMP                  T_RANGE_PMAX_TMP                     1001 2017-10-24 22:01:17

可以看到统计信息很陈旧了。我先收集其中一个分区的统计信息,作为对比。
exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'cmp',partname=>'YY1_TMP');

PL/SQL procedure successfully completed.


收集一个分区后的全局和分区统计信息

SQL> select table_name,num_rows,last_analyzed from dba_tables where table_name='CMP';

TABLE_NAME             NUM_ROWS LAST_ANALYZED
-------------------- ---------- -------------------
CMP                        4000 2018-09-12 05:28:45

SQL> select table_name,partition_name,num_rows,last_analyzed from dba_tab_partitions where table_name='CMP';

TABLE_NAME           PARTITION_NAME                   NUM_ROWS LAST_ANALYZED
-------------------- ------------------------------ ---------- -------------------
CMP                  YY1_TMP                               999 2018-09-12 05:28:45
CMP                  YY2_TMP                              1000 2017-10-24 22:01:17
CMP                  YY3_TMP                              1000 2017-10-24 22:01:17
CMP                  T_RANGE_PMAX_TMP                     1001 2017-10-24 22:01:17

全局统计信息已经更新为了最新的分区统计信息的时间。注意这个时间是2018-09-12 05:28:45。

现在全局收集统计信息
exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'cmp');

PL/SQL procedure successfully completed.

查看统计信息
SQL> select table_name,num_rows,last_analyzed from dba_tables where table_name='CMP';

TABLE_NAME             NUM_ROWS LAST_ANALYZED
-------------------- ---------- -------------------
CMP                        4000 2018-09-12 05:31:08

SQL>  select table_name,partition_name,num_rows,last_analyzed from dba_tab_partitions where table_name='CMP';

TABLE_NAME           PARTITION_NAME                   NUM_ROWS LAST_ANALYZED
-------------------- ------------------------------ ---------- -------------------
CMP                  YY1_TMP                               999 2018-09-12 05:31:08
CMP                  YY2_TMP                              1000 2018-09-12 05:31:08
CMP                  YY3_TMP                              1000 2018-09-12 05:31:08
CMP                  T_RANGE_PMAX_TMP                     1001 2018-09-12 05:31:08

全局和分区统计信息都已经变成了2018-09-12 05:31:08。收集全局统计信息,会把所有的分区全部收集一遍。

开启增量统计信息的条件
1.分区表的PUBLISH设为true
2.分区表的INCREMENTAL设为true
3.AUTO_SAMPLE_SIZE设为ESTIMATE_PERCENT
4.GRANULARITY设为AUTO

SQL> select dbms_stats.get_prefs('publish','scott','cmp') from dual;

DBMS_STATS.GET_PREFS('PUBLISH','SCOTT','CMP')
------------------------------
TRUE
SQL> select dbms_stats.get_prefs('granularity','scott','cmp') from dual;

DBMS_STATS.GET_PREFS('GRANULARITY','SCOTT','CMP')
------------------------------
AUTO

SQL> select dbms_stats.get_prefs('INCREMENTAL','scott','cmp') from dual;

DBMS_STATS.GET_PREFS('INCREMENTAL','SCOTT','CMP')
------------------------------
FALSE
参数都满足条件,开启增量统计信息
SQL> exec dbms_stats.set_table_prefs('scott','cmp','INCREMENTAL','TRUE');

PL/SQL procedure successfully completed.


收集一次统计信息看看
exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'cmp');

SQL> select table_name,num_rows,last_analyzed from dba_tables where table_name='CMP';

TABLE_NAME             NUM_ROWS LAST_ANALYZED
-------------------- ---------- -------------------
CMP                        4000 2018-09-12 07:02:12

SQL> select table_name,partition_name,num_rows,last_analyzed from dba_tab_partitions where table_name='CMP';

TABLE_NAME           PARTITION_NAME                   NUM_ROWS LAST_ANALYZED
-------------------- ------------------------------ ---------- -------------------
CMP                  YY1_TMP                               999 2018-09-12 07:02:12
CMP                  YY2_TMP                              1000 2018-09-12 07:02:12
CMP                  YY3_TMP                              1000 2018-09-12 07:02:12
CMP                  T_RANGE_PMAX_TMP                     1001 2018-09-12 07:02:12
可以看到,开启增量统计信息后第一次收集统计信息还是扫描了全部分区。

再次收集后查看统计信息
SQL> select table_name,num_rows,last_analyzed from dba_tables where table_name='CMP';

TABLE_NAME             NUM_ROWS LAST_ANALYZED
-------------------- ---------- -------------------
CMP                        4000 2018-09-12 07:05:11

SQL> select table_name,partition_name,num_rows,last_analyzed from dba_tab_partitions where table_name='CMP';

TABLE_NAME           PARTITION_NAME                   NUM_ROWS LAST_ANALYZED
-------------------- ------------------------------ ---------- -------------------
CMP                  YY1_TMP                               999 2018-09-12 07:02:12
CMP                  YY2_TMP                              1000 2018-09-12 07:02:12
CMP                  YY3_TMP                              1000 2018-09-12 07:02:12
CMP                  T_RANGE_PMAX_TMP                     1001 2018-09-12 07:02:12

全局统计信息时间更新了,但是分区统计信息还是之前的。

删除第一个分区10%的数据
SQL> select max(id) from cmp partition(YY1_TMP);

   MAX(ID)
----------
       999

SQL> delete from cmp where id<999*0.1+1;     

100 rows deleted.

SQL> commit;

Commit complete.

收集统计信息后查看统计信息
SQL> select table_name,num_rows,last_analyzed from dba_tables where table_name='CMP';

TABLE_NAME             NUM_ROWS LAST_ANALYZED
-------------------- ---------- -------------------
CMP                        3900 2018-09-12 07:10:28

SQL> select table_name,partition_name,num_rows,last_analyzed from dba_tab_partitions where table_name='CMP';

TABLE_NAME           PARTITION_NAME                   NUM_ROWS LAST_ANALYZED
-------------------- ------------------------------ ---------- -------------------
CMP                  YY1_TMP                               899 2018-09-12 07:10:28
CMP                  YY2_TMP                              1000 2018-09-12 07:02:12
CMP                  YY3_TMP                              1000 2018-09-12 07:02:12
CMP                  T_RANGE_PMAX_TMP                     1001 2018-09-12 07:02:12

只有全局和第一个分区统计信息更新了。
到这里增量统计信息是什么过程,大概也就明了了,后面讲深入一点东西。

前面说过了全局统计信息就是分区统计信息的汇总,这个汇总的过程不是简单的累加,因为比如num_rows行数这种信息是可以简单的累加的,但是NDV这种就不能了。比如:
partition1:1,2,3; NDV是3;
partition2:3,4;   NDV是2;
如果是简单的累加的话,那全局NDV就变成5了,这显然是不对的。
oracle为了解决这个问题,推出了解决方案:synopses
可以把synopses看做是一种特殊类型结构的统计信息。在增量统计信息开始后,每一个分区都会有一个synopses,数据库将synopses存储在sysaux表空间中,具体说是视图WRI$_OPTSTAT_SYNOPSIS_HEAD$和WRI$_OPTSTAT_SYNOPSIS$。不过这种方法也是存在两个弊端的:
1.比起不用synopses,用synopses后收集统计信息时间会久一点。
2.会增加sysaux的空间使用量,后面再说这个。

在解决NDV的问题上,oracle最开始使用的是自适应采样(adaptive sampling),具体做法就是数据库存储不同的值在一张哈希表里面,每一个不同的值会占据一个bucket,然后对半分裂bucket直到不能再分,分裂次数记为S,分裂后剩下的桶的数量即为B,NDV=B*(2^S)。这种算法占用空间非常大,而且在收集失效分区统计信息时要先删除synopses,会产生大量redo和undo。oracle在12.2使用了一个算法HyperLogLog(HLL)。熟悉redis的都知道该算法在redis中是用来基数统计的,大概就是利用分桶平均方法,分别计算每个桶内的基数,然后取平均值进行估计。
APPROXIMATE_NDV_ALGORITHM参数控制具体使用哪种估算方法。它有三种取值:
1.REPEAT OR HYPERLOGLOG
2.ADAPTIVE SAMPLING
3.HYPERLOGLOG
第一种是默认的,如果之前是自适应采样或者HLL,那么还是按照之前的形式,如果第一次,那么就用HLL算法。

当APPROXIMATE_NDV_ALGORITHM设置为HYPERLOGLOG时,INCREMENTAL_STALENESS参数可以控制是否synopses被认为是过期的。
1.ALLOW_MIXED_FORMAT
2.null
第一个参数自动收集统计信息job允许自适应采样和HLL同时存在,第二个参数会自动触发收集所有stale的统计信息,收集过程中强制用APPROXIMATE_NDV_ALGORITHM设置的HYPERLOGLOG。

再来看看那两张动态性能视图
SQL> desc WRI$_OPTSTAT_SYNOPSIS_HEAD$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 BO#                                       NOT NULL NUMBER
 GROUP#                                    NOT NULL NUMBER
 INTCOL#                                   NOT NULL NUMBER
 SYNOPSIS#                                 NOT NULL NUMBER
 SPLIT                                              NUMBER
 ANALYZETIME                                        DATE
 SPARE1                                             NUMBER
 SPARE2                                             CLOB

BO#:object_id
GROUP#:partition的object_id*2
INTCOL#:列的序号

select * from WRI$_OPTSTAT_SYNOPSIS_HEAD$
       BO#    GROUP#    INTCOL#    SYNOPSIS#    SPLIT    ANALYZETIME    SPARE1    SPARE2
1    75115    150232    1    1    0    2018/9/12 7:10:28        <CLOB>
2    75115    150234    1    2    0    2018/9/12 7:02:12        <CLOB>
3    75115    150236    1    3    0    2018/9/12 7:02:12        <CLOB>
4    75115    150238    1    4    0    2018/9/12 7:02:12        <CLOB>
5    75115    150232    2    5    0    2018/9/12 7:10:28        <CLOB>
6    75115    150234    2    6    0    2018/9/12 7:02:12        <CLOB>
7    75115    150236    2    7    0    2018/9/12 7:02:12        <CLOB>
8    75115    150238    2    8    0    2018/9/12 7:02:12        <CLOB>

11g:
SQL> desc WRI$_OPTSTAT_SYNOPSIS$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SYNOPSIS#                                 NOT NULL NUMBER
 HASHVALUE                                 NOT NULL NUMBER
这里这个哈希值其实也是HLL算法里面会用到的。

12.2c
desc WRI$_OPTSTAT_SYNOPSIS$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 BO#                                       NOT NULL NUMBER
 GROUP#                                    NOT NULL NUMBER
 INTCOL#                                   NOT NULL NUMBER
 HASHVALUE                                 NOT NULL NUMBER


11g:   
select a.bo#, a.group#, a.intcol#, a.analyzetime, b.hashValue
  from sys.wri$_optstat_synopsis_head$ a, sys.wri$_optstat_synopsis$ b
 where a.SYNOPSIS# = b.SYNOPSIS#
   and a.bo# = 75115
   and a.group# = 150232
   and a.intcol# = 1
   and a.group# = 150232;  
上面的sql返回了899行,a.group# = 150232和a.intcol# = 1确定了是第一个分区的第一列,也就是ID列。
select a.bo#, a.group#, a.intcol#, a.analyzetime, b.hashValue
  from sys.wri$_optstat_synopsis_head$ a, sys.wri$_optstat_synopsis$ b
 where a.SYNOPSIS# = b.SYNOPSIS#
   and a.bo# = 75115
   and a.group# = 150232
   and a.intcol# = 2;
将intcol#列改成2之后,代表的是name列,name列其实都是一个值,上述sql返回结果也只有1行。
如下:
SQL> select count(distinct id),count(distinct name) from cmp partition(YY1_TMP);

COUNT(DISTINCTID) COUNT(DISTINCTNAME)
----------------- -------------------
              899                   1


12c中默认APPROXIMATE_NDV_ALGORITHM
MING@PDBMING(MING)> select dbms_stats.get_prefs('approximate_ndv_algorithm','ming','cmp') from dual;

DBMS_STATS.GET_PREFS('APPROXIMATE_NDV_ALGORITHM','MING','CMP')
---------------------------------------------
REPEAT OR HYPERLOGLOG

更改approximate_ndv_algorithm
BEGIN
  DBMS_STATS.SET_TABLE_PREFS
    (   ownname => 'ming'
    ,   tabname => 'cmp'
    ,   pname   => 'approximate_ndv_algorithm'
    ,   pvalue  => 'hyperloglog' );
END;
/

BEGIN
  DBMS_STATS.SET_TABLE_PREFS
    (   ownname => 'ming'
    ,   tabname => 'cmp'
    ,   pname   => 'approximate_ndv_algorithm'
    ,   pvalue  => 'ADAPTIVE SAMPLING' );
END;
/


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21980353/viewspace-2214735/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2013-10-23

  • 博文量
    79
  • 访问量
    244431