ITPub博客

首页 > 数据库 > Oracle > 深入分区表的增量统计信息收集技术(incremetal statistics collection)

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

原创 Oracle 作者:oliseh 时间:2014-12-19 10:19:28 0 删除 编辑
分区表里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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1616867