我们在AWS RDS上有一个Oracle 19C数据库(19.0.0.0.ru-2021-04.ru -2021-04.r1)，它托管在一个4 CPU 32 GB RAM的实例上。数据库的大小不大(35 GB)， PGA聚合限制为8GB。目标是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，并且数据库重启总是在Auto Optimizer Stats Collection Job运行时发生。还有几个数据库，它们是相同的版本，其中观察到相同的模式，并且由AWS重新启动数据库。我们现在已经在这些数据库上禁用了这个作业，以避免这个问题的进一步发生，但是我们想要运行这个作业，因为禁用它可能会导致数据库中可用的旧统计数据。
对于如何解决这个问题，有什么建议吗?我在我的AWS RDS Oracle 18c和19c实例中发现了同样的问题，尽管我和你不在同一个补丁级别。
在我的例子中，我应用了这个解决方案，它工作了。
SQL> alter system set "_fix_control"='20424684:OFF' scope=both;
然而，在应用此更改之前，我强烈建议您在非生产环境中测试它，如果可以的话，尝试咨询Oracle支持。处理隐藏参数可能会导致意想不到的副作用，因此应用它需要自担风险。
与其完全放弃自动统计信息收集，不如尝试找到导致问题的任何特定对象。如果只有少数表负责大量的统计数据收集，则可以手动分析这些表或更改它们的首选项。
首先，使用下面的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;
/
数据库花费很长时间收集统计数据并不罕见，但数据库持续分析数千个对象并不罕见。遇到这个bug意味着你的数据库有一些不寻常的地方——你是在不断地删除和创建对象，还是你有大量的对象，每天有10%的数据被修改?您可能需要在一些流程中添加手动收集步骤。
完全关闭自动统计作业最终会导致许多性能问题。即使您不能添加手动收集步骤，您可能仍然希望保持启用作业。例如，如果对表的分析过于频繁，您可能希望增加"stale_percent"的表首选项。阈值从10%到20%:
begin
dbms_stats.set_table_prefs
(
ownname => user,
tabname => 'PGA_STATS_TEST',
pname => 'STALE_PERCENT',
pvalue => '20'
);
end;
/