Oracle 10 Gb System Management: Automatic SGA Memory Management

In Oracle 10 Gb system management, there is an automatic SGA memory management ASMM ). This function is described as follows:

To use automatic management, you need to set the parameters SGA_TARGET, STATISTICS_LEVEL = TYPICAL or ALL. Because the statistics set is not supported, the database does not have the necessary historical information to determine the size ). In Oracle9i and earlier versions, you can only use manual SGA memory management. The SGA_TARGET parameter does not exist, and the SGA_MAX_SIZE parameter is only an upper limit, not a dynamic target. However, the SGA_TARGET setting cannot exceed 10 Gb.

In Oracle 10 Gb, memory-related parameters can be classified into two categories:

◆ Automatically optimized SGA parameters: Currently, these parameters include DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, and JAVA_POOL_SIZE.

◆ Manual SGA parameters: These parameters include LOG_BUFFER, STREAMS_POOL, DB_NK_CACHE_SIZE, DB_KEEP_CACHE_SIZE, and DB_RECYCLE_CACHE_SIZE.

In Oracle 10 Gb, You can query V $ SGAINFO at any time to see which components of SGA can be adjusted. Oracle 10 Gb system management is greatly enhanced compared with previous versions)

When the automatic SGA memory management is adopted, the main parameter for determining the automatic adjustment component size is SGA_TARGET, which can be dynamically adjusted when the database is started and running, the maximum value can reach the value set by SGA_MAX_SIZE. The default value is SGA_TARGET. To add SGA_TARGET, you must set SGA_MAX_SIZE to a greater value before starting the database instance ). The database will use the SGA_TARGET value, and then subtract the size of other manually set components, such as DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE ), the calculated memory size is used to set the size of the default buffer pool, Shared Pool, large pool, and Java pool. During running, the instance dynamically allocates and revokes memory for the four memory zones as needed.

With the passage of time, the size of each SGA component becomes more and more fixed as the memory demand of the Instance becomes more and more determined. Even after the database is shut down and started again, the database can still remember the size of the component, so you do not have to start from the beginning every time to determine the correct size of the instance. This is achieved through four parameters with double underscores: __db_cache_size, _ JAVA_POOL_SIZE, _ LARGE_POOL_SIZE, and _ SHARED_POOL_SIZE. If the database is shut down normally or immediately, the database records these values in the storage parameter file SPFILE and uses these values to set the default size of each zone at startup.

In addition, if you know the minimum value of one of the four zones, you can set this parameter in addition to SGA_TARGET. The instance uses your settings as the lower limit, that is, the minimum possible size of the zone), but remember that Oracle cannot shrink the size of the corresponding zone to the minimum value.

The above section briefly introduces the automatic SGA memory management function in Oracle 10 Gb system management.

1. How to unlock users in Oracle
2. Oracle username re-indexing method
3. Explain how to query user tablespaces in Oracle
4. An analysis of the management method of the Oracle user permission table
5. Introduction to Oracle database backup and recovery features