优化实例内存！

1、自动内存管理

如果使用的是oracle11g版本以上的数据库，那么推荐使用自动内存管理。比如数据库在白天会处理大量事务的OLTP，在晚上会跑大量的批处理任务。那么白天就需要更大的SGA来缓存数据，以减少I/O操作，晚上就需要为每个进程分配更大的PGA来处理临时排序等。使用了自动内存管理就可以自动在SGA和PGA之间动态调整。用法也非常简单，只需要设置一个参数就可以了：

memory_target ：指定为数据库分配的目标内存。等于SGA加PGA之和。这里有另一个参数就不得不提一下：

memory_max_target ：指定为数据库分配的内存最大不得超过这个值，以免引起操作系统的swap交换。

2、管理多个缓冲池

使用了自动内存管理之后，有时候想把段钉在内存当中，就可以设置参数：db_keep_cache_size

又有些时候，某些大对象，偶尔会查询一两次，又不想在查询这个对象的时候把把buffer_cache中大量缓存清走，就可以设置参数：db_recycle_cache_size

3、设定内存最小值

当你发现oracle的自动内存管理并不能及时自动调整。因为自动内存管理也是在发现了现象之后才作出相应的调整，有一定的延迟性，那么就可以设置参数：pga_aggregate_target、sga_target

让数据库最少也分配参数值那么多的内存给PGA或着SGA。还可以设置参数：sga_max_size 让自动内存管理调整SGA时，不会超过这个参数值。

4、监控内存调整操作

查看内存分配情况：

sys@ORCL> select * from v$memory_dynamic_components;

COMPONENT                 CURRENT_SIZE   MIN_SIZE   MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER_TIME      GRANULE_SIZE
------------------------- ------------ ---------- ---------- ------------------- ---------- ------------- --------- ------------------- ------------
shared pool                  306184192  306184192  306184192                   0          0 STATIC                                           4194304
large pool                     4194304    4194304    4194304                   0          0 STATIC                                           4194304
java pool                      4194304    4194304    4194304                   0          0 STATIC                                           4194304
streams pool                   8388608    8388608    8388608                   0          0 STATIC                                           4194304
SGA Target                   587202560  587202560  587202560                   0          0 STATIC                                           4194304
DEFAULT buffer cache         255852544  255852544  255852544                   0          0 INITIALIZING                                     4194304
KEEP buffer cache                    0          0          0                   0          0 STATIC                                           4194304
RECYCLE buffer cache                 0          0          0                   0          0 STATIC                                           4194304
DEFAULT 2K buffer cache              0          0          0                   0          0 STATIC                                           4194304
DEFAULT 4K buffer cache              0          0          0                   0          0 STATIC                                           4194304
DEFAULT 8K buffer cache              0          0          0                   0          0 STATIC                                           4194304
DEFAULT 16K buffer cache             0          0          0                   0          0 STATIC                                           4194304
DEFAULT 32K buffer cache             0          0          0                   0          0 STATIC                                           4194304
Shared IO Pool                       0          0          0                   0          0 STATIC                                           4194304
PGA Target                   486539264  486539264  486539264                   0          0 STATIC                                           4194304
ASM Buffer Cache                     0          0          0                   0          0 STATIC                                           4194304

已选择16行。

查看内存分配是否合理：

sys@ORCL> select * from v$memory_target_advice;

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR    VERSION
----------- ------------------ ------------ ------------------- ----------
        512                 .5           88                   1          0
        768                .75           88                   1          0
       1024                  1           88                   1          0
       1280               1.25           88                   1          0
       1536                1.5           88                   1          0
       1792               1.75           88                   1          0
       2048                  2           88                   1          0

已选择7行。

查看内存调整状态：

sys@ORCL> select * from v$memory_resize_ops;

COMPONENT                 OPER_TYPE     OPER_MODE PARAMETER                 INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS    START_TIME          END_TIME
------------------------- ------------- --------- ------------------------- ------------ ----------- ---------- --------- ------------------- -------------------
shared pool               STATIC                  shared_pool_size                     0   306184192  306184192 COMPLETE  2013-11-01 15:09:52 2013-11-01 15:09:52
PGA Target                STATIC                  pga_aggregate_target                 0   486539264  486539264 COMPLETE  2013-11-01 15:09:52 2013-11-01 15:09:52
java pool                 STATIC                  java_pool_size                       0     4194304    4194304 COMPLETE  2013-11-01 15:09:52 2013-11-01 15:09:52
streams pool              STATIC                  streams_pool_size                    0     8388608    8388608 COMPLETE  2013-11-01 15:09:52 2013-11-01 15:09:52
SGA Target                STATIC                  sga_target                           0   587202560  587202560 COMPLETE  2013-11-01 15:09:52 2013-11-01 15:09:52
DEFAULT buffer cache      INITIALIZING            db_cache_size                255852544   255852544  255852544 COMPLETE  2013-11-01 15:09:52 2013-11-01 15:09:55
ASM Buffer Cache          STATIC                  db_cache_size                        0           0          0 COMPLETE  2013-11-01 15:09:52 2013-11-01 15:09:52
DEFAULT buffer cache      STATIC                  db_cache_size                        0   255852544  255852544 COMPLETE  2013-11-01 15:09:52 2013-11-01 15:09:52
DEFAULT 2K buffer cache   STATIC                  db_2k_cache_size                     0           0          0 COMPLETE  2013-11-01 15:09:52 2013-11-01 15:09:52
DEFAULT 4K buffer cache   STATIC                  db_4k_cache_size                     0           0          0 COMPLETE  2013-11-01 15:09:52 2013-11-01 15:09:52
DEFAULT 8K buffer cache   STATIC                  db_8k_cache_size                     0           0          0 COMPLETE  2013-11-01 15:09:52 2013-11-01 15:09:52
DEFAULT 16K buffer cache  STATIC                  db_16k_cache_size                    0           0          0 COMPLETE  2013-11-01 15:09:52 2013-11-01 15:09:52
DEFAULT 32K buffer cache  STATIC                  db_32k_cache_size                    0           0          0 COMPLETE  2013-11-01 15:09:52 2013-11-01 15:09:52
KEEP buffer cache         STATIC                  db_keep_cache_size                   0           0          0 COMPLETE  2013-11-01 15:09:52 2013-11-01 15:09:52
RECYCLE buffer cache      STATIC                  db_recycle_cache_size                0           0          0 COMPLETE  2013-11-01 15:09:52 2013-11-01 15:09:52
large pool                STATIC                  large_pool_size                      0     4194304    4194304 COMPLETE  2013-11-01 15:09:52 2013-11-01 15:09:52

已选择16行。

查看PGA指标：

sys@ORCL> select * from v$pgastat;

NAME                                VALUE UNIT
------------------------------ ---------- ------------
aggregate PGA target parameter  486539264 bytes
aggregate PGA auto target       368345088 bytes
global memory bound              97307648 bytes
total PGA inuse                  77356032 bytes
total PGA allocated              96108544 bytes
maximum PGA allocated           114157568 bytes
total freeable PGA memory        10878976 bytes
process count                          31
max processes count                    38
PGA memory freed back to OS     148307968 bytes
total PGA used for auto workar          0 bytes
eas

maximum PGA used for auto work    5700608 bytes
areas

total PGA used for manual work          0 bytes
areas

maximum PGA used for manual wo          0 bytes
rkareas

over allocation count                   0
bytes processed                 174565376 bytes
extra bytes read/written                0 bytes
cache hit percentage                  100 percent
recompute count (total)              2970

已选择19行。