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

来源：这里教程网时间：2026-03-03 12:01:18 作者：

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; /