Should i enable automatic SGA tuning ?

Automatic SGA tuning let oracle decide when to move memory between db cache and other pools, but it's possible that sometimes when oracle tries to resize a pool and don't find enough free chunks in other pools the database appears to hang.

The following query help me to monitor the resize operations :

select component,oper_type,status,count(*) from (select

        component,

        oper_type,

        oper_mode,

        parameter,

        initial_size,

        target_size,

        final_size,

        status,

        to_char(start_time,'dd-mon hh24:mi:ss') start_time,

        to_char(end_time,'dd-mon hh24:mi:ss')   end_time

from

        v$sga_resize_ops) group by component,oper_type,status; 

This query help me to determine which component oracle could not shrink or grow.

If you get too many ORA-04031 errors with ASMM enabled, i recommend you to turn it off first by setting sga_target = 0.

You should set a lower limit for each pool, so that oracle will not try to shrink it below the limit.

Sources :

• https://jonathanlewis.wordpress.com/2006/12/04/resizing-the-sga/ 
• https://jonathanlewis.wordpress.com/2007/04/16/sga-resizing/ 
• http://www.oraclemagician.com/white_papers/SGA_resizing.pdf

shell script to delete all not used oracle semaphores and shared memory

Here, save and try this script (kill_ipcs.sh) on your shell:

#!/bin/bash

ME=`whoami`

IPCS_S=`ipcs -s | egrep "0x[0-9a-f]+ [0-9]+" | grep $ME | cut -f2 -d" "`
IPCS_M=`ipcs -m | egrep "0x[0-9a-f]+ [0-9]+" | grep $ME | cut -f2 -d" "`
IPCS_Q=`ipcs -q | egrep "0x[0-9a-f]+ [0-9]+" | grep $ME | cut -f2 -d" "`


for id in $IPCS_M; do
  ipcrm -m $id;
done

for id in $IPCS_S; do
  ipcrm -s $id;
done

for id in $IPCS_Q; do
  ipcrm -q $id;
done

get_hugepages_settings.sh Linux bash script to compute values for the # recommended HugePages/HugeTLB configuration

#!/bin/bash
#
# hugepages_settings.sh
#
# 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
NUM_PG=1
# 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]*"`
do
   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`
   fi
done
# 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') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    *) echo "Unrecognized kernel version $KERN. Exiting." ;;
esac
# End

You should manage shared memory carefully if you are running multiple oracle instances on the same server

The ipcs command on LINUX/UNIX systems can help you to monitor the ORACLE SGA by displaying the size of each shared memory segment of the SGA.
If an oracle instance crashes abruptly, it may be possible that some shared memory segments are not released, if this happen you can use the ipcrm command to remove these segments.
The SHMMAX system setting allows you to increase the maximum size of a single shared memory segment.

To see how many oracle shared memory segments are used on your system

[oracle@dbbckp ~]$ ipcs

------ Shared Memory Segments --------

key        shmid      owner      perms      bytes      nattch     status      

0x00000000 77266945   oracle     640        4096       0                       

0x00000000 77299714   oracle     640        4096       0                       

0xc8f5d818 77332483   oracle     640        4096       0                       

0x00000000 77004804   oracle     640        4096       0                       

0x00000000 77037573   oracle     640        4096       0                       

0x4975d098 77070342   oracle     640        4096       0                       

------ Semaphore Arrays --------

key        semid      owner      perms      nsems     

0x56565656 688142     root       666        3         

0x0000041e 786449     root       644        1         

0x000003d4 819218     root       644        1         

0x0000033d 851987     root       644        1         

0xe5873228 233832468  oracle     640        125       

0xe5873229 233865237  oracle     640        125       

0xe587322a 233898006  oracle     640        125       

0xe587322b 233930775  oracle     640        125       

0xe587322c 233963544  oracle     640        125       

0xe587322d 233996313  oracle     640        125       

0xe587322e 234029082  oracle     640        125       

0xe587322f 234061851  oracle     640        125       

0xe5873230 234094620  oracle     640        125       

0x66d899d4 233046045  oracle     640        125       

------ Message Queues --------

key        msqid      owner      perms      used-bytes   messages    

0x000004d2 65537      root       666        0            0 

But if your are running multiple oracle instances on the same server, this output doesn't tell you which  instance is using which segments.

You can use the oracle sysresv utility to identify segments used by an instance (ORACLE_SID = DRTEST)

[oracle@dbbckp ~]$ sysresv

IPC Resources for ORACLE_SID "DRTEST" :

Shared Memory:

ID              KEY

77004804        0x00000000

77037573        0x00000000

77070342        0x4975d098

Semaphores:

ID              KEY

233046045       0x66d899d4

233078814       0x66d899d5

233111583       0x66d899d6

233144352       0x66d899d7

233177121       0x66d899d8

233209890       0x66d899d9

233242659       0x66d899da

233275428       0x66d899db

233308197       0x66d899dc

Oracle Instance alive for sid "DRTEST"

Now you can use the ipcrm command to remove these segments

ipcrm -s <ID> for Semaphores

ipcrm -m <ID> for Shared Memory

Automatic memory management

Oracle Initialization Parameters Best Practices

Best Practice	Reasoning
Oracle recommends that you use a binary server parameter file (spfile)	Use whichever type of initialization parameter file you’re comfortable with. If you have a requirement to use an spfile, then by all means implement one.
don’t set initialization parameters if you’re not sure of their intended purpose. When in doubt, use the default	Setting initialization parameters can have far-reaching consequences in terms of database performance. Only modify parameters if you know what the resulting behavior will be
For 11g, set the memory_target and memory_max_target initialization parameters	Doing this allows Oracle to manage all memory components for you.
For 10g, set the sga_target and sga_target_max initialization parameters.	Doing this lets Oracle manage most memory components for you
For 10g, set pga_aggregate_target and workarea_size_policy	Doing this allows Oracle to manage the memory used for the sort space
Starting with 10g, use the automatic UNDO feature. This is set using the undo_management and undo_tablespace parameters	Doing this allows Oracle to manage most features of the UNDO tablespace
Set open_cursors to a higher value than the default.  typically set it to 500. Active online transaction processing (OLTP) databases may need a much higher value	The default value of 50 is almost never enough. Even a small one-user application can exceed the default value of 50 open cursors.
Use at least two control files, preferably in different locations using different disks	If one control file becomes corrupt, it’s always a good idea to have at least one other control file available

How to determine the physical RAM size on Aix

lsattr -E -l sys0 -a realmem

vmstat relevant columns with descriptions

Column	Description
kthr	Kernel thread state changes per second over the sampling interval.
r	Number of kernel threads placed in run queue.
b	Number of kernel threads placed in the Virtual Memory Manager (VMM) wait queue (awaiting resource, awaiting input/output).
p	The number of threads waiting on raw I/Os (bypassing journaled file system (JFS)) to complete.
fi/fo	Number of file pages paged in/out per second.
cpu	Breakdown of percentage usage of CPU time. For multiprocessor systems, CPU values are global averages among all processors. Also, the I/O wait state is defined system-wide and not per processor.
us	Average percentage of CPU time executing in the user mode.
sy	Average percentage of CPU time executing in the system mode.
id	Average percentage of time that CPUs were idle and the system did not have an outstanding disk I/O request.
wa	CPU idle time during which the system had outstanding disk/NFS I/O request(s). If there is at least one outstanding I/O to a disk when wait is running, the time is classified as waiting for I/O. Unless asynchronous I/O is being used by the process, an I/O request to disk causes the calling process to block (or sleep) until the request has been completed. Once an I/O request for a process completes, it is placed on the run queue. If the I/Os were completing faster, more CPU time could be used.
pc	Number of physical processors consumed. Displayed only if the partition is running with shared processor.
ec	The percentage of entitled capacity consumed. Displayed only if the partition is running with the shared processor.

Aix Virtual Memory Management internals