Memory Management Tips

We discussed setting the STATISTICS_LEVEL parameter to TYPICAL or ALL to enable ASMM. For a more detailed understanding of various statistics levels, query the V$STATISTICS_LEVEL view.

You can use ALTER SESSION or ALTER SYSTEM to change the values if you decide to switch back and forth using ASMM. Use the following query to get your database session settings:

 SQL> select STATISTICS_NAME, SESSION_STATUS, ACTIVATION_LEVEL from V$STATISTICS_LEVEL; STATISTICS_NAME                          SESSION_ ACTIVAT ---------------------------------------- -------- ------- Buffer Cache Advice                      ENABLED  TYPICAL MTTR Advice                              ENABLED  TYPICAL Timed Statistics                         ENABLED  TYPICAL Timed OS Statistics                      DISABLED ALL Segment Level Statistics                 ENABLED  TYPICAL PGA Advice                               ENABLED  TYPICAL Plan Execution Statistics                DISABLED ALL Shared Pool Advice                       ENABLED  TYPICAL Modification Monitoring                  ENABLED  TYPICAL Longops Statistics                       ENABLED  TYPICAL Bind Data Capture                        ENABLED  TYPICAL Ultrafast Latch Statistics               ENABLED  TYPICAL Threshold-based Alerts                   ENABLED  TYPICAL Global Cache Statistics                  ENABLED  TYPICAL Cache Stats Monitor                      ENABLED  TYPICAL Active Session History                   ENABLED  TYPICAL Undo Advisor, Alerts and Fast Ramp up    ENABLED  TYPICAL 17 rows selected. 

Converting a Manual MemoryManaged Database to ASMM

Error While Setting STATISTICS_LEVEL

If you wish to limit the amount of statistics collected in your database, you will have to alter the STATISTICS_LEVEL parameter to BASIC. When you issue the ALTER SESSION or ALTER SYSTEM command to make this change, you will encounter an ORA-00830 error on your database with auto-tuned SGA. The only workaround for this problem is to the disable SGA auto tuning so that the database can terminate the collection of any statistics. After you disable it, run the command to set STATISTICS_LEVEL to BASIC. Now you have a database with improved features over Oracle 9i, but with less sophistication than a self-managed 10g.

If you want to enable automatic shared-memory management in a manual memorymanaged database, do the following:

1.	Find the value for SGA_TARGET using the following SQL query: [View full width] select ((select sum(value) from V$SGA)- (select current_size from V$SGA_DYNAMIC_FREE_MEMORY)) "SGA_TARGET_VALUE" from dual;
2.	Alter the database to set the SGA_TARGET at the new value. This can be done by editing the initSID.ora file and restarting the database, or by using the following SQL command: alter system set SGA_TARGET=<SGA_TARGET_VALUE> [scope={spfile|memory|both}]; SGA_TARGET_VALUE is either the value you got from step 1 or a number between the sum of all SGA component sizes and SGA_MAX_SIZE. Is SGA Component Sizing a Good Practice? If you specify the minimum size for an automatically tuned component, it reduces the total amount of memory available to Oracle for dynamic memory adjustment. This reduction in memory will hinder the system's ability to adapt to workload fluctuations. Do not specify minimum values for memory components unless you are absolutely sure of the nature of queries processed by the system. If you set the system to be managed by ASMM, it will help to improve system performance as well as optimize the utilization of available system resources.
3.	Enable complete automatic memory management by setting the values of the automatically sized SGA components listed in Table 5.1 to 0, as in step 2.
4.	If you wish to set the minimum size of one or more automatically tuned SGA components, set those component sizes to the desired value and set the values of the other automatically tuned components to 0, as in step 2. Oracle will allocate memory among other components as needed, from the available memory.

Tuning SGA_TARGET and SGA_MAX_SIZE

Oracle Database 10g controls the virtual memory used by the SGA. It can start instances with minimal memory and allow the instance to use more memory up to SGA_MAX_SIZE by increasing the memory allocated for SGA components. If the value for SGA_MAX_SIZE in the initSID.ora file or SPFILE is less than the sum of the memory allocated for all memory components, then the database ignores the value for SGA_MAX_SIZE at instance startup.

SGA_MAX_SIZE cannot be dynamically resized. It is the maximum size of the SGA for the entire instance lifetime. When you set SGA_TARGET in an instance, the value of SGA_MAX_SIZE is automatically set to the new value for SGA_TARGET. But you cannot raise SGA_TARGET to a value higher than SGA_MAX_SIZE. So use the steps in the preceding section, "Converting a Manual MemoryManaged Database to ASMM," to find appropriate values for SGA_TARGET. The SGA_TARGET value should always be lower than SGA_MAX. You will get ORA-00823 if you try to raise SGA_TARGET above SGA_MAX as given in Listing 5.1.

Listing 5.1. Tuning SGA_TARGET

 SQL> show parameter sga ; NAME                                 TYPE        VALUE ------------------------------------ ----------- ----------------------------- lock_sga                             boolean     FALSE pre_page_sga                         boolean     FALSE sga_max_size                         big integer 4G sga_target                           big integer 0 SQL> alter system set sga_target=500M; System altered. SQL>  show parameter sga ; NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ lock_sga                             boolean     FALSE pre_page_sga                         boolean     FALSE sga_max_size                         big integer 4G sga_target                           big integer 4G SQL> alter system set sga_target=5000M; alter system set sga_target=5000M * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-00823: Specified value of sga_target greater than sga_max_size 

Similarly, if you try to shrink SGA_TARGET to a value below the sum of individual components, you will get an ORA-00827 error. For resizing SGA to a lower value, reduce the size of individual components and try the shrink operation.

Most of the database applications will run better if the underlying SQL code is tuned properly. If you get bad performance with an application on an Oracle Database 10g instance, check the memory values and see if they are sufficiently high. If the values look good, examine the underlying SQL code. Use various SQL tuning methods to improve the performance of the application code. Verify the performance improvements before you initiate memory increases.