How to Check Free Memory in SGA Pools

  • by
The dynamic view gv$sga can tell you how much memory he occupied from OS. They won’t change very often. The real available memory of SGA is provided on gv$sgastat, you can check the free memory of every pool like this.

SQL> column "Free (MB)" format 9,999.99;
SQL> SELECT inst_id, pool, name, bytes/1024/1024 "Free (MB)" FROM gv$sgastat WHERE name='free memory' ORDER BY 1,2;

   INST_ID POOL         NAME                       Free (MB)
---------- ------------ -------------------------- ---------
         1 java pool    free memory                     4.00
         1 large pool   free memory                     3.16
         1 shared pool  free memory                   110.78
         2 java pool    free memory                     4.00
         2 large pool   free memory                     3.16
         2 shared pool  free memory                    93.03

6 rows selected.


For 10g and 11g, there are more information on overall free memory, for example:
SQL> SELECT * FROM gv$sga_dynamic_free_memory;

   INST_ID CURRENT_SIZE
---------- ------------
         1    490733568
         2    490733568

SQL> SELECT * FROM gv$sgainfo WHERE name LIKE 'Free%';

   INST_ID NAME                                  BYTES RES
---------- -------------------------------- ---------- ---
         1 Free SGA Memory Available         490733568
         2 Free SGA Memory Available         507510784


Though they provide views on overall free memory, I think the individual information on pools is more useful for analyzing performance.

Leave a Reply

Your email address will not be published. Required fields are marked *