Enabling Automatic Shared Memory Management

This section describes how to change to automatic shared memory management if either automatic memory management or manual shared memory management is currently enabled for your database instance.

To change to automatic shared memory management if automatic memory management is currently enabled:

If automatic memory management is currently enabled, but you would like to have more direct control over the sizes of the System Global Area (SGA) and instance Program Global Area (PGA), you can disable automatic memory management and enable automatic shared memory management. Follow these steps:

  1. In Oracle Enterprise Manager Database Express (EM Express), from the Configuration menu, select Initialization Parameters.

    The Initialization Parameters page appears, with the Current tab displayed.

  2. In the Search field, enter MEMORY_TARGET.

  3. Select MEMORY_TARGET, and then click Set.

    The Set Initialization Parameter page appears.

  4. In the Value field, enter 0, specify a Scope of Memory, and then click OK.

    A confirmation message appears.

    Note:

    This step changes automatic memory management to automatic shared memory management for the current session. To change automatic memory management to automatic shared memory management and keep it in effect after the database is restarted:

    • If your database uses a server parameter file, specify a Scope of SPFile as well as a Scope of Memory on the Set Initialization Parameter page.

    • If your database uses a text initialization parameter file, manually set the value of MEMORY_TARGET to 0 in that file.

  5. From the Configuration menu, select Memory.

    Note that in the SGA Memory subsection of the Memory Settings section, the Management Mode value is now Auto. This indicates that automatic shared memory management is enabled. The initialization parameter values shown on this page are the ones that have been specified in addition to MEMORY_TARGET.

    Description of the illustration auto_shared_mem_crop.gif follows
    Description of the illustration auto_shared_mem_crop.gif

To change to automatic shared memory management if manual shared memory management is currently enabled:

If manual shared memory management is currently enabled, but you would like Oracle Database to help you determine optimal sizes of the SGA and instance PGA, you can disable manual shared memory management and enable automatic shared memory management. Follow these steps:

  1. In SQL*Plus, run the following query in the database 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;

SGA_TARGET
----------
 371654656

    This value is approximately 354M.

  2. In EM Express, from the Configuration menu, select Initialization Parameters.

    The Initialization Parameters page appears.

  3. In the Search field, enter SGA_TARGET.
  4. Select SGA_TARGET, and then click Set.

    The Set Initialization Parameter page appears.

  5. In the Value field, enter the SGA_TARGET value from step 1 above (354M in this example), specify a Scope of Memory, and then click OK.

    A confirmation message appears.

    Note:

    This step changes manual shared memory management to automatic shared memory management for the current session. To change manual shared memory management to automatic shared memory management and keep it in effect after the database is restarted:

    • If your database uses a server parameter file, specify a Scope of SPFile as well as a Scope of Memory on the Set Initialization Parameter page.

    • If your database uses a text initialization parameter file, manually set the value of MEMORY_TARGET to 0 in that file.

  6. From the Configuration menu, select Memory.

    Note that in the SGA Memory subsection of the Memory Settings section, the Management Mode value is now Auto. This indicates that automatic shared memory management is enabled.

    Description of the illustration auto_shared_mem_crop.gif follows
    Description of the illustration auto_shared_mem_crop.gif
  7. Do one of the following:

    • For more complete automatic tuning, set the values of the automatically sized SGA components listed in the following table to zero on the Initialization Parameters page:

      SGA Component Initialization Parameter

      The shared pool

      SHARED_POOL_SIZE

      The large pool

      LARGE_POOL_SIZE

      The Java pool

      JAVA_POOL_SIZE

      The buffer cache

      DB_CACHE_SIZE

      The Streams pool

      STREAMS_POOL_SIZE

    • To control the minimum size of one or more automatically sized SGA components, set those component sizes to the desired value, as described in Oracle Database Administrator's Guide. Set the values of the other automatically sized SGA components to zero on the Initialization Parameters page.