Oracle System Global Area
Published by : Obay Salah , November 19, 2024
We mentioned that the Instance consists of Memory Structure and Processes, and we also defined that the Memory Structure consists of two components: System Global Area (SGA) and Program Global Area (PGA). Here we will discuss the SGA part of the memory used by the Oracle database, and understanding the purposes of these memory components is a vital matter for the database administrator.
Of course, the configuration of this memory also depends on the operating system that the database is using. For example, in the Solaris operating system, the System Administrator configures this part of the memory through the Kernel configuration.
The SGA memory divides into several components, one of which is required and essential:
- The Shared Pool
- The Log Buffer
- The Database Buffer Cache
Other optional components include:
- The Large Pool
- Java Pool
- Stream Pool
- The database buffer cache keep pool
- The database buffer cache recycle pool
- The database buffer cache nK block size pools
These are the components of the SGA memory, and of course, you can control these components through variables available in the Parameter File, referred to as Instance Parameters. Some of these parameters are Dynamic, meaning you can reconfigure them without shutting down the database (Downtime).
Some can be controlled automatically even before enabling the feature of Automatic Shared Memory Management, while others are Static, meaning you cannot reconfigure them without shutting down the Instance.
Of course, configuring the SGA affects performance, so there are consultants in the Oracle database who can assist you in determining the appropriate size for several components of the SGA memory.
To know the overall size of the SGA memory, you can perform the following inquiry:
select sum(bytes)/(1024*1024) size_in_mb from v$sgastat;
This is the size of the SGA memory in megabytes. Note that this result is the sum of several small components.
The final size of the SGA memory can change by adjusting the size of the components, but in general, it cannot exceed the final size of the SGA memory, which is the value specified in the SGA_MAX_SIZE variable.
The maximum size that the SGA memory can reach is SGA_MAX_SIZE, so the final size of the SGA memory is always less than or equal to this variable’s value.
If this variable has not been configured, it takes by default the size of the SGA memory at the moment of its startup.
The SGA memory components, except for the Log Buffer part, are measured by a unit called Granule, which is a portion of memory, and the size of this Granule varies according to the total size of the SGA memory.
The size of the Granule is 4MB if the total size of the SGA does not exceed 1000MB or 16MB if it exceeds that. In the Windows operating system, the size of the Granule is 8MB if the total size of the SGA exceeds 1GB.
Comments
no comment yet!