oracle数据库内存自动降低,开启oracle内存自动化管理

oracle11g在大的改进之一，是在oracle实例的内存管理方面。oracle11g中的新的内存管理特性自动化内存管理(automatic memory management)。在该新特性之下SGA与PGA将会根据需要自动的扩展与收缩。你只需要设置两个内存管理相关的参数memory_target与memory_max_target.

memory_target:该参数设置整个oracle数据库实例可以使用的内存量，oralce数据库实例在运行过程中会根据需求自动的调节SGA与PGA的大小。可以使用相应的alter system命令动态的修改memory_target的值。该参数是动态初始化参数。

memory_max_target:该参数设置oracle实例可以使用的最大内存量。在调整memory_target的值时必须保持以下的约束关系memory_target<=memory_max_target.该参数是静态初始化参数。

在创建数据库的时候，你可以通过设置memory_target，memory_max_target初始化参数来开启自动化内存管理特性。在数据库创建以后也可以通过设置memory_target，memory_max_target初始化参数来开启自动化内存管理，但是不会立即生效，需要重启实例。如果你不想设置SGA与PGA的最小值，可以把sga_target与pag_aggregate_target初始化参数都设置为0。

确定memory_target值的大小。

memory_target = sga_target + max(pga_aggregate_target, maximum pga allocated)

SQL> show parameter pga_aggregate_target;

NAME                                 TYPE        VALUE

------------------------------------ ----------- -----------------------

pga_aggregate_target                 big integer 100M

可见pga_aggregate_target=100M。

确定oracle实例启动以来PGA的最大容量。

SQL> select value/1024/1024 as value from v$pgastat

2  where name='maximum PGA allocated';

VALUE

----------

50.5244141

由查询可知自实例启动以来，分配给PGA的最大内存空间约是51MB。

确定实例的sga_target的大小。

SQL> select (

2         (select sum(value) from v$sga) -

3         (select current_size from v$sga_dynamic_free_memory)

4         ) "sga_target"

5         from dual;

sga_target

----------

313860096

由查询可知当前oracle实例的SGA大小是300MB。

memory_target=SGA+pga_aggregate_target

memory_target=352MB

当前确定是memory_target不一定是最合适，这里没有调优的步骤，只是为了转换到自动化内存管理。如果你的机器还有物理内存，可以把memory_target的值设置的大一些。

确定memory_max_target的值，即确定分配给oracle数据库的最大内存量，该值可是等于或者大于memory_target，为了减少不必要的麻烦，应该为将来memory_target大小的调整留有空间，所有一般设置memory_max_target>memory_target。我这里设置memory_max_target的值为400MB.

将memory_max_target初始化参数写入spfile中。

SQL> alter system set memory_max_target=400M scope=spfile;

System altered.

scope必须是spfile，因为memory_max_target不是动态初始化参数。

如果是使用pfile来启动数据库的，选择手动编辑该文件，设置这些参数。

memory_max_target =400M

memory_target =352M

如果在pfile中忽略memory_max_target参数，memory_max_target的值将会自动的等于memory_target

的值。如果在pfile中忽略memory_target参数，只包括了memory_max_target参数，那么memory_target将会默认设置为0.你可以在实例启动以后，动态修改，但是该值不能超出memory_max_target

的值。

关闭并重启实例

查看刚才设置的memory_max_target的值

SQL> show parameter memory_max_target

NAME                                 TYPE        VALUE

------------------------------------ ----------- ---------

memory_max_target                    big integer 400M

修改memory_target的值

SQL> show parameter target

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 400M

memory_target                        big integer 0

parallel_servers_target              integer     8

pga_aggregate_target                 big integer 100M

sga_target                           big integer 0

SQL>  select value/1024/1024 as value from v$pgastat

2   where name='maximum PGA allocated';

VALUE

----------

45.7822266

可见pga_aggregate_target=46M，确定oracle实例启动以来PGA的最大容量。

SQL> SELECT (

2     (SELECT SUM(value) FROM V$SGA) -

3     (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)

4     ) "SGA_TARGET"

5  FROM DUAL;

SGA_TARGET

----------

313626624

可见SGA_target的大小为300M，如果马上就修改memory_target=352M会报

值的范围不合法的提示，修改PGA的值为0M也会报错的。

##################################################################

SQL> alter system set memory_target=352M;

alter system set memory_target=352M

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least

400M

因为要满足memory_target>=ppga_aggregate_target+sga_target.

但是pga_aggregate_target 的值是100M，而SGA_target的值为300M。

352M<300M+100M所有出错了。

###################################################################

###################################################################

SQL>  alter system set pga_aggregate_target=0;

alter system set pga_aggregate_target=0

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00093: pga_aggregate_target must be between 10M and 4096G-1

如果直接修改pga_aggregate_target的值会报上面的错误。因为我们还没有

设置momory_target的值，如果直接是设置PGA的大小为0M的话，那不是等于不给

PGA分配内存嘛，所有我们通过上面的一个查询，确定自实例启动以来分配给

PGA的最大内存大小46M，先把PGA的值调下来。使

memory_target>=pga_aggregate_target+sga_target.即

352M>=50M+300M

####################################################################

SQL> alter system set pga_aggregate_target=50M;

System altered.

SQL> alter system set memory_target=352M;

System altered.

SQL> alter system set pga_aggregate_target=0M;

System altered.

这是我们已经启用oracle的内存自动化管理，并且设置SGA_target与

pga_aggregate_target参数的值为0。oracle这时就不会为SGA与PGA分配最小

内存大小了。oracle会更加工作负载自动的调整SGA与PGA的大小。

下面查看最终的内存分配结果。

SQL>  show parameter target

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 400M

memory_target                        big integer 352M

parallel_servers_target              integer     8

pga_aggregate_target                 big integer 0

sga_target                           big integer 0