Oracle 11g分区表上的并发统计信息收集

Question

我正在Oracle 11g上开发一个DWH。我们有一些按值划分的大表(250+百万行)。每个分区分配给不同的喂食源，每个分区独立于其他分区，因此可以同时加载和处理它们。

数据分布非常不均匀，我们有数百万行的分区和不超过100行的分区，但是我没有选择分区方案，顺便说一句，我不能改变它。

考虑到数据量，我们必须确保每个分区都有最新的统计数据，因为如果后续的详细说明没有对数据的最佳访问，它们将永远持续。

因此，对于每个并发ETL线程，我们

1. 截断分区
2. 从分期区域加载数据

SELECT /*+ APPEND */ INTO big_table PARTITION(part1) FROM temp_table WHERE partition_colum = PART1

(这样我们就有了直接路径，而不是锁定整个表)

1. 我们收集修改后的分区的统计信息。

在项目的第一阶段，我们使用了APPROX_GLOBAL_AND_PARTITION策略，并且非常有魅力。

 dbms_stats.gather_table_stats(ownname=>myschema,
                              tabname=>big_table,
                              partname=>part1,
                              estimate_percent=>1,
                              granularity=>'APPROX_GLOBAL_AND_PARTITION',
                              CASCADE=>dbms_stats.auto_cascade,
                              degree=>dbms_stats.auto_degree) 

但是，我们的缺点是，当我们加载一个小分区时，APPROX_GLOBAL部分占主导地位(仍然比全局分区快得多)，对于一个小分区，我们有10秒的加载时间和20分钟的统计数据。

因此，我们建议切换到11g的增量STATS特性，这意味着您没有指定已修改的分区，而是将所有参数保留在auto中，而Oracle具有魔力，可以自动理解哪个分区已被访问。它实际上起作用了，我们真的加快了小分区的速度。打开该功能后，调用变成

 dbms_stats.gather_table_stats(ownname=>myschema,
                              tabname=>big_table,
                              estimate_percent=>dbms_stats.auto_sample_size,
                              granularity=>'AUTO',
                              CASCADE=>dbms_stats.auto_cascade,
                              degree=>dbms_stats.auto_degree) 

注意，您不再传递分区，也没有指定示例百分比。

但是，我们有一个缺点，也许比前一个更糟糕，这与我们所拥有的高度并行性有关。

假设我们有两个同时开始的大分区，它们将几乎同时完成加载阶段。

1. 第一个线程结束insert语句，提交并启动stats收集。stats过程注意到有两个分区被修改(这是正确的，一个是满的，第二个是截断的，一个正在进行中)，正确地更新两个分区的状态。
2. 最后，第二个分区结束，收集统计数据，它将看到所有已更新的分区，什么也不做(这是不正确的，因为第二个线程同时提交了数据)。

结果是：

PARTITION NAME | LAST ANALYZED        | NUM ROWS | BLOCKS | SAMPLE SIZE
-----------------------------------------------------------------------
PART1          | 04-MAR-2015 15:40:42 | 805731   | 20314  | 805731
PART2          | 04-MAR-2015 15:41:48 | 0        | 16234  | (null)

其结果是，我偶尔会遇到一些非最优的计划(这意味着终止会话，手动刷新统计数据，再次手动启动进程)。

我甚至尝试在收集上设置一个独占锁，所以只有一个线程可以同时在同一个表上收集统计数据，但是没有什么改变。

IMHO --这是一种奇怪的行为，因为stats过程(第二次调用它时)应该检查第二个分区上的最后一次提交，并且应该看到它比上次统计数据收集时间更新。但似乎这事没有发生。

我做错了什么吗？是甲骨文的错误吗？如何保证所有的统计数据都是最新的，启用增量统计功能，以及高度的并发性？

Answer 1

我设法在这个功能上达成了一个很好的妥协。

PROCEDURE gather_tb_partiz(
    p_tblname IN VARCHAR2,
    p_partname IN VARCHAR2)
IS
  v_stale all_tab_statistics.stale_stats%TYPE;
BEGIN
  BEGIN
    SELECT stale_stats
    INTO v_stale
    FROM user_tab_statistics
    WHERE table_name = p_tblname
    AND object_type = 'TABLE';
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    v_stale := 'YES';
  END;
  IF v_stale = 'YES' THEN
    dbms_stats.gather_table_stats(ownname=>myschema, 
                                  tabname=> p_tblname,
                                  partname=>p_partname,
                                  degree=>dbms_stats.auto_degree,
                                  granularity=>'APPROX_GLOBAL AND PARTITION') ;
  ELSE
    dbms_stats.gather_table_stats(ownname=>myschema,
                                 tabname=>p_tblname,
                                 partname=>p_partname,
                                 degree=>dbms_stats.auto_degree,
                                 granularity=>'PARTITION') ;
  END IF;
END gather_tb_partiz;

在每个ETL的末尾，如果添加/删除/修改行的数量足够低，不能将表标记为陈旧表(默认情况下，可以使用STALE_PERCENT参数调优10%)，则只收集分区统计信息；否则收集全局和分区统计信息。

这使得小分区的ETL保持快速，因为没有全局分区必须恢复，大分区必须安全，因为后续的任何查询都会有新的统计信息，并且可能使用最优的计划。

增量统计无论如何都是启用的，所以每当必须重新计算全局时，它就会非常快，因为聚合分区级别的统计信息而不执行完整的扫描。

我不确定，在启用增量的情况下，"APPROX_GLOBAL和分区“和”全局和分区“在某些方面是否有区别，因为增量和大约都做了基本相同的事情:聚集统计数据和直方图而不进行完全扫描。

Answer 2

您是否尝试过使用增量统计数据，但仍然显式地命名要分析的分区？

 dbms_stats.gather_table_stats(ownname=>myschema,
                              tabname=>big_table,
                              partname=>part,
                              degree=>dbms_stats.auto_degree);

Answer 3

对于您的表，陈旧的(昨天的)全局状态没有完全无效的分区状态(0行)有害。我可以提出两种我们使用的可供选择的方法：

• 在加载所有分区之后，由ETL工具执行单独的全局统计数据收集。如果时间太长，请与estimate_percent合作，因为dbms_stats.auto_degree可能会超过1%
• 在将所有数据加载到DW之后，在一天晚些时候运行单独的数据库作业，收集全局(以及所有其他陈旧的)状态。

关键是陈腐的统计数据与新鲜数据的差别很小，但它们几乎一样好。如果统计数据显示0行，则它们将终止任何查询。