| Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-01 |
|
|
View PDF |
| Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-01 |
|
|
View PDF |
If you prefer to exercise more direct control over the sizes of individual memory components, you can disable automatic memory management and configure the database for manual memory management. There are two different manual memory management methods for the SGA, and two for the instance PGA.
The two manual memory management methods for the SGA vary in the amount of effort and knowledge required by the DBA. With automatic shared memory management, you set target and maximum sizes for the SGA. The database then tunes the total size of the SGA to your designated target, and dynamically tunes the sizes of many SGA components. With manual shared memory management, you set the sizes of several individual SGA components, thereby determining the overall SGA size. You then manually tune these individual SGA components on an ongoing basis.
For the instance PGA, there is automatic PGA memory management, in which you set a target size for the instance PGA. The database then tunes the size of the instance PGA to your target, and dynamically tunes the sizes of individual PGAs. There is also manual PGA memory management, in which you set maximum work area size for each type of SQL operator (such as sort or hash-join). This memory management method, although supported, is not recommended.
The following sections provide details on all of these manual memory management methods:
See Also:
Oracle Database Concepts for an overview of Oracle Database memory management methods.This section contains the following topics:
See Also:
Oracle Database Performance Tuning Guide for information about tuning the components of the SGA
Automatic Shared Memory Management simplifies SGA memory management. You specify the total amount of SGA memory available to an instance using the SGA_TARGET initialization parameter and Oracle Database automatically distributes this memory among the various SGA components to ensure the most effective memory utilization.
When automatic shared memory management is enabled, the sizes of the different SGA components are flexible and can adapt to the needs of a workload without requiring any additional configuration. The database automatically distributes the available memory among the various components as required, allowing the system to maximize the use of all available SGA memory.
Oracle Database remembers the sizes of the automatically tuned components across instance shutdowns if you are using a server parameter file (SPFILE). As a result, the system does need to learn the characteristics of the workload again each time an instance is started. It can begin with information from the past instance and continue evaluating workload where it left off at the last shutdown.
The SGA comprises a number of memory components, which are pools of memory used to satisfy a particular class of memory allocation requests. Examples of memory components include the shared pool (used to allocate memory for SQL and PL/SQL execution), the java pool (used for java objects and other java execution memory), and the buffer cache (used for caching disk blocks). All SGA components allocate and deallocate space in units of granules. Oracle Database tracks SGA memory use in internal numbers of granules for each SGA component.
The memory for dynamic components in the SGA is allocated in the unit of granules. Granule size is determined by total SGA size. Generally speaking, 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. Some platform dependencies may arise. For example, on 32-bit Windows NT, the granule size is 8 MB for SGAs larger than 1 GB. Consult your operating system specific documentation for more details.
You can query the V$SGAINFO view to see the granule size that is being used by an instance. The same granule size is used for all components in the SGA.
If you specify a size for a component that is not a multiple of granule size, Oracle Database rounds the specified size up to the nearest multiple. For example, if the granule size is 4 MB and you specify DB_CACHE_SIZE as 10 MB, the database actually allocates 12 MB.
The SGA_MAX_SIZE initialization parameter specifies the maximum size of the System Global Area for the lifetime of the instance. You can dynamically alter the initialization parameters affecting the size of the buffer caches, shared pool, large pool, Java pool, and streams pool but only to the extent that the sum of these sizes and the sizes of the other components of the SGA (fixed SGA, variable SGA, and redo log buffers) does not exceed the value specified by SGA_MAX_SIZE.
If you do not specify SGA_MAX_SIZE, then Oracle Database selects a default value that is the sum of all components specified or defaulted at initialization time. If you do specify SGA_MAX_SIZE, and at the time the database is initialized the value is less than the sum of the memory allocated for all components, either explicitly in the parameter file or by default, then the database ignores the setting for SGA_MAX_SIZE and chooses a correct value for this parameter.
You enable the automatic shared memory management feature by setting the SGA_TARGET parameter to a nonzero value. This parameter in effect replaces the parameters that control the memory allocated for a specific set of individual components, which are now automatically and dynamically resized (tuned) as needed.
Note:
The
STATISTICS_LEVEL initialization parameter must be set to TYPICAL (the default) or ALL for automatic shared memory management to function.The SGA_TARGET initialization parameter reflects the total size of the SGA. Table 5-1 lists the SGA components for which SGA_TARGET includes memory—the automatically sized SGA components—and the initialization parameters corresponding to those components.
Table 5-1 Automatically Sized SGA Components and Corresponding Parameters
| SGA Component | Initialization Parameter |
|---|---|
|
Fixed SGA and other internal allocations needed by the Oracle Database instance |
N/A |
|
The shared pool |
|
|
The large pool |
|
|
The Java pool |
|
|
The buffer cache |
|
|
The Streams pool |
|
The parameters listed in Table 5-2, if they are set, take their memory from SGA_TARGET, leaving what is available for the components listed in Table 5-1.
Table 5-2 Manually Sized SGA Components that Use SGA_TARGET Space
| SGA Component | Initialization Parameter |
|---|---|
|
The log buffer |
|
|
The keep and recycle buffer caches |
|
|
Nonstandard block size buffer caches |
|
In addition to setting SGA_TARGET to a nonzero value, you must set the value of all automatically sized SGA components to zero to enable full automatic tuning of these components.
Alternatively, you can set one or more of the automatically sized SGA components to a nonzero value, which is then used as the minimum setting for that component during SGA tuning. This is discussed in detail later in this section.
Note:
An easier way to enable automatic shared memory management is to use Oracle Enterprise Manager (EM). When you enable automatic shared memory management and set the Total SGA Size, EM automatically generates theALTER SYSTEM statements to set SGA_TARGET to the specified size and to set all automatically sized SGA components to zero.
If you use SQL*Plus to set SGA_TARGET, you must then set the automatically sized SGA components to zero or to a minimum value.
For optimal performance in most systems, the entire SGA should fit in real memory. If it does not, and if virtual memory is used to store parts of it, then overall database system performance can decrease dramatically. The reason for this is that portions of the SGA are paged (written to and read from disk) by the operating system.
See your operating system documentation for instructions for monitoring paging activity. You can also view paging activity from the Performance property page of the Host page of Enterprise Manager.
The V$SGAINFO view provides information on the current tuned sizes of various SGA components.
The V$SGA_TARGET_ADVICE view provides information that helps you decide on a value for SGA_TARGET.
SQL> select * from v$sga_target_advice order by sga_size;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
290 .5 448176 1.6578 1636103
435 .75 339336 1.2552 1636103
580 1 270344 1 1201780
725 1.25 239038 .8842 907584
870 1.5 211517 .7824 513881
1015 1.75 201866 .7467 513881
1160 2 200703 .7424 513881
The information in this view is similar to that provided in the V$MEMORY_TARGET_ADVICE view for automatic memory management. See "Monitoring and Tuning Automatic Memory Management" for an explanation of that view.
Enterprise Manager provides an easy-to-use graphical memory advisor to help you select an optimal size for SGA_TARGET. See Oracle Database 2 Day DBA for details.
See Also:
Oracle Database Reference for more information about these dynamic performance viewsThe procedure for enabling automatic shared memory management (ASMM) differs depending on whether you are changing to ASMM from manual shared memory management or from automatic memory management.
To change to ASMM from manual shared memory management:
Run the following query to obtain a value for SGA_TARGET:
SELECT ( (SELECT SUM(value) FROM V$SGA) - (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY) ) "SGA_TARGET" FROM DUAL;
Set the value of SGA_TARGET, either by editing the text initialization parameter file and restarting the database, or by issuing the following statement:
ALTER SYSTEM SET SGA_TARGET=value [SCOPE={SPFILE|MEMORY|BOTH}]
where value is the value computed in step 1 or is some value between the sum of all SGA component sizes and SGA_MAX_SIZE. For more information on the ALTER SYSTEM statement and its SCOPE clause, see Oracle Database SQL Language Reference.
Do one of the following:
For more complete automatic tuning, set the values of the automatically sized SGA components listed in Table 5-1 to zero. Do this by editing the text initialization parameter file or by issuing ALTER SYSTEM statements.
To control the minimum size of one or more automatically sized SGA components, set those component sizes to the desired value. (See the next section for details.) Set the values of the other automatically sized SGA components to zero. Do this by editing the text initialization parameter file or by issuing ALTER SYSTEM statements.
To change to ASMM from automatic memory management:
Set the MEMORY_TARGET initialization parameter to 0.
ALTER SYSTEM SET MEMORY_TARGET = 0;
The database sets SGA_TARGET based on current SGA memory allocation.
Do one of the following:
For more complete automatic tuning, set the values of the automatically sized SGA components listed in Table 5-1 to zero. Do this by editing the text initialization parameter file or by issuing ALTER SYSTEM statements.
To control the minimum size of one or more automatically sized SGA components, set those component sizes to the desired value. (See the next section for details.) Set the values of the other automatically sized SGA components to zero. Do this by editing the text initialization parameter file or by issuing ALTER SYSTEM statements.
For example, suppose you currently have the following configuration of parameters for an instance configured for manual shared memory management and with SGA_MAX_SIZE set to 1200M:
SHARED_POOL_SIZE = 200M
DB_CACHE_SIZE = 500M
LARGE_POOL_SIZE=200M
Also assume the following query results:
| Query | Result |
|---|---|
SELECT SUM(value) FROM V$SGA |
1200M |
SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY |
208M |
You can take advantage of automatic shared memory management by setting Total SGA Size to 992M in Oracle Enterprise Manager, or by issuing the following statements:
ALTER SYSTEM SET SGA_TARGET = 992M; ALTER SYSTEM SET SHARED_POOL_SIZE = 0; ALTER SYSTEM SET LARGE_POOL_SIZE = 0; ALTER SYSTEM SET JAVA_POOL_SIZE = 0; ALTER SYSTEM SET DB_CACHE_SIZE = 0; ALTER SYSTEM SET STREAMS_POOL_SIZE = 0;
where 992M = 1200M minus 208M.
This section provides a closer look at automatic shared memory management. It includes the following topics:
You can exercise some control over the size of the automatically sized SGA components by specifying minimum values for the parameters corresponding to these components. Doing so can be useful if you know that an application cannot function properly without a minimum amount of memory in specific components. You specify the minimum amount of SGA space for a component by setting a value for its corresponding initialization parameter.
Manually limiting the minimum size of one or more automatically sized components reduces the total amount of memory available for dynamic adjustment. This reduction in turn limits the ability of the system to adapt to workload changes. Therefore, this practice is not recommended except in exceptional cases. The default automatic management behavior maximizes both system performance and the use of available resources.
When the automatic shared memory management feature is enabled, the internal tuning algorithm tries to determine an optimal size for the shared pool based on the workload. It usually converges on this value by increasing in small increments over time. However, the internal tuning algorithm typically does not attempt to shrink the shared pool, because the presence of open cursors, pinned PL/SQL packages, and other SQL execution state in the shared pool make it impossible to find granules that can be freed. Therefore, the tuning algorithm only tries to increase the shared pool in conservative increments, starting from a conservative size and stabilizing the shared pool at a size that produces the optimal performance benefit.
The SGA_TARGET parameter can be dynamically increased up to the value specified for the SGA_MAX_SIZE parameter, and it can also be reduced. If you reduce the value of SGA_TARGET, the system identifies one or more automatically tuned components for which to release memory. You can reduce SGA_TARGET until one or more automatically tuned components reach their minimum size. Oracle Database determines the minimum allowable value for SGA_TARGET taking into account several factors, including values set for the automatically sized components, manually sized components that use SGA_TARGET space, and number of CPUs.
The change in the amount of physical memory consumed when SGA_TARGET is modified depends on the operating system. On some UNIX platforms that do not support dynamic shared memory, the physical memory in use by the SGA is equal to the value of the SGA_MAX_SIZE parameter. On such platforms, there is no real benefit in setting SGA_TARGET to a value smaller than SGA_MAX_SIZE. Therefore, setting SGA_MAX_SIZE on those platforms is not recommended.
On other platforms, such as Solaris and Windows, the physical memory consumed by the SGA is equal to the value of SGA_TARGET.
For example, suppose you have an environment with the following configuration:
SGA_MAX_SIZE = 1024M
SGA_TARGET = 512M
DB_8K_CACHE_SIZE = 128M
In this example, the value of SGA_TARGET can be resized up to 1024M and can also be reduced until one or more of the automatically sized components reaches its minimum size. The exact value depends on environmental factors such as the number of CPUs on the system. However, the value of DB_8K_CACHE_SIZE remains fixed at all times at 128M
Note:
When enabling automatic shared memory management, it is best to setSGA_TARGET to the desired nonzero value before starting the database. Dynamically modifying SGA_TARGET from zero to a nonzero 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.When SGA_TARGET is not set, the automatic shared memory management feature is not enabled. Therefore the rules governing resize for all component parameters are the same as in earlier releases. However, when automatic shared memory management is enabled, the manually specified sizes of automatically sized components serve as a lower bound for the size of the components. You can modify this limit dynamically by changing the values of the corresponding parameters.
If the specified lower limit for the size of a given SGA component is less than its current size, there is no immediate change in the size of that component. The new setting only limits the automatic tuning algorithm to that reduced minimum size in the future. For example, consider the following configuration:
SGA_TARGET = 512M
LARGE_POOL_SIZE = 256M
Current actual large pool size = 284M
In this example, if you increase the value of LARGE_POOL_SIZE to a value greater than the actual current size of the component, the system expands the component to accommodate the increased minimum size. For example, if you increase the value of LARGE_POOL_SIZE to 300M, then the system increases the large pool incrementally until it reaches 300M. This resizing occurs at the expense of one or more automatically tuned components.If you decrease the value of LARGE_POOL_SIZE to 200, there is no immediate change in the size of that component. The new setting only limits the reduction of the large pool size to 200 M in the future.
Parameters for manually sized components can be dynamically altered as well. However, rather than setting a minimum size, the value of the parameter specifies the precise size of the corresponding component. When you increase the size of a manually sized component, extra memory is taken away from one or more automatically sized components. When you decrease the size of a manually sized component, the memory that is released is given to the automatically sized components.
For example, consider this configuration:
SGA_TARGET = 512M
DB_8K_CACHE_SIZE = 128M
In this example, increasing DB_8K_CACHE_SIZE by 16 M to 144M means that the 16M is taken away from the automatically sized components. Likewise, reducing DB_8K_CACHE_SIZE by 16M to 112M means that the 16M is given to the automatically sized components.
If you decide not to use automatic memory management or automatic shared memory management, you must manually configure several SGA component sizes, and then monitor and tune these sizes on an ongoing basis as the database workload changes. This section provides guidelines on setting the parameters that control the sizes of these SGA components.
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 SQL statement and a text initialization parameter file, you can do one of the following:
Provide values for the initialization parameters that set SGA component sizes.
Omit SGA component size parameters from the text initialization file. Oracle Database chooses reasonable defaults for any component whose size you do not set.
This section contains the following topics:
There is no initialization parameter that in itself enables manual shared memory management. You effectively enable manual shared memory management by disabling both automatic memory management and automatic shared memory management.
To enable manual shared memory management:
Set the MEMORY_TARGET initialization parameter to 0.
Set the SGA_TARGET initialization parameter to 0.
You must then set values for the various SGA components, as described in the following sections.
The buffer cache initialization parameters determine the size of the buffer cache component of the SGA. You use them to specify the sizes of caches for the various block sizes used by the database. These initialization parameters are all dynamic.
The size of a buffer cache affects performance. Larger cache sizes generally reduce the number of disk reads and writes. However, a large cache may take up too much memory and induce memory paging or swapping.
Oracle Database supports multiple block sizes in a database. If you create tablespaces with non-standard block sizes, you must configure non-standard block size buffers to accommodate these tablespaces. The standard block size is used for the SYSTEM tablespace. You specify the standard block size by setting the initialization parameter DB_BLOCK_SIZE. Legitimate values are from 2K to 32K.
If you intend to use multiple block sizes in your database, you must have the DB_CACHE_SIZE and at least one DB_nK_CACHE_SIZE parameter set. Oracle Database assigns an appropriate default value to the DB_CACHE_SIZE parameter, but the DB_nK_CACHE_SIZE parameters default to 0, and no additional block size caches are configured.
The sizes and numbers of non-standard block size buffers are specified by the following parameters:
DB_2K_CACHE_SIZE DB_4K_CACHE_SIZE DB_8K_CACHE_SIZE DB_16K_CACHE_SIZE DB_32K_CACHE_SIZE
Each parameter specifies the size of the cache for the corresponding block size.
Note:
Platform-specific restrictions regarding the maximum block size apply, so some of these sizes might not be allowed on some platforms.DB_BLOCK_SIZE=4096 DB_CACHE_SIZE=1024M DB_2K_CACHE_SIZE=256M DB_8K_CACHE_SIZE=512M
In the preceding example, the parameter DB_BLOCK_SIZE sets the standard block size of the database to 4K. The size of the cache of standard block size buffers is 1024MB. Additionally, 2K and 8K caches are also configured, with sizes of 256MB and 512MB, respectively.
Note:
TheDB_nK_CACHE_SIZE parameters cannot be used to size the cache for the standard block size. If the value of DB_BLOCK_SIZE is nK, it is invalid to set DB_nK_CACHE_SIZE. The size of the cache for the standard block size is always determined from the value of DB_CACHE_SIZE.The cache has a limited size, so not all the data on disk can fit in the cache. When the cache is full, subsequent cache misses cause Oracle Database to write dirty data already in the cache to disk to make room for the new data. (If a buffer is not dirty, it does not need to be written to disk before a new block can be read into the buffer.) Subsequent access to any data that was written to disk and then overwritten results in additional cache misses.
The size of the cache affects the likelihood that a request for data results in a cache hit. If the cache is large, it is more likely to contain the data that is requested. Increasing the size of a cache increases the percentage of data requests that result in cache hits.
You can change the size of the buffer cache while the instance is running, without having to shut down the database. Do this with the ALTER SYSTEM statement.
Use the fixed view V$BUFFER_POOL to track the sizes of the different cache components and any pending resize operations.
You can configure the database buffer cache with separate buffer pools that either keep data in the buffer cache or make the buffers available for new data immediately after using the data blocks. Particular schema objects (tables, clusters, indexes, and partitions) can then be assigned to the appropriate buffer pool to control the way their data blocks age out of the cache.
The KEEP buffer pool retains the schema object's data blocks in memory.
The RECYCLE buffer pool eliminates data blocks from memory as soon as they are no longer needed.
The DEFAULT buffer pool contains data blocks from schema objects that are not assigned to any buffer pool, as well as schema objects that are explicitly assigned to the DEFAULT pool.
The initialization parameters that configure the KEEP and RECYCLE buffer pools are DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE.
Note:
Multiple buffer pools are only available for the standard block size. Non-standard block size caches have a singleDEFAULT pool.See Also:
Oracle Database Performance Tuning Guide for information about tuning the buffer cache and for more information about multiple buffer pools
The SHARED_POOL_SIZE initialization parameter is a dynamic parameter that lets you specify or adjust the size of the shared pool component of the SGA. Oracle Database selects an appropriate default value.
In releases before Oracle Database 10g Release 1, the amount of shared pool memory that was allocated was equal to the value of the SHARED_POOL_SIZE initialization parameter plus the amount of internal SGA overhead computed during instance startup. The internal SGA overhead refers to memory that is allocated by Oracle Database during startup, based on the values of several other initialization parameters. This memory is used to maintain state for different server components in the SGA. For example, if the SHARED_POOL_SIZE parameter is set to 64MB and the internal SGA overhead is computed to be 12MB, the real size of the shared pool is 64+12=76MB, although the value of the SHARED_POOL_SIZE parameter is still displayed as 64MB.Starting with Oracle Database 10g Release 1, the size of the internal SGA overhead is included in the user-specified value of SHARED_POOL_SIZE. If you are not using automatic memory management or automatic shared memory management, the amount of shared pool memory that is allocated at startup is equal to the value of the SHARED_POOL_SIZE initialization parameter, rounded up to a multiple of the granule size. You must therefore set this parameter so that it includes the internal SGA overhead in addition to the desired value for shared pool size. In the previous example, if the SHARED_POOL_SIZE parameter is set to 64MB at startup, then the available shared pool after startup is 64-12=52MB, assuming the value of internal SGA overhead remains unchanged. In order to maintain an effective value of 64MB for shared pool memory after startup, you must set the SHARED_POOL_SIZE parameter to 64+12=76MB.
When migrating from a release that is earlier than Oracle Database 10g Release 1, the Oracle Database 11g migration utilities recommend a new value for this parameter based on the value of internal SGA overhead in the pre-upgrade environment and based on the old value of this parameter. Beginning with Oracle Database 10g, the exact value of internal SGA overhead, also known as startup overhead in the shared pool, can be queried from the V$SGAINFO view. Also, in manual shared memory management mode, if the user-specified value of SHARED_POOL_SIZE is too small to accommodate even the requirements of internal SGA overhead, then Oracle Database generates an ORA-371 error during startup, with a suggested value to use for the SHARED_POOL_SIZE parameter.When you use automatic shared memory management in Oracle Database 11g, the shared pool is automatically tuned, and an ORA-371 error would not be generated.
The LARGE_POOL_SIZE initialization parameter is a dynamic parameter that lets you specify or adjust the size of the large pool component of the SGA. The large pool is an optional component of the SGA. You must specifically set the LARGE_POOL_SIZE parameter if you want to create a large pool. Configuring the large pool is discussed in Oracle Database Performance Tuning Guide.
The JAVA_POOL_SIZE initialization parameter is a dynamic parameter that lets you specify or adjust the size of the java pool component of the SGA. Oracle Database selects an appropriate default value. Configuration of the java pool is discussed in Oracle Database Java Developer's Guide.
The STREAMS_POOL_SIZE initialization parameter is a dynamic parameter that lets you specify or adjust the size of the Streams Pool component of the SGA. If STREAMS_POOL_SIZE is set to 0, then the Oracle Streams product transfers memory from the buffer cache to the Streams Pool when it is needed. For details, see the discussion of the Streams Pool in Oracle Streams Concepts and Administration.
The RESULT_CACHE_MAX_SIZE initialization parameter is a dynamic parameter that enables you to specify the maximum size of the result cache component of the SGA. Typically, there is no need to specify this parameter, because the default maximum size is chosen by the database based on total memory available to the SGA and on the memory management method currently in use. You can view the current default maximum size by displaying the value of the RESULT_CACHE_MAX_SIZE parameter. If you want to change this maximum size, you can set RESULT_CACHE_MAX_SIZE with an ALTER SYSTEM statement or you can specify this parameter in the text initialization parameter file. In each case, the value is rounded up to the nearest multiple of 32K.
If RESULT_CACHE_MAX_SIZE is 0 upon instance startup, the result cache is disabled. To reenable it you must set RESULT_CACHE_MAX_SIZE to a nonzero value (or remove this parameter from the text initialization parameter file to get the default maximum size) and then restart the database.
Note that after starting the database with the result cache disabled, if you use an ALTER SYSTEM statement to set RESULT_CACHE_MAX_SIZE to a nonzero value but do not restart the database, querying the value of the RESULT_CACHE_MAX_SIZE parameter returns a nonzero value even though the result cache is still disabled. The value of RESULT_CACHE_MAX_SIZE is therefore not the most reliable way to determine if the result cache is enabled. You can use the following query instead:
SELECT dbms_result_cache.status() FROM dual; DBMS_RESULT_CACHE.STATUS() --------------------------------------------- ENABLED
The result cache takes its memory from the shared pool, so if you increase the maximum result cache size, consider also increasing the shared pool size.
The view V$RESULT_CACHE_STATISTICS and the PL/SQL package procedure DBMS_RESULT_CACHE.MEMORY_REPORT display information to help you determine the amount of memory currently allocated to the result cache.
The PL/SQL package function DBMS_RESULT_CACHE.FLUSH clears the result cache and releases all the memory back to the shared pool.
See Also:
Oracle Database Performance Tuning Guide for more information about the result cache
Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_RESULT_CACHE package procedures and functions.
Oracle Database Reference for more information about the V$RESULT_CACHE_STATISTICS view.
Oracle Real Application Clusters Administration and Deployment Guide for information on setting RESULT_CACHE_MAX_SIZE for a cluster database.
You can set a few additional initialization parameters to control how the SGA uses memory.
The LOCK_SGA parameter, when set to TRUE, locks the entire SGA into physical memory. This parameter cannot be used in conjunction with automatic memory management or automatic shared memory management.
The SHARED_MEMORY_ADDRESS and HI_SHARED_MEMORY_ADDRESS parameters specify the SGA's starting address at runtime. These parameters are rarely used. For 64-bit platforms, HI_SHARED_MEMORY_ADDRESS specifies the high order 32 bits of the 64-bit address.
The USE_INDIRECT_DATA_BUFFERS parameter enables the use of the extended buffer cache mechanism for 32-bit platforms that can support more than 4 GB of physical memory. On platforms that do not support this much physical memory, this parameter is ignored. This parameter cannot be used in conjunction with automatic memory management or automatic shared memory management.
See Also:
Oracle Database Reference for more information on these initialization parameters
By default, Oracle Database automatically and globally 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 Database 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 do not specify the total instance PGA, DBCA chooses a reasonable default.
If you create the database with the CREATE DATABASE SQL statement and a text initialization parameter file, you can provide a value for PGA_AGGREGATE_TARGET. If you omit this parameter, the database chooses a default value.
With automatic PGA memory management, sizing of SQL work areas for all dedicated server sessions is automatic and all *_AREA_SIZE initialization parameters are ignored for these sessions. At any given time, the total amount of PGA memory available to active work areas on the instance is automatically derived from the parameter PGA_AGGREGATE_TARGET. This amount is set to the value of PGA_AGGREGATE_TARGET minus the PGA memory allocated for other purposes (for example, session memory). The resulting PGA memory is then allotted to individual active work areas based on their specific memory requirements.
There are dynamic performance views that provide PGA memory use statistics. Most of these statistics are enabled when PGA_AGGREGATE_TARGET is set.
Statistics on allocation and use of work area memory can be viewed in the following dynamic performance views:
V$SYSSTATV$SESSTATV$PGASTATV$SQL_WORKAREAV$SQL_WORKAREA_ACTIVEThe following three columns in the V$PROCESS view report the PGA memory allocated and used by an Oracle Database process:
PGA_USED_MEMPGA_ALLOCATED_MEMPGA_MAX_MEMNote:
The automatic PGA memory management method applies to work areas allocated by both dedicated and shared server process. See Oracle Database Concepts for information about PGA memory allocation in dedicated and shared server modes.See Also:
Oracle Database Reference for information about views mentioned in this section
Oracle Database Performance Tuning Guide for information about using these views
Oracle Database supports manual PGA memory management, in which you manually tune SQL work areas.
In releases earlier than Oracle Database 10g, 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 and CREATE_BITMAP_AREA_SIZE. Setting these parameters is difficult, because the maximum work area size is ideally selected from the data input size and the total number of work areas active in the system. These two factors vary greatly from one work area to another and from one time to another. Thus, the various *_AREA_SIZE parameters are difficult to tune under the best of circumstances.
For this reason, 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.
Note:
The initialization parameterWORKAREA_SIZE_POLICY is a session- and system-level parameter that can take only two values: MANUAL or AUTO. The default is AUTO. You can set PGA_AGGREGATE_TARGET, and then switch back and forth from auto to manual memory management mode. When WORKAREA_SIZE_POLICY is set to AUTO, your settings for *_AREA_SIZE parameters are ignored.