Problem
You want to automate memory management in your Oracle database. You have both OLTP and batch
jobs running in this database. You want to take advantage of the automatic memory management
feature built into Oracle Database 11g.
Solution
Here are the steps to implement automatic memory management in your database, if you’ve already set
either the SGA_TARGET or the PGA_AGGREGATE_TARGET parameters (or both). We assume that we are going to
allocate 2,000 MB to the MEMORY_MAX_TARGET parameter and 1,000 MB to the MEMORY_TARGET parameter.
1. Connect to the database with the SYSDBA privilege.
2. Assuming you’re using the SPFILE, first set a value for the MEMORY_MAX_TARGET
parameter:
SQL> alter system set memory_max_target=2G scope=spfile;
System altered.
You must specify the SCOPE parameter in the alter system command, because MEMORY_MAX_TARGET isn’t a dynamic parameter, which means you can’t change it on the fly while the instance is running.
3. Note that if you’ve started the instance with a traditional init.ora parameter
file instead of the SPFILE, you must add the following to your init.ora file:
memory_max_target = 2000M
memory_target = 1000M
4. Bounce the database.
5. Turn off the SGA_TARGET and the PGA_AGGREGATE_TARGET parameters by issuing
the following ALTER SYSTEM commands:
SQL> alter system set sga_target = 0;
SQL> alter system set pga_aggregate_target = 0;
6. Turn on automatic memory management by setting the MEMORY_TARGET parameter:
SQL> alter system set memory_target = 1000M;
From this point on, the database runs under the automatic memory management mode, with it shrinking and growing the individual allocations to the various components of Oracle memory according to the requirements of the ongoing workload. You can change the value of the MEMORY_TARGET parameter dynamically anytime, as long as you don’t exceed the value you set for the MEMORY_MAX_TARGET parameter.
■ Tip The term “target” in parameters such as memory_target and pga_memory_target means just that—Oracle will try to stay under the target level, but there’s no guarantee that it’ll never go beyond that. It may exceed the target allocation on occasion, if necessary.
You don’t have to set the SGA_TARGET and PGA_AGGREGATE_TARGET parameters to 0 in order to use automatic memory management. In Recipe 3-3, we show how to set minimum values for these parameters even when you choose to implement automatic memory management. That recipe assumes you’re implementing automatic memory management, but that for some reason, you need to specify your own minimum values for components such as the SGA and the PGA.
How It Works
In earlier releases of the Oracle database, DBAs used to set values for the various SGA components, or would specify values for the SGA and the PGA. Starting with the Oracle Database 11g release, Oracle enables you to completely automate the entire instance memory allocation, by just setting a single initialization parameter, MEMORY_TARGET, under what’s known as automatic memory management. In this Recipe, we show you how to set up the automatic memory management feature in your database.
If you’re creating a new Oracle database with the help of the Database Configuration Assistant (DBCA), you’re given a choice among automatic memory management, shared memory management, and manual memory management. Select the automatic memory management option, and specify the values for two automatic memory-related parameters: MEMORY_TARGET and MEMORY_MAX_TARGET. The first parameter sets the current value of the memory allocation to the database, and the second parameter sets the limit to which you can raise the first parameter if necessary.
Oracle’s memory structures consist of two distinct memory areas. The system global area (SGA) contains the data and control information and is shared by all server and background processes. The SGA holds the data blocks retrieved from disk by Oracle. The program global area (PGA) contains data and control information for a server process. Each server process is allocated its own chunk of the PGA. Managing Oracle’s memory allocation involves careful calibration of the needs of the database. Some database instances need more memory for certain components of the memory. For example, a data warehouse will need more PGA memory in order to perform huge sorts that are common in such an environment. Also, during the course of a day, the memory needs of the instance might vary; during
business hours, for example, the instance might be processing more online transaction processing (OLTP) work, whereas after business hours, it might be running huge batch jobs that involve data warehouse processing, jobs that typically need higher PGA allocations per each process.
In prior versions of the Oracle database, DBAs had to carefully decide the optimal allocation of memory to the individual components of the memory one allocated to the database. Technically, you can still manually set the values of the individual components of the SGA as well as set a value for the PGA, or partially automate the process by setting parameters such as SGA_TARGET and PGA_AGGREGATE_TARGET. Although Oracle still allows you to manually configure the various components of memory, automatic memory management is the recommended approach to managing Oracle’s memory allocation. Once you specify a certain amount of memory by setting the MEMORY_TARGET and MEMORY_MAX_TARGET parameters, Oracle automatically tunes the actual memory allocation, by
redistributing memory between the SGA and the PGA.
■ Tip When you create a database with the Database Configuration Assistant (DBCA), automatic memory management is the default.
Oracle Database 11g lets you automate all the memory allocations for an instance, including shared memory and the PGA memory, if you choose to implement automatic memory management by setting the MEMORY_TARGET and MEMORY_MAX_TARGET parameters. Under an automatic memory management regime, Oracle automatically tunes the total SGA size, the SGA component sizes, the instance PGA size, and the individual PGA size. This dynamic memory tuning by the Oracle instance optimizes database performance, as memory allocations are changed automatically by Oracle to match changing database workloads. Automatic memory management means that once you set the MEMORY_TARGET parameter, you can simply ignore the following parameters by not setting them at all:
• SGA_TARGET
• PGA_AGGREGATE_TARGET
• DB_CACHE_SIZE
• SHARED_POOL_SIZE
• LARGE_POOL_SIZE
• JAVA_POOL_SIZE\
If you’re moving from a system where you were using the SGA_TARGET and PGA_AGGREGATE_TARGET parameters, you can follow the procedures shown in the “Solution” section of this recipe to move to the newer automatic memory management mode of managing Oracle’s memory allocation. Note that while setting the MEMORY_TARGET parameter is mandatory for implementing automatic memory management, the MEMORY_MAX_TARGET parameter isn’t—if you don’t set this parameter, Oracle sets its value internally to that of the MEMORY_TARGET parameter. Also, the MEMORY_MAX_TARGET parameter acts as the upper bound for the MEMORY_TARGET parameter. Oracle has different minimum permissible settings for the MEMORY_TARGET
parameter, depending on the operating system. If you try to set this parameter below its minimum allowable value, the database will issue an error. Some of the memory components can’t shrink quickly and some components must have a minimum size for the database to function properly. Therefore, Oracle won’t let you set too low a value for the MEMORY_TARGET parameter. The following example shows this:
SQL> alter system set memory_target=360m scope=both;
alter system set memory_target=360m scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least
544M
SQL> alter system set memory_target=544m scope=both;
alter system set memory_target=544m scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least
624M
SQL> alter system set memory_target=624m scope=both;
System altered.
SQL>
You’ll notice that Oracle issued an error when we tried to set a very low value for the MEMORY_TARGET
parameter. Note that Oracle took iterations to decide to let you know the minimum allowable level for
the MEMORY_TARGET parameter.
How does one go about setting the value of the MEMORY_MAX_TARGET parameter? It’s simple—you just pick a value that’s high enough to accommodate not only the current workloads, but also the future needs of the database. Since the MEMORY_TARGET parameter is dynamic, you can alter it on the fly and if necessary, re-allocate memory among multiple instances running on a server. Just be sure that you set the value of the MEMORY_MAX_TARGET parameter to a size that’s at least equal to the combined value of the present settings of the SGA_TARGET and the PGA_AGGREGATE_TARGET parameters (if you're migrating from the 10g release). Always make sure to check with your system administrator, so you don’t allocate too high an amount of memory for your Oracle instance, which could result in problems such as paging and swapping at the operating system level, which will affect not only your Oracle database, but also everything else that’s running on that server.
