Database Administration (MOSC)

MOSC Banner

how to check for free and used memory in shared pool and buffer cache?

edited April 2023 in Database Administration (MOSC) 7 commentsAnswered ✓

hi,


how to check for free and used memory in shared pool and buffer cache?


I'm setting SGA_TARGET to 60GB and PGA_AGGREGATE_TARGET=10G so oracle manages SHARED POOL, BUFFER CACHE, JAVA POOL, LARGE POOL sizes automatically.


I use below query to check free memory in shared pool

SELECT INST_ID, POOL, NAME, BYTES/1024/1024 "SPACE MB" 
FROM gv$sgastat 
WHERE name = 'free memory' 
AND POOL = 'shared pool' 
ORDER BY INST_ID;


I'm having some doubts because I got an ORA-4031 error

ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","SELECT ROWNUM, LOG_TABLE, 

The ORA-4031 ERROR says there is no enough space in shared pool and and when I checked the same database instance 10 minutes later, I see shared pool on in that instances to have 19G free memory. I also checked for free shared pool memory in the other two RAC instances and they are all above 13G and 16G each.

Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center