To shut down and start the Oracle instance using SQL*Plus:

• OracleServiceSID, which is your Oracle instance.

• OracleORACLE_HOMETNSListener, which is your listener. The listener is required for clients to connect to your database.

In the preceding service names, SID refers to the system identifier for the instance and ORACLE_HOME refers to the Oracle home name.

To start or stop Oracle Database services:

1. Click Start and then select Control Panel.

   The Control Panel window opens.

2. Double-click the Administrative Tools icon, and then double-click the Services icon.

   The Services window opens, displaying all Windows services that are available on your system.

3. Locate the Oracle Database services listed at the beginning of this section. For example, if your SID is orcl, then locate the following services:

   • OracleServiceORCL

   • OracleOraDb11g_home1TNSListener

4. Start or stop the services, using the following steps for each service:

   1. Select the service name.

   2. In the Action menu, click Start or Stop.

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     32
      pga_aggregate_target           big integer 29M
      sga_target                     big integer 356M
      

      Or, on the Initialization Parameters page in Oracle Enterprise Manager Database Express (EM Express), you can enter "TARGET" in the Search field to display the values of all the initialization parameters with the string TARGET in the parameter name, as described in "Viewing and Modifying Initialization Parameters."

   2. Run the following query to determine the maximum instance Program Global Area (PGA) allocated since the database was started:

      SQL> select value from v$pgastat where name='maximum PGA allocated';
            
                 VALUE
            ----------
             246844416
      

      246844416 bytes is approximately 235M.

   3. Compute the maximum value between the query result from step 2.b 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 356M and PGA_AGGREGATE_TARGET is 29M as shown above, and if the maximum PGA allocated is determined to be 235M, then MEMORY_TARGET should be at least 591M (356M + 235M).

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 System Global Area (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 SQL*Plus 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.

     Or, you can also select the MEMORY_MAX_TARGET initialization parameter on the Initialization Parameters page in EM Express, click Set, specify a Scope of SPFile, and set a new value, as described in "Viewing and Modifying Initialization Parameters."

   • 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 (650M for this example)
     memory_target = mM (591M for this example)
     

     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.

   See "Shutting Down and Starting Up the Oracle Instance".

7. In EM Express, from the Configuration menu, select Memory.

   The Memory Management page appears. In the Memory Settings section, the Memory Management value is Auto. This indicates that Automatic Memory Management is enabled for the database. The initialization parameter values shown on this page are the ones that have been specified in addition to MEMORY_MAX_TARGET.

   
   Description of the illustration auto_memory_mgmt_crop.gif

8. If you started your Oracle Database instance with a server parameter file, make these changes to the following initialization parameter values:

   MEMORY_TARGET = nM; (591M for this example)
   SGA_TARGET = 0;
   PGA_AGGREGATE_TARGET = 0;
   

   You can also set these initialization parameter values using the Initialization Parameters page in EM Express, specifying a scope of SPFile. See "Viewing and Modifying Initialization Parameters"for more information.

   Note:

   The preceding steps instruct you to set SGA_TARGET and PGA_AGGREGATE_TARGET to zero so that the sizes of the SGA and instance PGA are tuned up and down as required, without restrictions. You can omit the statements that set these parameter values to zero and leave either or both of the values as positive numbers. In this case, the values act as minimum values for the sizes of the SGA or instance PGA.

To predict the percentage of time saved for different target memory sizes:

1. In EM Express, from the Configuration menu, select Memory.

   The Memory Management page appears. In the Memory Settings section, the Memory Management value is Auto. This indicates that Automatic Memory Management is enabled for the database.

2. Use the Memory Advisor graph (which appears to the right of the Memory Settings section) to predict the percentage of time saved for potential target memory sizes.

   
   Description of the illustration memory_advisor_graph.gif

   In the Memory Advisor graph:

   • Potential values for the MEMORY_TARGET initialization parameter (in MB) are represented on the horizontal axis of the graph. The current setting of the MEMORY_TARGET initialization parameter is indicated by a blue dot.

   • The corresponding values of time saved are represented on the vertical axis of the graph. The plotted values are expressed as a percentage relative to the current setting of the MEMORY_TARGET initialization parameter.

     Negative values represent the percentage of an increase in time consumed (when the memory allotted to Oracle is smaller than the current setting), while positive values represent the percentage of decrease in time consumed (when the memory alloted to Oracle is larger than the current setting.

   An orange line on the graph plots different values that can be specified for the MEMORY_TARGET initialization parameter. Click any dot on the orange line to see a prediction of the decrease in time consumed for the MEMORY_TARGET value represented by that dot.

   In this figure, the Memory Advisor graph indicates that increasing the current value of the MEMORY_TARGET initialization parameter will not decrease the percentage of time saved.

3. To change the value of the MEMORY_TARGET initialization parameter:

   1. Click Configure Memory on the Memory Management page.

      The Initialization Parameter page appears.

   2. Select the MEMORY_TARGET initialization parameter and click Set.

      The Set Initialization Parameter page appears.

   3. In the Scope field, enter the scope for this change.

      See "Viewing and Modifying Initialization Parameters" for more information about specifying a scope of Memory, or SPFile, or both.

   4. In the Value field, enter the new value for the MEMORY_TARGET initialization parameter.

   5. Click OK.

      A confirmation message appears.

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

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:

Before you modify memory settings for automatic shared memory management, use the SGA Advisor graph in Oracle Enterprise Manager Database Express (EM Express) to predict the percentage of time saved by using a different total System Global Area (SGA) size. This section assumes that automatic memory management is disabled, and that automatic shared memory management is enabled.

To predict the percentage of time saved for different SGA memory sizes:

1. In EM Express, from the Configuration menu, select Memory.

   The Memory Management page appears. In the Memory Settings section, the Management Mode value is Auto in the SGA Memory subsection.This indicates that Automatic Shared Memory Management is enabled for the database.

2. Use the SGA Advisor graph (which appears to the right of the Memory Settings section) to predict the percentage of time saved for potential SGA memory sizes.

   
   Description of the illustration sga_advisor_graph.gif

   In the SGA Advisor graph:

   • Potential values for the SGA_TARGET initialization parameter (in MB) are represented on the horizontal axis of the graph. The current setting of the SGA_TARGET initialization parameter is indicated by a blue dot.

   • The corresponding values of time saved are represented on the vertical axis of the graph. The plotted values are expressed as a percentage relative to the current setting of the SGA_TARGET initialization parameter.

     Negative values represent the percentage of an increase in time consumed (when the memory allotted to Oracle is smaller than the current setting), while positive values represent the percentage of decrease in time consumed (when the memory alloted to Oracle is larger than the current setting.

   An orange line on the graph plots different values that can be specified for the SGA_TARGET initialization parameter. Click any dot on the orange line to see a prediction of the decrease in time consumed for the SGA_TARGET value represented by that dot.

   In this figure, the SGA Advisor graph indicates that increasing the current value of the SGA_TARGET initialization parameter will not decrease the percentage of time saved.

3. To change the value of the SGA_TARGET initialization parameter:

   1. Click Configure Memory on the Memory Management page.

      The Initialization Parameter page appears.

   2. Select the SGA_TARGET initialization parameter and click Set.

      The Set Initialization Parameter page appears.

   3. In the Scope field, enter the scope for this change.

      See "Viewing and Modifying Initialization Parameters" for more information about setting a scope of Memory, or SPFile, or both.

   4. In the Value field, enter the new value for the SGA_TARGET initialization parameter.

   5. Click OK.

      A confirmation message appears.

Follow these steps to enable manual shared memory management:

Before you modify memory settings for manual shared memory management, use the Buffer Cache Advisor graph in Oracle Enterprise Manager Database Express (EM Express) to predict the estimated percentage of reads saved by using a different database cache size. This section assumes that automatic memory management is disabled, and that manual shared memory management is enabled.

To predict the percentage of reads saved for different database cache sizes:

1. In EM Express, from the Configuration menu, select Memory.

   The Memory Management page appears. In the Memory Settings section, the Management Mode value is Manual in the SGA Memory subsection.This indicates that Manual Shared Memory Management is enabled for the database.

2. Use the Buffer Cache Advisor graph (which appears to the right of the Memory Settings section) to predict the percentage of reads saved for potential database cache sizes.

   
   Description of the illustration buffer_cache_advisor_graph.gif

   In the Buffer Cache Advisor graph:

   • Potential values for the DB_CACHE_SIZE initialization parameter (in MB) are represented on the horizontal axis of the graph. The current setting of the DB_CACHE_SIZE initialization parameter is indicated by a blue dot.

   • The corresponding values of reads saved are represented on the vertical axis of the graph. The plotted values are expressed as a percentage relative to the current setting of the DB_CACHE_SIZE initialization parameter.

     Negative values represent the percentage of an increase in reads (when the memory allotted to Oracle is smaller than the current setting), while positive values represent the percentage of decrease in reads (when the memory alloted to Oracle is larger than the current setting.

   An orange line on the graph plots different values that can be specified for the DB_CACHE_SIZE initialization parameter. Click any dot on the orange line to see a prediction of the percentage of reads saved for the DB_CACHE_SIZE value represented by that dot.

   In this figure, the Buffer Cache Advisor graph indicates that increasing the current value of the DB_CACHE_SIZE initialization parameter will not increase the percentage of reads saved.

3. To change the value of the DB_CACHE_SIZE initialization parameter:

   1. Click Configure Memory on the Memory Management page.

      The Initialization Parameter page appears.

   2. Select the DB_CACHE_SIZE initialization parameter and click Set.

      The Set Initialization Parameter page appears.

   3. In the Scope field, enter the scope for this change.

      See "Viewing and Modifying Initialization Parameters" for more information about specifying a scope of MEMORY, or SPFile, or both.

   4. In the Value field, enter the new value for the DB_CACHE_SIZE initialization parameter.

   5. Click OK.

      A confirmation message appears.

To modify the instance PGA size:

1. In EM Express, from the Configuration menu, select Memory.

   The Memory Management page appears. If the database has either automatic shared memory management or manual shared memory management enabled, then the PGA Advisor Graph appears as the second chart to the right of the Memory Settings section.

2. Use the PGA Advisor graph to predict the estimated cache hit percentage for potential database cache sizes.

   
   Description of the illustration pga_advisor_graph.gif

   In the PGA Advisor graph:

   • Potential values for the PGA_AGGREGATE_TARGET initialization parameter are represented on the horizontal axis of the graph. The unit size (for example, MB or GB) is also indicated on the horizontal axis. The current setting of the PGA_AGGREGATE_TARGET initialization parameter is indicated by a blue dot.

   • The corresponding estimated cache hit percentage values are represented on the vertical axis of the graph. The plotted values are expressed as a percentage relative to the current setting of the PGA_AGGREGATE_TARGET initialization parameter.

   An orange line on the graph plots different values that can be specified for the PGA_AGGREGATE_TARGET initialization parameter. Click any dot on the orange line to see an estimate of the percentage of cache hits for the PGA_AGGREGATE_TARGET value represented by that dot.

   In this figure, the PGA Advisor graph indicates that increasing the current value of the PGA_AGGREGATE_TARGET initialization parameter will not increase the percentage of cache hit.

3. To change the value of the PGA_AGGREGATE_TARGET initialization parameter:

   1. Click Configure Memory on the Memory Management page.

      The Initialization Parameters page appears.

   2. Select the PGA_AGGREGATE_TARGET initialization parameter and click Set.

      The Set Initialization Parameter page appears.

   3. In the Scope field, enter the scope for this change.

      See "Viewing and Modifying Initialization Parameters" for more information about specifying a scope of Memory, or SPFile, or both.

   4. In the Value field, enter the new value for the PGA_AGGREGATE_TARGET initialization parameter.

   5. Click OK.

      A confirmation message appears.