时间戳

首页 > 解决方案 > 自动优化器统计收集作业导致 Oracle RDS 数据库重新启动

oracle - 自动优化器统计收集作业导致 Oracle RDS 数据库重新启动

问题描述

我们在 AWS RDS 上有一个 Oracle 19C 数据库 (19.0.0.0.ru-2021-04.rur-2021-04.r1)，它托管在一个 4 CPU 32 GB RAM 实例上。数据库的大小不大（35 GB），PGA Aggregate Limit 为 8GB，Target 为 4GB。每当计划的内部 Oracle Auto Optimizer Stats Collection Job (ORA$AT_OS_OPT_SY_nnn) 运行时，它会消耗大量 PGA 内存（大约 7GB），有时这会使数据库变得不稳定，AWS 会失去与 RDS 实例的通信，因此它会重新启动数据库。

我们认为这可能与现有的 Oracle 错误 30846782（19C+：使用 DBMS_STATS.GATHER_TABLE_STATS 时 PGA 快速/过度增长）有关，但 Oracle 和 AWS 已在我们使用的当前 19C 版本中修复了它。没有应用程序级别的操作会消耗这么多 PGA，并且在自动优化器统计收集作业运​​行时总是会重新启动数据库。还有几个数据库，它们在相同的版本上，观察到相同的模式并且数据库由 AWS 重新启动。我们现在已经在这些数据库上禁用了该作业，以避免进一步发生此问题，但是我们希望运行此作业，因为禁用它可能会导致旧的统计信息在数据库中可用。

有关如何解决此问题的任何指示？

标签： oracleamazon-rdsoracle19c

解决方案

与其完全放弃自动收集统计信息，不如尝试找出导致问题的任何特定对象。如果只有少数表负责收集大量统计信息，您可以手动分析这些表或更改它们的首选项。

首先，使用下面的 SQL 来查看哪些对象导致了最多的统计信息收集。根据 bug 30846782 中的测试用例，问题似乎只与调用 DBMS_STATS的次数有关。

select *
from dba_optstat_operations
order by start_time desc;

此外，您可以通过以下查询找到生成大量 PGA 内存的特定 SQL 语句或会话。（但是，如果数据库重新启动，AWR 可能不会保存记录的值。）

select username, event, sql_id, pga_allocated/1024/1024/1024 pga_allocated_gb, gv$active_session_history.*
from gv$active_session_history
join dba_users on gv$active_session_history.user_id = dba_users.user_id
where pga_allocated/1024/1024/1024 >= 1
order by sample_time desc;

如果问题仅与具有大量分区的少量表有关，您可以在单独的会话中手动收集该表的统计信息。收集统计信息后，在更改大约 10% 的数据之前不会再次分析该表。

begin
    dbms_stats.gather_table_stats(user, 'PGA_STATS_TEST');
end;
/

数据库花费很长时间收集统计数据的情况并不少见，但数据库不断分析数千个对象的情况并不罕见。遇到这个错误意味着您的数据库有一些不寻常的地方——您是否不断地删除和创建对象，或者您是否有大量的对象每天修改 10% 的数据？您可能需要在一些流程中添加手动收集步骤。

完全关闭自动统计作业最终会导致许多性能问题。即使您无法添加手动收集步骤，您仍可能希望保持启用该作业。例如，如果表的分析过于频繁，您可能希望将“STALE_PERCENT”阈值的表首选项从 10% 增加到 20%：

begin
    dbms_stats.set_table_prefs
    (
        ownname => user,
        tabname => 'PGA_STATS_TEST',
        pname   => 'STALE_PERCENT',
        pvalue  => '20'
    );
end;
/

推荐阅读