Oracle Memory Essentials

• There are five ways of managing memory in Oracle

• Enable automatic memory management where Oracle manages both SGA and PGA
• http://www.oracle-base.com/articles/11g/AutomaticMemoryManagement_11gR1.php
• http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/AMM1/Default.aspx
• Enable automatic SGA managment where Oracle manages SGA
• http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/memory004.htm#i1014121
• http://www.scribd.com/doc/2674966/Oracle-Automatic-Shared-Memory-Management-Asmm
• Enable automatic PGA management where Oracle manages PGA
• Manual SGA management
• Manual PGA management

• You can manage memory through EM as under:
• Under Home page -> Server-> Memory Advisor
• The following views provide information about dynamic resize operations:
  
  • V$MEMORY_CURRENT_RESIZE_OPS displays information about memory resize operations (both automatic and manual) which are currently in progress.
  • V$MEMORY_DYNAMIC_COMPONENTS displays information about the current sizes of all dynamically tuned memory components, including the total sizes of the SGA and instance PGA.
  • V$MEMORY_RESIZE_OPS displays information about the last 800 completed memory resize operations (both automatic and manual). This does not include in-progress operations.
  • V$MEMORY_TARGET_ADVICE displays tuning advice for the MEMORY_TARGET initialization parameter.
  • V$SGA_CURRENT_RESIZE_OPS displays information about SGA resize operations that are currently in progress. An operation can be a grow or a shrink of a dynamic SGA component.
  • V$SGA_RESIZE_OPS displays information about the last 800 completed SGA resize operations. This does not include any operations currently in progress.
  • V$SGA_DYNAMIC_COMPONENTS displays information about the dynamic components in SGA. This view summarizes information based on all completed SGA resize operations that occurred after startup.
  • V$SGA_DYNAMIC_FREE_MEMORY displays information about the amount of SGA memory available for future dynamic SGA resize operations.
• SGA Internals
• Relevant views: v$sga,v$sgainfo, v$sgastat
• You can display SGA related info by issuing show sga command
• SQL> show sga
• Total System Global Area 3.1277E+10 bytes
  Fixed Size                  2149312 bytes
  Variable Size            7247762496 bytes
  Database Buffers         2.3891E+10 bytes
  Redo Buffers              136527872 bytes
• The same info can be obtained as under:
• SQL> select name, value/(1024*1024*1024) as sizeGB from v$sga;
• NAME                     SIZEGB
  -------------------- ----------
  Fixed Size           .002001703
  Variable Size        6.75000483
  Database Buffers          22.25
  Redo Buffers         .127151489
  
  
  SQL> select sum(value)/(1024*1024*1024) as totalMB from v$sga;
  
     TOTALMB
  ----------
   29.129158
• Fixed Size
  Contains general information about the state of the database and the instance, which the background processes need to access. No user data is stored here. The size of the fixed portion is constant for a release and a plattform of Oracle, that is, it cannot be changed through any means such as altering the initialization parameters. For 11gR2, fixed SGA size is around 200MB
• Variable size
• The variable portion of SGA is called variable because its size (measured in bytes) can be changed.
  
  The variable portion consists of:
      Large Pool (large_pool_size)
      Shared Pool (shared_pool_size)
      Java Pool (java_pool_size)
  
  The size for the variable portion is roughly equal to the result of the following statement till Oracle 9i:
  
  SQL> select sum(bytes)/(1024*1024*1024) as sizeGB from v$sgastat where pool in ('shared pool' , 'java pool' ,'large pool') ;
  
      SIZEGB
  ----------
  6.75000446
• Post Oracle 9i, the variable component is calculated by adding free memory as shown by v$sgainfo to the above query

Variable Component(Show SGA) = Shared Pool + Large Pool + Java Pool + Overhead + free memory(9i onwards)

>  select name, bytes/(1024*1024*1024) as sizeGB from v$sgainfo;
Adding free sga memory available to the above query from v$sgastat should get you the variable SGA size as reported by show sga command.

• Database buffers consist of default buffer (db_cache_size), keep buffer (db_keep_cache_size) , recycle buffer (db_recycle_cache_size) and all non standard buffer (db_n_cache_size). Oracle has the following non standard buffer cache:
• DB_2k_cache_size
• DB_4K_cache_size
• DB_8k_cache_size
• db_16k_cache_size
• db_32k_cache_size.

 The default block size is determined by db_block_size parameter.Multiple buffer pools are only available for the standard block size. Non-standard block size caches have a single DEFAULT pool.

• Query v$buffer_pool to get the sizes of all database buffers configured for the instance.
  SQL> select name, block_size, current_size from v$buffer_pool ;
  NAME                 BLOCK_SIZE CURRENT_SIZE
  -------------------- ---------- ------------
  KEEP                       2048        10240
  RECYCLE                    2048         7168
  DEFAULT                    2048         2048
  DEFAULT                    8192          256
  DEFAULT                   16384         3072
  The above output indicates that the size of keep buffer is 10 GB, size of recycle buffer is 7GB and the size of default buffer is 2 GB. Also , an 8K buffer cache of size 256MB and a 16k buffer cache of size 3 gb is configured.
  The following query indicates that total buffer cache is 22GB as shown in the output of show sga command:
  SQL> select sum(current_size)/1024  as sizeGB from v$buffer_pool;
      SIZEGB
  ----------
       22.25