New Features of Oracle11g: AutoMemoryManagement

Welcome to the Oracle community forum and interact with 2 million technical staff. AutoMemoryManagement is a new feature proposed by Oracle10g. It has been further developed in the latest Oracle11g database. By using automatic memory management, the PGA and SGA memory in the Oracle database can be converted to each other based on the current workload.

Welcome to the Oracle community forum and interact with 2 million technical staff> Entering Auto Memory Management is a new feature proposed by Oracle10g and has been further developed in the latest Oracle11g database. By using automatic memory management, the PGA and SGA memory in the Oracle database can be converted to each other based on the current workload.

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

Auto Memory Management is a new feature proposed by Oracle10g. It has been further developed in the latest Oracle11g database. By using automatic memory management, the PGA and SGA memory in the Oracle database can be converted to each other, and the size of PGA and SGA in the Oracle memory area can be automatically set based on the current workload. This indirect memory conversion relies on the operating system's shared memory release mechanism to achieve internal instance optimization. Currently, this technology can be applied to Linux, Solaris, HPUX, AIX, Windows, and other operating systems.

First, let's review the automatic memory management features of Oracle10g. In Oracle10g databases, only the SHARED_POOL_SIZE, DB_CACHE_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, and STREAMS_POOL_SIZE SGA components can be automatically adjusted. The maximum value of PGA is determined by the initialization parameter quota, the maximum value of SGA is determined by the initialization parameter SGA_TARGET.

In the Oracle11g database, you no longer need to set the PGA_AGGREGATE_TARGET and SGA_TARGET parameters when using the automatic memory management feature, because both parameters have been modified to automatically optimized, these two parameters must be set unless you want to specify the minimum values of PGA and SGA. In the Oracle11g database, you need to set an initialization parameter named MEMORY_TARGET. This parameter refers to the memory size that can be used by the entire Oracle instance, including the overall size of PGA and SGA, within the memory size of MEMORY_TARGET, the memory used by PGA and SGA can be automatically converted to each other based on the current load. If the initial MEMORY_TARGET memory is not enough for the current database, Oracle11g also provides another initialization parameter MEMORY_MAX_TARGET. When the original memory is not enough, you can manually adjust the size of MEMORY_TARGET dynamically, but cannot exceed the value of MEMORY_MAX_TARGET. The following figure shows the memory size setting parameters of the Oracle11g database.

In addition, the Oracle11g database provides several views for monitoring automatic memory management:

V $ MEMORY_DYNAMIC_COMPONENTS: describes the status of all current memory components

V $ MEMORY_RESIZE_OPS: record the last 800 SGA size adjustment requests

X $ KMGSTFR: record the last 800 SGA conversion addresses in a loop.

_ MEMORY_MANAGEMENT_TRACING = 23: all memory conversion and adjustment behaviors are recorded as trace files.