|Applies To||RSA Product Set: RSA Identity Governance and Lifecycle|
This document is for understanding how to do an initial memory configuration for an Oracle database supporting RSA Identity Governance and Lifecycle or to verify if a database's current configuration is "in the ballpark."
Find Physical Memory
The first step is to find the physical memory on the box. On Linux this can be done using the command many ways. Here are two commands that can be used:
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 box:
Memory for Oracle
The next step is to determine how much can be dedicated to the database. Is this a configuration like an appliance where the application server and database reside on the same server? If so, then 66% of the memory can be allocated to the Oracle database. If it is a remote database, then we must understand if our database is the only one on that server. If the server is dedicated to our database then we can allocate 80% of the physical memory to the Oracle database. If it is a shared server then we must understand the maximum amount of memory that can be allocated to our database. For reference in this document we will use 48G as the available physical memory.
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, our calculated values would be:
The total database usage should be 66% of 48G which would equal 31.68G. SGA would be 20.64G. PGA would be 11.04G. Since Oracle won’t take fractional values we could continue to multiply out to get the BYTE value or we could just round down. Therefore, out final values would be SGA = 20G and PGA = 11G.
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 our percentage of usage increased we increased the SGA and PGA equally. Using our 48G of memory our calculated values would be:
The total database usage should be 80% of 48G which would equal 38.4G. SGA would be 24G. PGA would be 14.4G. Since Oracle won’t take fractional values we could continue to multiply out to get the BYTE value or we could just round down. Therefore out final values would be SGA = 24G and PGA = 14G.
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 SGA would be 33.6G and PGA would be 14.4G. Since Oracle won’t take fractional values we could continue to multiply out to get the BYTE value or we could just round down. Therefore out final values would be SGA = 33G and PGA = 14G.
If the SGA size is 10G or greater the server should also be configured for Huge Pages. For more information see the following links:
Automatic Memory Management
Some customers with DBA groups may want to user Oracle’s Automatic Memory Management (AMM) configuration. This requires setting the parameters MEMORY_TARGET and MEMORY_MAX_TARGET. In this configuration we will still doe the above calculations and the values will be set to the sum of the SGA and PGA. Assuming we are on an appliance with 48G of memory our values above were SGA = 20G and PGA = 11G.
So the new settings are MAX_TARGET = 31G and MAX_TARGET_SIZE = 31G. We should continue to set the SGA and PGA values but we will decrease their values by 50% so they will be SGA = 10G Nx PGA = 6G (Yes, I know I rounded up this time).
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 taking 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.
Note: The column to focus on is the left most column "Finding Name" where it is calling out Undersized SGA and/or PGA.
Seeing this in one AWR does NOT indicate a problem. If it is continually seen on multiple AWR’s 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 looked at to see if there is a single SQL statement that could potentially be causing excessive SGA usage. The same is true for the PGA as well. If it is continually seen in AWRs, then an investigation in what jobs are running during the day. Typically PGA is used more by our scheduled or background processes.