Oracle Database Memory Sizing for RSA Identity Governance & Lifecycle

[#1]

Article Number

000034569

Applies To

RSA Product Set: RSA Identity Governance & Lifecycle
RSA Version/Condition: 7.0.x, 7.1.x, 7.2.x
 

Issue

The purpose of this RSA Knowledge Base Article is to describe Oracle database memory sizing for the RSA Identity Governance & Lifecycle Oracle database, both the initial setup and verification of existing configurations.
 

Resolution

Find Physical Memory

The first step is to find the physical memory on the server. There are multiple ways to determine this on Linux. Here are two examples:

• Running the free command, and
• Viewing the /proc/meminfo file

Image description
 

If there is already an Oracle database on the server, an Automatic Workload Repository (AWR) will also indicate the amount of physical memory on the server:

Image description

Memory for Oracle

The next step is to determine how much memory can be dedicated to the RSA Identity Governance & Lifecycle Oracle database. If this is a configuration like an appliance where the application server and database reside on the same server, then 66% of the memory can be allocated to the database. If it is a remote database, then we must understand if the RSA Identity Governance & Lifecycle database is the only one on that server. If the server is dedicated to the RSA Identity Governance & Lifecycle database, then we can allocate 80% of the physical memory to the database. If it is a shared server, then we must understand the maximum amount of memory that can be allocated to the database. For reference in this document we will use 48G (GB or gigabytes) as the available physical memory.

Oracle memory is allocated as SGA (System Global Area) and PGA (Program Global Area). The PGA may be configured on the fly but the SGA requires a restart of the Oracle database.

• Appliance

For an appliance configuration we will allocate 66% of the memory to Oracle with the break down being 43% for the SGA and 23% for the PGA. Using our 48G of memory, the calculated values would be as follows.

The total database usage should be 66% of 48G which would equal 31.68G. The SGA would be 20.64G and the PGA would be 11.04G. Since Oracle does not take fractional values, we could continue to multiply out to get the BYTE value or we could just round down. Therefore, the final values would be SGA = 20G and PGA = 11G.

• Dedicated Server

For a dedicated server we will allocate 80% of the memory to Oracle with the break down being 50% for the SGA and 30% for the PGA. You will notice that as the percentage of usage increases, we increase the SGA and PGA equally. Using our 48G of memory the calculated values would be as follows.

The total database usage should be 80% of 48G which would equal 38.4G. The SGA would be 24G and the PGA would be 14.4G. Since Oracle does not take fractional values, we could continue to multiply out to get the BYTE value or we could just round down. Therefore the final values would be SGA = 24G and PGA = 14G.

• Shared Server

In an environment where the database is on a server running multiple databases, we will break things down a little differently. We will use all the memory that is available to our database.  In this scenario our breakdown will be 70% for the SGA and 30% for the PGA. Using our reference of 48G this results in an SGA of 33.6G and a PGA of 14.4G. Since Oracle does not take fractional values we could continue to multiply out to get the BYTE value or we could just round down. Therefore the final values would be SGA = 33G and PGA = 14G.

Once the correct SGA and PGA values have been determined, set these values as follows. Login into the server as the oracle user and login to SQL*Plus as the SYS user. This example assumes an appliance with 48G.
 

$ sqlplus / as sysdba 
SQL> alter system set sga_max_size=20G scope=spfile;
SQL> alter system set sga_target=20G scope=spfile;
SQL> alter system set pga_aggregate_target=11G scope=spfile;

If the SGA size is less than or equal to 10G, then restart the database as the oracle user. If the SGA size is greater than 10G, optionally restart the database or wait until HugePages is configured and reboot the system (See the section below on HugePages and memlock.)
 

acm stop
acm stopdb
acm startdb
acm start

• HugePages and memlock

If the SGA size is greater than 10G, it is recommended that the server be configured for HugePages. When HugePages is configured, memlock also needs to be set. Modification of these settings requires a system reboot.

NOTE: To use HugePages, you must be using Automatic Shared Memory Management (ASMM) and NOT Automatic Memory Management (AMM). HugePages is incompatible with AMM and to use HugePages, AMM must be disabled.
 

1.   Determine if HugePages is already configured on your system. As the root user execute:

grep Huge /proc/meminfo

If the output shows a value of zero for all settings with the exception of AnonHugePages and Hugepagesize, then HugePages has not been configured.

2. Configuring HugePages is beyond the scope of this RSA Knowledge Base Article. If you are on a hardware appliance and you find HugePages is not configured, please contact RSA Identity Governance & Lifecycle Customer Support for assistance and mention this RSA Knowledge Base Article ID 000034569 for reference. For all other environments, please contact your operating system vendor who should be able to guide you through the process of configuring HugePages and memlock.

Future Growth

These values are a starting point for the database and it is a best practice to continue to monitor the needs of the database as the application is running. This is best accomplished by periodically capturing an AWR during a day of activity. If there is a potential problem with the size of the SGA or PGA, you would see indications of an undersized SGA or PGA in the AWR.

For example,
 

Image description

The column to focus on is the left most column Finding Name where it is calling out both an undersized SGA and an undersized PGA.

Seeing this in one AWR does not necessarily indicate a problem. If it is continually seen in multiple AWRs, then it is something that needs to be investigated. An undersized SGA message for instance, could be generated by a single report that is scheduled during daytime hours. If it continues to occur, then multiple AWRs on a smaller interval need to be reviewed to see if there is a single SQL statement that could potentially be causing excessive SGA usage. The same is true for PGA. If undersized PGA messages are continually seen in AWRs, then investigate what jobs are running during the day. Typically PGA is used more by our scheduled or background processes.
 

Notes

 Automatic Memory Management (AMM)

For customer-supplied databases, some DBAs may want to use Oracle’s Automatic Memory Management (AMM) configuration. Use of AMM is discouraged with RSA Identity Governance & Lifecycle Oracle databases because AMM is not compatible with HugePages. To use HugePages, AMM just be disabled.

If using AMM is required at your site, the parameters MEMORY_TARGET and MEMORY_MAX_TARGET need to be set. Calculate the SGA and PGA as noted above (depending on whether you have a dedicated server or a shared server) and set the values to the sum of the SGA and PGA. Assuming you are on a dedicated server with 48G of memory (SGA = 24G and PGA = 14G), then the new settings are MAX_TARGET = 38G and MAX_TARGET_SIZE = 38G.  

The SGA and PGA also need to be set. However, they should be set to 50% of their calculated values. In this case, 50% of the SGA and PGA values are SGA=12G and PGA=7G.

Set these values as follows. Login into the server as the oracle user and login to SQL*Plus as the SYS user.
 

$ sqlplus / as sysdba 
SQL> alter system set sga_max_size=12G scope=spfile;
SQL> alter system set sga_target=12G scope=spfile;
SQL> alter system set pga_aggregate_target=7G scope=spfile;
SQL> alter system set memory_max_target=38G scope=spfile;
SQL> alter system set memory_target=38G scope=spfile;
$ acm stop 
$ acm stopdb 
$ acm startdb 
$ acm start