10G Automatic Shared Memory Management and Memory Advisors

Introduction to Automatic Shared Memory Management
The automatic shared memory management feature of Oracle10G simplifies administration of the various memory components that make up the System Global Area (SGA). In previous releases, database administrators were required to specify the amount of memory allocated to the buffer cache, shared pool, java pool and large pool. Setting, monitoring and adjusting these parameters was time-consuming and oftentimes guesswork. The process could be described as being more of an "art" than a "science." Sizing them incorrectly usually led to poor performance and/or out-of-memory conditions.

Oracle10G administrators are able to set a single dynamic parameter named SGA_TARGET to allocate memory to the Oracle SGA. Oracle10G uses the value contained in SGA_TARGET to determine how much memory to allocate to the buffer cache, shared pool, large pool and java pool. If the SGA_TARGET is set to a non-zero value and the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL, the database will perform automatic memory reallocation.

During normal database operations, the database engine periodically reviews memory usage and redistributes memory between the components accordingly. The resizing process is called automatic SGA segment sizing in the 10G manuals. Oracle 10G provides a new background process named MMAN (Memory Manager) that is responsible for sizing the memory components and scheduling resizing operations. The MMON process checks performance statistics every few minutes and uses this information to determine the best distribution of memory. For example, if a batch job activates numerous parallel processes, MMAN will review memory allocations to ensure that the large pool is sized correctly. If MMAN determines that the large pool needs to be increased to handle the additional parallel processes, it will reduce memory from the other cache areas and increase the large pool.

MMAN stores the component sizes in the SPFILE and uses those settings to initially size the memory areas during database start. Oh, and one last thing, MMAN uses the different memory advisories during its analysis. Just like we'll learn to do later in this blog!

Setting the SGA_TARGET to 0 disables automatic SGA segment sizing and requires that the SHARED_POOL_SIZE, LARGE_POOL_SIZE, and JAVA_POOL_SIZE parameters be set. The parameters controlled by automatic segment SGA sizing are now referred to as auto-tuned SGA parameters. The remaining memory configuration parameters that are not set automatically are referred to as manually tuned parameters.

If you set any of the auto-tuned SGA parameters and set SGA_TARGET to a non-zero value, Oracle will activate automatic memory management and use the auto-tuned SGA parameter values as minimum settings. Oracle will not reduce the cache area's size below the specified value. For example, if the value of SGA_TARGET is 1 GIG and the SHARED_POOL parameter is set to 500 MEGs, Oracle will not reduce the size of the shared pool to below 500 MEGs when performing automatic memory resizing.

The SGA_MAX_SIZE initialization parameter still defines the maximum amount of memory that can be allocated to the Oracle SGA. As a result, SGA_TARGET must be set to a value that is less than or equal to the value of SGA_MAX_SIZE. If you change the value of SGA_TARGET, Oracle automatically adjusts the size of the various auto-tuned cache areas. The SGA_MAX_SIZE parameter can be reduced until it reaches the user-specified or Oracle default minimum sizes of all auto-tuned cache areas added together.

Manually-Sized Cache Areas
The following initialization parameters must be set to size the manually sized cache areas:

• DB_KEEP_CACHE_SIZE - keep pool
• DB_RECYCLE_CACHE_SIZE - recycle pool
• STREAMS_POOL_SIZE - streams pool
• DB_nK_CACHE_SIZE - non standard blocksize cache areas
• LOG_BUFFER - database log buffer

The memory that is allocated to these cache areas is deducted from the SGA_TARGET and the resulting free memory is then allocated to the various auto-tuned cache areas. Reducing the value of SGA_TARGET has no affect on manually sized cache areas.

Memory Advisor Demo
As stated previously, the memory advisors are not activated when the database is configured to use automatic memory allocation. But they can be used when the database is configured to use manual memory allocation. A link to the Memory Advisor Home page is provided on the Advisor Central panel. The link's title doesn't fully describe all of the functionality the Memory Advisor Home page provides. The Memory Advisor Home page allows administrators to toggle the database back and forth between automatic and manual memory allocation, change manual cache area sizes and activate the memory advisors. The Memory Advisor Home page is split into SGA and PGA panels. The PGA (Program Global Area) is not controlled by automatic memory management and must be administered separately.

You'll notice that Oracle sized my shared pool much larger than my buffer cache. That's because I was testing numerous stored procedures that did not access large amounts of data. As a result, MMON sized the shared pool and data buffer cache memory areas accordingly.

I clicked on the "Disable" button to deactivate automatic memory management, which will then allow me to activate and demo the memory advisors. 10G Grid Control displays the Disable Automatic Shared Memory Management page. The page displays the current cache area sizes and allows me to resize them during the switch from automatic to manual memory management.

When I click OK to deactivate automatic memory management, 10G Grid Control displays the Memory Advisor Home page. Enterprise Manager displays an informational message at the top of the panel stating that automatic memory management was successfully disabled. 10G Grid Control also displays input boxes that allow me to manually resize the cache areas and two new advice buttons that activate the memory advisors.

If I click on the advice buttons, 10G Grid Control displays the memory advisor associated with that cache area. Here are screenshots of the Shared Pool and Buffer Cache advisors. If I close these two advisors and click on the PGA link, 10G Grid Control displays the PGA Administration Panel. The PGA Administration panel displays the current PGA allocations and an input box that allows me to change the PGA allocation. In addition, the page also displays the Advice and PGA Memory Utilization Details buttons. Clicking on the Advice button displays the PGA Memory Advisor. I can also display single and multi-pass PGA information by clicking on the PGA Memory Usage Details button.

Recommendation
We use automatic memory allocation religiously and have found no need to set minimum cache area sizes. But most of our 10G applications do not have cache area size requirements that dramatically vary from one instant to the next. We have been monitoring the actual sizes of the auto-tuned cache areas and found that 10G is resizing them on a regular basis. We are happy with the memory allocations that Oracle has been defining for us so we intend to configure all new 10G databases to use automatic memory management. If anything happens to change this decision, I'll let you know immediately.

Thanks for reading!

Chris Foot