Oracle - Monitoring free memory in Large Pool and Shared pool

Monitoring free memory in Large Pool and Shared pool on Oracle
Monitoring Free Memory in Large Pool and Shared Pool (Oracle)
by Avnish Rastogi

The below process was developed to monitor free memory in shared pool and large pool proactively. Lack of memory in these two pools will cause Oracle to generate ORA-4031. Another reason for ORA-4031 is memory pool fragmentation. When we try to allocate large chunk of memory, Oracle will first flush the memory segments, which aren’t in use and merge them. If there is still not enough large piece of continuous memory, Oracle will generate ORA-4031. If your application uses Java, then Oracle may also generate ORA-4031 if Java pool is not big enough.



Oracle provides several System tables, which DBA can use to troubleshoot ORA-4031 errors.



V$SQLAREA—Oracle uses this view to store information of every sql statement and pl/sql block executed since your database is started.



X$KSMLRU—Oracle uses this view to keep tracks allocation in shared pool that causes other objects to aged out.

X$KSMSP—You can use this view to find out how free space is currently allocated.

V$SGASTAT—Oracle uses this view to displace detailed information about SGA allocation. You can use this view to see free memory in share pool and large pool. I used primary v$SGASTAT to develop following process for monitoring free memory in shared pool and large pool.



CREATE OR REPLACE MemoryMgmt

(

mem_threshold IN NUMBER /* IN MB */

) IS

large_free_mem NUMBER;

shared_free_mem NUMBER;

datetime VARCHAR2(20);

message VARCHAR2(200);

BEGIN

EXECUTE IMMEDIATE

'SELECT bytes FROM v$sgastat

WHERE pool = ''large pool'' AND name = ''free memory'''

INTO large_free_mem;



EXECUTE IMMEDIATE

'SELECT bytes FROM v$sgastat

WHERE pool = ''shared pool'' AND name = ''free memory'''

INTO shared_free_mem;



SELECT TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS') INTO datetime FROM dual;



-- If Free Large Memory is less than INPUT memory then write an alert in alert log.

-- If Free Large Memory is greater than INPUT memory then write information in alert log.

IF ((large_free_mem < (mem_threshold*1024*1024)) OR (shared_free_mem < (mem_threshold*1024*1024)))

THEN

message := datetime || ' ** MM-0001 ** Large Pool Free Memory : ' || TO_CHAR(large_free_mem/1024/1024,'9999.99') || ' and Shared Pool Free Memory : ' || TO_CHAR(shared_free_mem/1024/1024,'9999.99') || ' MB. **';

ELSE

message := datetime || ' ** Large Pool Free Memory : ' || TO_CHAR(large_free_mem/1024/1024,'9999.99') || ' and Shared Pool Free Memory : ' || TO_CHAR(shared_free_mem/1024/1024,'9999.99') || ' MB. **';

END IF;

DBMS_SYSTEM.KSDWRT(2, message);

END MemoryMgmt;



Note that DBMS_SYSTEM package is not a documented package from Oracle and KSDWRT procedure allows to write any string into Oracle Alert Log file.



You can use two approach to resolve memory issue; one is to allocate memory using ‘alter system’ command another approach is to use any monitoring utility to monitor oracle alert log and generate email or page to OnCall DBA to allocate memory. We used second approach and used BMC Log Monitor to monitor MM-0001 string and generate trouble ticket to OnCall person. In our environment we are running thing procedure every 15 minutes using Oracle DBA jobs.