About Automatic Memory Management

The simplest way to manage instance memory is to allow the Oracle Database instance to automatically manage and tune it for you. To do so (on most platforms), you set only atargetmemory size initialization parameter (MEMORY_TARGET) and optionally amaximummemory size initialization parameter (MEMORY_MAX_TARGET). The total memory that the instance uses remains relatively constant, based on the value ofMEMORY_TARGET, andthe instance automatically distributes memory between the system global area (SGA) and the instance program global area (instance PGA). As memory requirements change, the instance dynamically redistributes memory between the SGA and instance PGA.

When automatic memory management is not enabled, you must size both the SGA and instance PGA manually.

Because theMEMORY_TARGETinitialization parameter is dynamic, you can changeMEMORY_TARGETat any time without restarting the database.MEMORY_MAX_TARGET, which is not dynamic, serves as an upper limit so that you cannot accidentally setMEMORY_TARGETtoo high, and so that enough memory is set aside for the database instance in case you do want to increase total instance memory in the future. Because certain SGA components either cannot easily shrink or must remain at a minimum size, the instance also prevents you from settingMEMORY_TARGETtoo low.

If you create your database with Database Configuration Assistant (DBCA) and choose the basic installation option, automatic memory management is enabled. If you choose advanced installation, Database Configuration Assistant (DBCA) enables you to select automatic memory management.

Enabling Automatic Memory Management

If you did not enable automatic memory management upon database creation (either by selecting the proper options in DBCA or by setting the appropriate initialization parameters for theCREATE DATABASESQL statement), you can enable it at a later time. Enabling automatic memory management involves a shutdown and restart of the database.

To enable automatic memory management

Start SQL*Plus and connect to the database asSYSDBA.

Calculate the minimum value forMEMORY_TARGETas follows:

Determine the current sizes ofSGA_TARGETandPGA_AGGREGATE_TARGETby entering the following SQL*Plus command:

SHOW PARAMETER TARGET

SQL*Plus displays the values of all initialization parameters with the stringTARGETin the parameter name.

NAME TYPE VALUE------------------------------------ ----------- ----------------archive_lag_target integer 0db_flashback_retention_target integer 1440fast_start_io_target integer 0fast_start_mttr_target integer 0memory_max_target big integer 0memory_target big integer 0parallel_servers_target integer 16pga_aggregate_target big integer 90Msga_target big integer 272M

Run the following query to determine the maximum instance PGA allocated since the database was started:

select value from v$pgastat where name='maximum PGA allocated';

Compute the maximum value between the query result from step 2b andPGA_AGGREGATE_TARGET. AddSGA_TARGETto this value.

memory_target = sga_target + max(pga_aggregate_target, maximum PGA allocated)

For example, ifSGA_TARGETis 272M andPGA_AGGREGATE_TARGETis 90M as shown above, and if the maximum PGA allocated is determined to be 120M, thenMEMORY_TARGETshould be at least 392M (272M + 120M).

Choose the value forMEMORY_TARGETthat you want to use.

This can be the minimum value that you computed in step 2, or you can choose to use a larger value if you have enough physical memory available.

For theMEMORY_MAX_TARGETinitialization parameter, decide on a maximum amount of memory that you would want to allocate to the database for the foreseeable future. That is, determine the maximum value for the sum of the SGA and instance PGA sizes. This number can be larger than or the same as theMEMORY_TARGETvalue that you chose in the previous step.

Do one of the following:

If you started your Oracle Database instance with a server parameter file, which is the default if you created the database with the Database Configuration Assistant (DBCA), enter the following command:

ALTER SYSTEM SET MEMORY_MAX_TARGET = nM SCOPE = SPFILE;

wherenis the value that you computed in Step4.

TheSCOPE=SPFILEclause sets the value only in the server parameter file, and not for the running instance. You must include thisSCOPEclause becauseMEMORY_MAX_TARGETis not a dynamic initialization parameter.

If you started your instance with a text initialization parameter file, manually edit the file so that it contains the following statements:

memory_max_target = nMmemory_target = mM

wherenis the value that you determined in Step4, andmis the value that you determined in step3.

Note:

In a text initialization parameter file, if you omit the line forMEMORY_MAX_TARGETand include a value forMEMORY_TARGET, the database automatically setsMEMORY_MAX_TARGETto the value ofMEMORY_TARGET. If you omit the line forMEMORY_TARGETand include a value forMEMORY_MAX_TARGET, theMEMORY_TARGETparameter defaults to zero. After startup, you can then dynamically changeMEMORY_TARGETto a nonzero value, provided that it does not exceed the value ofMEMORY_MAX_TARGET.

Shut down and restart the database.

If you started your Oracle Database instance with a server parameter file, enter the following commands:

ALTER SYSTEM SET MEMORY_TARGET = nM;ALTER SYSTEM SET SGA_TARGET = 0;ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0;

wherenis the value that you determined in step3.

About Automatic Shared Memory Management

Automatic Shared Memory Management simplifies SGA memory management. You specify the total amount of SGA memory available to an instance using theSGA_TARGETinitialization 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.

If you are using a server parameter file (SPFILE), the database remembers the sizes of the automatically tuned SGA components across instance shutdowns. As a result, the database instance does not need to learn the characteristics of the workload again each time the instance is started. The instance can begin with information from the previous instance and continue evaluating workload where it left off at the last shutdown.

distibute：分配。

Components and Granules in the SGA

The SGA comprises several memorycomponents, 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 ofgranules. 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 theV$SGAINFOview 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 specifyDB_CACHE_SIZEas 10 MB, the database actually allocates 12 MB.

Enabling Manual Shared Memory Management

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 theMEMORY_TARGETinitialization parameter to 0.

Set theSGA_TARGETinitialization parameter to 0.

You must then set values for the various SGA components, as described in the following sections.

Enabling Automatic Shared Memory Management

The 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 forSGA_TARGET:

SELECT ( (SELECT SUM(value) FROM V$SGA) - (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY) ) "SGA_TARGET"FROM DUAL;

Set the value ofSGA_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}]

wherevalueis the value computed in step1or is some value between the sum of all SGA component sizes andSGA_MAX_SIZE. For more information on theALTERSYSTEMstatement and itsSCOPEclause, seeOracle Database SQL Language Reference.

Do one of the following:

For more complete automatic tuning, set the values of the automatically sized SGA components listed inTable 6-1to zero. Do this by editing the text initialization parameter file or by issuingALTERSYSTEMstatements.

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 issuingALTERSYSTEMstatements.

To change to ASMM from automatic memory management:

Set theMEMORY_TARGETinitialization parameter to 0.

ALTER SYSTEM SET MEMORY_TARGET = 0;

The database setsSGA_TARGETbased on current SGA memory allocation.

Do one of the following:

For more complete automatic tuning, set the sizes of the automatically sized SGA components listed inTable 6-1to zero. Do this by editing the text initialization parameter file or by issuingALTERSYSTEMstatements.

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 sizes of the other automatically sized SGA components to zero. Do this by editing the text initialization parameter file or by issuingALTERSYSTEMstatements.