Oracle's sga_max_size parameters

Sga_max_size

Sga_max_size This parameter, as the name suggests, is used to control the maximum size of the SGA using virtual memory, where the meaning of virtual memory may be blurred, so that the maximum size that Oracle can allocate to the SGA in memory can be understood first. Now explain what I mean by "virtual memory" here, which is exactly what it should be: real memory and virtual memory. We know that the OS will use virtual memory when actual memory is not available in the OS. Oracle is a system software running on top of the OS, and it is also a program that requests the OS to give it as much memory as its SGA (Oracle, for example, uses 500M of memory for SGA, or sga_max_size=500m), and the OS typically does not start at Oracle Give it all the actual memory, and probably just 200M.

As the program runs, Oracle constantly needs memory, and assuming that all the real memory of the computer is only 500M, then it is quite certain that the OS could not allocate all 500M real memory to the SGA of Oracle, and possibly to 350M, and the remaining 150M uses virtual memory. When Oracle's SGA reaches 500M (that is, it reaches the size specified by Sga_max_size), the SGA is actually made up of 350M real memory and 150M of virtual memory, and if Oracle wants to continue to request memory to use the SGA, then the OS is no longer allocated memory because it has reached the maximum value of sga_max_size. This example, although extremely extreme, even if the OS actually has 1G of memory, Oracle's SGA may not be all composed of actual memory, possibly by 400M actual memory and 100M of virtual memory, which is determined by the operating system's memory management policy. Now, obviously, there's a problem, assuming that my machine's physical memory (real memory) is sufficient, how can I have all of the SGA memory requested by Oracle in physical memory, because it is not cost-effective to assume that virtual memory is used to bring additional PAGE in/page out I/O operations. This problem is actually fixed in the physical memory of the SGA problem, which involves two other parameters Lock_sga and PRE_PAGE_SGA and the specific operation of the system to support the memory lock problem, this is not discussed here.

So it's simple to say that when an instance starts, each memory area allocates only the minimum size needed for the instance, and in the subsequent run, expands their size as needed, and their total size is sga_max_size.

Based on the composition of the SGA above, it is easy to get a formula that calculates the actual value of the SGA, as follows:

Actual size of SGA =

Db_cache_size

+ db_keep_cache_size

+ db_recycle_cache_size

+ db_nk_cache_size

+ shared_pool_size

+ large_pool_size

+ java_pool_size

+ streams_pool_size (new memory pool in 10g)

+ log_buffers+11k (Redo LOG Buffer protection page)

+ 1MB

+ 16M (SGA internal memory consumption, suitable for 9i and previous versions)

The sga_max_size is the sum of the size of each part of the memory area when it reaches the maximum value defined. To modify the size of the sga_max_size, you must restart the database instance. In this case, it is possible that in SPFile, the total size of the SGA in each memory area is larger than the sga_max_size. Oracle will do the following: When the instance is started again, if the total memory sum of the SGA is found to be greater than sga_max_size, it will modify the Sga_max_size value to the value of the total for each memory area of the SGA.

Sql> Show parameter SGA;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

LOCK_SGA Boolean FALSE

PRE_PAGE_SGA Boolean FALSE

Sga_max_size Big Integer 276M

Sga_target Big Integer 276M

Modify Sga_max_size Size

Sql> alter system set sga_max_size=300m Scope=spfile;

System altered.

Changes will not take effect directly

Sql> Show Parameter SGA

NAME TYPE VALUE

------------------------------------ --------------------------------- --------------- ---------------

LOCK_SGA Boolean FALSE

PRE_PAGE_SGA Boolean FALSE

Sga_max_size Big Integer 276M
Sga_target Big Integer 276M