具有大量插入和删除的繁忙表上的优化程序统计信息

Question

环境：Oracle Database 19C

有问题的表有几个数字数据类型列和一个 CLOB 数据类型列。 该表已正确编制索引，并且还有一个夜间收集统计信息作业。

以下是表上的操作-

• PL/SQL 批处理过程从以外部表形式呈现的平面文件中插入 400 到 500 万条记录
• 插入操作后，另一个批处理将读取行并更新某些列
• 每日清除过程删除不再需要的行

我的问题是 - 是否应该在对表执行插入和/或删除操作后立即触发收集统计信息？

根据此 Oracle 文档批量加载的在线统计信息收集，批量加载仅在对象为空时自动收集联机统计信息。我的进程不会从中受益，因为当我加载数据时，表不为空。

但是在线统计信息收集适用于使用直接路径插入到空段上的选择操作中。所以接下来我将尝试附加提示。有什么想法吗？

Answer 1

在 Oracle 12c 之前，最佳做法是在批量加载后立即收集统计信息。但是，根据 Oracle 的 SQL 调优指南，许多应用程序未能做到这一点，因此他们针对某些操作自动执行此操作。

我建议查看字典视图DBA_TAB_STATISTICS、DBA_IND_STATISTICS和DBA_TAB_MODIFICATIONS，看看你的表是如何表现的：

CREATE TABLE t AS SELECT * FROM all_objects;
CREATE INDEX i ON t(object_name);
SELECT table_name, num_rows, stale_stats
FROM DBA_TAB_STATISTICS WHERE table_name='T'
UNION ALL
SELECT index_name, num_rows, stale_stats 
FROM DBA_IND_STATISTICS WHERE table_name='T';
TABLE_NAME   NUM_ROWS   STALE_STATS
T               67135   NO
I               67135   NO 

如果插入数据，统计信息将标记为过时：

INSERT INTO t SELECT * FROM all_objects;
TABLE_NAME   NUM_ROWS   STALE_STATS
T               67138   YES
I               67138   YES
SELECT inserts, updates, deletes 
FROM DBA_TAB_MODIFICATIONS 
WHERE table_name='T';
INSERTS UPDATES DELETES
67140       0       0

同样，对于更新和删除：

UPDATE t SET object_id = - object_id WHERE object_type='TABLE';
4,449 rows updated.
DELETE FROM t WHERE object_type = 'SYNONYM';
23,120 rows deleted.
INSERTS UPDATES DELETES
67140    4449   23120

当您收集统计信息时，stale_stats再次变为"否"，并且"DBA_TAB_MODIFICATIONS*"返回到零(或空行(

EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL, 'T');
TABLE_NAME   NUM_ROWS   STALE_STATS
T              111158   YES
I              111158   YES

请注意，当表(或分区(为空时，'INSERT/*+ APPEND */仅收集统计信息。此处记录了此限制。

因此，我建议您在您的代码中，在插入，更新和删除完成后，检查表是否出现在USER_TAB_MODIFICATIONS中。如果统计数据陈旧，我会收集统计数据。

我也会研究分区。检查您是否可以在全新的分区中插入，更新和收集统计信息，这会更快一些。并检查您是否可以通过删除整个分区来清除数据，这会快得多。