To View SGA Information

The (SGA) System Global Area is shared memory structures that are created at instance startup. They hold information about

the instance, and control its behavior. The following query gives a window into the various memory pools available in the

SGA.

SELECT NAME, VALUE
  FROM v$parameter
 WHERE NAME IN
          ('shared_pool_size', 'java_pool_size', 'streams_pool_size',
           'log_buffer', 'db_cache_size', 'db_2k_cache_size',
           'db_4k_cache_size', 'db_8k_cache_size', 'db_16k_cache_size',
           'db_32k_cache_size', 'db_keep_cache_size', 'db_recycle_cache_size',
           'large_pool_size');

and the sizes of the various pools in use.

SELECT NAME, pool, ROUND (BYTES / 1024 / 1024, 2) free_mb
  FROM v$sgastat
 WHERE NAME IN
          ('%free memory%', 'parameters', 'memory in use', 'db_block_buffers',
           'log_buffer', 'dictionary_cache,', 'sql area', 'library cache');