在 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;

編譯成功。