oracle11g内存自动管理

自动内存管理

从11G开始，可以配置memory_target与memory_max_target进行自动内存管理。设置此参数后，SGA可以与PGA互相转换，进行动态的内存调整

MEMORY_TARGET：内存目标大小，可以用alter system动态改变，不需要重启数据库。

MEMORY_MAX_TARGET：内存目标最大值，不可以动态修改，需要alter system set ....scope=spfile;进行修改。

注：如果只设置MEMORY_TARGET而未设置MEMORY_MAX_TARGET，系统自动将MEMORY_MAX_TARGET的值设置为MEMORY_TARGET大小。如果只设置MEMORY_MAX_TARGET而未设置MEMORY_TARGET，则系统将MEMORY_TARGET默认设置为0，当数据库启动后，可以进行动态调整，但其最大值不能超过MEMORY_MAX_TARGET。

如果之前未开启自动内存管理，需要设置SGA_TARGET和PGA_AGGREGATE_TARGET为0

ALTER SYSTEM SET MEMORY_TARGET = nM;

ALTER SYSTEM SET SGA_TARGET = 0;

ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0;

监控与优化

select * from v$memory_target_advice order by memory_size;

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR    VERSION
----------- ------------------ ------------ ------------------- ----------
        180                 .5          458               1.344          0
        270                .75          367              1.0761          0
        360                  1          341                   1          0
        450               1.25          335               .9817          0
        540                1.5          335               .9817          0
        630               1.75          335               .9817          0
        720                  2          335               .9817          0

MEMORY_SIZE                NUMBER        --内存总大小
MEMORY_SIZE_FACTOR         NUMBER        --内存值与实际值的比例
ESTD_DB_TIME               NUMBER        --消耗的db_time
ESTD_DB_TIME_FACTOR        NUMBER        --消耗的db_time与当前值的比例
VERSION                    NUMBER        --版本

V$MEMORY_TARGET_ADVICE provides information about how the MEMORY_TARGET parameter should be sized based on current sizing and satisfaction metrics.
  
  

   
   
Column
Datatype
Description
MEMORY_SIZE
NUMBER
If
 the MEMORY_SIZE_FACTOR column has a value of 1, then this column shows the current size of memory, as set by theMEMORY_TARGET initialization parameter.
If the value of the MEMORY_SIZE_FACTOR column is less than or greater than 1, then this column shows a proposed memory size.
MEMORY_SIZE_FACTOR
NUMBER
A
 multiplier for the current memory size. Possible values are 0.25, 0.5, 0.75, 1, 1.5, 1.75, and 2. This multiplier times the current memory size equals the value of the MEMORY_SIZE column.
ESTD_DB_TIME
NUMBER
For
 current memory size (MEMORY_SIZE_FACTOR = 1), the amount of database time required to complete the current workload. For a proposed memory size, the estimated amount of database time that would be required if theMEMORY_TARGET parameter
 were changed to the proposed size.
ESTD_DB_TIME_FACTOR
NUMBER
For
 a proposed memory size, ratio of estimated database time to current database time
VERSION
NUMBER
Version
 number of this recommendation (this snapshot of the V$MEMORY_TARGET_ADVICE view)

  
  
  
  

   
   于此同时可以配置pre_page_sga=true,让ORACLE在启动的时候能够全部加载到内存中。但不可以配置lock_sga,否则在启动的时候会如下报错。
  
  
  
  

   
   
   
   
    
    
    
    
   
   
   
   
    
    ORA-00847: MEMORY_TARGET/MEMORY_MAX_TARGET and LOCK_SGA cannot be set together
   
   
   
   

    
    

     
     Cause: MEMORY_TARGET/MEMORY_MAX_TARGET was set to a non-zero value and LOCK_SGA was also set
    
    
   
   
   
   

    
    

     
     Action: Do not set MEMORY_TARGET or MEMORY_MAX_TARGET if LOCK_SGA is set to TRUE.