Monthly Archives June 2013

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...

Read More