分区表的增量统计信息收集
分区表的统计信息存在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
2 75115 150234 1 2 0 2018/9/12 7:02:12
3 75115 150236 1 3 0 2018/9/12 7:02:12
4 75115 150238 1 4 0 2018/9/12 7:02:12
5 75115 150232 2 5 0 2018/9/12 7:10:28
6 75115 150234 2 6 0 2018/9/12 7:02:12
7 75115 150236 2 7 0 2018/9/12 7:02:12
8 75115 150238 2 8 0 2018/9/12 7:02:12
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;
/
oracle分区表的增量收集统计信息
-
水逸冰
2018-09-16 00:05:11 -
Oracle
- 原创