ORA-04031: unable to allocate 3936 bytes of shared memory (“shared pool”,”SELECT * FROM Table…”,”sga heap(1,0)”,”kglsim object batch”)

by Snehashish Ghosh

Scenario


 ORA-04031: unable to allocate 3936 bytes of shared memory (“shared pool”,”SELECT * FROM Table…”,”sga heap(1,0)”,”kglsim object batch”)

Cause


ORA-04031: unable to allocate 3936 bytes of shared memory ,  issue is caused by insufficient memory for shared pool to extend.

Solution


1. Check the shared pool usage statistics by issuing the following query:

show parameter sga

NAME TYPE VALUE
———————————— ———– ——
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1G
sga_target big integer 1G

select pool,sum(bytes)/1024/1024 MB from v$sgastat group by pool;

POOL MB
———— ———-
150.99
java pool 8
streams pool 48
shared pool 970
large pool 8

It can be observed from the above statistics that shared pool is occupying around 95% of the available memory for SGA.

2. Try to flush the shared pool:

alter system flush shared_pool;

3. If the shared pool usage is not minimized then you need to restart the database.

Shutdown immediate;

startup restrict;

select pool,sum(bytes)/1024/1024 MB from v$sgastat group by pool;

alter system disable restricted session;

Conclusion 


 If you encounter ORA-04031: unable to allocate 3936 bytes of shared memory then first try to flush shared pool by informing the application user that their may be temporary performance related issue due to re parsing of sql statements.

If flushing shared pool does not help then database server need to be restarted.

Similar to the above error message ORA-04030 tells the issue is with Program Global Area or PGA. Try increasing PGA memory by increasing PGA_AGGREGATE_TARGET or restart the database server.

 

Leave a reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

*