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 a target memory size initialization parameter (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET). The total memory that the instance uses remains relatively constant, based on the value of MEMORY_TARGET, and the 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.

大通网站制作公司哪家好，找创新互联公司！从网页设计、网站建设、微信开发、APP开发、响应式网站建设等网站项目制作，到程序开发，运营维护。创新互联公司从2013年创立到现在10年的时间，我们拥有了丰富的建站经验和运维经验，来保证我们的工作的顺利进行。专注于网站建设就选创新互联公司。

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

Because the MEMORY_TARGET initialization parameter is dynamic, you can change MEMORY_TARGET at any time without restarting the database. MEMORY_MAX_TARGET, which is not dynamic, serves as an upper limit so that you cannot accidentally set MEMORY_TARGET too 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 setting MEMORY_TARGET too 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 the CREATE DATABASE SQL 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

1. Start SQL*Plus and connect to the database as SYSDBA.

   
   

2. Calculate the minimum value for MEMORY_TARGET as follows:

   1. Determine the current sizes of SGA_TARGET and PGA_AGGREGATE_TARGET by entering the following SQL*Plus command:

      SHOW PARAMETER TARGET
      

      SQL*Plus displays the values of all initialization parameters with the string TARGET in the parameter name.

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

   2. 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';
      

   3. Compute the maximum value between the query result from step 2b and PGA_AGGREGATE_TARGET. Add SGA_TARGET to this value.

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

   For example, if SGA_TARGET is 272M and PGA_AGGREGATE_TARGET is 90M as shown above, and if the maximum PGA allocated is determined to be 120M, then MEMORY_TARGET should be at least 392M (272M + 120M).

3. Choose the value for MEMORY_TARGET that 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.

4. For the MEMORY_MAX_TARGET initialization 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 the MEMORY_TARGET value that you chose in the previous step.

5. 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;
     

     where n is the value that you computed in Step 4.

     The SCOPE = SPFILE clause sets the value only in the server parameter file, and not for the running instance. You must include this SCOPE clause because MEMORY_MAX_TARGET is 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 = nM
     memory_target = mM
     

     where n is the value that you determined in Step 4, and m is the value that you determined in step 3.

   Note:

   In a text initialization parameter file, if you omit the line for MEMORY_MAX_TARGET and include a value for MEMORY_TARGET, the database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET. If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero. After startup, you can then dynamically change MEMORY_TARGET to a nonzero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.

6. Shut down and restart the database.

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

   where n is the value that you determined in step 3.

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:

1. Set the MEMORY_TARGET initialization parameter to 0.

2. Set the SGA_TARGET initialization 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:

1. 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;
   

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

3. Do one of the following:

   • For more complete automatic tuning, set the values of the automatically sized SGA components listed in Table 6-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:

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

2. Do one of the following:

   • For more complete automatic tuning, set the sizes of the automatically sized SGA components listed in Table 6-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 sizes of the other automatically sized SGA components to zero. Do this by editing the text initialization parameter file or by issuing ALTER SYSTEM statements.