Oracle Memory Management and Performance Tuning

• Relevant Oracle Document


• What is SGA and PGA
  • SGA is a shared memory segment used by all Oracle processes, that contains:

  
  

  Buffer cache (DB_CACHE_SIZE): to store any data being queried or modified, to avoid repeated access from the physical disk

  
  

  Shared pool (SHARED_POOL_SIZE): cached SQL statements, data dictionary (account data, table/index description), cached stored procedures

  
  

  Redo log buffer

  
  

  Large Pool (LARGE_POOL_SIZE): buffering large I/O requests

  
  

  • PGA is allocated in each Oracle process, the memory area for various SQL operations:

  
  

  sorting

  
  

  group by

  
  

  hash-join

  
  

  • The sum of SGA and all PGAs should be less than physical memory size
  
  
• To lock SGA in memory

This will prevent any segment of SGA to be paged out, and also reduce the OS overhead for looking up a list of pages to be swapped out.

• Linux, set max shared memory size to 64G




Edit /etc/sysctl.conf

kernel.shmmax = 68719476736 in bytes




Run sysctl -p




Double check it: /sbin/sysctl -a |grep shm




• Linux, allow user oracle to lock 8G memory




Edit /etc/security/limits.conf

* hard memlock 8388608 in kB

oracle soft memlock 8388608




• Oracle parameter

lock_sga=TRUE

• Restart Linux and Oracle

• To check shared memory is locked in

ipcs -m




• Oracle Automatic memory management for SGA

This will allow Oracle dynamically adjust various pool sizes, based on load.

• Set following parameters




SGA_TARGET=6G this will trigger Automatic memory management. The followings are just for references.

SGA_MAX_SIZE=6G

PRE_PAGE_SGA=FALSE TRUE will refresh SGA every time a process is up, not a good idea for dedicated server mode, where there are a lot of processes come and go.

LOCK_SGA=TRUE




• Do not set following parameters (=0)




DB_CACHE_SIZE

JAVA_POOL_SIZE

LARGE_POOL_SIZE

SHARED_POOL_SIZE

STREAM_POOL_SIZE default is 10% of shared_pool_size




• Automatic PGA Memory
  • Set target for total PGAs used by all oracle processes

  
  

  PGA_AGGREGATE_SIZE=2G by default, it is 20% of SGA

  WORKAREA_SIZE_POLICY=AUTO

  
  

  • In auotmatic PGA mode, following parameters are ignored

  
  

  SORT_AREA_SIZE

  HASH_AREA_SIZE

  BITMAP_MERGE_AREA_SIZE

  CREATE_BITMAP_AREA_SIZE

  
  

  • SGA_TARGET + PGA_AGGREGATE_ATRGET < physical memory size