ORA-12801: error signaled in parallel query + ORA-04031: unable to allocate 65560 bytes of shared memory (“large pool”)

Yesterday I got request from one of the user saying his job failed giving following error:


Severity Timestamp Node Thread Message Code Message
ERROR 2014/10/24 02:59:32 PM node-uat-informatika READER_1_1_1 RR_4035 SQL Error [
ORA-12801: error signaled in parallel query server P178, instance rmb-uat-db-gik1:DBGIK1 (1)
ORA-12853: insufficient memory for PX buffers: current 4347776K, max needed 78766080K
ORA-04031: unable to allocate 65560 bytes of shared memory ("large pool","unknown object","large pool","PX msg pool")
Database driver error...
Function Name : Execute
SQL Stmt : SELECT APPS.XXXLA_TRX_NOT_IN_GL_V.COUNTRY_CODE, APPS.XXXLA_TRX_NOT_IN_GL_V.EVENT_ID, APPS.XXXLA_TRX_NOT_IN_GL_V.PKEY, APPS.XXXLA_TRX_NOT_IN_GL_V.TDS_BATCH_ID, APPS.XXXLA_TRX_NOT_IN_GL_V.TYPE FROM APPS.XXXLA_TRX_NOT_IN_GL_V
Oracle Fatal Error
Database driver error...
Function Name : Execute
SQL Stmt : SELECT APPS.XXXLA_TRX_NOT_IN_GL_V.COUNTRY_CODE, APPS.XXXLA_TRX_NOT_IN_GL_V.EVENT_ID, APPS.XXXLA_TRX_NOT_IN_GL_V.PKEY, APPS.XXXLA_TRX_NOT_IN_GL_V.TDS_BATCH_ID, APPS.XXXLA_TRX_NOT_IN_GL_V.TYPE FROM APPS.XXXLA_TRX_NOT_IN_GL_V
Oracle Fatal Error].

ORA-04031 is error message related to lack of available SGA memory component. When a process needs some memory from SGA and memory is not available in this situation ORA-04031 occurs.

Ofcourse in my case large pool was getting full. but WHY? I concerntrated on other error i.e. ORA-12801: error signaled in parallel query server P178.

After checking parameterd related to parallel processing, I found parallel_max_servers was set to very high value : 2560.

After checking v$sga_dynamic_compnents for large_pool size I got following stats:


COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER_TIME GRANULE_SIZE
---------------- ------------ ---------- ---------- ------------------- ---------- ------------- --------- -------------------- ------------
large pool 2617245696 335544320 5100273664 0 4116 GROW IMMEDIATE 24-OCT-2014 15:03:47 67108864

So we can say large_pool was grown & given ORA-04031 once it was having insufficient memory to grow.

I decided to minimize parallel_max_servers.


15:06:11 SQL> alter system set parallel_max_servers=200;
system altered.

v$sga_dynamic_compnents statistics for large_pool post parameter change:


COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER_TIME GRANULE_SIZE
---------------- ------------ ---------- ---------- ------------------- ---------- ------------- --------- -------------------- ------------
large pool 536870912 335544320 5100273664 0 4118 SHRINK DEFERRED 24-OCT-2014 15:06:41 67108864

As soon as I minimized parallel_max_servers, current size for large_pool decreased from 2617MB to 536MB.

I suggested User to rerun his job. He confirmed about successful execution of job 🙂

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath

Advertisements

4 thoughts on “ORA-12801: error signaled in parallel query + ORA-04031: unable to allocate 65560 bytes of shared memory (“large pool”)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s