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

6 replies

  1. You have made some decent points there. I checked on the web for additional information about the issue and found most people will go along with your views on this website.

  2. Like!! Really appreciate you sharing this blog post.Really thank you! Keep writing.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s