深入分区表的增量统计信息收集技术(incremetal statistics collection)

最新推荐文章于 2021-04-03 15:26:01 发布

cuiqiong6888

最新推荐文章于 2021-04-03 15:26:01 发布

阅读量231

点赞数

文章标签：数据库

分区表里global_stats=YES的全局统计信息是否准确关系到optimizer能否选择较优的执行计划，对分区表执行全局统计会不可避免的产生FTS加重系统负担，尤其对于DW环境里规模较大的分区表而言更是如此。incremental statistics collect正是在这一背景下应运而生，简单的说incremental statistics collect会实时记录分区表里每个partition每列值的更新情况，这一信息保存在SYSAUX表空间里，后续根据这一信息在执行全局统计时仅会针对有变化的partition进行statistics collect，并将收集的结果与没有变化过的partition原有的统计信息进行整合，计算出准确的global stats，省去了必须去扫描每一个partition的步骤。下面我们就挖掘一下 incremental statistics collect的特性

####非incremental方式下新加分区后对整个分区表收集统计信息，会全量扫描分区表中所有的分区，即使那些没有改变过的分区也会被重新扫描一遍

---候选测试表为IMS_RES_MONITOR_2，其下有476个partition
select table_name,count(*) from dba_tab_partitions group by table_name order by 2 desc;

TABLE_NAME COUNT(*)
------------------------------ ----------
IMS_RES_MONITOR_2 476

---查看该表的几个统计偏好设置，这几个值在incremental collection时需要用到，目前均为默认值
select dbms_stats.get_prefs('granularity','JD','IMS_RES_MONITOR_2') from dual;
DBMS_STATS.GET_PREFS('GRANULARITY','JD','IMS_RES_MONITOR_2')
--------------------------------------------------------------------------------------------------------------------------------------------
AUTO

select dbms_stats.get_prefs('incremental','JD','IMS_RES_MONITOR_2') from dual;
DBMS_STATS.GET_PREFS('INCREMENTAL','JD','IMS_RES_MONITOR_2')
--------------------------------------------------------------------------------------------------------------------------------------------
FALSE

select dbms_stats.get_prefs('estimate_percent','JD','IMS_RES_MONITOR_2') from dual;

DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT','JD','IMS_RES_MONITOR_2')
--------------------------------------------------------------------------------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE

---清除该表的统计信息，确保global、partition级没有统计信息
exec dbms_stats.delete_table_stats(ownname=>'JD',tabname=>'IMS_RES_MONITOR_2');
select num_rows,blocks from dba_tables where table_name='IMS_RES_MONITOR_2';
NUM_ROWS BLOCKS
---------- ----------

select table_name,partition_name,num_rows,blocks,last_analyzed from dba_tab_partitions where table_name='IMS_RES_MONITOR_2';
TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS LAST_ANALYZED
------------------------------ ------------------------------ ---------- ---------- -----------------
IMS_RES_MONITOR_2 P20130605
IMS_RES_MONITOR_2 P20130606
IMS_RES_MONITOR_2 P20130607
IMS_RES_MONITOR_2 P20130617
IMS_RES_MONITOR_2 P20130619
...省略了部分输出

---执行初始统计
alter system flush shared_pool;
alter system flush buffer_cache;
set timing on
exec dbms_stats.gather_table_stats(ownname=>'JD',tabname=>'IMS_RES_MONITOR_2');

PL/SQL procedure successfully completed.

Elapsed: 00:01:12.42

---查看初次分析后global与partition级的统计信息
select num_rows,blocks,last_analyzed from dba_tables where table_name='IMS_RES_MONITOR_2';

NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- -----------------
2276 63458 20141217 10:33:36

select table_name,partition_name,num_rows,blocks,last_analyzed from dba_tab_partitions where table_name='IMS_RES_MONITOR_2' and rownum<10
TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS LAST_ANALYZED
------------------------------ ------------------------------ ---------- ---------- -----------------
IMS_RES_MONITOR_2 P20130605 0 238 20141217 10:32:37
IMS_RES_MONITOR_2 P20130606 0 366 20141217 10:32:37
IMS_RES_MONITOR_2 P20130607 0 302 20141217 10:32:37
IMS_RES_MONITOR_2 P20130617 0 174 20141217 10:32:38
IMS_RES_MONITOR_2 P20130619 0 430 20141217 10:32:38

---添加一个分区(由于存在最大分区，所以先要把这个最大分区drop掉再重建)
select high_value,partition_name,table_name from dba_tab_partitions where table_name='IMS_RES_MONITOR_2' and partition_name='PLAST'

HIGH_VALUE PARTITION_NAME TABLE_NAME
-------------------------------------------------------------------------------- ------------------------------ ------------------------------
MAXVALUE PLAST IMS_RES_MONITOR_2

alter table jd.IMS_RES_MONITOR_2 drop partition PLAST;
alter table jd.IMS_RES_MONITOR_2 add partition p20150103 values less than (to_date('20150104','yyyymmdd')) tablespace ts_acct_dat_01;
alter table jd.IMS_RES_MONITOR_2 add partition PLAST values less than (maxvalue) tablespace ts_acct_dat_01;

---针对新建的p20150103、PLAST分区进行统计信息收集，确认这两个分区已经有最新的统计信息了
exec dbms_stats.gather_table_stats(ownname=>'JD',tabname=>'IMS_RES_MONITOR_2',partname=>'p20150103');
exec dbms_stats.gather_table_stats(ownname=>'JD',tabname=>'IMS_RES_MONITOR_2',partname=>'PLAST');

select table_name,partition_name,num_rows,blocks,last_analyzed from dba_tab_partitions where table_name='IMS_RES_MONITOR_2' and partition_name in ('P20150103','PLAST');
TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS LAST_ANALYZED
------------------------------ ------------------------------ ---------- ---------- -----------------
IMS_RES_MONITOR_2 P20150103 0 0 20141217 10:41:27
IMS_RES_MONITOR_2 PLAST 0 0 20141217 10:41:31

---重新收集global统计
alter system flush shared_pool;
alter system flush buffer_cache;
exec dbms_stats.gather_table_stats(ownname=>'JD',tabname=>'IMS_RES_MONITOR_2');

PL/SQL procedure successfully completed.

Elapsed: 00:01:00.57

---查看global和partition级的统计信息已经刷新为最新时间了
select num_rows,blocks,last_analyzed from dba_tables where table_name='IMS_RES_MONITOR_2';
NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- -----------------
2276 63458 20141217 10:45:45

---全量方式下即使没有更改过的分区的统计信息也会被重刷一遍
select table_name,partition_name,num_rows,blocks,last_analyzed from dba_tab_partitions where table_name='IMS_RES_MONITOR_2' and rownum<10;

TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS LAST_ANALYZED
------------------------------ ------------------------------ ---------- ---------- -----------------
IMS_RES_MONITOR_2 P20130605 0 238 20141217 10:44:54
IMS_RES_MONITOR_2 P20130606 0 366 20141217 10:44:54
IMS_RES_MONITOR_2 P20130607 0 302 20141217 10:44:54
IMS_RES_MONITOR_2 P20130617 0 174 20141217 10:44:54
IMS_RES_MONITOR_2 P20130619 0 430 20141217 10:44:54

####incremental方式下新加分区后对整个分区表收集统计信息，仅会读取更新过的分区表的统计信息，汇总至global统计即可
---设定incremental统计偏好，必须将INCREMENTAL设置为TRUE(默认为FALSE)、GRANULARITY和ESTIMATE_PERCENT保持默认值
exec dbms_stats.set_table_prefs('JD','IMS_RES_MONITOR_2','INCREMENTAL','TRUE');

select dbms_stats.get_prefs('granularity','JD','IMS_RES_MONITOR_2') from dual;
DBMS_STATS.GET_PREFS('GRANULARITY','JD','IMS_RES_MONITOR_2')
--------------------------------------------------------------------------------------------------------------------------------------------
AUTO

select dbms_stats.get_prefs('incremental','JD','IMS_RES_MONITOR_2') from dual;
DBMS_STATS.GET_PREFS('INCREMENTAL','JD','IMS_RES_MONITOR_2')
--------------------------------------------------------------------------------------------------------------------------------------------
TRUE

select dbms_stats.get_prefs('estimate_percent','JD','IMS_RES_MONITOR_2') from dual;

DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT','JD','IMS_RES_MONITOR_2')
--------------------------------------------------------------------------------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE

---增加一个分区
alter table jd.IMS_RES_MONITOR_2 drop partition PLAST;
alter table jd.IMS_RES_MONITOR_2 add partition p20150104 values less than (to_date('20150105','yyyymmdd')) tablespace ts_acct_dat_01;
alter table jd.IMS_RES_MONITOR_2 add partition PLAST values less than (maxvalue) tablespace ts_acct_dat_01;

---调整为incremental方式的统计耗时比非incremental方式下要长，原因是对每个分区里的每一个列新增了一个hash值，这个被oracle称为synopsis，synopsis信息存放在SYSAUX表空间，维护synopsis信息使得incremental方式下收集统计信息的耗时明显变长，关于synopsis后面会详细解释
exec dbms_stats.gather_table_stats(ownname=>'JD',tabname=>'IMS_RES_MONITOR_2');

PL/SQL procedure successfully completed.

Elapsed: 00:01:40.39

---切换为incremental方式后的首次统计收集会全量收集分区表以及分区表里所有分区的统计信息，以下查询可以看出不管是partition-level还是table-level都已经更新为最新时间戳了
select table_name,partition_name,num_rows,blocks,last_analyzed from dba_tab_partitions where table_name='IMS_RES_MONITOR_2' and partition_name in ('P20150104','PLAST');
TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS LAST_ANALYZED
------------------------------ ------------------------------ ---------- ---------- -----------------
IMS_RES_MONITOR_2 P20150104 0 0 20141217 11:01:19
IMS_RES_MONITOR_2 PLAST 0 0 20141217 11:01:54

select table_name,partition_name,num_rows,blocks,last_analyzed from dba_tab_partitions where table_name='IMS_RES_MONITOR_2' and rownum<10;
TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS LAST_ANALYZED
------------------------------ ------------------------------ ---------- ---------- -----------------
IMS_RES_MONITOR_2 P20130605 0 238 20141217 11:00:27
IMS_RES_MONITOR_2 P20130606 0 366 20141217 11:00:27
IMS_RES_MONITOR_2 P20130607 0 302 20141217 11:00:28
IMS_RES_MONITOR_2 P20130617 0 174 20141217 11:00:28
IMS_RES_MONITOR_2 P20130619 0 430 20141217 11:00:28
IMS_RES_MONITOR_2 P20130620 0 302 20141217 11:00:28
IMS_RES_MONITOR_2 P20130621 0 302 20141217 11:00:28
IMS_RES_MONITOR_2 P20130623 0 302 20141217 11:00:28
IMS_RES_MONITOR_2 P20130624 0 430 20141217 11:00:28

select num_rows,blocks,last_analyzed from dba_tables where table_name='IMS_RES_MONITOR_2';
NUM_ROWS BLOCKS LAST_ANALYZED GLO
---------- ---------- ----------------- ---
2276 63458 20141217 11:03:31 YES

---再次收集统计信息
alter system flush shared_pool;
alter system flush buffer_cache;
exec dbms_stats.gather_table_stats(ownname=>'JD',tabname=>'IMS_RES_MONITOR_2');

---执行后发现global层面的统计信息已经更新
select num_rows,blocks,last_analyzed from dba_tables where table_name='IMS_RES_MONITOR_2';
NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- -----------------
2276 63458 20141217 11:46:57

---但分区级的统计信息更新时间还停留在上次
SQL> select table_name,partition_name,num_rows,blocks,last_analyzed from dba_tab_partitions where table_name='IMS_RES_MONITOR_2' and rownum<10;

TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS LAST_ANALYZED
------------------------------ ------------------------------ ---------- ---------- -----------------
IMS_RES_MONITOR_2 P20130605 0 238 20141217 11:00:27
IMS_RES_MONITOR_2 P20130606 0 366 20141217 11:00:27
IMS_RES_MONITOR_2 P20130607 0 302 20141217 11:00:28
IMS_RES_MONITOR_2 P20130617 0 174 20141217 11:00:28
IMS_RES_MONITOR_2 P20130619 0 430 20141217 11:00:28
IMS_RES_MONITOR_2 P20130620 0 302 20141217 11:00:28
IMS_RES_MONITOR_2 P20130621 0 302 20141217 11:00:28
IMS_RES_MONITOR_2 P20130623 0 302 20141217 11:00:28
IMS_RES_MONITOR_2 P20130624 0 430 20141217 11:00:28

---再创建一个新分区
alter table jd.IMS_RES_MONITOR_2 drop partition PLAST;
alter table jd.IMS_RES_MONITOR_2 add partition p20150105 values less than (to_date('20150106','yyyymmdd')) tablespace ts_acct_dat_01;
alter table jd.IMS_RES_MONITOR_2 add partition PLAST values less than (maxvalue) tablespace ts_acct_dat_01;

---再次执行global级的统计
alter system flush shared_pool;
alter system flush buffer_cache;
set timing on
exec dbms_stats.gather_table_stats(ownname=>'JD',tabname=>'IMS_RES_MONITOR_2');

PL/SQL procedure successfully completed.

Elapsed: 00:01:45.64

---统计完后查看global和partition级的统计信息，发现其中仅新建的两个分区和全局的统计信息得到了更新
select num_rows,blocks,last_analyzed from dba_tables where table_name='IMS_RES_MONITOR_2';

NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- -----------------
2276 63458 20141217 12:01:51

SQL> select table_name,partition_name,num_rows,blocks,last_analyzed from dba_tab_partitions where table_name='IMS_RES_MONITOR_2' order by last_analyzed desc;

TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS LAST_ANALYZED
------------------------------ ------------------------------ ---------- ---------- -----------------
IMS_RES_MONITOR_2 P20150105 0 0 20141217 12:01:50
IMS_RES_MONITOR_2 PLAST 0 0 20141217 12:00:11
IMS_RES_MONITOR_2 P20141126 1 110 20141217 11:01:19
IMS_RES_MONITOR_2 P20141127 1 110 20141217 11:01:19

####incremental statistics collection是如何实现的
---本测试中选择名为P20140329的partition进行测试
select table_name,partition_name,num_rows,blocks,last_analyzed from dba_tab_partitions where table_name='IMS_RES_MONITOR_2' and partition_name='P20140329';
TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS LAST_ANALYZED
------------------------------ ------------------------------ ----------------------- ----------------------- -----------------
IMS_RES_MONITOR_2 P20140329 17 110 20141217 11:00:55

---关于Synopis，可以理解为oracle为每个partition中的每一个列值生成的一张hash表
synopsis信息存放在SYSAUX里的两张表：
sys.wri$_optstat_synopsis_head$和sys.wri$_optstat_synopsis$
表结构如下：
desc sys.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

desc sys.wri$_optstat_synopsis$
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
BO# NOT NULL NUMBER
GROUP# NOT NULL NUMBER
INTCOL# NOT NULL NUMBER
HASHVALUE NOT NULL NUMBER

BO#代表分区表的object_id，本例中object_id=3064698
select object_name,object_id from dba_objects where object_name='IMS_RES_MONITOR_2' and object_type not like '%PARTITION%'

OBJECT_NAME OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
IMS_RES_MONITOR_2 3064698

group#至除以2得到subobject_name的object_id，也就是partition的id，本例中名为P20140329的分区，其object_id=4646416，那么group#=9292832就代表了这个分区
select object_name,subobject_name,object_type,object_id from dba_objects where object_name='IMS_RES_MONITOR_2' and subobject_name='P20140329';

OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE OBJECT_ID
------------------------------ ------------------------------ ------------------- ----------
IMS_RES_MONITOR_2 P20140329 TABLE PARTITION 4646416

INTCOL#：列的序号，本例中intcol#=1表示resource_id这个字段
select table_name,column_name,column_id from dba_tab_columns where table_name='IMS_RES_MONITOR_2' and column_id=1;

TABLE_NAME COLUMN_NAME COLUMN_ID
------------------------------ ------------------------------ ----------
IMS_RES_MONITOR_2 RESOURCE_ID 1

hashvalue：针对每一个列值会生成一个hash value，这个hash value才是实现incremental statistics collect的关键，oracle会根据这个hash值是否变化来决定是否要对这个partition重新收集统计

----我们看一下IMS_RES_MONITOR_2表里名为P20140329的分区resource_id列当前的hash值，共有17个
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.bo#=b.bo# and a.bo#=3064698 and a.group#=b.group# and a.intcol#=b.intcol# and a.intcol#=1 and a.group#=9292832;

BO# GROUP# INTCOL# ANALYZETIME HASHVALUE
---------- ---------- ---------- ----------------- ----------
3064698 9292832 1 20141217 11:00:55 4446974364460335394
3064698 9292832 1 20141217 11:00:55 9361946326222687396
3064698 9292832 1 20141217 11:00:55 3217208375921250414
3064698 9292832 1 20141217 11:00:55 13942627879913658026
3064698 9292832 1 20141217 11:00:55 261920373660441827
3064698 9292832 1 20141217 11:00:55 5170298820085325499
3064698 9292832 1 20141217 11:00:55 11206406075889999811
3064698 9292832 1 20141217 11:00:55 2561514943054471972
3064698 9292832 1 20141217 11:00:55 14068223268773651280
3064698 9292832 1 20141217 11:00:55 8292967130511617500
3064698 9292832 1 20141217 11:00:55 13810791139834474882
3064698 9292832 1 20141217 11:00:55 10918063814993801858
3064698 9292832 1 20141217 11:00:55 3598794041972912951
3064698 9292832 1 20141217 11:00:55 17019049280771226092
3064698 9292832 1 20141217 11:00:55 4338555575609523184
3064698 9292832 1 20141217 11:00:55 3974316234907155776
3064698 9292832 1 20141217 11:00:55 4934883602423086651

17 rows selected.

----p20140329分区的值也有17行
select resource_id from jd.ims_res_monitor_2 partition (p20140329);

RESOURCE_ID
-----------------
40100014633293
1076127377
1037223472
40100010153870
40100012648869
1103182014
40100014934637
40100009770277
1098949940
40100011540132
40100002960909
40100013734049
40100013416379
40100010149652
1046540285
40100015074922
40100010900764

17 rows selected.

---我们对p20140329分区的数据做一下修改，看一下是否这个分区会被重新分析
select last_analyzed from dba_tab_partitions where table_name='IMS_RES_MONITOR_2' and partition_name='P20140329';

LAST_ANALYZED
-----------------
20141217 11:00:55

update jd.ims_res_monitor_2 partition (p20140329) set resource_id=1 where resource_id=40100014633293;

1 row updated.

commit;

Commit complete.

select resource_id from jd.ims_res_monitor_2 partition (p20140329)

RESOURCE_ID
---------------------
1
1076127377
1037223472
40100010153870
40100012648869
1103182014
40100014934637
40100009770277
1098949940
40100011540132
40100002960909
40100013734049
40100013416379
40100010149652
1046540285
40100015074922
40100010900764

exec dbms_stats.gather_table_stats(ownname=>'JD',tabname=>'IMS_RES_MONITOR_2');

select last_analyzed from dba_tab_partitions where table_name='IMS_RES_MONITOR_2' and partition_name='P20140329';

LAST_ANALYZED
-----------------
20141218 21:04:17

---再次检查column列的hash值发现和上一次输出相比唯一的区别在于用1049436110058863352替换了8292967130511617500，其它值均不变
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.bo#=b.bo# and a.bo#=3064698 and a.group#=b.group# and a.intcol#=b.intcol# and a.intcol#=1 and a.group#=9292832;
BO# GROUP# INTCOL# ANALYZETIME HASHVALUE
---------- ---------- ---------- ----------------- ----------
3064698 9292832 1 20141218 21:04:15 4446974364460335394
3064698 9292832 1 20141218 21:04:15 9361946326222687396
3064698 9292832 1 20141218 21:04:15 3217208375921250414
3064698 9292832 1 20141218 21:04:15 13942627879913658026
3064698 9292832 1 20141218 21:04:15 261920373660441827
3064698 9292832 1 20141218 21:04:15 5170298820085325499
3064698 9292832 1 20141218 21:04:15 11206406075889999811
3064698 9292832 1 20141218 21:04:15 2561514943054471972
3064698 9292832 1 20141218 21:04:15 14068223268773651280
3064698 9292832 1 20141218 21:04:15 13810791139834474882
3064698 9292832 1 20141218 21:04:15 10918063814993801858
3064698 9292832 1 20141218 21:04:15 3598794041972912951
3064698 9292832 1 20141218 21:04:15 17019049280771226092
3064698 9292832 1 20141218 21:04:15 1049436110058863352
3064698 9292832 1 20141218 21:04:15 4338555575609523184
3064698 9292832 1 20141218 21:04:15 3974316234907155776
3064698 9292832 1 20141218 21:04:15 4934883602423086651

###为了验证oracle是根据hash值判断是否需要重新收集统计，下面人工将修改的那一行值恢复成原值，然后人工update表a,sys.wri$_optstat_synopsis$里的hash值

---修改前先记录一下P20140329分区前一次的统计信息
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.bo#=b.bo# and a.bo#=3064698 and a.group#=b.group# and a.intcol#=b.intcol# and a.intcol#=1 and a.group#=9292832
BO# GROUP# INTCOL# ANALYZETIME HASHVALUE
----------------------- ----------------------- ----------------------- ----------------- -----------------------
3064698 9292832 1 20141219 08:49:46 4446974364460335394
3064698 9292832 1 20141219 08:49:46 9361946326222687396
3064698 9292832 1 20141219 08:49:46 3217208375921250414
3064698 9292832 1 20141219 08:49:46 13942627879913658026
3064698 9292832 1 20141219 08:49:46 261920373660441827
3064698 9292832 1 20141219 08:49:46 5170298820085325499
3064698 9292832 1 20141219 08:49:46 11206406075889999811
3064698 9292832 1 20141219 08:49:46 2561514943054471972
3064698 9292832 1 20141219 08:49:46 14068223268773651280
3064698 9292832 1 20141219 08:49:46 13810791139834474882
3064698 9292832 1 20141219 08:49:46 10918063814993801858
3064698 9292832 1 20141219 08:49:46 3598794041972912951
3064698 9292832 1 20141219 08:49:46 17019049280771226092
3064698 9292832 1 20141219 08:49:46 1049436110058863352
3064698 9292832 1 20141219 08:49:46 4338555575609523184
3064698 9292832 1 20141219 08:49:46 3974316234907155776
3064698 9292832 1 20141219 08:49:46 4934883602423086651

---修改分区表resource_id字段改回原值
update jd.ims_res_monitor_2 partition (p20140329) set resource_id=40100014633293 where resource_id=1;

commit;

select resource_id from jd.ims_res_monitor_2 partition (p20140329) ;

RESOURCE_ID
-----------------------
40100014633293
1076127377
1037223472
40100010153870
40100012648869
1103182014
40100014934637
40100009770277
1098949940
40100011540132
40100002960909
40100013734049
40100013416379
40100010149652
1046540285
40100015074922
40100010900764

---更新sys.wri$_optstat_synopsis$表里的hash值
update sys.wri$_optstat_synopsis$ set hashvalue=8292967130511617500 where bo#=3064698 and intcol#=1 and group#=9292832 and hashvalue=1049436110058863352;

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.bo#=b.bo# and a.bo#=3064698 and a.group#=b.group# and a.intcol#=b.intcol# and a.intcol#=1 and a.group#=9292832

BO# GROUP# INTCOL# ANALYZETIME HASHVALUE
----------------------- ----------------------- ----------------------- ----------------- -----------------------
3064698 9292832 1 20141219 08:49:46 4446974364460335394
3064698 9292832 1 20141219 08:49:46 9361946326222687396
3064698 9292832 1 20141219 08:49:46 3217208375921250414
3064698 9292832 1 20141219 08:49:46 13942627879913658026
3064698 9292832 1 20141219 08:49:46 261920373660441827
3064698 9292832 1 20141219 08:49:46 5170298820085325499
3064698 9292832 1 20141219 08:49:46 11206406075889999811
3064698 9292832 1 20141219 08:49:46 2561514943054471972
3064698 9292832 1 20141219 08:49:46 14068223268773651280
3064698 9292832 1 20141219 08:49:46 13810791139834474882
3064698 9292832 1 20141219 08:49:46 10918063814993801858
3064698 9292832 1 20141219 08:49:46 3598794041972912951
3064698 9292832 1 20141219 08:49:46 17019049280771226092
3064698 9292832 1 20141219 08:49:46 8292967130511617500
3064698 9292832 1 20141219 08:49:46 4338555575609523184
3064698 9292832 1 20141219 08:49:46 3974316234907155776
3064698 9292832 1 20141219 08:49:46 4934883602423086651

17 rows selected.

---又一次刷新统计信息，观察P20140329分区的统计是否会被刷新
exec dbms_stats.gather_table_stats(ownname=>'JD',tabname=>'IMS_RES_MONITOR_2');

---虽然我们提前更新了hash值，但oracle还是重新收集了一遍统计，看来内部的控制逻辑比我们想象的要复杂
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.bo#=b.bo# and a.bo#=3064698 and a.group#=b.group# and a.intcol#=b.intcol# and a.intcol#=1 and a.group#=9292832;
BO# GROUP# INTCOL# ANALYZETIME HASHVALUE
----------------------- ----------------------- ----------------------- ----------------- -----------------------
3064698 9292832 1 20141219 09:13:06 4446974364460335394
3064698 9292832 1 20141219 09:13:06 9361946326222687396
3064698 9292832 1 20141219 09:13:06 3217208375921250414
3064698 9292832 1 20141219 09:13:06 13942627879913658026
3064698 9292832 1 20141219 09:13:06 261920373660441827
3064698 9292832 1 20141219 09:13:06 5170298820085325499
3064698 9292832 1 20141219 09:13:06 11206406075889999811
3064698 9292832 1 20141219 09:13:06 2561514943054471972
3064698 9292832 1 20141219 09:13:06 14068223268773651280
3064698 9292832 1 20141219 09:13:06 8292967130511617500
3064698 9292832 1 20141219 09:13:06 13810791139834474882
3064698 9292832 1 20141219 09:13:06 10918063814993801858
3064698 9292832 1 20141219 09:13:06 3598794041972912951
3064698 9292832 1 20141219 09:13:06 17019049280771226092
3064698 9292832 1 20141219 09:13:06 4338555575609523184
3064698 9292832 1 20141219 09:13:06 3974316234907155776
3064698 9292832 1 20141219 09:13:06 4934883602423086651

由于要维护这两套synopsis表，所以不可避免的会额外占用一定的存储空间，空间的大小和分区表里分区的数量，每个分区的列数多少均有关系，就拿测试所用的表ims_res_monitor_2来举例，一共有2333行，13个列，实际使用空间为160977bytes，WRI$_OPTSTAT_SYNOPSIS_HEAD$,WRI$_OPTSTAT_SYNOPSIS$这两张表合计使用的空间为595185bytes，所以这笔空间开销还是很大的，空间换时间的又一典型。

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

转载于:http://blog.itpub.net/53956/viewspace-1372944/

cuiqiong6888

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
深入分区表的增量统计信息收集技术(incremetal statistics collection)

分区表里global_stats=YES的全局统计信息是否准确关系到optimizer能否选择较优的执行计划，对分区表执行全局统计会不可避免的产生FTS加重系统负担，尤其对于DW环境里规模较大的分区表而言更是如此。increme...
复制链接

扫一扫