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
This tutorial covers the following topics:
|Using Automatic Memory Management|
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.
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.
Before you perform this tutorial, you should:
Install Oracle Database 11g.
Download and unzip the amm.zip file into your working directory (that is, wkdir ) and navigate into your working directory.
Automatic Memory Management is an extension to the existing Automatic Shared Memory Management functionality. Automatic Memory Management allows for PGA memory to be automatically transferred to SGA memory when needed, and vice versa.
The following scenario illustrates the automatic transfer of PGA memory to SGA memory and vice versa.
Perform the following steps to understand the use of Automatic Memory Management:
Open a terminal window as user oracle. Navigate to the directory where you unzipped the amm.zip file. Start a SQL*Plus session as user SYS, and execute the amm_setup.sql 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!
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.
On the Memory Advisors page, check that Automatic Memory Management is enabled. You can enable/disable Automatic Memory Management from this page.
From the same terminal session, connect 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. This statement is going to consume a lot of SGA to execute.
From the same terminal session, execute the query2.sql script. This script starts the same parallel query with a degree of parallelism set to 26. This statement is going to use even more SGA than the previous execution.
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 is going to use a lot of PGA. Note that it can execute for at least 5 minutes.
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, PGA was transferred to SGA. Because the third run was using a lot of PGA, memory was transferred from the SGA back to the PGA. All this was done automatically without generating "out of memory" errors. If you want to get a global advice on how to set your MEMORY_TARGET parameter, you can click on the Advice button at the top of this page.
Exit from your SQL*Plus session. Modify the directories in the amm_cleanup.sh script to point to the directory where you placed the files. Then execute the amm_cleanup.sh script. This script generates a new AWR snapshot to force an ADDM analysis.
In this tutorial, you learned how to use Automatic Memory Management.