收集分区上的表统计信息处理整个表

Question

我有一个有大约 20 个分区的表。每个分区有大约 1.9 亿条记录。我需要在处理过程中定期收集表上的统计信息，我使用 DBMS_STATS 执行此操作。GATHER_TABLE_STATS命令。当表只有 1 个分区时，过去大约需要 4 分钟才能完成。随着时间的推移，分区的数量增加了，gather_table_stats所需的时间也增加了。然后，我通过在 GATHER_TABLE_STATS 命令中添加 PARTNAME 参数来切换到仅收集分区上的统计信息，但所需的时间并没有减少。我什至创建了一个只有 1,000 行的新分区，当我收集该分区的统计信息时，仍然需要 22 到 25 分钟才能完成。 我查看了USER_TAB_PARTITIONS表，我看到LAST_ANALYZED列仅在GATHER_TABLE_STATS中指定的分区进行更新，因此我相信统计信息仅在我的单个分区上收集，但是为什么需要这么长时间？ 如果有帮助，这就是我的DDL。请注意，我正在创建一个本地索引。没有其他表具有对此表的外键引用。

CREATE TABLE LAR_ALLOCATION_PER_PART (
PROC_MONTH                DATE        NOT NULL,
COUNTRY_CODE              VARCHAR2(2) NOT NULL,
PART_NUMBER               VARCHAR2(20), 
CUSTOMER_CODE             VARCHAR2(32),
LAR_ID                    NUMBER     NOT NULL,
GROSS_SALES_AMOUNT        NUMBER,
ALLOCATION_AMOUNT         NUMBER,
WARRANTY_AMOUNT           NUMBER,
CURRENCY_CODE             VARCHAR2(5),
CONSTRAINT LAR_ALLOC_PP_COUNTRY_CODE_FK FOREIGN KEY (COUNTRY_CODE) REFERENCES SUPPORTED_COUNTRY (COUNTRY_CODE),
CONSTRAINT LAR_ALLOC_PP_PART_NUM_FK FOREIGN KEY (PART_NUMBER) REFERENCES PART_CLASSIFICATION (ODS_PART_NUMBER),
CONSTRAINT LAR_ALLOC_PP_LAR_ID_FK FOREIGN KEY (LAR_ID) REFERENCES LEDGER_ALLOCATION_RULE (ID)
)
PARTITION BY RANGE(PROC_MONTH)
INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION prior2017 VALUES LESS THAN (TO_DATE('01-JAN-2017', 'DD-MON-YYYY'))
);
CREATE INDEX LAR_ALLOCATION_PER_PART_IDX
ON LAR_ALLOCATION_PER_PART
(COUNTRY_CODE, LAR_ID, CUSTOMER_CODE, PART_NUMBER) LOCAL;

这是我用来收集统计信息的命令：

BEGIN 
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME  => 'MY_SCHEMA',
TABNAME  => 'LAR_ALLOCATION_PER_PART',
PARTNAME => 'SYS_P40553', --Jan 2020:  1,000 records
OPTIONS  => 'GATHER AUTO',
DEGREE   => DBMS_STATS.DEFAULT_DEGREE,
CASCADE  => TRUE);
END;

我尝试将级联设置为 FALSE，将 DEGREE 调整为 32，甚至将ESTIMATE_PERCENT值设置为 10，但对运行时没有任何统计显着影响。

我刚刚看到了关于使用增量统计信息的答案，我将尝试一下，但我想了解为什么在分区上收集统计信息需要这么长时间，以及我是否做错了什么。

Answer 1

您可能需要查看DBMS_STATS.GATHER_TABLE_STATS的GRANULARITY参数。

默认情况下，将收集分区和全局表的统计信息。 将值更改为APPROX_GLOBAL AND PARTITION可以避免重新收集全局表的统计信息。

---

INCREMENTAL绝对是一个好主意，应该能够显着帮助。 尽管增量统计信息存在一些限制。 例如，您必须使用默认ESTIMATE_PERCENT进行收集。

收集单个分区的统计信息速度很慢的原因是一个很长的故事。

它从优化程序需要知道值的数量和非重复值的数量开始。 非重复值的数量通常更有用。 例如，如果我们查询select * from employee where employee_id = 1，Oracle 可以查看EMPLOYEE_ID的独特性，确定谓词返回一行，并且索引将是完美的。 另一方面，如果我们查询select * from employee where status = 'terminated'，Oracle 可以查看STATUS的区别性，确定谓词返回许多行，全表扫描是更好的匹配。

查找非重复值的数量比查找值的总数更复杂。 朴素的算法会对值进行排序或哈希处理，但这需要大量的时间和空间。 相反，Oracle 可以使用像 HyperLogLog 这样的算法来根据表的单次传递来估计值。 这就是为什么您需要将ESTIMATE_PERCENT保持为默认值 - 扫描整个表比对表的 10% 进行排序更快。

但是，通过分区查找非重复值的数量变得更加复杂。 Oracle 需要知道每个分区和整个表的不同值的数量。 向单个分区添加少量行可能会显著更改整个表的结果，这就是 Oracle 默认情况下必须重新扫描整个表的原因。

例如，想想生日问题。 想象一下，有一个用于人群的分区，带有BIRTHDAY列。 如果分区 A 有 15 个不同的生日，分区 B 有 15 个不同的生日，则整个表有多少个不同的生日？ 可能不到30。

增量统计信息通过为每个分区创建概要来解决此问题。 这些概要可以快速组合在一起，以重新估计非重复值的全局数量，而无需重新扫描每个分区。 它只需要为每个分区存储一点额外的数据。