Configuring Oracle SGA/PGA ?

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
-->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
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 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? )


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_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)
POOL Free memory(MB)
------------ ---------------
shared pool 2143
large pool 706
java pool 1756
streams pool 512
------------------------------------ ----------- ------------------------------
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
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 50G
pga_aggregate_target big integer 30G
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.


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
Configuring HugePages for Oracle on Linux (x86-64)


Oracle SQL Plan Changed?

Let’s look at below in detail

Why Execution Plan gets changes? 
How to fix Bad SQL Plan? 
Image flow of SQL Profile & SQL Plan BaseLine
Create SQL Profile ( Considering SQL Tuning Adviser Suggestions)
How to Create SQL Profile ? ( Which script is used?)

How to fix Bad SQL Plan? .

You can fix the SQL Plan by either SQL Profile / SQL Plan Baseline
Let’s take look at what they are and differences?

SQL profiles and SQL plan baselines help improve the performance of SQL statements by ensuring that the optimizer uses only optimal plans.
==>Baselines define the set of execution plans that can be used by each query, 
    SQL Profiles only provide additional information to “push” the optimizer to favor one or another plan,
==>SQL plan baselines are proactive, whereas SQL profiles are reactive.
==>SQL plan baselines reproduce a specific plan, whereas SQL profiles correct optimizer cost estimates.
SQL plan baselines prevent the optimizer from using suboptimal plans in the future.

Image flow of SQL Profile & SQL Plan BaseLine.


Create SQL Profile ( Considering SQL Tuning Adviser Suggestions)

1) Run SQL Tuning Adviser(sqltrpt.sql/OEM) and get the suggestions for available best plan
2) Create SQL Profile using SQLT Script (coe_xfr_sql_profile.sql)
3) Flush the SQL ID from Memory ( Cancel any Jobs w.r to SQL ID,then flush)
4) Now you could see the new plan is reflected ( From gv$sql) 

This is best plan available for this SQL ID , we have to force the good plan by SQL Profile creation

————— ———–
2538395789 .437————> not reproducibe
2141716862 100.932———–last seen 2018-12-03/17:00:03 original plan
420475214 4898.131 ========== current Plan

How to Create SQL Profile ? ( Which script is used?)

Using coe_xfr_sql_profile.sql ,we can create it ( No need of Installation ,Just Download SQLTXPLAIN (SQLT),Refer Download SQLT section

SQL >  @coe_xfr_sql_profile.sql
SQL> @/dba/srinalla/scripts/sqlt/sqlt/utl/coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)

Enter value for 1: 8ap1zyhp2q7xd

--------------- -----------
     2538395789        .437
     2819292650        .599
     3897290700       4.068
     3111005336        4.21
     3446519203         6.4

Parameter 2:

PLAN_HASH_VALUE (required)
Enter value for 2: 2141716862

Values passed to coe_xfr_sql_profile:
SQL_ID : "8ap1zyhp2q7xd"
PLAN_HASH_VALUE: "2141716862"

Execute coe_xfr_sql_profile_8ap1zyhp2q7xd_2141716862.sql
on TARGET system in order to create a custom SQL Profile
with plan 2141716862 linked to adjusted sql_text.

Now, You can create the SQL Profile with above Script

SQL> @coe_xfr_sql_profile_8ap1zyhp2q7xd_2141716862.sql
PL/SQL procedure successfully completed.
  ... manual custom SQL Profile has been created 

<strong><span style="color:#0000ff;">FROM SQL Tuning Advisor, this Can be run from DB Node as below(or OEM Cloud Control)</span></strong>
SQL @?/rdbms/admin/sqltrpt.sql

Specify the Sql id ~~~~~~~~~~~~~~~~~~ 
Enter value for sqlid: 8ap1zyhp2q7xd 
Sql Id specified: 8ap1zyhp2q7xd 
Tune the sql ~~~~~~~~~~~~
Tuning Task Name : TASK_313228
 Tuning Task Owner : SYS 
Workload Type : Single SQL Statement 
Time Limit(seconds): 1800 
Completion Status : COMPLETED 
Started at : 12/10/2018 02:09:31 
Completed at : 12/10/2018 02:16:37

Schema Name: APPS SQL ID : 8ap1zyhp2q7xd 
SQL Text : SELECT /*+ leading (gjl gjh) */ GJL.EFFECTIVE_DATE ACC_DATE,
1- SQL Profile Finding (see explain plans section below)
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 82.16%)
 ------------------------------------------ -
 Consider accepting the recommended SQL profile. 
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_313228',task_owner => 'SYS', replace => TRUE); 
id plan hash last seen elapsed (s) origin note 
-- ---------- -------------------- ------------ --------------- -------------- 
1 3013459780 2018-06-03/23:58:03 2.674 STS not reproducible 
2 3897290700 2018-12-06/14:00:54 7.268 AWR not reproducible 
3 2141716862 2018-12-03/17:00:03 22.321 AWR original plan
 4 1462347114 2018-12-06/17:00:38 50.572 AWR not reproducible 
5 1471114134 2018-12-07/16:00:04 60.236 AWR not reproducibe

3- Alternative Plan Finding
Some alternative execution plans for this statement were found by searching the system's real-time and historical performance data. The following table lists these plans ranked by their average elapsed time. 
See section "ALTERNATIVE PLANS SECTION" for detailed information on each plan.


Database SQL Tuning Guide
Using Oracle baselines you can fix the sql plan for a SQLID
How To Improve SQL Statements Performance: Using SQL Plan Baselines
SQL Profiles Vs SQL Plan Baselines? by Girish Kumar
What is the difference between SQL Profiles and SQL Plan Baselines?
One Easy Step To Get Started With SQL Plan Baselines
2 Useful Things To Know About SQL Plan Baselines
Parsing SQL Statements in Oracle


Performace Tuning (DB/EBS)


My Posts    Tuning (WIP)-Private

Oracle SQL Plan Changed?
Configuring Oracle SGA/PGA ?

( Still Writing Notes for below..)
What is Table Fragmentation in Oracle ? ( Finding & Removing)
What is Oracle Partitioning – ( Why ?,How ? &Benefits)
OS Watcher-OSWbb/RDA-Remote Diagnostics Agent/ORAchk – Health Checks
Locks vs Latch/Enqueues & Its Types 

Database Performance ( Article Collection)

Oracle Statistics (All About)>An Excellent Article about end to end to Statistics for DB/SCHEAMA/object level
DBMS_ADVISOR–>Details of How to execute SQL access advisor (DBMS_ADVISOR)?
DB Performance Toolkit –> Overview of Performance tools (AWR/ADDM/ASH,SQLT & SQLHC) & What data you need to collect for Performance Issues for Oracle SR?
Performace Tuning DB level->Collection of articles related to DB Time,Hints ,Execution plans,Shared pool,WAIT EVENTS,Latch Issues & Sql Trace & Autotrace

================ Newly Added on 01-JAN-2019=================== (Gavin Soorma(Oracle ACE) Articles)

Oracle Wait Events Causes And Resolutions->Just going through
Why Do My Execution Plans Not Change After Gathering Statistics?->Just going through
Performance Tuning Tips And Techniques->Just going through
ASH And AWR Performance Tuning Scripts->Just going through
Using The Awr History Tables To Compare Performance – Part 1->Just going through
Using The Awr History Tables To Compare Performance – Part 2>->Just going through

EBS Performance ( Article Collection)

Gathering EBS Database Statistics
Recommended Database Parameters for EBS
Approach to Performance Tuning Oracle Apps
Webcast:EBS performance tuning
Troubleshooting Performance Issues??
Debugging ??
Database Partitioning-EBS
Tuning the EBS Database Layer
Tuning All Layers of EBS
Queries for Troubleshooting Adv Typologies
Pinning Objects to Improve Apps Performance
SR Data for Perf Issue