Oracle Concept

• Oracle Memory Management
• Activating Huge Page
  
• Parameter File
• Parallel Execution
• Access Control List
• Database Constraints

Activating Huge Page

 Metalink note 361468.1 describes the fundamental steps for enabling Oracle to use huge pages.

Changes have to be implemented on OS and DB side.

• Step 1: Have the memlock user limit set in /etc/security/limits.conf file. Set the value (in KB) slightly smaller than installed RAM

nr_hugepages * Hugepagesize

40960         *  2048         = 83886080

This is a soft limit which can be ideally set slightly smaller than RAM and/or slightly bigger than SGA.

• Step 2: Re-logon to the Oracle product owner account (e.g. 'oracle') and check the memlock limit (ulimit -l)
• Step 3: If you have Oracle Database 11g or later, the default database created uses the Automatic Memory Management (AMM) feature which is incompatible with HugePages. Disable AMM before proceeding. To disable, set the initialization parameters MEMORY_TARGET and MEMORY_MAX_TARGET to 0 (zero).

memory_target and memory_max_target need to be set to 0

• Step 4: Make sure that all your database instances are up (including ASM instances) as they would run on production. Use the script hugepages_settings.sh in Document 401749.1 to calculate the recommended value for the vm.nr_hugepages kernel parameter (/etc/sysctl.conf file)

Run of the script returned value 40963 (the requested 40960 was not well calculated for the size of the SGA. By using use_large_pages=ONLY this will create problem at startup as described below)

Indeed, the hugepages_settings.sh script includes # Cumulative number of pages required to handle the running shared memory segments in the calculation.

for SEG_BYTES in `ipcs -m | cut -c44-300 | awk '{print $1}' | 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

 

• Edit the file /etc/sysctl.conf and set the vm.nr_hugepages parameter there:

...

vm.nr_hugepages = 1496

...

• Server reboot
• Apply the following change in order to allow Oracle to use huge pages:

ALTER SYSTEM SET use_large_pages = ONLY scope=spfile;

This setting will force Oracle to check SGA size and total number of hugepages allocated by OS at startup. if whole SGA cannot be allocated in hugepages the database won't startup

 ERROR:

 Failed to allocate shared global region with large pages, unix errno = 12.

 

Aborting Instance startup.

 

ORA-27137: unable to allocate Large Pages to create a shared memory segment

ALTER SYSTEM SET use_large_pages = TRUE scope=spfile;

Starting with 11.2.0.3 Oracle will now allocate what it can of the SGA in hugepages and if it runs out, it will allocate the rest of the SGA using small pages. With this new behavior additional shared memory segments are an expected side effect. Part of the change is to ensure that each shared memory segment making up the SGA only contains sub-areas with an identical alignment requirement - hence the SGA will spread over more separate SHM segments. In this supported mixed page mode the database will exhaust the available hugepages, before switching to regular sized pages. 

• If needed, adjust the SGA size (actually Oracle checks the value of sga_max_size at startup, not sga_target) (in this case, the OS settings suggested by customer were wrong, thus I had to resize SGA in order to allocate whole SGA in hugepages; other than that, no SGA adjustment should be done, because the number of hugepages is calculated keeping already in consideration the SGA size):

ALTER SYSTEM SET sga_max_size=81604378624 scope=spfile;

ALTER SYSTEM SET sga_target=81604378624 scope=both;

If not set properly, you would get something like this:

SQL> startup

ORA-27137: unable to allocate large pages to create a shared memory segment

Linux-x86_64 Error: 12: Cannot allocate memory

Additional information: 2097152

Alert log:

Starting ORACLE instance (normal)

************************ Large Pages Information *******************

Parameter use_large_pages = ONLY

Per process system memlock (soft) limit = 80 GB

 

Large Pages unused system wide = 40960 (80 GB)

Large Pages configured system wide = 40960 (80 GB)

Large Page size = 2048 KB

 

ERROR:

  Failed to allocate shared global region with large pages, unix errno = 12.

  Aborting Instance startup.

  ORA-27137: unable to allocate Large Pages to create a shared memory segment

 

ACTION:

  Total System Global Area size is 80 GB. Prior to next instance restart:

1. Increase the number of unused large pages (page size 2048 KB)

to at least 40964 (80 GB) to allocate 100% System Global Area

with large pages.

2. Large pages are automatically locked into physical memory.

Increase the per process memlock (soft) limit to at least 80 GB to lock

100% System Global Area's large pages into physical memory

            sga_max_size needs to be set precisely and also by keeping in consideration additional pages allocated by Oracle as shared memory segments (you can check them via ipcs -m).

An optimal configuration is shown at startup as follows:

Starting ORACLE instance (normal)
************************ Large Pages Information *******************
Parameter use_large_pages = ONLY
Per process system memlock (soft) limit = 82 GB

Total Shared Global Region in Large Pages = 80 GB (100%)

Large Pages used by this instance: 40961 (80 GB)
Large Pages unused system wide = 2 (4096 KB)
Large Pages configured system wide = 40963 (80 GB)
Large Page size = 2048 KB
********************************************************************

• DB bounce

 

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'