在 11.2.0.4 运行 utlrp.sql 出现 ORA-04031 错误解决方法

在 11.2.0.4 运行 utlrp.sql 出现 ORA-04031 错误解决方法

上次遇到一个案例：

在客户现场11.2.0.4版本运行utlrp.sql 编译脚本时，出现以下错误:
ORA-12801: error signaled in parallel query server P237
ORA-12853: insufficient memory for PX buffers: current 84352K, max needed 13280400K
ORA-04031: unable to allocate 65560 bytes of shared memory (“large pool”,”unknown object”,”large pool”,”PX msg pool”)
ORA-06512: at “SYS.UTL_RECOMP”, line 804
ORA-06512: at line 4

原因是:

Cause
 The degree of parallelism used for recompilation is determined automatically based on instance parameters cpu_count and parallel_threads_per_cpu.
 On systems with high number of cpus that can lead to high degree of parallelism and lead to high memory usage overall and high memory usage in the large pool since it is used by parallel execution for message buffers.

By default utlrp.sql passes a 0 to utlprp.sql:

从上面解释可以知道依赖于cpu_count的值和parallel_threads_per_cpu的值，如果cpu多，这个并行度就会高，导致large pool会使用更多的内存

所以解决这个问题就简单了

减少实例的并行度值

alter system set PARALLEL_MAX_SERVERS=5 scope=both;
alter system set PARALLEL_SERVERS_TARGET=2 scope=both;

编译成功。