Improving Memory Management with Automatic Memory Management

Purpose

This tutorial describes how to use Automatic Memory Management to avoid as much as possible "out of memory" errors.

Time to Complete

Approximately 15 minutes

Topics

This tutorial covers the following topics:

	Overview
	Prerequisites
	Using Automatic Memory Management
	Summary

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

With Automatic Memory Management, Oracle Database 11g automates the sizing of PGA and SGA components according to your workload. The simplest way to manage memory is to allow the database to automatically manage and tune it for you. To do so (on most platforms), you have to set only a target memory size initialization parameter (MEMORY_TARGET) and a maximum memory size initialization parameter (MEMORY_MAX_TARGET). Because the target memory initialization parameter is dynamic, you can change the target memory size at any time without restarting the database. The maximum memory size serves as an upper limit so that you do not accidentally set the target memory size too high. Because certain SGA components either cannot easily shrink or must remain at a minimum size, the database also prevents you from setting the target memory size too low.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.	Install Oracle Database 11g.
2.	Download and unzip the amm.zip file into your working directory.

Back to Topic List

Using Automatic Memory Management

Automatic Memory Management dynamically adjusts memory allocation based on usage requirements.

Perform the following steps to understand the use of Automatic Memory Management:

1.	Open a terminal window as user oracle. Navigate to the /home/oracle/wkdir/amm directory. Execute the amm_setup.sh script. This script creates all necessary objects for this OBE. It also enables Automatic Memory Management, and makes sure parallel queries run during this OBE are using large pool memory for better visualization later in Enterprise Manager. Note: Never run this script on your production environment! ./amm_setup.sh The final step in the script re-starts your database with an SGA sized as follows:
2.	Open your browser window, and log on to Enterprise Manager as user SYS. Once on the Home page, click the Server tab. On the Server subpage, click Memory Advisors link.
3.	On the Memory Advisors page, check that Automatic Memory Management is enabled. You can enable/disable Automatic Memory Management from this page.
4.	From your terminal session, start SQLPlus connecting as user amm* (password amm) and execute the query1.sql script. This script starts a parallel query with a degree of parallelism set to 24. The database has been configured so this statement will consume a lot of memory in the Large Pool.
5.	From the same terminal session, execute the query2.sql script. This script starts the same parallel query with a degree of parallelism set to 25. This statement will require even more Large Pool memory.
6.	From the same terminal session, execute the query3.sql script. This script invokes a PL/SQL procedure that build a big array in memory. This execution will require a larger Shared Pool. Note that it can execute for at least 5 minutes.
7.	Go back to your Enterprise Manager session and click the Refresh button at the top of the Memory Advisors page. You should observe the impact of the previous three runs on your memory. Because the first two runs were using a lot of Large Pool memory, other memory areas were dynamically reduced to accommodate this. Because the third run required a larger Shared Pool, memory was again dynamically reallocated. All this was done automatically without generating "out of memory" errors.
8.	Exit SQLPlus. Then execute the amm_cleanup.sh* script. This script resets your database back to the settings you had prior to this OBE. ./amm_cleanup.sh

Back to Topic Lis t

Summary

In this tutorial, you learned how to use Automatic Memory Management.

Back to Topic List