May 22, 2024
PGA and UGA Wrap-Up- Memory Structures-2
The parameters that have the greatest effect on the overall size of the SGA are as follows:
•\ JAVA_POOL_SIZE: Controls the size of the Java pool.
•\ SHARED_POOL_SIZE: Controls the size of the shared pool (to some degree).
•\ LARGE_POOL_SIZE: Controls the size of the large pool.
•\ STREAMS_POOL_SIZE: Controls the size of the Streams pool.
•\ DB_*_CACHE_SIZE: Eight of these CACHE_SIZE parameters control the sizes of the various buffer caches available.
•\ LOG_BUFFER: Controls the size of the redo log buffer (to some degree).
•\ SGA_TARGET: Used with automatic SGA memory management and can be changed online.
•\ SGA_MAX_SIZE: Used to control the size of the SGA.
•\ MEMORY_TARGET: Used with automatic memory management (both PGA and SGA are automatically sized).
•\ MEMORY_MAX_SIZE: Used to control the maximum amount of memory Oracle should strive to use over both the PGA and SGA sizes under automatic memory management. This is really just a target; the PGA may exceed the optimum size if the number of users increases beyond some level or a session(s) allocates large untunable bits of memory as demonstrated earlier.
•\ INMEMORY_SIZE: Sets the size of the in-memory area in the SGA used for the in-memory column store. The default is zero which means in- memory is not in use.
•\ MEMOPTIMIZE_POOL_SIZE: Sets the memory size for the memoptimized rowstore feature.
•\ DB_FLASH_CACHE_SIZE: Sets the size of the flash cache used in conjunction with DB_FLASH_CACHE_FILE.
Using ASMM is simply a matter of setting the SGA_TARGET parameter to the desired SGA size, leaving out the other SGA-related parameters altogether. The database instance will take it from there, allocating memory to the various pools as needed and even taking memory away from one pool to give to another over time. This is the most common way of sizing the SGA.
When using AMM, you simply set the MEMORY_TARGET. The database instance will then decide the optimal SGA size and PGA size—and those components will be set up appropriately and do their own automatic memory management within their respective boundaries. Further, the database can and will resize the SGA and PGA allocations as the workload changes over time.
Regardless of the type of memory management you use, you’ll find that memory is allocated to the various pools in the SGA in units called granules. A single granule is an area of memory of 4MB, 8MB, or 16MB in size. The granule is the smallest unit of allocation, so if you ask for a Java pool of 5MB and your granule size is 4MB, Oracle will actually allocate 8MB to the Java pool (8 being the smallest number greater than or equal to 5 that is a multiple of the granule size of 4).
The size of a granule is determined by the size of your SGA (this sounds recursive to a degree, as the size of the SGA is dependent on the granule size). You can view the granule sizes used for each pool by querying V$SGA_DYNAMIC_COMPONENTS. In fact, we can use this view to see how the total SGA size might affect the size of the granules.
First, let’s set the SGA to a small value, and then restart the instance, and observe the granule sizes. I’ll connect to the root container of my database when running the following commands:
Note If your instance is currently running, the STARTUP FORCE command will shut down the instance (abort mode) and restart it.
Note This is the SGA information for the Oracle instance started with the initialization parameter file in the previous example. We specified the SGA and PGA sizes ourselves in that parameter file. Therefore, we are using ASMM and automatic PGA memory management, but not the AMM setting, which would have sized and resized our PGA/SGA settings for us.
In this example, I used ASMM and controlled the size of the SGA via the single parameter SGA_TARGET. When my SGA size is under about 1GB, the granule is 4MB. When the SGA size is increased to some threshold over 1GB (it will vary slightly from operating system to operating system and even from release to release), I see an increased granule size:
SQL> alter system set sga_target = 1512m scope=spfile;
SQL> startup force
SGA> show parameter sga_target
NAME TYPE VALUE
sga_target big integer 1520M
Now when we look at the SGA components:
SQL> select component, granule_size from v$sga_dynamic_components;
COMPONENT GRANULE_SIZE
As you can see, at 1.5GB of SGA, my pools will be allocated using 16MB granules, so any given pool size will be some multiple of 16MB. With this in mind, let’s look at each of the major SGA components in turn.
More Details