oracle 9i内存配置为动态分配的步骤

修改数据库内存动态分配的步骤：

a)         确认目前的内存分配情况

SQL> show sga
          SQL>show parameter db_block_size
            Old parameters that are still active:
         SQL>show parameter db_block_buffers
         SQL>show parameter buffer_pool_keep
         SQL>show parameter buffer_pool_recycle
              New parameters to be set:
         SQL>show parameter sga_max_size
         SQL>show parameter cache_size        SQL>show parameter statistics_level 

b)      确定新的参数设置

Specification of the new parameters
Specification of Keep pool and Recycle pool:
   SQL> show parameter buffer_pool_keep
  SQL> show parameter buffer_pool_recycle
  Set the new parameters the same as the old values, that is:
   db_recycle_cache_size = buffer_pool_recycle
   db_keep_cache_size = buffer_pool_keep
   See note 564861.
   As a rule, these two parameters are not set.
Calculation of sga_max_size:
The following must be established for this parameter:

•           Current SGA size (lower limit for sga_max_size)
              SQL>show sga

•            Physical memory of the database server (max. upper limit)
             Determining the physical memory:
            Unix: For example, using the top command                       (/usr/local/bin/top -> Memory)
  Windows: For example, from the System Settings (Task Manager)

• How much physical memory of the database server can be occupied by the Oracle instance (upper limit)?

• An Oracle shadow process is generated for each database connection (Dedicated Server Model). The memory occupied by these processes also has to be taken into account when calculating the memory available for the SGA (refer to note 619876), to prevent a paging from occurring.

• There may be other Oracle instances on the same server or other applications that must divide the physical memory.

• For performance reasons, as much physical memory as possible should be assigned to the Oracle instance, but at least as much as before. However, the SGA must not become so large that paging occurs (swap, temporary storing of memory pages on disk). This disadvantage would outweigh the advantage of a large SGA.

Calculation of db_cache_size:
Consider the following: Up to now, the size of the Buffer Cache was specified by the number of blocks (db_block_buffers). The new parameter db_cache_size is specified in bytes. For an unchanged size of the Buffer Cache, set

   db_cache_size = db_block_buffers * db_block_size

Specification of db_2k_cache_size, db_4k_cache_size and so on.
As long as no tablespaces are to be created in the database that use a different block size than the primary block size (standard block size), no buffers should be allocated in the SGA for these block sizes either.

db_cache_advice and statistics_level
The statistics_level parameter should be set to TYPICAL or ALL. Only at this point are the statistics provided in the V$DB_CACHE_ADVICE view.

The STATISTICS_LEVEL parameter continues to activate more 'Advisor' Oracle servers internally.

The parameter DB_CACHE_ADVICE, which was provided up to now especially for V$DB_CACHE_ADVICE, was replaced by the parameter STATISTICS_LEVEL. DB_CACHE_ADVICE should therefore no longer be used.
   Up to now, the following applied:
   SQL> alter system set DB_CACHE_ADVICE=ON;  Now the following applies:
   SQL>alter system set statistics_level='TYPICAL';

c)      配置新参数
Set the value calculated above for 'X'.
   SQL>connect / as sysdba
  SQL> REM Delete old SGA parameter
   SQL> alter system reset buffer_pool_keep scope = spfile sid='*';
   SQL> alter system reset buffer_pool_recycle scope = spfile sid='*';
  SQL> alter system reset db_block_buffers scope = spfile sid='*';
   SQL> REM Set new SGA parameter
  SQL> alter system set sga_max_size=X scope = spfile;
  SQL> alter system set db_cache_size=X scope = spfile;
  SQL> REM statistics_level = ALL or TYPICAL
   SQL> alter system set statistics_level='TYPICAL' scope = spfile;
If necessary, set:
   SQL> alter system set db_keep_cache_size=X scope = spfile;
   SQL> alter system set db_recycle_cache_size=X scope = spfile;

d)       Restart the database to activate the new parameters:
  SQL>connect / as sysdba
   SQL> shutdown
   SQL> startup
   SQL> show sga
The following SGA parameters must now have values > 0:
   SQL> show parameter sga_max_size
   SQL> show parameter db_cache_size
The following old SGA parameters must now have the value 0:
   SQL> show parameter db_block_buffers
  SQL> show parameter buffer_pool_keep
   SQL> show parameter buffer_pool_recycle
The following parameter is unchanged:
   SQL> show parameter db_block_size