Skip Headers
Oracle® Database 2 Day DBA
11g Release 1 (11.1)

Part Number B28301-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

Managing Memory

This section provides background information about managing memory for the Oracle instance, and includes instructions about how to adjust the memory allocation for the Oracle instance. It contains the following topics:

About Memory Management

Memory management involves maintaining optimal sizes for the Oracle instance memory structures as demands on the database change. The memory that must be managed is the System Global Area (SGA) memory and the instance Program Global Area (instance PGA) memory. The instance PGA memory is the collection of memory allocations for all individual PGAs.

Beginning with Release 11g, Oracle Database can manage the SGA memory and instance PGA memory completely automatically. You designate only the total memory size to be used by the instance, and Oracle Database dynamically exchanges memory between the SGA and the instance PGA as needed to meet processing demands. This capability is referred to as automatic memory management. In this memory management mode, the database also dynamically tunes the sizes of the individual SGA components and the sizes of the individual PGAs.

If you want to exercise more direct control over the sizes of the SGA and instance PGA, you can use the Memory Advisors page of Database Control to disable automatic memory management and enable automatic shared memory management. When you do this, you also implicitly enable automatic PGA memory management. With automatic shared memory management, you set target and maximum sizes for the SGA. The database then tunes the total size of the SGA to your designated target, and dynamically tunes the sizes of all SGA components. With automatic PGA memory management, you set a target size for the instance PGA. The database then tunes the size of the instance PGA to your target, and dynamically tunes the sizes of individual PGAs.

If you want complete control of individual SGA component sizes, you can use the Memory Advisors page of Database Control to disable both automatic memory management and automatic shared memory management. This effectively enables manual shared memory management. In this mode, you set the sizes of several individual SGA components, thereby determining the overall SGA size. You then manually tune these individual SGA components on an ongoing basis. This mode is intended for experienced DBAs only. Note that in this mode, automatic PGA memory management remains enabled.

Note:

Although it is possible to disable automatic PGA memory management, it is not recommended, and cannot be done using the Memory Advisors page of Database Control.

Table 5-3 summarizes the various memory management modes that you can set using the Memory Advisors page of Database Control.

Table 5-3 Oracle Database Memory Management Modes

Memory Management Mode You Set Oracle Database Automatically Tunes

Automatic memory management

  • Total memory size for this instance

  • (Optional) Maximum memory size for this instance

  • Total SGA size

  • SGA component sizes

  • Instance PGA size

  • Individual PGA sizes

Automatic shared memory management and automatic PGA memory management

(Automatic memory management disabled)

  • SGA target size

  • (Optional) SGA maximum size

  • Instance PGA target size

  • SGA component sizes

  • Individual PGA sizes

Manual shared memory management and automatic PGA memory management

(Automatic memory management and automatic shared memory management disabled)

  • Shared pool size

  • Buffer cache size

  • Java pool size

  • Large pool size

  • Instance PGA target size

  • Individual PGA sizes


Note:

Automatic Memory Management is not available on all platforms. See Oracle Database Administrator's Guide for more information about supported platforms.

If you choose the basic installation option when you install the database, automatic memory management is enabled. If you choose advanced installation, DBCA enables you to select from the three memory management modes. Oracle recommends that you enable automatic memory management.

Whichever memory management mode you choose, you may have occasion to adjust memory settings as demands on the database or on its host computer change. Reasons why you adjust memory settings include the following:

  • You receive a memory-related alert or error message.

  • You receive a memory-related recommendation from Automatic Database Diagnostic Monitor (ADDM).

  • You want to change the amount of memory allocated to accommodate future growth in memory demand.

You can use a memory advisor to help you adjust memory sizes. See "Modifying Memory Settings – Automatic Shared Memory Management" for an example of using a memory advisor.

Enabling Automatic Memory Management

If you did not enable automatic memory management when you installed and configured your database, Oracle recommends that you do so after installation, unless you are an experienced DBA with specific reasons to manually tune memory sizes. With automatic memory management, the Oracle instance dynamically tunes all memory components to optimize performance as the workload changes.

To enable automatic memory management:

  1. Go to the Database Home page, logging in as user SYS.

    See "Accessing the Database Home Page".

    Note:

    You must log in as user SYS to change the maximum memory size, which is a static initialization parameter.
  2. At the top of the page, click Server to view the Server subpage.

  3. In the Database Configuration section, click Memory Advisors.

    The Memory Advisors page appears with the SGA subtab displayed by default.

  4. If the Maximum SGA Size field already contains a positive number that is greater than or equal to the desired total amount of memory to allocate to the database, skip to Step 13.

  5. In the Maximum SGA Size (MB) field, enter the maximum permissible size for database memory.

    Use the sum of the current sizes of the SGA and instance PGA as a guideline, and optionally add some room for growth.

  6. Click Apply.

    A confirmation page appears, indicating that you must restart the database.

  7. Click Yes.

    The Restart Database: Specify Host and Target Database Credentials page appears.

  8. Enter host credentials and database credentials.

    Supply host credentials for any operating system user that has execute permission on SQL*Plus. Supply database credentials for a database user with the SYSDBA privilege (such as the user SYS).

  9. Click OK.

    The Restart Database: Confirmation page appears.

  10. Click Yes to proceed with database restart.

  11. Wait a few minutes, and then click Refresh.

    The Database Home page appears.

  12. Return to the Memory Advisors page.

  13. Next to Automatic Memory Management Disabled, click Enable.

    The Enable Automatic Memory Management page appears.

  14. In the field titled Total Memory Size for Automatic Memory Management, enter the desired amount of memory to allocate to the database, and then click OK.

    A confirmation message appears.

Modifying Memory Settings – Automatic Memory Management

Modifying memory settings for automatic memory management involves using the Memory Advisors page of Database Control to change the total memory size setting. The procedure for changing this setting differs depending on whether or not your desired new setting exceeds the current setting for maximum memory size. If the new setting exceeds the maximum memory size setting, you must first increase the maximum memory size accordingly.

To modify total memory size (new value does not exceed maximum memory size):

  1. Go to the Database Home page.

    See "Accessing the Database Home Page".

  2. At the top of the page, click Server to view the Server subpage.

  3. In the Database Configuration section, click Memory Advisors.

    The Memory Parameters page appears.

    Description of mem_param_auto.gif follows
    Description of the illustration mem_param_auto.gif

  4. Do one of the following:

    • In the Total Memory Size field, enter a new value that is less than or equal to the value in the current Maximum Memory Size field.

    • To use the Memory Advisor to help you choose a new size, click Advice.

      The Memory Size Advice page appears.

      Click a point on the curve to change the total memory size. The new size is displayed below the graph. Continue clicking the curve until the desired value is displayed, and then click OK.

  5. On the Memory Advisors page, click Apply.

    A confirmation message appears.

To modify total memory size (new value exceeds maximum memory size):

  1. Go to the Database Home page, logging in as user SYS.

    See "Accessing the Database Home Page".

    Note:

    You must log in as user SYS to change the maximum memory size, which is a static initialization parameter.
  2. At the top of the page, click Server to view the Server subpage.

  3. In the Database Configuration section, click Memory Advisors.

    The Memory Advisors page appears.

  4. In the Maximum Memory Size (MB) field, enter a new value for the maximum total memory to allocate to the database.

    This value must be greater than or equal to the desired new value for total memory size.

  5. Click Apply.

    A confirmation page appears, indicating that you must restart the database.

  6. Click Yes.

    The Restart Database page appears.

  7. Enter host credentials and database credentials.

    Supply host credentials for any user that has execute permission on SQL*Plus. Supply database credentials for a database user with the SYSDBA privilege (such as user SYS).

  8. Click OK.

    The Restart Database: Confirmation page appears.

  9. Click Yes to proceed with database restart.

  10. Wait a few minutes, and then click Refresh.

    The Database Home page appears.

  11. Return to the Memory Advisors page.

  12. Do one of the following:

    • In the Total Memory Size field, enter a new value that is less than or equal to the value in the current Maximum Memory Size field.

    • To use the Memory Advisor to help you choose a new size, click Advice.

      The Memory Size Advice page appears.

      Click a point on the curve to change the total memory size. The new size is displayed below the graph. Continue clicking the curve until the desired value is displayed, and then click OK.

  13. On the Memory Advisors page, click Apply.

    A confirmation message appears.

See Also:

Modifying Memory Settings – Automatic Shared Memory Management

Modifying memory settings for automatic shared memory management involves using the Memory Advisors page of Database Control to change the total SGA size. This section assumes that automatic memory management is disabled, that automatic shared memory management is enabled, and that the desired new value for the total SGA size does not exceed the current value for maximum SGA size.

Note:

If you want a new total SGA size that exceeds the current value for maximum SGA size, you must first log in as user SYS, change the maximum SGA size, and restart the database. A similar procedure is described in "To modify total memory size (new value exceeds maximum memory size):".

To modify the total SGA memory size:

  1. Go to the Database Home page.

    See "Accessing the Database Home Page".

  2. At the top of the page, click Server to view the Server subpage.

  3. In the Database Configuration section, click Memory Advisors.

    The Memory Advisors page appears, with the SGA subpage shown.

    Description of mem_current_allocation.gif follows
    Description of the illustration mem_current_allocation.gif

  4. In the Current Allocation section, do one of the following:

    • In the Total SGA Size (MB) field, enter a new total size for the SGA.

    • To use the SGA Advisor to help you choose a new SGA size, click Advice.

      The SGA Size Advice window opens.

      Description of mem_allocation_advice.gif follows
      Description of the illustration mem_allocation_advice.gif

      Click a point on the curve to change the total SGA size. The new SGA size is displayed below the graph. Continue clicking the curve until the desired value is displayed, and then click OK.

      This example graph shows that at the current time, increasing the total SGA size is not expected to provide any gain in performance.

  5. On the Memory Advisors page, click Apply.

    A confirmation message appears.

See Also:

Modifying Memory Settings – Automatic PGA Memory Management

Modifying memory settings for automatic PGA memory management involves using the Memory Advisors page of Database Control to modify the instance PGA size. This section assumes that automatic memory management is disabled, and that automatic PGA memory management is enabled.

Note:

When you disable automatic memory management, automatic PGA memory management is enabled by default.

To modify the instance PGA size:

  1. Go to the Database Home page.

    See "Accessing the Database Home Page".

  2. At the top of the page, click Server to view the Server subpage.

  3. In the Database Configuration section, click Memory Advisors.

    The Memory Advisors page appears, with the SGA subpage shown.

  4. Click PGA to view the PGA subpage.

    Description of mem_param_pga.gif follows
    Description of the illustration mem_param_pga.gif

  5. Do one of the following:

    • In the Aggregate PGA Target field, enter a new size for the instance PGA.

    • To use the PGA Advisor to help you choose a new instance PGA size, click Advice.

      The PGA Aggregate Target Advice window appears. Click a point on the curve to change the instance PGA size, and then click OK.

    See Oracle Database Performance Tuning Guide for information about sizing the instance PGA.

  6. Click Apply.

    A confirmation message appears.

See Also: