Oracle-DOP引发的内存CPU高消耗问题

背景:

最近分析了一次Oracle数据库服务器内存CPU高消耗问题，除了发现全表扫描的高消耗SQL之外，还发现开启DOP(automatic degree of Parallelism)所导致的并行进程消耗CPU内存问题。

问题:

Oracle数据库内存CPU高消耗问题。

查看CPU的使用进程，主要为大量的Oracle用户进程。

从数据库以及操作系统层面看，主要的内存使用进程，均为ora_pnnn并行进程，单个进程最大使用超过1G。

从以上信息，可以初步确认服务器的CPU，内存主要消耗为大量的Oracle pnnn并行进程，接下来，查看并行进程产生的原因。

从数据库层面查询并行进程的调用情况，可以发现主要为应用用户执行ar1zauftbwtbt语句引发。

分析ar1zauftbwtbt语句的执行计划，C表与M表进行hash外连接，主要的消耗在于对表C表的并行全表扫描，并行操作为DOP功能所触发。

这里我们不去展开对全表扫描的分析，语句查询条件是有创建索引的，但由于where查询扫描的数量较大，索引区分度不高，导致执行计划并未走索引，而是走全表。

接下来，我们主要分析一下执行计划的并行操作行为，并行操作为DOP功能所触发，DOP在数据库参数parallel_degree_policy设置为auto的情况下开启，默认下为关闭状态manual。

在开启了DOP的情况下，数据库会自动为估算执行时间大于PARALLEL_MIN_TIME_THRESHOLD(默认设置为auto,即10秒)的sql开启并行，开启并行度根据数据库的资源统计信息以及并行参数设置所分配。

通过10053分析语句DOP的产生过程，可以发现语句的估算时间为12213ms超过10秒的阈值，导致语句的执行计划触发了DOP。

而语句的DOP并行度为2，是通过数据库资源统计信息，io calibrate计算得出的。

问题总结:

当前数据库内存，CPU使用高的原因为

1 数据库存在大范围全表扫描SQL。

2 数据库开启了DOP（automaticdegree of Parallelism），导致执行估算时间超过10秒的SQL在执行时自动产生了大量的并行进程，引发服务器CPU，内存高消耗。

问题调整建议:

1增大使用DOP的SQL估算执行时间阈值，建议调整PARALLEL_MIN_TIME_THRESHOLD为20秒，避免SQL触发DOP产生大量并行进程。

2 关闭DOP功能,默认情况下DOP为manual，开启DOP会导致不可控的产生大量并行进程消耗服务器CPU，内存资源，对于需要并行执行的语句，建议使用hint进行语句级别的控制，此外，关闭DOP可能导致语句性能出现下降，建议方案在测试环境进行测试再上线生产。

3 对于非实时一致性的查询SQL，建议放备库执行，避免对主库产生影响。

4 应用结合业务场景优化SQL代码逻辑，避免SQL大范围扫描以及减少执行次数。