在Oracle 11g partiitioned表上收集并发统计信息-Java 学习之路

我正在开发Oracle 11g上的DWH . 我们有一些大表（2.5亿行），按值划分 . 每个分区都分配给不同的馈送源，每个分区独立于其他分区，因此可以同时加载和处理它们 .

数据分布非常不均匀，我们有数百万行的分区，并且分区不超过一百行，但我没有选择分区方案，顺便说一下我无法改变它 .

考虑到数据量，我们必须确保每个分区始终具有最新的统计信息，因为如果后续的详细说明没有对数据的最佳访问权限，它们将永远持续下去 .

所以对于每个并发的ETL线程，我们

截断分区
从暂存区域加载数据

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

（这种方式我们有直接路径，我们不锁定整个表）

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

在项目的第一阶段，我们使用了 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部分占主导地位（仍然比GLOBAL快很多），而对于一个小分区，我们有例如10秒的加载和20分钟的统计 .

因此我们建议切换到11g的 INCREMENTAL STATS 功能，这意味着你不要自动了解哪些分区已被触摸 . 它确实有效，我们已经加快了小分区的速度 . 打开电话后，电话就变成了

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)

请注意，您不再通过分区，并且未指定样本百分比 .

但是，我们有一个缺点，可能比前一个更糟糕，这与我们的高水平并行性有关 .

假设我们有两个同时启动的大分区，它们几乎同时也会完成加载阶段 .

第一个线程结束插入语句，提交并启动统计信息收集 . 统计程序通知有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)

结果是我偶尔会出现非最佳计划（这意味着杀死会话，手动刷新统计数据，再次手动启动进动） .

我甚至试图在收集上放置一个独占锁，所以一次只能有一个线程可以在同一个表上收集统计信息，但没有任何改变 .

恕我直言这是一个奇怪的行为，因为统计程序，第二次被调用，应该检查第二个分区上的最后一次提交，并且应该看到它比最后的统计数据收集时间更新 . 但似乎没有发生 .

难道我做错了什么？这是Oracle的错误吗？如何保证所有统计信息始终是最新的，并且启用了增量统计功能，并且具有高级别的并发性？

6 回答

我设法通过这个功能达成了妥协 .

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的末尾，如果添加/删除/修改的行数足够低而不将表标记为过时（默认情况下为10％，可以使用STALE_PERCENT参数进行调整），我只收集分区统计信息;否则我收集全局和分区统计信息 .

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

无论如何都要启用增量统计，因此每当必须重新计算全局时，它都非常快，因为聚合分区级别统计信息并且不执行完整扫描 .

我不确定，在增量启用的情况下，“APPROX_GLOBAL AND PARTITION”和“GLOBAL AND PARTITION”在某些方面确实有所不同，因为incremental和approx基本上都是相同的：聚合统计数据和直方图而不进行全面扫描 .

回复于 2024-04-20T12:48:50+08:00

您是否尝试过增量统计信息，但仍明确指定要分析的分区？

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

回复于 2024-04-20T12:48:50+08:00

-1
对于您的表，陈旧（昨天）的全局统计数据不如完全无效的分区统计数据（0行）有害 . 我可以提出2个我们使用的替代方法：
- 在加载所有分区后立即由ETL工具执行单独的GLOBAL统计信息收集 . 如果花费的时间太长，可以使用estimate_percent，因为dbms_stats.auto_degree可能会超过1％
- 在将所有数据加载到DW之后，在白天晚些时候运行的单独数据库作业中收集全局（以及所有其他陈旧）统计信息 .
关键在于，与新鲜度略有不同的陈旧统计数据几乎同样出色 . 如果统计信息显示0行，则它们将终止任何查询 .
回复于 2024-04-20T12:48:50+08:00
1

考虑到您要实现的目标，您需要在所有分区的特定时间间隔上运行统计信息，而不是在加载每个分区的过程结束时运行统计信息 . 如果这是一个实时表并且全天候不断发生数据加载可能会很有挑战性，但由于这些是大型DW表，我真的怀疑是这种情况 . 因此，最好的办法是在加载所有分区时收集统计信息，这将确保收集数据发生变化或缺少统计信息的分区的统计信息，并根据分区级别统计信息和概要更新全局统计信息 .

但是，要执行此操作，您需要打开表的增量功能（11gR1） .

EXEC DBMS_STATS.SET_TABLE_PREFS('<Owner>','BIG_TABLE','INCREMENTAL','TRUE');

在每次加载结束时，使用 GATHER_TABLE_STATS 命令收集表统计信息 . 您无需指定分区名称 . 另外，请勿指定粒度参数 .

EXEC DBMS_STATS.GATHER_TABLE_STATS('<Owner>','BIG_TABLE');

回复于 2024-04-20T12:48:50+08:00
1
请检查是否已使用DBMS_STATS设置表首选项以收集增量统计信息 . This oracle blog解释了在受影响的每一行之后将收集统计数据 .

增量统计信息维护需要收集有关将更改全局或表级统计信息的任何分区的统计信息 . 例如，在表中插入或更新一行后，列的最小值或最大值可能会更改
```
BEGIN 
DBMS_STATS.SET_TABLE_PREFS(myschema,'BIG_TABLE','INCREMENTAL','TRUE'); 
END;
```
回复于 2024-04-20T12:48:50+08:00
2

我对它有点生疏，所以首先要问一个问题：你是否尝试过序列化分区加载？如果是这样，统计数据运行的时间和程度如何？请注意，由于加载时间比统计信息收集小得多，我想这也可以作为临时解决方法 .

追加提示会影响重做大小，这意味着事务只是跟踪某些内容，因此统计信息可能无法计算新数据：http://oracle-base.com/articles/misc/append-hint.php

大声思考：由于直接路径插入确实在分区末尾追加行并最终在最后更新元数据，因此已经运行的线程收集统计信息可能已读取未更新（陈旧）数据 . 因此它可能不是一个错误，锁定线程将无法完成任何事情 .

例如，您可以测试此行为暂时将您的表/分区切换到LOGGING，并查看它是如何工作的（当然，速度较慢，但这是一个测试） . 你可以做到吗？

编辑：增量统计数据应该仍然有效，甚至禁用并行统计信息收集，因为无论如何收集，它都会依赖增量值：https://blogs.oracle.com/optimizer/entry/incremental_statistics_maintenance_what_statistics

回复于 2024-04-20T12:48:50+08:00

在Oracle 11g partiitioned表上收集并发统计信息

6 回答

相关问题