What Is Automatic Shared Memory Management?
| Until Oracle 9i, the Oracle server allotted the required memory for fixed SGA and other internal allocations. The common problems with improper SGA sizing are undersized memory, leading to poor performance and out-of-memory errors (ORA-4031); and oversized memory, leading to wasted memory and latch contention. This scenario has changed with the introduction of Automatic Shared Memory Management, which is described in the following paragraphs. Automatic Shared Memory Management (ASMM) is the new manageability feature that enables Oracle Database 10g to automatically determine the appropriate values for SGA components within the total size limits of SGA. The commonly tuned SGA components are the database buffer cache, the shared pool, the large pool, and the Java pool. These initialization parameters are referred to as auto-tuned SGA parameters. In Oracle Database 10g, the DBA can assign the total amount of SGA available to an instance with the SGA_TARGET initialization parameter. Oracle database automatically distributes this memory among its various subcomponents for the most effective memory utilization. The SGA_TARGET parameter includes all SGA memory, including automatically sized components, manually sized components, and internal allocations during the database startup. Log buffer, other buffer caches (for example, recycle and keep), fixed SGA, and internal allocations are considered manually sized components in Oracle Database 10g. The default value for SGA_TARGET parameter is 0, with ASMM being disabled. The SGA is made up of pools of memory such as the shared pool, java pool, buffer cache, and so on. These SGA components allocate and deallocate space in measurement units of granules. The granule size is determined by the total SGA size. As a generalization, for most platforms with total SGA size equal to or less than 1GB, granule size is 4MB. Granule size is usually 16MB (8MB for Windows) for SGAs larger than 1GB. Some platform dependencies may arise. Refer to your operating systemspecific documentation for more details. The granule size can be identified from the V$SGAINFO view. All dynamic components in the SGA have the same granule size. If you specify the size of a SGA component at a value different from a multiple of its granule size, Oracle Database will round the specified size up to the nearest higher multiple. If the granule size is 16MB and you specify DB_CACHE_SIZE as 30MB, for example, the database will actually allocate 32MB.
To use ASMM, STATISTICS_LEVEL must be set to TYPICAL (default) or ALL. You can query the V$STATISTICS_LEVEL view to get more information on the status of the statistics controlled by the STATISTICS_LEVEL parameter. When ASMM is enabled in the database, the sizes of various SGA components are flexible. They resize among themselves to adapt to the workload needs without any additional DBA intervention. The database will automatically distribute the SGA among the various components as needed, maximizing the consumption of all available memory. If the manually tuned parameters are set, they consume their memory from SGA_TARGET, leaving remaining memory for automatically tuned components as listed in Table 5.1.
With automatic SGA management enabled, Oracle's internal tuning algorithm continuously monitors the workload performance and increases the shared pool as needed to reduce the number of parses. It increases the value in small chunks over time until the optimal size is reached, but does not shrink it back. The presence of open cursors, pinned PL/SQL packages, and other SQL execution states in the shared pool make it impossible to find granules that can be freed. With manual configuration, compiled SQL statements may frequently age out of the shared pool with inadequate size.
SGA Sizing ConsiderationsThe DBA can control the size of the automatically tuned SGA components by specifying minimum values for the components. This is helpful when an application needs a minimum amount of memory in order for specific components to work properly. You can query the V$SGA_DYNAMIC_COMPONENTS and V$SGAINFO views to get the current actual size of each SGA component, or get this information from the OEM memory configuration page. When SGA_TARGET is resized, automatically tuned components (without a set minimum value) are affected by the operation. All manually tuned components remain unaffected. When enabling Automatic Shared Memory Management, it is best to set SGA_TARGET to the desired non-zero value before starting the database. Dynamically modifying SGA_TARGET from zero to a non-zero value may not achieve the desired results because the shared pool may not be able to shrink. After startup, you can dynamically tune SGA_TARGET up or down as required. We discussed the new Memory Manager (MMAN) background process used by ASMM, in Chapter 1, "Exploring Oracle Database 10g Structure." The MMAN background process coordinates the sizing of the memory components and acts as a memory broker. It keeps track of all memory components and their pending resize operations. You can use the following V$ views to get more information about SGA components and their dynamic resizing:
|
EAN: 2147483647
Pages: 214