Oracle Memory Management

Oracle Memory Management

Oracle has always tried to provide advisory tools for the Oracle DBA who wants to monitor and re-size their data buffers (db_block_buffers, db_cache_size):

Oracle7 – x$kcbcbh
Oracle9i – v$db_cache_advice
Oracle 10g – Oracle Automatic Shared Memory Management (ASMM)
Oracle 11g – Oracle Automatic Memory Management (AMM)
The Oracle Automatic Shared Memory Management is a feature that automatically readjusts the sizes of the main pools (db_cache_size, shared_pool_size, large_pool_size, java_pool_size) based on existing workloads.

 

  We have asmm in 10 g onwards and amm in 11g onwards,

ASMM – 
         10g onwards , sga and pga were still separate. Do you want to control the sga and pga separately and have them auto tuned? then just set the pga aggregate target and sga target/max size 

  Note :

  1.) SGA -system global area includes majorly , except sga_maz_size ,rest all parameters are dynamic and can be changed without bringing down db.
                Large pool – parallel query execution ,backup and restoration .
                Shared pool - library cache, dictionary cache, result cache (11g),message queues, latch and lock areas, 
                Streams pool - buffer areas for the streams or Xstreams in golden gate
                Java pool - The JAVA Pool holds the JAVA execution code , many internal routines, such as import and export

Below are the manual SGA size parameters :

DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
DB_nK_CACHE_SIZE (n = 2, 4, 8, 16, 32)
LOG_BUFFER
STREAMS_POOL_SIZE

Manual SGA parameters are specified by the user, and the given sizes precisely control the sizes of their corresponding components.

When SGA_TARGET is set, the total size of manual SGA size parameters is subtracted from the SGA_TARGET value, and balance is given to the auto-tuned SGA components.

The memory consumed by manually sized components reduces the amount of memory available for automatic adjustment. For example, in the following configuration:
SGA_TARGET = 256M
DB_8K_CACHE_SIZE = 32M


The instance has only 224 MB (256 - 32) remaining to be distributed among the automatically sized components.

  2.) The DB Memory should be changed to include pga aggregate target set to 1G , sga_target set to 12G, and sga_max_size set to 16G.  We don’t want to use AMM on 11.1.  ASMM is much better here. 

  3.)  Yes, use asmm.  Set to 12G SGA target ,  16G SGA Max,  and 1G PGA.  You will need an outage for this. For setting pga alone we don’t require outage .

  4.) By having a larger SGA max than target , it will allow us to dynamically increase from 12G to 16G without restart.  If we have continued performance reports, you can just increase up to 16G

  5.) You will have to eliminate the other specified pool sizes to enable asmm such as

       shared_pool_size = 500000000

       shared_pool_reserved_size = 50000000

  6.) Database strapped for SGA

ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","SELECT CASE :B11 WHEN :B10 T...","Typecheck","kgghteInit")
ORA-06512: at line 1
04031. 00000 -  "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"

We have 3 option to overcome ORA-04031

            a.)Flush the shared pool
       alter system flush shared_pool;
     b.)Increase the SGA ( see below )
     c.)Restart the DB

7.) DataBase strapped for PGA - running out of memory
 ORA-04030: out of process memory when trying to allocate 8216 bytes (PLS PGA hp,PL/SQL STACK)

 select s.sid,p.pid,p.spid,p.pga_used_mem, p.pga_alloc_mem  from gv$session s, gv$process p where s.paddr=p.addr
and  p.pga_alloc_mem >= 10000000
and s.username like 'PA%'
order by p.pga_alloc_me

 

 

AMM – 
       11G onwards, sga/pga managed as one (so for example, you could shrink the pga to gain more sga etc).Do you want to do totally automatic? then just set one or two parameters (memory_target memory_max_size)

 If I'm looking after 100 databases, probably 10 of them are "mission critical" in terms of importance, availability, performance etc. I'll probably use manual settings on these, because I'll be very proactive in monitoring them etc. For the other 90, which are less important, I'll probably turn on AMM and not worry about the minutiae. 

 

SOP to change SGA

 1. Shutdown database.
 2. Edit pfile for new sga_target size.

   pga_aggregate_target = 1073741824

   sga_target=12884901888

   sga_max_size=17179869184

 3.SQL> startup pfile='E:\oracle\product\10.2.0\db_1\database\inittranstest.ora'