深入分割槽表的增量統計資訊收集技術(incremetal statistics collection)
分割槽表裡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，所以這筆空間開銷還是很大的，空間換時間的又一典型。
####非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/8494287/viewspace-1411709/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Fixed Objects Statistics統計資訊收集 - 2Object
- 大資料量分割槽表統計資訊的管理大資料
- Oracle表分割槽技術概述Oracle
- [zt] 收集基於成本的優化統計資料 - 分割槽表優化
- 深入學習Oracle分割槽表及分割槽索引Oracle索引
- 深入學習分割槽表及分割槽索引(1)索引
- [筆記]statistics資訊的收集筆記
- mysql表分割槽技術詳細介紹MySql
- 資料泵匯入分割槽表統計資訊報錯（七）
- 資料泵匯入分割槽表統計資訊報錯（二）
- 資料泵匯入分割槽表統計資訊報錯（四）
- 資料泵匯入分割槽表統計資訊報錯（三）
- 資料泵匯入分割槽表統計資訊報錯（六）
- 資料泵匯入分割槽表統計資訊報錯（五）
- Oracle的分割槽索引技術Oracle索引
- 重新收集oracle表的統計資訊Oracle
- Oracle 分割槽(partition)技術Oracle
- 【YashanDB知識庫】收集分割槽表統計資訊取樣率小於1導致SQL執行計劃走偏SQL
- 深入學習分割槽表及分割槽索引(5)--建立range-hash組合分割槽(續)索引
- Mysql資料分片技術(一)——初識表分割槽MySql
- PostgreSQL：傳統分割槽表SQL
- oracle分割槽交換（exchange）技術Oracle
- 如何查詢分割槽表的分割槽及子分割槽
- oracle分割槽表和分割槽表exchangeOracle
- rebuild分割槽表分割槽索引的方法Rebuild索引
- Oracle10g 優化統計資訊(自動)不包含統計分割槽表中的local index ?Oracle優化Index
- (轉)ORACLE 分割槽表的設計Oracle
- PLSQL根據分割槽表的分割槽名批次truncate分割槽SQL
- 資料庫分割槽的文章收集資料庫
- 全面學習分割槽表及分割槽索引(13)--分隔表分割槽索引
- oracle分割槽表和非分割槽表exchangeOracle
- 【學習筆記】分割槽表和分割槽索引——分割槽表的其他管理（三）筆記索引
- 範圍分割槽表和INTERVAL分割槽表對於SPLIT分割槽的區別
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- 【統計資訊】Oracle常用的收集統計資訊方式Oracle
- mysql資料庫分割槽技術MySql資料庫
- Oracle 11g手工收集表統計資訊Oracle
- oracle 分割槽表move和包含分割槽表的lob moveOracle