Memory, SGA, PGA

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active



SQL> show parameter sea
SQL> show parameter target

column name format a31; select name, bytes/1024/1024 Mb from v$sgainfo; select * from v$sgainfo; $ free -m


Disclaimer: Values of the memory parameters shown on this page are examples, you must use your own parameters



If the database will not start after our changes, we will have a chance to start it using pfile. Create backup:

SQL> create pfile='/tmp/pfile.txt' from spfile;

RMAN> backup current controlfile;

SQL> alter system set sga_max_size=150G scope=spfile;
SQL> alter system set sga_target=150G scope=spfile;

SQL> show parameter shared_pool_size
SQL> alter system set shared_pool_size=40G scope=spfile;          -- not more than the memory_max_target, remember about the other memory pools

SQL> alter system set java_pool_size=2G scope=spfile;
SQL> alter system set DB_cache_size=6G scope=spfile;
SQL> alter system set use_large_pages=auto scope=spfile;

SQL> shut immediate
SQL> startup

show parameter target

SQL> alter system set memory_max_target=2000m scope=spfile;       -- not more than the SGA_MAX_SIZE
SQL> alter system set memory_target=1400m;                        -- not more than the  memory_max_target


ORA-00845: MEMORY_TARGET not supported on this system

$ umount /dev/shm
$ mount -t tmpfs shmfs -o size=5500m /dev/shm

memory_target and memory_max_target should be less that the value above


SQL> alter system set memory_max_target=3g;

ORA-20095: specified initialization parameter cannot be modified

SQL> alter system set memory_max_target=3g scope=spfile;

MEMORY_MAX_TARGET is a static parameter and therefore can only be changed with a SCOPE=SPFILE clause


SQL> alter system set sga_target=90G scope=spfile;

ORA-27137: unable to allocate large pages to create a shared memory segment
Linux-x86_64 Error: 12: Cannot allocate memory

Note: script from the, already have been tested

# Linux bash script to compute values for the
# recommended HugePages/HugeTLB configuration
# Note: This script does calculation for all shared memory
# segments available when the script is run, no matter it
# is an Oracle RDBMS shared memory segment or not.
# Check for the kernel version
KERN=`uname -r | awk -F. '{ printf("%d.%d\n",$1,$2); }'`
# Find out the HugePage size
HPG_SZ=`grep Hugepagesize /proc/meminfo | awk {'print $2'}`
# Start from 1 pages to be on the safe side and guarantee 1 free HugePage
# Cumulative number of pages required to handle the running shared memory segments
for SEG_BYTES in `ipcs -m | awk {'print $5'} | grep "[0-9][0-9]*"`
   MIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`
   if [ $MIN_PG -gt 0 ]; then
      NUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`
# Finish with results
case $KERN in
   '2.4') HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc -q`;
          echo "Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL" ;;
   '2.6' | '3.8' | '3.10' | '4.1' ) echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    *) echo "Unrecognized kernel version $KERN. Exiting." ;;
# End
$ chmod u+x
$ ./
Recommended setting: vm.nr_hugepages = 305


$ sudo su -
# cp /etc/sysctl.conf /etc/sysctl.conf.bak
# vi /etc/sysctl.conf	  set the value greater than or equal to the value displayed by the script



SQL> alter system set memory_target=0;


Enter your email to join our mailing list

Quick Contact