大数据量分区表统计信息的管理

对于大数据量分区表，当数据加载后，如果不能及时更新统计信息，将导致sql产生不正确的执行计划，引起查询性能的下降，而对于一个数据加载比较频繁的分区表，往往无法在每次加载数据
后及时收集统计信息，而且对于数据量比较大的表来说，收集统计信息本身就是比较耗费资源的操作。这种情况下，可以采用DBMS_STATS.COPY_TABLE_STATS这个存储过程来将一个分区的
统计信息复制到另外一个分区，从而避免由于无法及时更新统计信息而导致的sql执行计划出现问题。
SQL> create or replace function display_raw (rawval raw, type varchar2)
  2   return varchar2
  3   is
  4      cn     number;
  5      cv     varchar2(32);
  6      cd     date;
  7      cnv    nvarchar2(32);
  8      cr     rowid;
  9      cc     char(32);
 10    begin
 11       if (type = 'NUMBER') then
 12          dbms_stats.convert_raw_value(rawval, cn);
 13          return to_char(cn);
 14       elsif (type = 'VARCHAR2') then
 15          dbms_stats.convert_raw_value(rawval, cv);
 16          return to_char(cv);
 17       elsif (type = 'DATE') then
 18          dbms_stats.convert_raw_value(rawval, cd);
 19          return to_char(cd);
 20       elsif (type = 'NVARCHAR2') then
 21          dbms_stats.convert_raw_value(rawval, cnv);
 22          return to_char(cnv);
 23       elsif (type = 'ROWID') then
 24          dbms_stats.convert_raw_value(rawval, cr);
 25          return to_char(cnv);
 26       elsif (type = 'CHAR') then
 27          dbms_stats.convert_raw_value(rawval, cc);
 28          return to_char(cc);
 29       else
 30          return 'UNKNOWN DATATYPE';
 31       end if;
 32    end;
 33    /

Function created.

SQL> set lines 132 pages 100
SQL> col COLUMN_NAME for a20
SQL> col PARTITION_NAME for a10
SQL> col low_val for a30
SQL> col high_val for a30
SQL> col data_type for a20
SQL> alter session set nls_date_format='yyyymmdd hh24:mi:ss';

Session altered.

SQL> SELECT TABLE_NAME,PARTITION_NAME,NUM_ROWS,TO_CHAR(LAST_ANALYZED,'YYYYMMDD HH24:MI:SS') FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='SALES';


TABLE_NAME                     PARTITION_NAME                   NUM_ROWS TO_CHAR(LAST_ANAL
------------------------------ ------------------------------ ---------- -----------------
SALES                          P201301                             99920 20140207 20:50:59
SALES                          P201302                             96640 20140207 20:50:59
SALES                          P201303                            100680 20140207 20:50:59
SALES                          P201304                            100500 20140207 20:50:59
SALES                          P201305                             99780 20140207 20:50:59
SALES                          P201306                                 0 20140207 20:50:59
SALES                          P201307                                 0 20140207 20:50:59
SALES                          P201308                                 0 20140207 20:50:59
SALES                          P201309                                 0 20140207 20:50:59
SALES                          P201310                                 0 20140207 20:50:59
SALES                          P201311                                 0 20140207 20:50:59
SALES                          P201312                                 0 20140207 20:50:59

12 rows selected.

SQL> select
  2       a.column_name, a.partition_name,
  3       a.num_distinct,
  4       display_raw(a.low_value,b.data_type) as low_val,
  5       display_raw(a.high_value,b.data_type) as high_val,
  6       b.data_type
  7    from
  8       dba_part_col_statistics a, dba_tab_cols b
  9    where
 10      a.owner='SYS' and
 11     b.owner='SYS' and 
 12      a.table_name='SALES' and
 13      a.table_name=b.table_name and
 14      a.column_name=b.column_name and
 15      a.column_name = 'TIME_ID' and
 16      a.low_value is not null
 17   order by 1, 2
 18   /


COLUMN_NAME          PARTITION_ NUM_DISTINCT LOW_VAL                        HIGH_VAL                       DATA_TYPE
-------------------- ---------- ------------ ------------------------------ ------------------------------ --------------------
TIME_ID              P201301               1 20130110 00:00:00              20130110 00:00:00              DATE
TIME_ID              P201302               1 20130210 00:00:00              20130210 00:00:00              DATE
TIME_ID              P201303               1 20130310 00:00:00              20130310 00:00:00              DATE
TIME_ID              P201304               1 20130410 00:00:00              20130410 00:00:00              DATE
TIME_ID              P201305               1 20130510 00:00:00              20130510 00:00:00              DATE

12 rows selected.

SQL> EXEC DBMS_STATS.COPY_TABLE_STATS (user, 'SALES', 'p201305', 'p201306', FORCE=>TRUE);

PL/SQL procedure successfully completed.

SQL> select
  2       a.column_name, a.partition_name,
  3       a.num_distinct,
  4       display_raw(a.low_value,b.data_type) as low_val,
  5       display_raw(a.high_value,b.data_type) as high_val,
  6       b.data_type
  7    from
  8       dba_part_col_statistics a, dba_tab_cols b
  9    where
 10      a.owner='SYS' and
 11     b.owner='SYS' and 
 12      a.table_name='SALES' and
 13      a.table_name=b.table_name and
 14      a.column_name=b.column_name and
 15      a.column_name = 'TIME_ID' and
 16      a.low_value is not null
 17   order by 1, 2
 18   /

COLUMN_NAME          PARTITION_ NUM_DISTINCT LOW_VAL                        HIGH_VAL                       DATA_TYPE
-------------------- ---------- ------------ ------------------------------ ------------------------------ --------------------
TIME_ID              P201301               1 20130110 00:00:00              20130110 00:00:00              DATE
TIME_ID              P201302               1 20130210 00:00:00              20130210 00:00:00              DATE
TIME_ID              P201303               1 20130310 00:00:00              20130310 00:00:00              DATE
TIME_ID              P201304               1 20130410 00:00:00              20130410 00:00:00              DATE
TIME_ID              P201305               1 20130510 00:00:00              20130510 00:00:00              DATE
TIME_ID              P201306               1 20130601 00:00:00              20130701 00:00:00              DATE

6 rows selected.

SQL> SELECT TABLE_NAME,PARTITION_NAME,NUM_ROWS,TO_CHAR(LAST_ANALYZED,'YYYYMMDD HH24:MI:SS') FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='SALES';

TABLE_NAME                     PARTITION_   NUM_ROWS TO_CHAR(LAST_ANAL
------------------------------ ---------- ---------- -----------------
SALES                          P201301        100020 20140207 08:42:09
SALES                          P201302         98820 20140207 08:42:09
SALES                          P201303         98960 20140207 08:42:09
SALES                          P201304        100520 20140207 08:42:09
SALES                          P201305        100280 20140207 08:42:09
SALES                          P201306        100280 20140207 08:42:09
SALES                          P201307             0 20140207 08:42:09
SALES                          P201308             0 20140207 08:42:09
SALES                          P201309             0 20140207 08:42:09
SALES                          P201310             0 20140207 08:42:09
SALES                          P201311             0 20140207 08:42:09
SALES                          P201312             0 20140207 08:42:09

12 rows selected.

SQL> EXEC DBMS_STATS.COPY_TABLE_STATS (user, 'SALES', 'p201305', 'p201307', FORCE=>TRUE);

PL/SQL procedure successfully completed.

SQL> select
  2       a.column_name, a.partition_name,
  3       a.num_distinct,
  4       display_raw(a.low_value,b.data_type) as low_val,
  5       display_raw(a.high_value,b.data_type) as high_val,
  6       b.data_type
  7    from
  8       dba_part_col_statistics a, dba_tab_cols b
  9    where
 10      a.owner='SYS' and
 11     b.owner='SYS' and 
 12      a.table_name='SALES' and
 13      a.table_name=b.table_name and
 14      a.column_name=b.column_name and
 15      a.column_name = 'TIME_ID' and
 16      a.low_value is not null
 17   order by 1, 2
 18   /

COLUMN_NAME          PARTITION_ NUM_DISTINCT LOW_VAL                        HIGH_VAL                       DATA_TYPE
-------------------- ---------- ------------ ------------------------------ ------------------------------ --------------------
TIME_ID              P201301               1 20130110 00:00:00              20130110 00:00:00              DATE
TIME_ID              P201302               1 20130210 00:00:00              20130210 00:00:00              DATE
TIME_ID              P201303               1 20130310 00:00:00              20130310 00:00:00              DATE
TIME_ID              P201304               1 20130410 00:00:00              20130410 00:00:00              DATE
TIME_ID              P201305               1 20130510 00:00:00              20130510 00:00:00              DATE
TIME_ID              P201306               1 20130601 00:00:00              20130701 00:00:00              DATE
TIME_ID              P201307               1 20130701 00:00:00              20130801 00:00:00              DATE

7 rows selected.

SQL> SELECT TABLE_NAME,PARTITION_NAME,NUM_ROWS,TO_CHAR(LAST_ANALYZED,'YYYYMMDD HH24:MI:SS') FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='SALES';

TABLE_NAME                     PARTITION_   NUM_ROWS TO_CHAR(LAST_ANAL
------------------------------ ---------- ---------- -----------------
SALES                          P201301        100020 20140207 08:42:09
SALES                          P201302         98820 20140207 08:42:09
SALES                          P201303         98960 20140207 08:42:09
SALES                          P201304        100520 20140207 08:42:09
SALES                          P201305        100280 20140207 08:42:09
SALES                          P201306        100280 20140207 08:42:09
SALES                          P201307        100280 20140207 08:42:09
SALES                          P201308             0 20140207 08:42:09
SALES                          P201309             0 20140207 08:42:09
SALES                          P201310             0 20140207 08:42:09
SALES                          P201311             0 20140207 08:42:09
SALES                          P201312             0 20140207 08:42:09

12 rows selected.

可以看出，对于分区键所在的列，oracle自动将列的最大值和最小值置为分区边界值，对于其它列的统计信息则是直接复制，虽然这不能完全反映数据分布的真实情况，但能最大程度的接近真实的统计信息。

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

转载于:http://blog.itpub.net/10972173/viewspace-1078328/