Using Automatic Memory Management

Purpose

This tutorial describes how you can use Enterprise Manager to automatically manage memory allocation for your instance.

Time to Complete

Approximately 20 minutes

Topics

This tutorial covers the following topics:

	Overview
	Prerequisites
	Changing the Total SGA Size
	Using the PGA Advisor
	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

One of the core initiatives undertaken by Oracle Database 10g is to simplify Oracle database administration. As part of that effort, many of the repetitive and manual tasks have been automated. In this section, you proactively manage and automate some of the mundane tasks related to Oracle Instance Memory Configuration. By automating memory configuration, you have more time to deal with real application or business issues that affect your enterprise.

The Memory Advisor is an intelligent expert system within the Oracle database that proactively determines optimal settings for various SGA and PGA components. When automated, the Oracle database automatically adjusts the settings for the various pools and caches according to the requirements of the workload.

Back to Topic List

Prerequisites

In order to perform this tutorial, you need to perform the following step:

Perform the Installing Oracle Database 10g on Windows tutorial.

Back to Topic List

Changing the Total SGA Size

In this section, you change the total SGA size. To change the total SGA size, you must make sure that the maximum SGA size is large enough. Perform the following steps:

1.	Open your browser and enter the following URL (Replace <hostname> with your own host name or IP address): http://<hostname>:1158/em Enter sys as the username, specify password as oracle, connect as SYSDBA, and then click Login.
2.	Scroll down to the bottom of the Database home page and click Advisor Central under Related Links.
3.	Click Memory Advisor.
4.	Note the values of your Total SGA Size and your Maximum SGA Size, in our example, 276 MB. (You will need them later to reset your SGA values.) Then scroll down to the bottom of the page. Change Maximum SGA Size to 300 MB and click Apply.
5.	If the Max SGA Size parameter is changed, the database needs to be restarted. Click Yes to confirm.
6.	Specify the host credentials and the database credentials. Click OK. Host Credentials Username: oracle Password: oracle Database Credentials Username: sys Password: oracle Connect As: SYSDBA
7.	Confirm the spfile location and click Yes to restart the database.
8.	The database restart operation is in progress. Wait for a few minutes, and then click Refresh.
9.	Steps 9a through 9c may not be necessary. If you click Refresh before the database instance has started up completely, perform the following steps: a. Click Logout. b. Click Login. c. Enter sys as the username, specify oracle as password, connect as SYSDBA. Click Login.
10.	Scroll down and click Advisor Central under Related Links.
11.	Click Memory Advisor.
12.	Scroll down and click Advice.
13.	This graph shows the usage details. Click OK. Note: The graph displayed can vary depending on the workload.
14.	Change the Total SGA Size parameter to 290 MB. Click Apply.
15.	You see that the allocation for various SGA components has changed automatically.
16.	Set the Total SGA Size and Maximum SGA Size parameters to the values noted in step 4, for example 276 MB. Click Apply. Then follow the steps (covered earlier in this tutorial) to restart your database.

Back to Topic List

Using the PGA Advisor

To allocate memory associated with the PGA, perform the following steps:

1.	Click the PGA tab.
2.	Click Advice.
3.	The PGA Aggregate Target Advice graph shows the frequency in which data is found in cache so that you do not have to access the disk. In this case, it should be noted that the current PGA Aggregate Size is set to approximately 92 MB, and 100% of all the requested services are got from memory. The PGA Aggregate Size implies that (based on current workloads and the number of sessions in the database) no more than 92 MB should be allocated for all PGAs in this database. Click Cancel. Note: This graph may vary depending on the workload.
4.	Click PGA Memory Usage Details.
5.	This graph shows the usage details in memory size requests and executions percentages for various PGA memory requests. Click OK.

Back to Topic List

Summary

In this tutorial, you learned how to:

	Change the Max SGA Size
	Change the Total SGA Size
	Use the PGA advisor to make sure the memory is allocated appropriately

Back to Topic List