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)
#tuning
