Several SGA memory allocation affect the number of parameters, in addition to SGA_MAX_SIZE, others are dynamic parameters that can be used during operation in the database ALTER SYSTEM statement to dynamically modify the parameters. SGA size is dynamic, by modifying these parameters can SGA increased or reduced.
SGA SGA memory components and grain
By the number of memory components, they are used to meet the specific memory allocation request memory pool. Such as the shared pool (used for buffering disk data blocks), all of the SAG with a smallest unit of memory allocation and recovery of memory space particles, Oracle in SAG within the system memory unit to track the distribution and use.
SGA memory allocation is distributed through the particle, the particle size of the size of the decision by the SGA. Generally to be, in most platforms, if the SGA is less than equal to 1G, then the particle size of 4MB, if more than 1G, particle size 16MB, is different in that 32-bit Windows platforms, SGA is larger than 1G, the particle size of the bit 8MB instead of 16MB, detailed information, see the operating system documentation.
V $ SGAINFO system by trying to view an example of the use of particle size. The same size for all the dynamic SGA components.
SGA component if the specified size is not an integer multiple of the particle size, then Oracle will seek full and rounded by their integer value assigned to SGA memory components. Rather than the size you specify. For example, if particle size is 4MB , you give DB_CACHE_SIZE parameter is set to 10MB, then the actual allocation of memory will be 12MB.
Limit the size of SGA
SGA_MAX_SIZE parameter specifies the SGA during the life of the instance of the maximum number. Dynamically modify these parameters affect the Buffer cache, shared pool, large pool, java pool, and streams pool size, and the total size of the SGA. But can not exceed SGA_MAX_SIZE parameters set value.
If not specified, the default initialization parameter to specify all the memory components and for a default value.
Use automatic shared memory management
By using SGA_TARGET parameters to enable automatic shared memory management. Previously manually assigned similar java_pool, large_pool this component will be automatically shared memory management, In addition, you must ensure that STATISTICS_LEVEL initialization parameter is set to TYPICAL (default) or ALL.
SGA_TARGET initialization parameter reflects the size of SGA.
Table 2-3 Automatic distribution of the size of SGA components and the corresponding parameters
SGA component initialization parameters
Fixed SGA and other Oracle database instance is not required to use the internal distribution
Shared Pool SHARED_POOL_SIZE
LARGE_POOL_SIZE large pool
Java pool JAVA_POOL_SIZE
High number of buffer cache DB_CACHE_SIZE
Table 2-4 Manually allocated space the size of SGA components using SGA_TARGET
SGA component initialization parameters
Log buffer LOG_BUFFER
The keep and recycle buffer caches DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
Non-standard block size buffer cache high number DB_nK_CACHE_SIZE
Stream Pond STREAMS_POOL_SIZE
In manual management mode, execute the following SQL queries are SGA_TARGET value.
SELECT (
(SELECT SUM (value) FROM V $ SGA) -
(SELECT CURRENT_SIZE FROM V $ SGA_DYNAMIC_FREE_MEMORY)
) "SGA_TARGET"
FROM DUAL;
Then you can delete the Oracle automatically manages the shared memory corresponding initialization parameter.
For example, the current administration in manual mode you by the following configuration, and SGA_MAX_SIZE set to 1200M:
SHARED_POOL_SIZE = 200M
DB_CACHE_SIZE = 500M
LARGE_POOL_SIZE = 200M
Its dynamic performance view search results follows
SHARED_POOL_SIZE = 200M
DB_CACHE_SIZE = 500M
LARGE_POOL_SIZE = 200M
The corresponding dynamic performance view of its search results are as follows:
SELECT SUM (value) FROM V $ SGA = 1200M
SELECT CURRENT_VALUE FROM V $ SGA_DYNAMIC_FREE_MEMORY = 208M
You can replace the manual distribution of size parameters (SHARED_POOL_SIZE, DB_CACHE_SIZE, LARGE_POOL_SIZE) to play the advantages of automatic shared memory management.
SGA_TARGET = 992M
992M is the 1200M get minus 208M.
By default, when you set a value for SGA_TARGET, the value of the parameters corresponding to all the automatically sized SGA components is set to zero. However, you can still exercise some control over the size of the automatically sized 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 initial start parameter. Here is an example configuration:
SGA_TARGET = 256M
SHARED_POOL_SIZE = 32M
DB_CACHE_SIZE = 100M
In this example, the shared pool and the default buffer pool will not be sized smaller than the specified values (32 M and 100M, respectively). The remaining 124M (256 minus 132) is available for use by all the manually and automatically sized components .
The actual distribution of values among the SGA components might look like this:
Actual shared pool size = 64M
Actual buffer cache size = 128M
Actual Java pool size = 60M
Actual large pool size = 4M
This parameter value determines the minimum allocation of SGA space. V $ SGA_DYNAMIC_COMPONENTS show the actual size of the current SGA components. You can also use Enterprise Manager memory configuration page to view the current SGA components of the actual value.
Dynamic Modification of SGA parameters
You can use the ALTER SYSTEM SQL statement dynamically modify parameters and control SGA_TARGET SGA memory components, the value of other parameters. In the following paragraphs is described species
SGA_TARGET initialization parameters of the dynamic changes
SGA_TARGET parameter values can be specified to SGA_MAX_SIZE parameter values can also be reduced, if the reduction of the value of SGA_TARGET, the system will adjust the size of shared memory and free memory. SGA_TARGET you can reduce the size of SGA memory components until it reaches the minimum value so far. 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.
When SGA_TARGET is modified, the consumption of physical memory depends on the operating system. In some UNIX platforms do not support dynamic shared memory. By the SGA to use the physical memory size is equal to SGA_MAX_SIZE parameter value. In such a small platform set SGA_TARGET parameters value of the parameter in SGA_MAX_SIZE no real benefit. in these platforms is not recommended to set SGA_MAX_SIZE parameters.
In other platforms, such as Solaris and Windows physical memory consumption is equal to SGA_TARGET parameter value.
When the size parameter adjustment SGA_TARGET affected only those who are automatically managed when the SGA memory components, and not to set initialization parameters minimum. Manually manage the SGA memory components that are not affected.
For example, if your environment consists of the following configuration:
SGA_MAX_SIZE = 1024M
SGA_TARGET = 512M
DB_8K_CACHE_SIZE = 128M
In this case, SGA_TARGET can dynamically adjust the 1024M, can be reduced to the Buffer Cache, Java Pool, Shared Pool, Large Pool in one or more SGA memory components is reduced to minimum. Exact value depends on Environmental factors, such as CPU number, but the value will always remain DB_8K_CACHE_SIZE 128M size.
When SGA_TARGE reduced, if the automatic management of the SGA memory components have set the minimum value, then these components will not be reduced. Look at the following parameters.
SGA_MAX_SIZE = 1024M
SGA_TARGET = 512M
DB_CACHE_SIZE = 96M
DB_8K_CACHE_SIZE = 128M again
In this example, if SGA_TARGET reduced, DB_8K_CACHE_SIZE always fixed in the 128M size, the size of other DB_CACHE_SIZE not less than 96M. So SGA_TARGET can reduce the number is limited.
Modify the parameters of the automatic management component
If not set SGA_TARGET initialization parameter, then the automatic SGA Guanli 的 functions are disabled in. Therefore, its initialization parameters of the meaning and Zaoqi's version of the same. Danshi, when automatic shared memory management functions Qi Yong, the Ji are automatically Management of SGA components to set the value, then this task is the parameter value is the lower limit. can dynamically modify the corresponding parameter value.
Modify the parameters manually adjust the size of the
Manually adjust the size of the parameters can be dynamically modified. But not the specified minimum, but the exact value.
Manual shared memory management
Number if you decide not to use automatic shared memory management, you must manually configure SGA for each component. This section provides information on the settings that control the size of each SGA component initialization parameters.
Set the buffer cache initialization parameters
Buffer initialization parameter determines the speed slow slow speed buffer size of SGA memory components. You can use them to specify a different number of database block size of the cache. Is a dynamic initialization parameter. If you intend to use the database more than kinds of block size, you must set the initialization parameters and at least one DB_CACHE_SIZE DB_nK_CACHE_SIZE initialization parameter. Oracle Database assigns a default value appropriate to B_CACHE_SIZE parameters, but DB_nK_CACHE_SIZE default value is 0, and no additional cache is allocated. buffer size affects performance. larger cache size would reduce the number of disk read and write, more memory, reduce the memory paging and exchange.
DB_CACHE_SIZE initialization parameters
DB_CACHE_SIZE initialization parameter replaces the DB_BLOCK_BUFFERS initialization parameters, DB_BLOCK_BUFFERS used in earlier versions. DB_CACHE_SIZE parameter specifies in bytes the size of the standard block size buffer cache. Therefore, to DB_CACHE_SIZE specify a value, you would determine the number of buffers that you need and multiple that value times the block size specified in DB_BLOCK_SIZE.
For compatibility with earlier versions of the database, DB_BLOCK_BUFFERS parameters can still be used, but it retains a static parameter and can not resize with the other parameters of the dynamic combination
DB_nK_CACHE_SIZE initialization parameters
Non-standard block size and number of block buffer initialization parameters specified by the following:
DB_2K_CACHE_SIZE
DB_4K_CACHE_SIZE
DB_8K_CACHE_SIZE
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE
Each parameter to specify the appropriate block size buffer cache size. For example:
DB_BLOCK_SIZE = 4096
DB_CACHE_SIZE = 12M
DB_2K_CACHE_SIZE = 8M
DB_8K_CACHE_SIZE = 4M
In this case, specify the standard database block size of the parameter value is 4K. Standard block size buffer cache size is 12M.
In addition, 2K and 8K of cache size and were configured as 8M 4M.
Note:
Parameters can not be used to adjust the standard block DB_nK_CACHE_SIZE size. For example, if DB_BLOCK_SIZE set to 2K,
Set DB_2K_CACHE_SIZE. Equal to 2K is invalid. Standard cache block size is always determined by the DB_CACHE_SIZE.
Specify the shared pool size
SHARED_POOL_SIZE initialization parameter is a dynamic parameter allows you to specify or adjust the size of SGA components of the shared pool.
Oracle database, select a suitable default value. Shared pool configuration in <
Large pool size specified
LARGE_POOL_SIZE initialization parameter is a dynamic parameter allows you to specify or adjust the size of SGA components of the large pool.
Large pool is an optional component of the SGA. If you want to create a large pool, you must specify the initialization parameters LARGE_POOL_SIZE.
Configure a large pool to discuss <
Java pool size specified
JAVA_POOL_SIZE initialization parameter is a dynamic parameter allows you to specify or adjust the size of SGA components of the Java pool.
Oracle database, select a suitable default value. Java pool is configured in the <
Pool size specified stream
STREAMS_POOL_SIZE initialization parameter is a dynamic parameter allows you to specify or adjust the SGA components of the flow pool size.
If STREAMS_POOL_SIZE is set to 0, then Oracle will use the flow products to meet the SGA shared pool memory needs.
Current pool configuration in <
View information on the SGA
The following view provides information about the dynamic SGA components and their size information:
View Description
V $ SGA shows a summary of information on the SGA.
V $ SGAINFO display information on the size of SGA, including the size information of different SGA components, memory, particle size, and free memory space.
V $ SGASTAT Show details SGA.
V $ SGA_DYNAMIC_COMPONENTS show SGA components. This view summarizes the examples start from the SGA resize operations since all the information.
V $ SGA_DYNAMIC_FREE_MEMORY will adjust the SGA size is displayed as a free memory space available.
V $ SGA_RESIZE_OPS Displays large cupboard on the recent 400 SGA resize operations information.
V $ SGA_CURRENT_RESIZE_OPS shows the current ongoing SGA resize operations on information. An adjustment operation is the dynamic SGA components increase or decrease.