10
Memory Management Techniques
CERTIFICATION OBJECTIVES
10.01 Implement Automatic Memory Management
10.02 Manually configure SGA parameters
10.03 Configure automatic PGA memory management
With any computing resource, memory is finite and is usually the most expensive computing resource in your environment. On your database servers, Oracle could be competing for memory resources with other non-database applications (although Oracle recommends dedicating servers for database functions). In any case, once you know how much memory Oracle can use, you must decide how to divide the memory among the Oracle processes and functions. You might be able to completely automate Oracle memory management, but you will likely encounter situations in which you’ll need to tweak some of your memory settings manually.
This chapter starts out with a review of the Oracle memory structures. (See Chapter 1 for an in-depth explanation of physical storage structures, memory structures, and background processes.) Oracle has many levels of memory automation; you can choose a “one-size-fits-all" approach, tune all memory yourself, or set up something in between. You can completely automate the total amount of memory using Automatic Memory Management (AMM), and Oracle will automatically adjust the memory allocation between the System Global Area (SGA) and the instance Program Global Area (PGA) automatically. If you want to have slightly more control over the total size of the SGA, you can use Automatic Shared Memory Management (ASMM) to set a target and maximum size for the SGA only, and Oracle will still automatically tune each SGA component. Understanding which SGA components can be automatically tuned and which must be manually tuned is key to passing the memory management questions on the exam.
Finally, the chapter wraps up with PGA memory management. You’ll learn how to automatically and manually allocate and tune the PGA memory allocation and how to monitor the performance of the instance PGA.
CERTIFICATION OBJECTIVE 10.01
Implement Automatic Memory Management
Oracle recommends that you configure a new database using AMM, and then monitor the database to see how well Oracle can manage the total memory allocation. You can switch your database to ASMM at a later time to exercise more control over the total size of the SGA, but still let Oracle manage the memory allocation for individual components of the SGA.
First, you’ll get a brief refresher on Oracle memory structures and the relationship between the memory structures within the SGA and the PGA. Next, you’ll learn how to configure AMM and learn about the dependencies between the various memory initialization parameters. Even with AMM enabled, you can still set a related memory initialization parameter to a lower bound to prevent Oracle from auto-tuning the parameter to a low or zero value. You’ll also see how to exercise a little more control over memory allocation by enabling ASMM. Regardless of your level of memory automation, you’ll need to know the dynamic performance views you can use to monitor how well Oracle is utilizing memory.
Understanding Oracle Memory Structures
Oracle uses the server’s physical memory to hold many things for an Oracle instance:
The Oracle executable code itself
Session information
Individual processes associated with the database
Information shared between processes (such as locks on database objects)
In addition, the memory structures contain user and data dictionary SQL statements, along with cached information that is eventually permanently stored on disk, such as data blocks from database segments and information about completed transactions in the database. The data area allocated for an Oracle instance is called the System Global Area (SGA). The Oracle executables reside in the software code area. In addition, an area called the Program Global Area (PGA) is private to each server and background process; one PGA is allocated for each user session or server process. The sum of memory for each private PGA area must be less than or equal to the server level PGA maximum limit.
Figure 10-1 (repeated from Chapter 1 for your convenience) shows the components of these Oracle memory structures.
FIGURE 10-1 Oracle logical memory structures
System Global Area
The SGA is a group of memory structures for an Oracle instance that are shared by the users of the database instance. When an Oracle instance is started, memory is allocated for the SGA based on the values specified in the initialization parameter file or hard-coded in the Oracle software depending on the automation level you’ve chosen.
Memory in the SGA is allocated in units of granules. A granule can be either 4MB or 16MB, depending on the total size of the SGA. If the SGA is less than or equal to 128MB, a granule is 4MB; otherwise, it is 16MB. The next few sections cover the highlights of how Oracle uses each section in the SGA.
Buffer Caches The database buffer cache holds blocks of data from disk that have been recently read to satisfy a SELECT statement or that contain modified blocks that have been changed or added from a Data Manipulation Language (DML) statement. Oracle allows for tablespaces with up to five different block sizes (one block size for the default, and up to four others). Each block size requires its own buffer cache.
Oracle can use two additional caches with the same block size as the default block size: the KEEP buffer pool and the RECYCLE buffer pool. Both of these pools allocate memory independently of other caches in the SGA.
When a table is created, you can specify the pool where the table’s data blocks will reside by using the BUFFER_POOL KEEP or BUFFER_POOL RECYCLE clause in STORAGE clause. For tables that you use frequently throughout the day, it would be advantageous to place the tables into the KEEP buffer pool to minimize the I/O needed to retrieve blocks in the table.
Shared Pool The shared pool contains two major subcaches: the library cache and the data dictionary cache. The library cache holds information about SQL and PL/SQL statements that are run against the database. In the library cache, because it is shared by all users, many different database users can potentially share the same SQL statement.
Along with the SQL statement itself, the execution plan of the SQL statement is stored in the library cache. The second time an identical SQL statement is run, by the same user or a different user, the execution plan is already computed, improving the execution time of the query or DML statement. If the library cache is sized too small, then frequently used execution plans can be flushed out of the cache, requiring just as frequent reloads of SQL statements into the library cache.
The data dictionary is a collection of database tables, owned by the SYS and SYSTEM schemas, that contain the metadata about the database, its structures, and the privileges and roles of database users. The data dictionary cache holds the most frequently requested rows from the data dictionary tables, except for table and column descriptions unless they are frequently accessed. Data blocks from tables in the data dictionary are used continually to assist in processing user queries and other DML commands.
If the data dictionary cache is too small, requests for information from the data dictionary will cause extra I/O to occur; these I/O-bound data dictionary requests are called recursive calls and should be avoided by sizing the data dictionary cache correctly.
Redo Log Buffer The redo log buffer holds the most recent changes to the data blocks in the datafiles. When the redo log buffer is one-third full, or every 3 seconds, or when a log switch occurs (a log switch can be forced), Oracle writes redo log records to the redo log files. The Log Writer (LGWR) process will write the redo log records to the redo log files when 1MB of redo is stored in the redo log buffer. The entries in the redo log buffer, once written to the redo log files, are critical to database recovery if the instance crashes before the changed data blocks are written from the buffer cache to the datafiles. A user’s committed transaction is not considered complete until the redo log entries and the transaction’s System Change Number (SCN) have been successfully written to the redo log files.
Large Pool The large pool is an optional area of the SGA. It is used for transactions that interact with more than one database, message buffers for processes performing parallel queries, and RMAN parallel backup and restore operations. As the name implies, the large pool makes available large blocks of memory for operations that need to allocate large blocks of memory at a time.
Java Pool The Java pool is used by the Oracle JVM (Java Virtual Machine) for all Java code and data within a user session. Storing Java code and data in the Java pool is analogous to SQL and PL/SQL code cached in the shared pool, except the Java pool is not subdivided as much as the shared pool.
Streams Pool The streams pool holds data and control structures to support the Oracle Streams feature of Oracle Enterprise Edition. Oracle Streams manages the sharing of data and events in a distributed environment.
Program Global Area
The PGA is an area of memory allocating dynamic sections of itself, privately for one set of connection processes. Each set of connection processes is a connection or session into a database. A session can originate as a single user connection from one user, or as a shared connection as part of a pool from an application or a web server. The configuration of the PGA depends on the connection configuration of the Oracle database: either shared server or dedicated.
In a shared server configuration, multiple users share a connection to the database, minimizing memory usage on the server, but potentially affecting response time for user requests. In a shared server environment, the SGA holds the persistent session information for a user instead of the PGA.
Shared server environments are ideal for a large number of simultaneous connections to the database with infrequent or short-lived requests. In a dedicated server environment, each user process gets its own connection to the database; the PGA contains the session memory for this configuration. The PGA also includes a sort area that is used whenever a user request requires a sort, bitmap merge, or hash join operation.
Software Code Area
Software code areas store the Oracle executable files that are running as part of an Oracle instance. These code areas are static in nature and change only when a new release of the software is installed. Typically, the Oracle software code areas are located in a privileged memory area separate from other user programs.
Oracle software code is strictly read-only and can be installed either shared or nonshared. Installing Oracle software code as sharable saves memory when multiple Oracle instances are running on the same server and at the same software release level.
Configuring Automatic Memory Management
A good starting point for a new database installation is to use Automatic Memory Management. Using AMM, Oracle takes your memory target and automatically balances SGA and PGA memory based on the current workload. You use two initialization parameters to control AMM, MEMORY_TARGET and MEMORY_MAX_TARGET. You use MEMORY_TARGET to dynamically set the combined SGA and PGA memory usage. By now you should be very familiar with the ALTER SYSTEM command used to change MEMORY_TARGET (unless MEMORY_MAX_TARGET is exceeded):
The parameter MEMORY_MAX_TARGET, in contrast, is an upper bound for MEMORY_TARGET and is not dynamic. You set MEMORY_MAX_TARGET as a static parameter to ensure that you cannot set MEMORY_TARGET too high while the database is running. If MEMORY_MAX_TARGET is not set at instance startup, it is set to the value of MEMORY_TARGET.
Table 10-1 details the effects of setting other initialization parameters when MEMORY_TARGET is set to a nonzero value. This table will be referenced again later in the chapter.
TABLE 10-1 Dependencies for a Nonzero MEMORY_TARGET
When |
The behavior is… |
Both |
|
|
Both parameters are still auto-tuned, but |
|
Both parameters are still auto-tuned, but |
Both |
Both |
|
The exam very specifically gives you scenarios combining various initialization parameters such as TABLE 10-2 Dependencies for a Zero MEMORY_TARGET
When |
The behavior is… |
|
Oracle auto-tunes SGA components, and PGA is auto-tuned whether or not it is explicitly set |
|
Some SGA components must be explicitly specified, and PGA is auto-tuned |
Table 10-2 details the effects and behavior of other related initialization parameters when MEMORY_TARGET is not set, or explicitly set to zero.
To enable automatic memory management, you can use the current values for SGA_TARGET and PGA_AGGREGATE_TARGET to calculate the desired value for MEMORY_TARGET. Follow these steps:
1. Use the SQL*Plus command SHOW PARAMETER TARGET to determine the current SGA and PGA targets.
2. Find the maximum PGA allocated from V$PGASTAT using the PGA statistic ‘maximum PGA allocated’.
3. Calculate MEMORY_TARGET by adding the SGA target value to the maximum of the PGA target and the maximum PGA allocated since the instance was started.
4. Set the MEMORY_MAX_TARGET in the SPFILE to a value at least as high as MEMORY_TARGET in step 3.
5. Restart the instance.
6. Set MEMORY_TARGET in both the running instance and the SPFILE to the value calculated in step 3, and set both SGA_TARGET and PGA_AGGREGATE_TARGET to zero if you don’t want a minimum value for those parameters.
EXERCISE 10-1
Enable Automatic Memory Management
In this exercise, you have upgraded your database from Oracle 10g to 11g and want to enable automatic memory management:
1. Use the SQL*Plus command SHOW PARAMETER TARGET to determine the current SGA and PGA targets:
2. Find the maximum PGA allocated from V$PGASTAT:
Since the value from V$PGASTAT is higher that the PGA target value, you will use 222M in the next step.
3. Calculate MEMORY_TARGET by adding the current SGA target value to the larger of the PGA target and the maximum PGA allocated since the instance was started:
4. Set the MEMORY_MAX_TARGET in the SPFILE to a value at least as high as your calculation for MEMORY_TARGET in step 3 (adding 100MB in this case):
5. Restart the instance.
You need to shut down the instance, since MEMORY_MAX_TARGET is not a dynamic parameter.
6. Set MEMORY_TARGET in both the running instance and the SPFILE to the value calculated in step 3, and set both SGA_TARGET and PGA_AGGREGATE_TARGET to zero if you don’t want a minimum value for those parameters:
Monitoring Automatic Memory Management
As with most, if not all, features of Oracle Database, you can monitor the feature using either Enterprise Manager (EM) or dynamic performance views. To monitor Automatic Memory Management (AMM), start at the EM home page, select the Server tab, and then click the Memory Advisors link under Database Configuration. You’ll see the screen shown in Figure 10-2.
FIGURE 10-2 Monitoring AMM using EM
You can see the total memory allocated as the sum of the SGA and PGA allocation. At the bottom of the screen, you can see the SGA memory allocation by SGA components. This screen also provides the option to switch back and forth between SGA and PGA.
Alternatively, you can go straight to the source and use dynamic performance views to see the status of memory components and look for tuning advice:
V$MEMORY_DYNAMIC_COMPONENTS Current status of all memory components.
V$MEMORY_RESIZE_OPS A circular buffer of the last 800 memory sizing requests.
V$MEMORY_TARGET_ADVICE Tuning advice for the MEMORY_TARGET initialization parameter.
In this example, the V$MEMORY_TARGET_ADVICE view is queried to compare the effects of changing the MEMORY_TARGET initialization parameter:
The value of 1 for the column MEMORY_SIZE_FACTOR reflects the current value for MEMORY_SIZE, and for this instance, MEMORY_SIZE is 404MB. EST_DB_TIME is the amount of time required to complete the current workload. In this scenario, decreasing the MEMORY_SIZE value will gradually increase the amount of time required to complete the current workload. Increasing the amount of memory by about 100MB will noticeably improve the performance of the instance. However, any increases beyond 100MB given the current workload will not have much effect on performance.
CERTIFICATION OBJECTIVE 10.02
Manually Configure SGA Parameters
If you want to exercise more control over memory allocation for the SGA (PGA memory tuning is covered later in this chapter), you can switch from completely AMM to Automatic Shared Memory Management (ASMM). Enabling ASMM is as easy as setting SGA_TARGET to the desired value. Even after enabling ASMM, you can still control the minimum size of the components within the SGA controlled by SGA_TARGET. In addition, you will still need to tune some initialization parameters manually.
|
Understanding Automatic Shared Memory Management
Using ASSM helps you simplify SGA memory management if you want a fixed amount of memory allocated to the SGA. You will likely experience less memory-related errors when components such as the buffer cache can expand during heavy online transaction processing (OLTP) activity; or, conversely, if the large pool can expand at the expense of the buffer cache during non-peak hours when OLTP activity is low and RMAN backups require more memory in the large pool.
The adjustments to the automatically tuned parameters are saved across shutdowns if you are using an SPFILE. Therefore, you don’t have to wait for Oracle to relearn the optimal parameters every time you restart the instance.
ASMM uses the Memory Manager (MMAN) background process to coordinate changes in memory size. Every few minutes, MMAN checks the size of each component and makes adjustments when one component needs more memory and another component is not using its allocation fully.
Switching to ASMM
If you are switching to ASMM, you are either switching from completely manual shared memory management or from AMM; the procedure is slightly different for each scenario. In both cases, the value of SGA_TARGET is set to the desired SGA size.
Switching from Manual Memory Management to ASMM
If you are using manual SGA memory management, your SGA-related parameters should already be set to somewhat reasonable values. Use the sum of the values in the VALUE column of the V$SGA dynamic performance view, less the memory identified by CURRENT_SIZE in the dynamic performance view V$SGA_DYNAMIC_FREE_MEMORY, which is currently not in use. Use the result of this calculation to set SGA_TARGET.
To maximize Oracle’s automatic tuning, set the initialization parameters in Table 10-3 to zero using ALTER SYSTEM commands.
TABLE 10-3 Auto-tuned SGA Parameters
SGA Component |
Initialization Parameter |
Shared pool |
|
Large pool |
|
Java pool |
|
Buffer cache |
|
Streams pool |
|
You can also leave any of these parameters with a nonzero value, and Oracle will ensure that the memory allocated for that memory area does not fall below that value. For example, you may want to ensure that the size of the shared pool does not fall below 128MB for a poorly designed application that will fail unless the shared pool is at least 128MB. But keep in mind that this prevents other SGA components from using the memory allocated for the shared pool. You should have to use this feature rarely or never, but when you do, use it with caution.
EXERCISE 10-2
Enable Automatic Shared Memory Management
In this exercise, you’ll switch from manual memory management to ASMM.
1. Using this query, calculate a value for SGA_TARGET based on the current values:
In other words, calculate the total memory currently allocated to SGA components, less the amount of unused memory within the allocation.
2. Use this value to set the value of SGA_TARGET (remember that this new value must be less than SGA_MAX_SIZE):
3. Set the initialization parameters in Table 10-3 to zero:
Switching from AMM to ASMM
Switching from AMM to ASMM is even easier than switching from manual memory management to ASMM. First, set the value of MEMORY_TARGET to zero:
After you issue this command, Oracle automatically sets SGA_TARGET based on the current SGA memory usage. As you did when converting from manual memory management to ASMM, set the initialization parameters in Table 10-3 to zero to maximize Oracle’s ability to auto-tune those parameters.
Disabling ASMM
You can dynamically disable ASMM by setting SGA_TARGET to zero:
All of the initialization parameters in Table 10-3 are set to their current auto-tuned value. Note that the current values will most likely be different from any minimum values assigned to those parameters in the SPFILE.
Identifying ASMM Manually Tuned Parameters
When you use ASMM, only the parameters in Table 10-3 are auto-tuned. The following SGA parameters, however, must be manually tuned:
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
DB_nK_CACHE_SIZE (where n = 2, 4, 8, 16, or 32)
LOG_BUFFER
When you set these parameters manually, the memory allocated to these parameters is subtracted from the memory specified by SGA_TARGET.
Modifying the SGA_TARGET Parameter
Because SGA_TARGET is a dynamic parameter, you can adjust its size as long as it is not greater than SGA_MAX_SIZE. SGA_MAX_SIZE is analogous to the parameters MEMORY_MAX_TARGET and MEMORY_TARGET in that it provides an upper limit to the value of SGA_TARGET.
When you increase the value of SGA_TARGET, the additional memory is distributed among the other memory components that are currently being auto-tuned. Similarly, reducing the value of SGA_TARGET proportionally reduces the memory from the memory components that are auto-tuned.
Modifying Auto-Tuned Parameters
As mentioned, the parameters in Table 10-3 can be manually adjusted even if you set SGA_TARGET to auto-tune these parameters. At instance startup, any nonzero values for these parameters sets a lower limit for the memory allocated to the component. When the instance is running, you can change the values of these parameters as well. However, the memory allocated to the component is not changed unless the value you specify with ALTER SYSTEM is greater than the amount of memory currently allocated to the component. For example, if SHARED_POOL_SIZE is set to 300MB, and the current auto-tune value is 400MB, then setting SHARED_POOL_SIZE to 500MB will automatically allocate another 100MB from the other auto-tuned components.
On the other hand, if SHARED_POOL_SIZE is set to 300MB, and the current auto-tune value is 400MB, then setting SHARED_POOL_SIZE to 350MB will not change the memory allocated to the shared pool. The amount of memory allocated to the shared pool, however, cannot fall below 350MB when Oracle auto-tunes all SGA components.
Tuning SGA Components
Ideally, all of the SGA should fit into physical memory and should not be swapped out to disk as virtual memory. You can set the initialization parameter LOCK_SGA on some operating systems to TRUE to ensure that the SGA will always be in physical memory. Unfortunately, you cannot use AMM or ASMM with the LOCK_SGA parameter.
Your tuning goal, when manually tuning the SGA buffer cache, is to have a high buffer cache hit ratio. In other words, requested blocks are already in the buffer cache instead of on disk.
A high hit ratio might not necessarily be an indication of a well-tuned system. For example, a query running 100 times per second may request the same small set of data blocks over and over, artificially raising the hit ratio. In this case, application tuning (or user education) is in order.
Similarly, a low hit ratio may not mean a poorly tuned system. Large full table scans, where the table is larger than the buffer cache, might be necessary because the optimizer determines it will take less time than using an index. However, a full table scan will not take advantage of blocks that might already be in the buffer cache for the table that is being scanned.
CERTIFICATION OBJECTIVE 10.03
Configure Automatic PGA Memory Management
PGA memory (the total instance PGA memory and the PGA memory allocated to each user or background process) is automatically managed along with SGA memory when you use AMM. If you need further control of PGA memory, you can use the initialization parameter PGA_AGGREGATE_TARGET to set a desired upper limit for PGA memory used by all server and background processes.
Understanding the components of the PGA will help you understand how to set PGA_AGGREGATE_TARGET. You also need to understand which dynamic performance views will help you see how efficiently the PGA is using memory.
Understanding PGA Components
As mentioned at the beginning of this chapter, the PGA is a memory region containing data and control information for server and background processes. This memory is dedicated to the server or background process. In other words, it is not shared with any other server or background process. The total of all PGA memory allocated to all server and background processes is known as the instance or aggregate PGA.
If you have configured shared servers for your database connections, the connection information is located in the SGA in either the shared pool or the large pool. This makes sense because the SGA is a shared area. PGA memory contains the following components:
Private SQL area Bind information and run-time memory structures for each session’s execution of the same SQL statement (in the library cache).
Cursor and SQL area Named cursors created by Oracle Call Interface (OCI) or Pro*C applications.
Work area Memory to support memory-intensive operators such as ORDER BY, GROUP BY, hash joins, bitmap operations and bulk loads.
Session memory Session variables such as logon information; if you are using shared servers, session memory is shared in the shared pool or large pool.
Configuring PGA Memory Management
Automatic PGA memory management is enabled by default and behaves similarly whether you are using AMM or not. PGA_AGGREGATE_TARGET is either derived by AMM or you set it explicitly. Oracle then automatically manages the work areas for all sessions within the PGA depending on the workload. Initialization parameters such as SORT_AREA_SIZE and HASH_AREA_SIZE, which were the subject of frequent tuning headaches in previous versions of Oracle, are deprecated in Oracle Database 11g and are retained for backward compatibility. When automatic PGA memory management is enabled, these parameters are ignored if set but are calculated and show up in dynamic performance views such as V$PARAMETER:
When you create a new instance or until you have run representative workloads, you do not have any statistics available to precisely size the PGA using PGA_AGGREGATE_TARGET. Therefore, as a starting point, you must first determine how much server memory to allocate to Oracle. A typical value for a single instance, leaving memory for the operating system and assuming no other applications on the server, is 80 percent of physical memory.
From the memory allocated to Oracle, you can divide the memory between the SGA and PGA depending on the type of database applications you will run on the server:
Online Transaction Processing (OLTP) PGA can be as low as 20 percent of available memory.
Decision Support Systems (DSS) running memory-intensive queries PGA can range from 50 to 70 percent of available memory, with 50 percent as a good starting point.
Managing PGA Memory
Because PGA_AGGREGATE_TARGET is not a hard upper bound, Oracle may allocate more memory to the PGA during a typical workload. Thus, you should monitor PGA memory usage using dynamic performance views and, of course, the PGA Memory Advisor via the EM web interface.
The dynamic performance view V$PGA_STAT provides an overview of all PGA memory, as you can see from this query:
The value for cache hit percentage is the first place to look, which ideally is 100 percent if all work areas for all processes received all the memory they need. The value for cache hit percentage is calculated using extra bytes read/written. This value is the number of extra bytes that were read or written using multiple passes because the work areas did not have enough memory.
You can monitor an individual session’s PGA memory using V$PROCESS and these columns:
PGA_USED_MEM PGA memory currently in use by the process.
PGA_ALLOC_MEM Memory allocated for the process, including memory not yet released to the operating system.
PGA_MAX_MEM Maximum PGA memory ever used by the process.
For a quick overview of PGA memory usage and tuning recommendations, start from the EM home page, click the Server tab, and then click the Memory Advisors link under the Database Configuration heading. On the Memory Advisors page, click the PGA tab, and you’ll see the page shown in Figure 10-3.
FIGURE 10-3 Viewing PGA memory statistics and tuning advice using EM
As you can see, all the statistics on this page are derived from dynamic performance views such as V$PGASTAT.
CERTIFICATION SUMMARY
The chapter started with a brief refresher of Oracle memory structures. Even though this topic was covered thoroughly in Chapter 1, a reiteration of the Oracle memory structures is extremely helpful when considering how Oracle automatically manages and tunes memory and allocates the right amount of memory to the SGA and PGA from the total memory available.
The next topic, Automatic Memory Management, is the “set it and forgetit” feature of Oracle Database 11g that can greatly simplify memory management. After you set the total amount of memory that Oracle can use for the SGA and PGA using the MEMORY_TARGET parameter, Oracle automatically adjusts the total size of the SGA and PGA (and the structures within the SGA and PGA) to accommodate changing workloads, such as OLTP during the day, DSS queries in the evening, and backup operations overnight. Setting the memory target is only half of the story. You also need to monitor how Oracle is managing the memory. You saw the dynamic performance views as well as the EM pages you can use to monitor memory usage.
Allowing Oracle to completely manage all memory works in many environments, but there are always exceptions to the rule. Therefore, you may want to exercise a bit more control over SGA memory using the parameter SGA_TARGET. Even when the total amount of SGA memory is fixed and Oracle dynamically changes the memory usage of areas within the SGA, you can still set minimum values for several initialization parameters depending on your application requirements. You learned how to switch from either AMM or manual SGA management to ASMM. Furthermore, when ASMM does not provide the performance or fine-grained control you need over memory allocation, you can disable ASMM and revert back to manual SGA memory management.
The chapter wrapped up with a discussion of PGA memory management. Whether you are using AMM, ASMM, or manual SGA memory management, PGA memory management is automated using the parameter PGA_AGGREGATE_TARGET. Using AMM sets PGA_AGGREGATE_TARGET automatically. For ASMM and manual SGA memory management, you set PGA_AGGREGATE_TARGET manually and monitor PGA memory usage using dynamic performance views and EM.
TWO-MINUTE DRILL
Implement Automatic Memory Management
The System Global Area (SGA) is shared by all server and background processes.
The Program Global Area (PGA) is private to each server and background process unless you are using shared servers for user session connections.
The software code area contains the Oracle executable files that run as part of an Oracle instance.
You configure AMM by setting the parameter MEMORY_TARGET.
MEMORY_MAX_TARGET is an upper bound for MEMORY_TARGET. The former is not a dynamic parameter.
When MEMORY_TARGET is set and both SGA_TARGET and PGA_AGGREGATE_TARGET are set, then SGA_TARGET and PGA_AGGREGATE_TARGET are used as minimum values.
When MEMORY_TARGET is set and neither SGA_TARGET nor PGA_AGGREGATE_TARGET are set, then SGA_TARGET is set to 60 percent and PGA_AGGREGATE_TARGET is set to 40 percent.
Manually Configure SGA Parameters
You can set MEMORY_TARGET to zero and SGA_TARGET to a nonzero value to exercise more control over SGA memory.
Adjustments to automatically tuned SGA parameters are saved across instance restarts.
ASMM uses the MMAN background process to coordinate changes in memory size.
The five auto-tuned ASMM initialization parameters are SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, DB_CACHE_SIZE, and STREAMS_POOL_SIZE.
The manually tuned ASMM initialization parameters are DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE, DB_nK_CACHE_SIZE, and LOG_BUFFER.
You can easily disable ASMM by setting SGA_TARGET to zero, but then the current auto-tuned ASMM initialization parameters are set to their current values.
The static parameter SGA_MAX_SIZE is the upper limit for the value of SGA_TARGET.
Configure Automatic PGA Memory Management
The parameter PGA_AGGREGATE_TARGET sets an upper limit for memory used by all server and background processes and enables auto-tuning of PGA memory.
PGA memory areas include private SQL areas, named cursors, work areas for sorting operations, and session-specific memory variables.
For OLTP systems, PGA memory can be as low as 20 percent of the memory allocated for Oracle.
For DSS systems, PGA memory can be as high as 70 percent of the memory allocated for Oracle.
The dynamic performance view V$PGASTAT contains a complete overview of PGA memory usage since instance startup.
The columns PGA_USED_MEM, PGA_ALLOC_MEM, and PGA_MAX_MEM in the dynamic performance view V$PROCESS detail the PGA memory usage for an individual process.
SELF TEST
The following questions will help you measure your understanding of the material presented in this chapter. Read all the choices carefully, because there might be more than one correct answer. Choose all correct answers for each question.
Implement Automatic Memory Management
1. Identify the true statement about Automatic Memory Management (AMM).
A. MEMORY_TARGET must be less than MEMORY_MAX_TARGET, and MEMORY_TARGET is a dynamic parameter.
B. MEMORY_TARGET enables AMM, and it is a static parameter.
C. MEMORY_MAX_TARGET enables AMM, and it is a static parameter.
D. MEMORY_MAX_TARGET enables AMM, and it is a dynamic parameter.
2. You set your initialization parameters as follows:
How does the value of MEMORY_TARGET vary in this instance?
A. MEMORY_TARGET ranges from 500M to 750M.
B. MEMORY_TARGET ranges from 500M to 1G.
C. MEMORY_TARGET ranges from 300M to 1G.
D. MEMORY_TARGET ranges from 300M to 750M.
E. You cannot set SGA_TARGET and PGA_AGGREGATE_TARGET when you set MEMORY_TARGET
3. You set your initialization parameters as follows:
You do not set PGA_AGGREGATE_TARGET. How do the values of SGA_TARGET and PGA_AGGREGATE_TARGET vary in the running instance?
A. Both parameters are auto-tuned, but PGA_AGGREGATE_TARGET starts out at MEMORY_TARGET – SGA_TARGET.
B. Only SGA_TARGET is auto-tuned, and PGA_AGGREGATE_TARGET is fixed at MEMORY_TARGET – SGA_TARGET.
C. Both parameters are auto-tuned, but PGA_AGGREGATE_TARGET starts out at 40 percent of available memory by default.
D. SGA_TARGET is fixed at 300M, and PGA_AGGREGATE_TARGET starts out at MEMORY_TARGET – SGA_TARGET.
Manually Configure SGA Parameters
4. Which of the following initialization parameters control Automatic Shared Memory Management (ASSM)?
A. SGA_MAX_SIZE
B. MEMORY_TARGET
C. MEMORY_MAX_TARGET
D. SGA_TARGET
E. SGA_MAX_TARGET
5. Which of the following parameters can you set to zero to maximize the auto-tuning capabilities of ASMM? (Choose all that apply.)
A. LOG_BUFFER
B. STREAMS_POOL_SIZE
C. DB_CACHE_SIZE
D. SHARED_POOL_SIZE
E. DB_8K_CACHE_SIZE
6. You want to switch from AMM to ASMM. What is the correct statement you need to run? (Choose the best answer.)
A. alter system set memory_max_target = 0;
B. alter system set memory_target = 0;
C. alter system set sga_target = 500m;
D. alter system set sga_max_size = 750m;
Configure Automatic PGA Memory Management
7. Your instance is configured for shared servers. Which of the following memory areas reside in private PGA memory? (Choose all that apply.)
A. Session bind information
B. Session memory
C. Log buffers
E. Bitmap join work areas
8. Your server’s physical memory is 8GB and no other applications or Oracle instances are running on the server. For a new Oracle instance running a decision support system, what is a good starting point for setting PGA_AGGREGATE_TARGET?
A. 3.2GB
B. 1.6GB
C. 4.48GB
D. 6.4GB
SELF TEST ANSWERS
Implement Automatic Memory Management
1. 
D. MEMORY_TARGET enables AMM; it is a dynamic parameter and cannot be more than MEMORY_MAX_TARGET.
A, B, and C are incorrect.
2. B. When you set MEMORY_TARGET, SGA_TARGET, and PGA_AGGREGATE_TARGET, then SGA_TARGET and PGA_AGGREGATE_TARGET are minimum values for the SGA and PGA—and MEMORY_TARGET can range from the sum of SGA_TARGET + PGA_AGGREGATE_TARGET to MEMORY_MAX_TARGET.
A is wrong because MEMORY_TARGET can be increased up to MEMORY_MAX_TARGET while the instance is running. C is wrong because the sum of SGA_TARGET + PGA_AGGREGATE_TARGET is a starting value for MEMORY_TARGET. D is wrong because the sum of SGA_TARGET + PGA_AGGREGATE_TARGET is a starting value for MEMORY_TARGET, and MEMORY_TARGET can be set as high as the value of MEMORY_MAX_TARGET. E is wrong because you can set SGA_TARGET and PGA_AGGREGATE_TARGET as minimum values in conjunction with MEMORY_TARGET.
3. A. SGA_TARGET starts out with a minimum value of 300M, PGA_AGGREGATE_TARGET starts out at MEMORY_TARGET – SGA_TARGET, and both parameters are auto-tuned.
B is wrong because both parameters are auto-tuned when MEMORY_TARGET is set. C is wrong because PGA_AGGREGATE_TARGET starts out at MEMORY_TARGET – SGA_TARGET. D is wrong because SGA_TARGET has a minimum value of 300M.
Manually Configure SGA Parameters
4. D. You set SGA_TARGET to enable ASSM, and you can still control the minimum values for auto-tuned parameters controlled by SGA_TARGET.
A is wrong because SGA_MAX_SIZE is the upper limit for the dynamic parameter SGA_TARGET. B is wrong because MEMORY_TARGET controls AMM, not ASMM. C is wrong because MEMORY_MAX_TARGET is the upper limit for the value of MEMORY_TARGET. E is wrong because there is no such parameter SGA_MAX_TARGET.
5. B, C, and D. In addition to STREAMS_POOL_SIZE, DB_CACHE_SIZE, and SHARED_POOL_SIZE, ASSM auto-tunes LARGE_POOL_SIZE and JAVA_POOL_SIZE.
A and E are wrong. LOG_BUFFER and all DB_nK_CACHE_SIZE parameters are not auto-tuned by ASSM.
6. B. When you set MEMORY_TARGET to zero, Oracle automatically sets SGA_TARGET based on current SGA memory usage and enables ASMM.
A is wrong because MEMORY_MAX_TARGET is not a dynamic parameter and does not disable AMM. C is wrong because setting SGA_TARGET while AMM is enabled only sets a minimum value for the SGA while AMM is enabled. D is wrong because SGA_MAX_SIZE only sets a maximum value for SGA_TARGET and does not disable AMM.
Configure Automatic PGA Memory Management
7. A, D, and E. Bind information, run-time memory structures, named cursors, and work areas are always in PGA memory.
B and C are wrong. Session memory is in the PGA only in a dedicated server configuration. Log buffers are always in the SGA.
8. A. Oracle memory is typically 80 percent of the server memory, with 50 percent of the remaining memory as a good starting point for PGA_AGGREGATE_TARGET in a DSS system. Therefore, PGA_AGGREGATE_TARGET = 0.50 * (8GB * 0.80) = 3.2GB.
B, C, and D are wrong because these are all incorrect starting points for PGA_AGGREGATE_TARGET. For OLTP systems, PGA can be as low as 20 percent, but not for DSS applications. Even for DSS applications, PGA can be as high as 70 percent but should start out at 50 percent of memory available to Oracle.