Oracle 内存参数设置

Oracle 在9i版本之前，需要对内存参数手动设置，由于系统的负载是动态变化的，很难设置一个合适的值。

在Oracle9i版本之后，有了自动内存管理的功能，大大方便了DBA的工作。只要对PGA，SGA，Log_buffer 三个参数值设置好，Oralce会根据系统的负载动态平衡各个参数的设置。

如何设置PGA的参数:

在Oracle EM 内存参数页面，可以查看Oracle 对于PGA的建议，这是Oracle对当前时间以来系统的负载预估出来相应的PGA及对当前系统的性能的提高比例。

也可以通过查询下面的表

SELECT PGA_TARGET_FOR_ESTIMATE / 1024 / 1024,
       PGA_TARGET_FACTOR,
       BYTES_PROCESSED, --TOTAL BYTES PROCESSED BY ALL THE WORK AREAS CONSIDERED BY THIS ADVICE (IN BYTES)
       ESTD_EXTRA_BYTES_RW, --  ESTIMATED NUMBER OF EXTRA BYTES WHICH WOULD BE READ OR WRITTEN IF PGA_AGGREGATE_TARGET WAS SET TO THE VALUE OF THE PGA_TARGET_FOR_ESTIMATE COLUMN.
       ESTD_PGA_CACHE_HIT_PERCENTAGE, --预计CACHE数据的命中率,计算公式 BYTES_PROCESSED / (BYTES_PROCESSED + ESTD_EXTRA_BYTES_RW)
       ESTD_OVERALLOC_COUNT --若非0，则意味着PGA不够大，不能够去处理工作区域的工作量
  FROM V$PGA_TARGET_ADVICE
 ORDER BY PGA_TARGET_FOR_ESTIMATE

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

设置SGA：

在Oracle EM 内存参数页面，可以查看Oracle 对于SGA的建议，这是Oracle对当前时间以来系统的负载预估出来的相应的PGA及对当前系统的性能的提高比例。

也可以通过查询下面的表

SELECT SGA_SIZE, --SGA的大小
       SGA_SIZE_FACTOR, --以当前sga为比重，每个sga size的比例
       ESTD_DB_TIME, --所有session活动时间的总和，内存越大，相对处理的时间就越短
       ROUND(100 * ((1 - A.ESTD_DB_TIME_FACTOR) / 1), 2), --针对当前的SGA改善的百分比
       ESTD_PHYSICAL_READS --预计物理读
  FROM V$SGA_TARGET_ADVICE A
 ORDER BY SGA_SIZE;
-----------------------------------------------------------------------------------------------------------------------

设置log buffer

一般情况下不用设置这个值，默认值即可，oracle的LGWR会频繁的把此缓存的内容写入磁盘。

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

SGA 的结构:

1.DATA BUFFER CACHE --数据缓存,读取数据须先把磁盘的数据写入此块区域(*)
--SHOW PARAMETER DB_CACHE_SIZE
2.SHARED POOL--共享池
  A.LIBRARY CACHE --解析过的SQL存放的区域
     A1.解析SQL
     A2.解析PLSQL
  B.DICTIONARY CACHE --数据字典的缓存区域，提高解析SQL的性能
--SHOW PARAMETER SHARED_POOL_SIZE
3.LOG BUFFER--日志缓存，后由LGWR进程写入磁盘(*)
--SHOW PARAMETER LOG_BUFFER
4.JAVA POOL
--SHOW PARAMETER JAVA_POOL
5.LARGE POOL --RMAN或者共享连接模式会用到
6.STREAM POOL --9.2以后的流复制会用到

 

其中对ORALCE 影响比较大的三个部分
1.DB CACHE SIZE
2.SHARE POOL
3.LOG BUFFER 

 

SELECT * FROM V$SGA_DYNAMIC_COMPONENTS--SGA的五个参数可以通过这个视图查看

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

以下内容来至于Oracle 编程艺术的摘抄:

PGA的设置
WORKAREA_SIZE_POLICY：这个参数可以设置为MANUAL或AUTO，如果是MANUAL，会使用排序区和散列区大小参数来控制分配的内存量；
如果是AUTO，分配的内存量会根据数据库中的当前工作负载而变化。默认值是AUTO，这也是推荐的设置
假设WORKAREA_SIZE_POLICY设置为AUTO，PGA_AGGREGATE_TARGET有一个非0值，就会使用这种新引入的自动PGA内存管理

1.PGA_AGGREGATE_TARGET是一个上限目标，而不是启动数据库时预分配的内存大小。可以把PGA_AGGREGATE_TARGET设置为一个超大的值（远远大于服务器上实际可用的物理内存量），
你会看到，并不会因此分配很大的内存

2.串行（非并行查询）会话会使用PGA_AGGREGATE_TARGET中的很少一部分，大约5%或者更少。所以，如果把PGA_AGGREGATE_TARGET设置为100 MB，可能每个工作区（例如，排序或散列工作区）只会使用大约不到5 MB。
你的会话中可能为多个查询分配有多个工作区，或者一个查询中就有多个排序/散列操作，但是不论怎样，每个工作区只会用PGA_AGGREGATE_TARGET中不到5%的内存。

3.随着服务器上工作负载的增加（可能有更多的并发查询和更多的并发用户），分配给各个工作区的PGA内存量会减少。数据库会努力保证所有PGA分配的总和不超过PGA_AGGREGATE_TARGET设置的阈值。这就像有一位DBA整天坐在控制台前，
不断地根据数据库中完成的工作量来设置SORT_AREA_SIZE和HASH_AREA_SIZE参数。稍后会通过一个测试来观察这种行为。

4.一个并行查询最多可以使用PGA_AGGREGATE_TARGET的30%，每个并行进程会在这30%中得到自己的那一份。也就是说，每个并行进程能使用的内存量大约是0.3*PGA_ AGGREGATE_TARGET / (并行进程数)

Oracle建议使用自动管理pga，当不想使用自动自动模式，可以使用alter session 命令，充分利用所有可用的资源。

---------------------------------------------------------------------------------------------------------
SGA 内存管理
要使用自动SGA内存管理，参数STATISTICS_LEVEL必须设置为TYPICAL或ALL。如果不支持统计集合，数据库就没有必要的历史信息来确定大小。

采用自动SGA内存管理时，确定自动调整组件大小的主要参数是SGA_TARGET，这个参数可以在数据库启动并运行时动态调整，
最大可以达到SGA_MAX_SIZE参数设置的值（默认等于SGA_TARGET，所以如果想增加SGA_TARGET，就必须在启动数据库实例之前先把SGA_MAX_SIZE设置得大一些）。