how to check for free and used memory in shared pool and buffer cache?
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.