Oracle 11gR2-Partitioned Tables Using Incremental Statistics

在Oracle 11g中统计信息还推出了一个叫”INCREMENTAL”的选项。该选项用于增量的方式收集分区表的全局统计信息,同时它也会收集修改了分区的统计信息。这个功能非常的实用,例如一些表是按照时间字段来分区,就打按月分区的分区表来说,4月和5月就是历史分区,而6月份就是新的分区,新的分区当然就会插入一些数据,而历史分区基本上以查询为主,很少做修改。这样数据库在收集统计信息的时候,只收集6月份分区的统计信息,同时在增量更新全局的统计信息即可。下面我们来看看具体怎么操作:

create table test1
( 
  id1 number,
  id2 varchar2(10),
  id3 varchar2(10)
)
partition by range(id1)
(
  partition p1 values less than(2500),
  partition p2 values less than(5000),
  partition p3 values less than(7500),
  partition p4 values less than(10000)
);

begin
  for i in 0..9999
  loop
    insert into test1 values(i, 'a'||i, 'b'||i);
  end loop;
  commit;
end;
/

begin
  dbms_stats.set_table_prefs( OWNNAME => 'U1',TABNAME => 'TEST1',PNAME   => 'INCREMENTAL',PVALUE  => 'TRUE');
end;
/

SQL> select * from dba_tab_stat_prefs where table_name ='TEST1';

OWNER           TABLE_NAME      PREFERENCE_NAME                PREFERENCE_VALUE
--------------- --------------- ------------------------------ ------------------------------
U1              TEST1           INCREMENTAL                    TRUE

这里我们通过dbms_stats.set_table_prefs的方法,把U1用户下的TEST1表的增量统计信息收集的功能先打开。通过查看dba_tab_stat_prefs视图确认INCREMENTAL收集的功能已经启用。INCREMENTAL参数设置成True,将仅仅针对数据修改过的分区进行增量收集,同时在更新全局的统计信息。而False则为所有的分区重新收集统计信息,同时在更新全局的统计信息。
在这里我们还有一个GRANULARITY参数,在收集统计信息的时候,可以控制对分区或者子分区进行收集。其中包含以下几种值。

AUTO -根据分区类型确定粒度。这是默认值。
GLOBAL-收集全局统计数据
GLOBAL AND PARTITION -收集全局和分区级统计信息
ALL – 收集全局,分区和子分区级统计信息
PARTITION – 收集分区级别统计信息
SUBPARTITION – 收集子分区级别的统计信息

当前我们建立的range分区,我们执行收集统计信息,他会收集修改的分区的统计信息,同时增量修改全局统计信息

begin
  dbms_stats.gather_table_stats('U1','TEST1'); 
end; 
/
SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'; 

SQL> select owner,table_name,num_rows,last_analyzed from dba_tables where table_name ='TEST1'; 

OWNER                TABLE_NAME             NUM_ROWS LAST_ANALYZED
-------------------- -------------------- ---------- -------------------
U1                   TEST1                     10000 2018-06-05 23:55:49

SQL> select partition_name,last_analyzed,num_rows from dba_tab_partitions where table_name ='TEST1' order by partition_position;

PARTITION_NAME                 LAST_ANALYZED         NUM_ROWS
------------------------------ ------------------- ----------
P1                             2018-06-05 23:55:48       2500
P2                             2018-06-05 23:55:49       2500
P3                             2018-06-05 23:55:49       2500
P4                             2018-06-05 23:55:49       2500

SQL> select column_name, num_distinct, num_nulls,HISTOGRAM from dba_tab_col_statistics where table_name='TEST1';

COLUMN_NAME      NUM_DISTINCT  NUM_NULLS
---------------- ------------ ----------
ID1                     10000          0
ID2                     10000          0
ID3                     10000          0

SQL> select o.name, c.name, decode(bitand(h.spare2, 8), 8, 'yes', 'no') incremental 
  2  from sys.hist_head$ h, sys.obj$ o, sys.col$ c 
  3  where h.obj#=o.obj# and o.obj# = c.obj# and h.intcol# = c.intcol#  and o.name='TEST1';

NAME                           NAME                           INC
------------------------------ ------------------------------ ---
TEST1                          ID1                            yes
TEST1                          ID2                            yes
TEST1                          ID3                            yes

这里让我们给分区P1增加一些数据。然后再收集一次统计信息,我们可以发现表上全局的统计信息和分区P1的统计信息上的时间更新了,而P2、P3、P4分区的时间没有变。

SQL> insert into u1.test1 partition(p1) select level, 'd'||level, 'e'||level from dual connect by level <2500; 2499 rows created. SQL> commit;
Commit complete.

SQL> begin
2 dbms_stats.gather_table_stats('U1','TEST1');
3 end;
4 /
PL/SQL procedure successfully completed.

SQL> select owner,table_name,num_rows,last_analyzed from dba_tables where table_name ='TEST1';

OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED
-------------------- -------------------- ---------- -------------------
U1 TEST1 12499 2018-06-06 00:00:01

SQL> select partition_name,last_analyzed,num_rows from dba_tab_partitions where table_name ='TEST1' order by partition_position;

PARTITION_NAME LAST_ANALYZED NUM_ROWS
------------------------------ ------------------- ----------
P1 2018-06-06 00:00:01 4999
P2 2018-06-05 23:55:49 2500
P3 2018-06-05 23:55:49 2500
P4 2018-06-05 23:55:49 2500

SQL> select column_name, num_distinct, num_nulls,HISTOGRAM from dba_tab_col_statistics where table_name='TEST1';

COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM
-------------------- ------------ ---------- ---------------
ID1 10050 0 NONE
ID2 12499 0 NONE
ID3 12407 0 NONE

可以发现,Oracle新版本吗提高了在大型分区表上收集统计信息的性能。

分享到: 更多

Post a Comment

Your email is never published nor shared. Required fields are marked *