Let’s look at below in detail
What is SGA/PGA ? SGA/PGA Suggestions (Best Practices) Configuring SHMMAX and SHMALL for Oracle in Linux Linux Huge Pages for Oracle (For Large SGA On Linux) Disabling Transparent HugePages SGA/PGA Suggestions ( From AWR/ADDM) SGA/PGA Paremeters ( What is? ) SGA/PGA Details ( From v$views)
SGA is shared memory, so it is allocated when the database is started.
PGA (Process Global Area) is private memory allocated to individual processes,it can’t be pre-allocated,One need basis it will be allocated in RAM
SGA/PGA Suggestions (Best Practices)
OS Reserved RAM --> 10% of RAM for Linux & 20% or RAM for Windows Oracle Database Connections RAM -->pga_aggregate_target(RAM regions for sorting and hash joins) Oracle SGA Sizing for RAM --> sga_max_size/sga_target
SGA Allocation (Capacity Planning) 45% of ram size RAM SIZE = 484 MB, So 45 % should be = 216 MB -->Split the 216 MM for SGA,PGA,BACKGROUNG PROCESSES -->Fixed background process requires = 40 MB SGA =160 MB PGA =16 MB
Configuring SHMMAX and SHMALL for Oracle in Linux
Shared memory is important for the Oracle Database System Global Area (SGA). Shared memory is nothing but part of Unix IPC System (Inter Process Communication) maintained by kernel where multiple processes share a single chunk of memory to communicate with each other. If there is insufficient shared memory, the Oracle database instance will not start. Set the SHMMAX and SHMALL to maximum values,for SGA Requirements
SHMALL ==>(40% RAM), total size of Shared Memory Segments System wide set in “pages”.
SHMMAX ==> the maximum size (in bytes) of a single shared memory segment set
If the SHMMAX is set incorrectly, for example too low, you may receive the following error:
ORA-27123: unable to attach to shared memory segment.
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device.
is not relevant when you use /dev/shm POSIX shared memory (Oracle AMM).
Note :
SHMALL is the division of SHMMAX/PAGE_SIZE, e.g:. 1073741824/4096=262144. Make SHMALL smaller than free RAM to avoid paging. 32-bit servers -3GB 64-bit servers - Half the RAM Oracle recommends that the SHMALL should be the sum of the SGA regions divided by 4096 (The Linux page size).
-->Oracle 11g uses AMM by default,which relies on POSIX shared memory (/dev/shm-maps shared memory to files using a virtual shared memory filesystem) ,uses 4 KB pages(default PAGE_SIZE in Linux) and set to 50 % of RAM (allocated on demand and can be swapped) For performance reasons,any Oracle database that uses more than 4 GB of SGA should use kernel Huge Pages. Note:Linux kernel Huge Pages are setup manually, use 2M (2048 KB) pages, cannot be swapped and are reserved at system start up. AMM uses either Sys V(virtual) shared memory(ipcs) including kernel hugepages or (POSIX) /dev/shm for SGA, but not both at the same time.
oracle@srinalla-db-1:~ $ grep -i shm /etc/sysctl.conf|grep -v '^#';ls -lrth /proc/sys/kernel/shm*;cd /proc/sys/kernel/;cat shmall;cat shmmni;cat shmmax kernel.shmall = 154618822656 kernel.shmmax = 154618822656 kernel.shmmni = 4096 -rw-r--r-- 1 root root 0 Dec 21 03:11 /proc/sys/kernel/shmall -rw-r--r-- 1 root root 0 Dec 21 03:11 /proc/sys/kernel/shmmni -rw-r--r-- 1 root root 0 Dec 21 03:11 /proc/sys/kernel/shmmax 154618822656 4096 154618822656
oracle@srinalla-db-1:~ $ cat /etc/*-release ;ipcs -l Red Hat Enterprise Linux Server release 5.11 (Tikanga) ------ Shared Memory Limits -------- max number of segments = 4096 /* SHMMNI */ max seg size (kbytes) = 150994944 /* SHMMAX */ max total shared memory (kbytes) = 618475290624 /* SHMALL */ min seg size (bytes) = 1 ------ Semaphore Limits -------- max number of arrays = 4096 max semaphores per array = 256 max semaphores system wide = 32000 max ops per semop call = 100 semaphore max value = 32767 ------ Messages: Limits -------- max queues system wide = 2878 max size of message (bytes) = 8192 default max size of queue (bytes) = 65535
Linux Huge Pages for Oracle (For Large SGA On Linux)
With HugePages, the Linux memory page size is set at 2MB (instead of the default 4K). This will improve OS performance when running Oracle databases with large SGA sizes. For 11g,Enable ASMM feature additionally ,Refer more Linux HugePages The AMM and HugePages are not compatible. One needs to disable AMM on 11g to be able to use HugePages. See Document 749851.1 for further information. ebsdba@ebsdb-prd-01:~ $ grep Hugepagesize /proc/meminfo Hugepagesize: 2048 kB oracle@of3200:~ Oracle Linux: Script to find Recommended Linux HugePages (Doc ID 401749.1) HugePages on Linux: What It Is... and What It Is Not... (Doc ID 361323.1)
Disabling Transparent HugePages Starting from RHEL6/OL6, Transparent HugePages are implemented and enabled by default. This is causing node reboots in RAC installations and performance problems on both single instance and RAC installations. Oracle recommends disabling Transparent HugePages on all servers running Oracle databases, as described in this MOS note (Doc ID 1557478.1) Check setting below # cat /sys/kernel/mm/transparent_hugepage/enabled [always] madvise never #
SGA/PGA Suggestions ( From AWR/ADDM)
1)Undersized SGA Findings in ADDM Report:
2)SGA/PGA Advice Views select * from v$SGA_TARGET_ADVICE ;
(Remember, bigger does not necessarily mean better/faster.)
select * from v$PGA_TARGET_ADVICE;
SGA/PGA Paremeters ( What is? )
SGA
Some parameters to consider in SGA sizing include SGA_MAX_SIZE --> For Dynamic Allocation set this higher than sga_target SGA_TARGET --> new(in 10g) -->Automatically sizes SGA components dynamic,Can be increased till SGA_MAX_SIZE,Change in value of affects only automatically sized components the (redo) log buffers, the shared pool, Java pool, streams pool, buffer cache, keep/recycle caches, and if they are specified, the non-standard block size caches. -------------->Usually, sga_max_size and sga_target will be the same value DB_CACHE_SIZE - ->RAM buffer for the data buffers DB_XK_CACHE_SIZE SHARED_POOL_SIZE ->RAM buffer for Oracle and the library cache. LARGE_POOL_SIZE -> For RMAN parallel queries & Parallel execution allocates buffers out of the large pool only when parallel_automatic_tuning=true. LOG_BUFFER ->RAM buffer for redo logs
PGA
PGA_AGGREGATE_LIMIT -> (12c),limit on Total PGA per Instance can't be below the PGA_AGGREGATE_TARGET. ** To revert to the pre-12c functionality,set parameter value to "0" PGA_AGGREGATE_TARGET-->(9i), soft limit on the PGA used by the instance _pga_max_size ->
SGA/PGA Details ( From v$views)
set lines 200 pages 300 Select Round(sum(bytes)/1024/1024,0) "Total Free memory(MB)" From V$sgastat Where Name Like '%free memory%'; Select POOL, Round(bytes/1024/1024,0) "Free memory(MB)" From V$sgastat Where Name Like '%free memory%'; show parameter sga ; show parameter pga; select NAME,round(BYTES/(1024*1024*1024),0) as "Size(GB)",round(BYTES/(1024*1024),0) as "Size(MB)" ,RESIZEABLE from v$sgainfo order by 2 desc;
Total Free memory(MB) --------------------- 5117 SQL> POOL Free memory(MB) ------------ --------------- shared pool 2143 large pool 706 java pool 1756 streams pool 512 SQL> NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ allow_group_access_to_sga boolean FALSE lock_sga boolean FALSE pre_page_sga boolean TRUE sga_max_size big integer 100G sga_target big integer 85G unified_audit_sga_queue_size integer 1048576 SQL> NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_limit big integer 50G pga_aggregate_target big integer 30G SQL> NAME Size(GB) Size(MB) RES -------------------------------- ---------- ---------- --- Maximum SGA Size 100 102400 No Buffer Cache Size 70 71936 Yes Free SGA Memory Available 15 15360 Shared Pool Size 12 11776 Yes Startup overhead in Shared Pool 5 5078 No Java Pool Size 2 1792 Yes Streams Pool Size 1 512 Yes Large Pool Size 1 768 Yes In-Memory Area Size 0 0 No Shared IO Pool Size 0 256 Yes Data Transfer Cache Size 0 0 Yes Granule Size 0 256 No Redo Buffers 0 249 No Fixed SGA Size 0 7 No 14 rows selected.
References:
Oracle PGA behavior PGA and SGA sizing on linux Monitoring Oracle SGA & PGA Memory Changes Oracle SGA Sizing Optimal SHMMAX for Oracle Monitoring SGA (Free Memory) Using v$sgastat Get confused about how to calculate SHMMAX and SHMALL on Linux. Configuring SHMMAX and SHMALL for Oracle in Linux https://docs.oracle.com/database/121/LADBI/app_manual.htm#LADBI7864 Configuring HugePages for Oracle on Linux (x86-64)
