Thursday, March 26, 2015

ORA-12853: insufficient memory for PX buffers: current 3091232K, max needed 6786000K

Got below error while running utlrp

ERROR at line 1:
ORA-12801: error signaled in parallel query server P619, instance (2)
ORA-12853: insufficient memory for PX buffers: current 3091232K, max needed
6786000K
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 865
ORA-06512: at line 4


As it was a “PX” (parallelism) error, and my system has a lot of processors, the problem was that my default parallel_max_servers was 970!
This number would be acceptable if I had enough free memory (specially pga) to accommodate all of them, but I didn’t. My MEMORY_TARGET was the minimal.

So to resolve it, I had to change this parameter to a lower value:

SQL> show parameter parallel_max_servers

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers                 integer     970


Brought this down to 50

SQL> alter system set parallel_max_servers=50 scope=both sid='*';

System altered.

This stopped the error!!.

Hope this works for you

1 comment: