5.0 Memory Management
There are two different memory management methods for the SGA, and two for the instance PGA.
To allow ASMM (Automatic Shared Memory Management) you specify the total amount of SGA memory available to an instance using the SGA_TARGET initialization parameter and Oracle instance automatically distributes this memory among the various SGA components to ensure the most effective memory utilization.
The Oracle instance automatically distributes the available memory among the various components as required, allowing the system to maximize the use of all available SGA memory. If you are using a server parameter file (SPFILE), the instance remembers the sizes of the automatically tuned SGA components across instance shutdowns. The instance can begin with information from the previous instance and continue evaluating workload where it left off at the last shutdown.
All SGA components allocate and deallocate space in units of granules. Granule size is determined by total SGA size. On most platforms, if the total SGA size is equal to or less than 1 GB, then granule size is 4 MB. For SGAs larger than 1 GB, granule size is 16 MB. If you specify a size for a component that is not a multiple of granule size, Oracle instance rounds the specified size up to the nearest multiple. You can query the V$SGAINFO view to see the granule size that is being used by an instance.
Memory management involves maintaining optimal sizes for the Oracle instance memory structures as demands on the database change. These structures are the system global area (SGA) and the instance program global area (collection of all individual PGAs).
There are two different memory management methods for the SGA, and two for the instance PGA.
- Automatic SGA Memory Management
- Manual SGA Memory Management
- Automatic PGA Memory Management
- Manual PGA Memory Management
5.0.1 Automatic SGA Memory Management
To allow ASMM (Automatic Shared Memory Management) you specify the total amount of SGA memory available to an instance using the SGA_TARGET initialization parameter and Oracle instance automatically distributes this memory among the various SGA components to ensure the most effective memory utilization.
The Oracle instance automatically distributes the available memory among the various components as required, allowing the system to maximize the use of all available SGA memory. If you are using a server parameter file (SPFILE), the instance remembers the sizes of the automatically tuned SGA components across instance shutdowns. The instance can begin with information from the previous instance and continue evaluating workload where it left off at the last shutdown.
All SGA components allocate and deallocate space in units of granules. Granule size is determined by total SGA size. On most platforms, if the total SGA size is equal to or less than 1 GB, then granule size is 4 MB. For SGAs larger than 1 GB, granule size is 16 MB. If you specify a size for a component that is not a multiple of granule size, Oracle instance rounds the specified size up to the nearest multiple. You can query the V$SGAINFO view to see the granule size that is being used by an instance.
Table below lists the SGA components that are automatically sized when SGA_TARGET is set.
The V$SGA_TARGET_ADVICE view provides information that helps you decide on a value for SGA_TARGET.
You can change to ASMM from manual shared memory management as:
- Obtain a value for SGA_TARGET.
- Set the value of SGA_TARGET, either by editing the text initialization parameter file and restarting the database, or by issuing the following statement:
- Set the values of the automatically sized SGA components listed in table given above to zero. Do this by editing the text initialization parameter file or by issuing ALTER SYSTEM statements.
5.0.2 Manual SGA Memory Management
To use MSMM (Manual Shared Memory Management) you can configure several SGA component sizes manually, and then monitor and tune these sizes on an ongoing basis as the database workload changes. If you create your database with DBCA and choose manual shared memory management, DBCA provides fields where you must enter sizes for the buffer cache, shared pool, large pool, and Java pool. It then sets the corresponding initialization parameters in the server parameter file (SPFILE) that it creates. If you instead create the database with the CREATE DATABASE statement and a text initialization parameter file, provide values for the initialization parameters that set SGA component sizes.
To enable manual shared memory management, set the MEMORY_TARGET and SGA_TARGET initialization parameter to 0. You must then set values for the various SGA components.
5.0.3 Automatic PGA Memory Management
By default, Oracle instance automatically manages the total amount of memory dedicated to the instance PGA. You can control this amount by setting the initialization parameter PGA_AGGREGATE_TARGET. Oracle instance then tries to ensure that the total amount of PGA memory allocated across all database server processes and background processes never exceeds this target.
If you create your database with DBCA, you can specify a value for the total instance PGA. DBCA then sets the PGA_AGGREGATE_TARGET initialization parameters in the server parameter file (SPFILE) that it creates. If you create the database with the CREATE DATABASE statement and a text initialization parameter file, you can provide a value for PGA_AGGREGATE_TARGET.
Following dynamic performance views provide PGA memory use statistics.
- V$PGA_TARGET_ADVICE
- V$PGASTAT
- V$SQL_WORKAREA
5.0.4 Manual PGA Memory Management
In manual PGA memory management, you manually tune SQL work areas. The database administrator controlled the maximum size of SQL work areas by setting the following parameters:
- SORT_AREA_SIZE
- HASH_AREA_SIZE
- BITMAP_MERGE_AREA_SIZE
- CREATE_BITMAP_AREA_SIZE
Oracle strongly recommends that you leave automatic PGA memory management enabled. If you decide to tune SQL work areas manually, you must set the WORKAREA_SIZE_POLICY initialization parameter to MANUAL.
5.0.5 Database Smart Flash Cache
If your database is running on Solaris or Oracle Linux, you can optionally add another memory component: Database Smart Flash Cache (the flash cache). The flash cache is an extension of the SGA-resident buffer cache, providing a level 2 cache for database blocks. It can improve response time and overall throughput, especially for read-intensive online transaction processing (OLTP) workloads. The flash cache resides on one or more flash disk devices, which are solid state storage devices that use flash memory.
You can add flash cache when all of the following are true:
- The Buffer Pool Advisory section of your Automatic Workload Repository (AWR) report or STATSPACK report indicates that doubling the size of the buffer cache would be beneficial.
- db file sequential read is a top wait event.
- You have spare CPU.
As a general rule, size the flash cache to be between 2 times and 10 times the size of the buffer cache. Any multiplier less than two would not provide any benefit. If you are using automatic shared memory management, make the flash cache between 2 times and 10 times the size of SGA_TARGET. Using 80% of the size of SGA_TARGET instead of the full size would also suffice for this calculation.
For each database block moved from the buffer cache to the flash cache, a small amount of metadata about the block is kept in the buffer cache. For a single instance database, the metadata consumes approximately 100 bytes. For an Oracle Real Application Clusters (Oracle RAC) database, it is closer to 200 bytes. You must therefore take this extra memory requirement into account when adding the flash cache.
- If you are managing memory manually, increase the size of the buffer cache by an amount approximately equal to the number of database blocks that fit into the flash cache multiplied by 100 (or 200 for Oracle RAC).
- If you are using automatic shared memory management, increase the size of SGA_TARGET.
Following are the initialization parameters that you use to configure the flash cache.
You can use ALTER SYSTEM to set db_flash_cache_size to zero to disable the flash cache. Dynamically changing the size of the flash cache is not supported.
