How can we tune Oracle database with 64GB Memory?

Document ID : KB000016409
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

  We have seen many times that database was undersized or not properly configured. Default deployment of database is using only small amount of RAM for caching underlying data (1GB). So even if users have big machines with 64GB of memory, it is not used. One should do an initial health check seeing if database is configured properly.

Question:

How can we tune Oracle database with 64GB Memory?

Environment:
All current supported APM releases
Answer:

- Oracle tuning example for 64GB RAM

OS TUNING (pre-req for other changes)
 
Modify the following OS files and add/change the following parameters:
 
A- >> /etc/sysctl.conf
kernel.shmmax=32984576
kernel.shmmni=4096
kernel.shmall=16106
kernel.sem=1010 143420 1010 142
vm.nr_hugepages=9032
 
B- >> /etc/security/limits.conf
oracle soft memlock 19421338
oracle hard memlock 19421338
 
C- Disable TRANSPARENT HUGE PAGES
 
#######################################################
 
1- ORACLE SGA changes:

a. DB_CACHE_SIZE: Change value to 20GB
b. SHARED_POOL_SIZE: Change value to 5GB.
c. SHARED_POOL_RESERVED_SIZE: Change value to 500m
d. "_MEMORY_BROKER_STAT_INTERVAL": Change "_memory_broker_stat_interval" to 999, the goal is to avoid grow / shrink of SGA.
f. SESSION_CACHED_CURSORS: Change value to 200
g. LOG_ARCHIVE_MAX_PROCESSES: Change value to 9.
h. SGA_TARGET: Change value to 32768M.
i. SGA_MAX_SIZE: Change value to 32768M.
J. MEMORY_TARGET: Change value to 0.
K. MEMORY_MAX_SIZE: Change value to 0.
 
#######################################################
 
2- SGA ASM Configuration:

a. SGA_MAX_SIZE / SGA_TARGET: Change value to 1500mb.
b. MEMORY_MAX_TARGET / MEMORY_TARGET: Change value 0.

Additional Information:

- APM database is growing fast - too many appmap_states tables.
https://support.ca.com/us/knowledge-base-articles.TEC1846866.html