DBA Notes: 2011/12/19
Cheng Li
(Database: Oracle 9i, OS: AIX)
ORA-04031 share pool can’t allocate memory
Issue Case: share pool can’t allocate memory
=== ODM Proposed Solution ===
To try to solve this problem please implement the following changes:
(1) Increase the shared pool size to at least 750 M
(2) Increase the size of the reserved shared pool to 250M
(3) Set parameter _kghdsidx_count = 1 to use only 1 subpool and prevent more memory fragmentation.
The amount of requested memory in the error messages (4200 bytes) is indicating the problem is raised when trying to allocate reserved memory.
Apart of this in trace files we can see they are using 2 subpools for only 500M of shared pool (due to the number of CPUs), which leads to memory fragmentation and ora-4031 errors.
Working in 9.2.0.7 they got a bunch of lack of memory errors:
ORA-04031: unable to allocate 4200 bytes of shared memory ("shared pool","INSERT /*+ APPEND BYPASS_REC...","library cache","kkslpkp - literal info.")
repeated until the shared pool was flushed.
The memory settings:
shared_pool_reserved_size big integer 157286400
shared_pool_size big integer 536870912
For the request to monitor the memory usage of share pool, we should able to get information from an internal table X$KSMSP.
We would apply following SQL to check share pool memory usage using:
SQL> select KSMCHCOM, KSMCHCLS, sum(KSMCHSIZ) from x$ksmsp group by KSMCHCOM, KSMCHCLS;
KSMCHCOM KSMCHCLS SUM(KSMCHSIZ)
---------------- -------- -------------
PARAMETER TABLE freeabl 2072
LISTEN ADDRESS freeabl 2344
kwqmncini-stat freeabl 144
plwppwp:PLW_STR freeabl 96
listener addres freeabl 32
parameter handl freeabl 55232
KSXP test table freeabl 1344
multiblock re freeabl 25800
KKKI consumer recr 4184
dispatcher serv freeabl 40
bt_subentry freeabl 304
KSMCHCOM KSMCHCLS SUM(KSMCHSIZ)
---------------- -------- -------------
KSPD key heap recr 4184
qmnsf_heap recr 4184
…………………………………………….
145 rows selected.
I would recommend using following SQLs to monitor memory trunks usage and fragmentation status:
1) To check size of “free” and “recreate able” memory trunks
SQL> select KSMCHCLS, sum(KSMCHSIZ)/1024/1024 MBytes from x$ksmsp WHERE KSMCHCLS in ('free', 'recr') and KSMCHSIZ>4200 group by KSMCHCLS;
KSMCHCLS MBYTES
-------- ----------
recr 4.9582901
free 56.9457474
2) To check number of “free” and “recreate able” memory trunks large than 4200 bytes size of free and recreate able memory trunks
SQL> select count(*) NUM_MEM_TRUNKS from x$ksmsp WHERE KSMCHCLS in ('free', 'recr') and KSMCHSIZ>4200 ;
NUM_MEM_TRUNKS
--------------
1755
If the size or number of “free” and “recreate able” memory trunk size is lower than a limited value, we should take flush or enlarge memory actions. The size limitation should not be lower than 15% of total memory. And the number of memory trunks (trunk size larger than 4200 bytes) is less than 15% total trunks.
Check share pool cashed objects:
SELECT OWNER,
NAME||' - '||TYPE OBJECT,
SHARABLE_MEM
FROM v$db_object_cache
WHERE SHARABLE_MEM > 10000
AND TYPE IN ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
ORDER BY SHARABLE_MEM
http://www.eygle.com/archives/2005/01/oracleaexiieaee_1.html
http://www.dba-oracle.com/t_x$ksmlru_x$ksmsp_shared_pool_monitoring.htm
http://vsbabu.org/oracle/sect13.html
http://www.dbtan.com/2009/11/xksmsp.html
http://www.dbtan.com/2009/12/shared_pool_reserved_size-parameter.html
a689a5a926e446e79fc384b0e60e53ff.jpg