SGA Oracle database Tuning

With the article Tuning SGA Oracle database, I describe the functionality of the Oracle database SGA created.

The SGA of the Oracle database is the area of ​​RAM that is used by the Oracle database and allocated during the startup phase. More specifically, nomount is allocated during the startup phase.

When the startup command is run, Oracle reads the information from the spfile.
If it is consistent with the environment in which it is started, that is, if the environment meets the parameters indicated in the spfile, the instance will allocate the memory by completing the nomount startup phase.


 
The SGA MEMORY areas
The EMS has other memory areas inside, each with its own function.

The main memory areas are:

SHARED POOL - Area dedicated to query libraries
BUFFER CACHE - Area dedicated to the loading of data in memory accessed by frequency
REDO BUFFER - Area dedicated to writing REDO LOG
JAVA POOL - Area dedicated to JAVA data processing
etc…

These memory areas can be managed automatically by Oracle.

If you want to disable the automatic management of the memory areas inside the SGA, you must specify the size of the various memory areas on the spfile.

At that point the management would no longer be dynamic, as with the fixed dimensions.

The automatic management mode of the internal areas of the SGA is called ASMM Automatic Shared Memory Management and was introduced in Oracle 10g, applicable through the parameters sga_target, sga_max_size, pga_aggregate and the resetting of the other memory areas.

AMM vs ASMM
Furthermore, from the Oracle 11g version, an additional automatic memory management mode has been implemented, extended to the size of the SGA and PGA.

This mode is called AMM Automatic Memory Management and is activated by populating the memory_target and memory_max_target parameters and removing the sga_target, sga_max_size, pga_aggregate parameters from the spfile:

SGA Oracle database tuning
Commands to enable ADM

alter system set  memory_max_target=4G scope=spfile; 
alter system set memory_target=3G scope=spfile;

Commands to disable ASMM:
alter system reset sga_max_size scope=spfile; 
alter system reset sga_target scope=spfile;

Always check Oracle database official notes and OS official notes, based on the operating system hosting the database.

AMM is the current mode recommended by Oracle, however, in databases with an EMS greater than 4G, Oracle recommends enabling the LARGE PAGE functionality, regardless of the operating system.

To enable large page

This is because the default RAM memory is allocated with 4k pins, and in the event that the SGA had a size of 15G, the operating system would be forced to use billions of 4k pins to make up the size of 15G.

This can put the operating system in difficulty or in any case not at peak performance.

Benefits LARGE PAGE
Furthermore, if the LARGE PAGE is enabled the SGA will occupy the RAM memory by allocating 2M pins and this would allow the operating system to manage millions of pins and not billions of pins.

Enabling the Large Page feature is highly recommended due to the net improvements in database performance and stability.

Each operating system has its own particularities to enable this function.

Advice

So, the advice I can give you, always look online for the official documentation of the operating system and database in which you have to make these types of changes.
Check the Large Page and Tuning SGA enabling mode, but above all, before making any changes, always save the spfile with the following command:

Saving the SPFILE with the creation of the PFILE

create pfile='/tmp/pfile.ora' from spfile;

In case the modification goes wrong and the db does not open, it is possible to return to the initial parameters with the PFILE previously saved.

The sequence of commands is as follows:

sqlplus / as sysdbaCopy

startup pfile='/tmp/pfile.ora'Copy

create spfile from pfile='/tmp/pfile.ora'

with these commands the database will open with the parameters prior to the modification.

In the case of an RAC, the operations are the same. You need to start the database manually, recreate the spfile, close the database and start with the CRS commands, available on the srvctl start database page.

To view my Oracle tutorials click here!
At this LINK, however, you will find SQL queries.

Stay up to date on our activities by joining our community:
Facebook
Youtube
Linkedin