ORACLE 收集统计数据后基数估计错误

这是分区表中升序日期的常见问题。第一件事是验证增量统计数据是否有效。

SELECT notes
  FROM dba_tab_col_statistics
 WHERE table_name = 'MYTABLE'

您应该在每一列中看到“增量”。如果不这样做，那么您需要让增量统计数据正常工作。要使增量统计数据发挥作用，您必须设置以下选项：

GRANULARITY -> AUTO
INCREMENTAL -> TRUE
PUBLISH -> TRUE
ESTIMATE_PERCENT -> DBMS_STATS.AUTO_SAMPLE_SIZE
APPROXIMATE_NDV_ALGORITHM -> LIKE '%HYPERLOGLOG%'
INCREMENTAL_LEVEL -> PARTITION

然后，您必须使用这些设置收集统计数据一次，这最初将进行全局收集。首先删除表上的统计信息以从头开始可能是个好主意。然后，后续收集应该仅收集过时的分区并使用它来估计全局列最小/最大/不同值。

另外，您不希望日期列上有直方图，除非您有一个过度代表的神奇值（例如 12/31/9999），或者单行具有可追溯到 1970 年或 1900 年的虚假日期或某个愚蠢的日期到目前为止，简单的最小/最大平均值确实会很差。直方图用于倾斜，而不是像大多数日期列那样用于均匀分布的值。我想我记得读过直方图会覆盖增量统计数据，因此这可能是从始终升序的日期列中删除它们的另一个原因。在我们的大数据仓库中，我们在日期列上没有任何直方图，尤其是那些分区键的直方图。我们只依赖增量，它通常效果很好。

假设您的增量统计数据正在工作，接下来的事情是查看统计数据收集是否发生得太晚（错误的查询可能会在主要分区修改/加载之后很快执行，然后才有机会收集新数据的统计数据）。要检查这一点，请查看LAST_ANALYZED并与错误查询的开始时间和上次主要数据加载的时间进行交叉检查。

您还可以仔细检查日期列的最大值是否大致正确。您必须根据其原始值对其进行解码。以下是一组可用于显示列的最小/最大值的函数：

CREATE OR REPLACE FUNCTION CAST_RAW_TO_CHAR (bdr IN raw) 
  RETURN varchar2
  DETERMINISTIC 
AS  
BEGIN
  RETURN utl_raw.cast_to_varchar2(bdr);
EXCEPTION 
  WHEN OTHERS THEN 
    RETURN NULL;
END CAST_RAW_TO_CHAR;
/
CREATE OR REPLACE FUNCTION CAST_RAW_TO_NUMBER (bdr IN raw) 
  RETURN number
  DETERMINISTIC 
AS  
BEGIN
  RETURN utl_raw.cast_to_number(bdr);
EXCEPTION 
  WHEN OTHERS THEN 
    RETURN NULL;
END CAST_RAW_TO_NUMBER;
/
CREATE OR REPLACE FUNCTION CAST_RAW_TO_DATE (bdr IN raw) 
  RETURN date
  DETERMINISTIC 
AS  
BEGIN
  RETURN
     date'1-1-1'
     + NUMTOYMINTERVAL(
         100 * (to_number(substr(bdr,1,2), 'xx') - 100) + 
         to_number(substr(bdr,3,2), 'xx') - 101, 
       'year')
     + NUMTOYMINTERVAL(to_number(substr(bdr,5,2), 'xx')-1, 'month')
     + NUMTODSINTERVAL(to_number(substr(bdr,7,2), 'xx')-1, 'day')
     + NUMTODSINTERVAL(to_number(substr(bdr,9,2), 'xx') - 1, 'hour')   
     + NUMTODSINTERVAL(to_number(substr(bdr,11,2), 'xx') - 1, 'minute')   
     + NUMTODSINTERVAL(to_number(substr(bdr,13,2), 'xx') - 1, 'second');
EXCEPTION 
  WHEN OTHERS THEN 
    RETURN NULL;
END CAST_RAW_TO_DATE;
/

CREATE OR REPLACE FUNCTION CAST_RAW_TO_DISPLAY_STRING (bdr IN raw, data_type IN varchar2)
  RETURN varchar2
  DETERMINISTIC 
AS
BEGIN
  RETURN CASE WHEN (bdr IS NULL) THEN NULL
              WHEN (data_type LIKE '%CHAR%') THEN cast_raw_to_char(bdr) 
              WHEN (data_type = 'NUMBER') THEN TO_CHAR(cast_raw_to_number(bdr))
              WHEN (data_type = 'DATE' OR data_type LIKE '%TIMESTAMP%') THEN TO_CHAR(cast_raw_to_date(bdr))
         END;
END;
/

现在查询统计数据：

SELECT /*+ NO_MERGE(cs) NO_MERGE(tc) */
       cs.owner,
       cs.table_name,
       cs.column_name,
       cs.num_distinct,
       tc.data_type,
       cast_raw_to_display_string(cs.low_value,tc.data_type) low_value_display,
       cast_raw_to_display_string(cs.high_value,tc.data_type) high_value_display,
       cs.density,
       cs.num_nulls,
       cs.num_buckets,
       cs.last_analyzed,
       cs.sample_size,
       cs.global_stats,
       cs.user_stats,
       cs.notes,
       cs.avg_col_len,
       cs.histogram,
       cs.scope
  FROM dba_tab_col_statistics cs,
       dba_tab_cols tc
 WHERE cs.table_name = 'MYTABLE'
   AND cs.owner = tc.owner
   AND cs.table_name = tc.table_name
   AND cs.column_name = tc.column_name

当您的查询不好时（在收集统计信息之前）运行此命令，以查看最大值是否明显低于应有的值（以及可能晚几周或更长时间的日期）。这是统计数据未按时收集的另一个迹象。您可能需要在加载完成后调整统计信息收集窗口或将手动统计信息调用（不覆盖任何参数）添加到加载数据的代码中。

CARDINALITY如果所有其他方法都失败了，并且您无法获得与您的应用程序模式相符的统计数据计时，那么您始终可以投入统计数据毛巾，并使用或提示来提示查询，告诉 Oracle 期望该表中有一百万行OPT_ESTIMATE：

SELECT /*+ CARDINALITY(x,1000000) */ *
  FROM mytable x,
       someothertable y
 WHERE x.joinid = y.id

SELECT /*+ OPT_ESTIMATE(TABLE x ROWS=1000000) */ *
  FROM mytable x,
       someothertable y
 WHERE x.joinid = y.id