“Out of Range” 谓词越界 dbms_stats.copy_table_stats

DBMS_STATS.COPY_TABLE_STATS存储过程通常用于大的分区表的统计信息复制，当增加一个新分区后，如果立即收集统计信息使用dbms_stats.gather_table_stats当前的数据又不能反应数据的真实分布，更容易因统计不准确出现错误的执行计划或out-of-range(越界)使CBO估算错误的Cardination,  所以可以使用dbms_stats.copy_table_stats直接复制上个分区的统计给新的分区，如把6月份的分区复制给7月份分区，这种帐期分区在分区表中非常常见，尤其在运营商帐务系统中甚至超过90%。但是有些需要注意如有些低版本的column low value值不对的bug, 及copy不更新global stats的column stats的low value， 同时收集global stats的对于一个上TB的分区表资源消耗的代价是巨大的。

语法：

EXEC DBMS_STATS.COPY_TABLE_STATS (‘owner’, ‘tabname’, ‘SourcePart’, ‘TargetPart’);

Oracle optimizer Blog很多年前就写过关于 out-of-range .

大表通常为了提高查询性能和简化分区管理拆成多个逻辑数据片叫做分区，Oracle 查询优化器依赖表级的统计信息（global statistics）和分区表级的统计信息（partition statistics）为一个 SQL 生成执行计划，如果查询只查询一个分区时CBO只访问分区级的统计信息，如果访问多个分区需要访问表的全局的统计信息的分区级的统计信息。

Out of range意思是where 谓词条件中提供的值超出了column上的统计信息的最小值和最大值，CBO会根据谓词值和最大值之间的距离计算Selectivity, 值离列统计信息的最大值越远记录的Selectivity就越低，这种情况常见于日期类型的范围分区。 CBO会因为统计信息列的最大值不正确，导致谓词越界，从而低估了Selectivity产生了不佳的执行计划。

从10.2.0.4以后听版本可以使用Copy table stats存储复制旧的统计到新分区，用于防止out of range, 同时该存储过程可以同时复制依赖的对象的统计信息到新的分区或子分区，如列和local partition index等。 在开始的版本中使用下面的方式复制 范围 分区列的最小值和最大值，用分区表的上限做为第一个分区的最大值，用上个分区的上限制做为下个分区的最小值 ，可以选择性的复制如rows,blks, NDV.

我们就是每个月末把本月分区的统计信息复制到下个月的分区表。在11.2.0.2及以前的版本中，可能会因为把上个分区的最小值做为下个分区的最小值产生微差的Cardination, 从而影响表之前join的方式，如201806 201807这个月份分区，201807分区的分区列的最小值可能会是201806,而不是201807， 这个在后面的版本做为BUG修复，当前版本可以从view很容易验证。

https://github.com/gregrahn/oracle-scripts/blob/master/display_raw.sql
-- 
--  display_raw.sql
--
--  DESCRIPTION
--    helper function to print raw representation of column stats minimum or maximum
--  
--  Created by Greg Rahn on 2011-08-19.
-- 

create or replace function display_raw (rawval raw, type varchar2)
return varchar2
is
    cn     number;
    cv     varchar2(32);
    cd     date;
    cnv    nvarchar2(32);
    cr     rowid;
    cc     char(32);
    cbf    binary_float;
    cbd    binary_double;
begin
    if (type = 'VARCHAR2') then
        dbms_stats.convert_raw_value(rawval, cv);
        return to_char(cv);
    elsif (type = 'DATE') then
        dbms_stats.convert_raw_value(rawval, cd);
        return to_char(cd);
    elsif (type = 'NUMBER') then
        dbms_stats.convert_raw_value(rawval, cn);
        return to_char(cn);
    elsif (type = 'BINARY_FLOAT') then
        dbms_stats.convert_raw_value(rawval, cbf);
        return to_char(cbf);
    elsif (type = 'BINARY_DOUBLE') then
        dbms_stats.convert_raw_value(rawval, cbd);
        return to_char(cbd);
    elsif (type = 'NVARCHAR2') then
        dbms_stats.convert_raw_value(rawval, cnv);
        return to_char(cnv);
    elsif (type = 'ROWID') then
        dbms_stats.convert_raw_value(rawval, cr);
        return to_char(cr);
    elsif (type = 'CHAR') then
        dbms_stats.convert_raw_value(rawval, cc);
        return to_char(cc);
    else
        return 'UNKNOWN DATATYPE';
    end if;
end;
/

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
col DATA_TYPE for a20
col TABLE_NAME for a25
col low_val for a25
col high_val for a25
col PARTITION_NAME for a15
col COLUMN_NAME for a25
select a.table_name,
   a.column_name,
   display_raw(a.low_value,b.data_type) as low_val,
   display_raw(a.high_value,b.data_type) as high_val,
   b.data_type
from
   dba_tab_col_statistics a, dba_tab_cols b
where
   a.table_name ='&tabname' and
   a.table_name=b.table_name and
   a.column_name=b.column_name and a.owner='&owner' and a.column_name='&colname'
/

select a.table_name,a.partition_name,
   a.column_name,
   display_raw(a.low_value,b.data_type) as low_val,
   display_raw(a.high_value,b.data_type) as high_val,
   b.data_type
from
   dba_part_col_statistics a, dba_tab_cols b
where
   a.table_name ='&tab_name' and
   a.table_name=b.table_name and
   a.column_name=b.column_name and a.owner='&owner' and a.column_name='&colname' and a.partition_name='&part_name'
/

Note:

使用上面的SQL可以验证表级或分区级列上的low/high 值。如果表级或分区级不正确可以使用dbms_stats.set_table_stats手动指定。 另 外copy 分区级的统计信息oracle只是自动复制分区级的相关统计信息，如自动计算分区列的low/high value,  但默认global stats不会更新。

收集全局的统计信息依赖于分区表的大小是非常消耗资源的，及时变动了很小的分区。在ORACLE 11G中， 可以从分区级得到全局的统计信息，如行数，块数就是各分区的和。 但是NDV不可以从分区级的NDV计算到， oracle为分区级每列维护了一个叫synopsis 的结构， 这样全局的NDV就通过全并所有分区的synopsis计算得到。

另外从ORACLE 11.1版本引入了incremental maintenance增量统计信息的特性，默认是半闭的，可以手动打开，然后不需要指定分区就可以根据分区级的统计信息和synopsis的改变和miss计算更新global 统计信息。在10G是用‘APPROX_GLOBAL AND PARTITION’ 的GRANULARITY参数估算更新global统计信息的。

SQL> @col2 ANBOB.ch_ANBOB_subs_target

   COLUMN_NAME    DATA_TYPE   N NUM_DISTINCT LOW_VALUE               HIGH_VALUE                                    DENSITY  NUM_NULLS NUM_BUCKETS
-------------- ----------- - ------------ ----------------------- --------------------------------------------- ---------- ---------- -----------
OBJECTTYPE     VARCHAR2    Y            2 636D4368616E6E656C      636D4F7073636F7265                                    .5          0           1
CARRYINGTYPE   VARCHAR2    Y            3 6365526563657074696F6E  636564657653756273                            .333333333          0           1
VALTYPE        VARCHAR2    Y            1 31                      31                                                     1          0           1
REMARKS        VARCHAR2    Y           41 323031343039            CAC73347CEAA33CAC73447CEAA34C6E4D3E0CEAA30    .024390244 1237658300           1
TARGET_VAL     VARCHAR2    N       727134 2D31                    B6A9BBF5BBE1B7C7B6A8D6C6BBFAC3F7D0C7BBFA      1.3753E-06          0           1
TARGET_ID      NUMBER      N          154 C3060105                C41501011C                                    .006493506          0           1
SUBSID         NUMBER      N     36642733 80                      C809453B3D1D420D06                            2.7291E-08          0           1
CYCLE          NUMBER      N           14 CBJ0F0B                CBJ1102                                      .071428571          0           1
REGION         NUMBER      N            7 C2040C                  C20424                                        .142857143          0           1

SQL> select utl_raw.cast_to_number('CBJ0F0B') from dual;

UTL_RAW.CAST_TO_NUMBER('CBJ0F0B')
----------------------------------
                            201410

SQL> select utl_raw.cast_to_number('CBJ1102') from dual;

UTL_RAW.CAST_TO_NUMBER('CBJ1102')
----------------------------------
                            201601

Note:

当前表全局的统计信息中的列LOW/HIGH VALUE与实际不符，可以使用下面的SQL手动修改LOW/High value， 前几日因为这个问题有SQL出现了性能问题，因为该库的trace_enable=false, （该参数虽可以内存级修改但也是重启实例生效）无法做10053 trace.

SQL> set serveroutput on
SQL>  declare
  2   l_srec sys.dbms_stats.statrec;
  3   l_distcnt number;
  4   l_density number;
  5   l_nullcnt number;
  6   l_avgclen number;
  7   l_statown varchar2(80);
  8  minvv NUMBER;
  9  maxvv number;
 10  dt date;
 11  maxrv raw(32) := l_srec.maxval;
 12  minrv raw(32) := l_srec.minval;
 13  SREC SYS.DBMS_STATS.STATREC;
 14  NUMVALS SYS.DBMS_STATS.numarray;
 15  
 16  BEGIN
 17  sys.dbms_stats.get_column_stats('ANBOB','CH_ANBOB_SUBS_TARGET','CYCLE',distcnt=>l_distcnt,density=>l_density,nullcnt=>l_nullcnt,srec=>l_srec,avgclen=>l_avgclen,statown=>l_statown);
 18  dbms_output.put_line('No of Distinct Values::::'||l_distcnt);
 19  dbms_output.put_line('Density              ::::'||l_density);
 20  dbms_output.put_line('NullCount            ::::'||l_nullcnt);
 21  dbms_output.put_line('Average Row Length   ::::'||l_avgclen);
 22  dbms_output.put_line('Stat Owner           ::::'||l_statown);
 23  
 24  maxrv := l_srec.maxval;
 25  minrv := l_srec.minval;
 26  
 27  dbms_stats.convert_raw_value(minrv,minvv) ;
 28  dbms_stats.convert_raw_value(maxrv,maxvv) ;
 29  dbms_output.put_line('PRE MAX VALUE ::'|| maxvv ) ;
 30  dbms_output.put_line('PRE MIN VALUE ::'|| minvv ) ;
 31  dbms_output.put_line('******************************');
 32  
 33  SREC.EPC:=2;
 34  NUMVALS:= SYS.DBMS_STATS.NUMARRAY(201708,201808);
 35  SYS.DBMS_STATS.PREPARE_COLUMN_VALUES(SREC,NUMVALS);
 36  
 37  dbms_output.put_line('******************************');
 38  SYS.DBMS_STATS.SET_COLUMN_STATS('ANBOB','CH_ANBOB_SUBS_TARGET','CYCLE',SREC=>SREC,NO_INVALIDATE =>false);
 39  COMMIT;
 40  sys.dbms_stats.get_column_stats('ANBOB','CH_ANBOB_SUBS_TARGET','CYCLE',distcnt=>l_distcnt,density=>l_density,nullcnt=>l_nullcnt,srec=>l_srec,avgclen=>l_avgclen,statown=>l_statown);
 41  
 42  maxrv := l_srec.maxval;
 43  minrv := l_srec.minval;
 44  
 45  dbms_stats.convert_raw_value(minrv,minvv) ;
 46  dbms_stats.convert_raw_value(maxrv,maxvv) ;
 47  
 48  dbms_output.put_line('POST MAX VALUE ::'||maxvv) ;
 49  dbms_output.put_line('POST MIN VALUE ::'||minvv) ;
 50  
 51  END;
 52  /
No of Distinct Values::::14
Density              ::::.0714285714285714
NullCount            ::::0
Average Row Length   ::::5
Stat Owner           ::::
PRE MAX VALUE ::201601
PRE MIN VALUE ::201410
******************************
******************************
POST MAX VALUE ::201808
POST MIN VALUE ::201708

PL/SQL procedure successfully completed.