oracle性能优化之内存

引自：http://log-cd.iteye.com/blog/562052，以下内容非原文（原文内容正确率-。-）

一、名词解释 
(1)SGA：System Global Area是Oracle Instance的基本组成部分，在实例启动时分配;系统全局域SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区。 

(2)共享池：Shared Pool用于缓存最近被执行的SQL语句和最近被使用的数据定义，主要包括：Library cache（共享SQL区）和Data dictionary cache（数据字典缓冲区）。  共享SQL区是存放用户SQL命令的区域，数据字典缓冲区存放数据库运行的动态信息。 

(3)缓冲区高速缓存：Database Buffer Cache用于缓存从数据文件中检索出来的数据块，可以大大提高查询和更新数据的性能。 

(4)大型池：Large Pool是SGA中一个可选的内存区域，它只用于shared server环境。 

(5)Java池：Java Pool为Java命令的语法分析提供服务。 

(6)PGA：Process Global Area是为每个连接到Oracle database的用户进程保留的内存。 

二、分析与调整 
(1)系统全局域： 
  SGA与操作系统、内存大小、cpu、同时登录的用户数有关。可占OS系统物理内存的1/3到1/2。 
a.共享池： 
查看共享SQL区的使用率： 

select(sum(pins-reloads))/sum(pins) "Library cache" from v$librarycache;--动态性能表

这个使用率应该在90％以上，否则需要增加共享池的大小。 

查看数据字典缓冲区的使用率： 

select (sum(gets-getmisses-usage-fixed))/sum(gets) "Data dictionary cache" from v$rowcache;--动态性能表

这个使用率也应该在90％以上，否则需要增加共享池的大小。 

修改共享池的大小: 

ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;

b.缓冲区高速缓存： 
查看数据库数据缓冲区的使用情况： 

select 1-((select sum(value) from v$sysstat where name like '%physical reads%')/(select sum(value) from v$sysstat where name like '%consistent gets' or name like '%db block gets%')) from dual;

计算出来数据缓冲区的使用命中率＝1-(physical reads/(db block gets+consistent gets)),这个命中率应该在90％以上，否则需要增加数据缓冲区的大小。 

c.日志缓冲区 
查看日志缓冲区的使用情况: 

sql1-SELECT name, value  FROM v$sysstat WHERE name IN ('redo entries','redo log space requests');
select n2/n3 from dual;
其中n2和n3是sql1查询出的结果

查询出的结果可以计算出日志缓冲区的申请失败率：   
申请失败率＝requests/entries，申请失败率应该接近于0，否则说明日志缓冲区开设太小，需要增加ORACLE数据库的日志缓冲区。 

d.大型池： 
    可以减轻共享池的负担，可以为备份、恢复等操作来使用，不使用LRU算法来管理。其大小由数据库的‘共享模式/db模式’如果是共享模式的话，要分配的大一些。 
    指定Large Pool的大小： 

ALTER SYSTEM SET LARGE_POOL_SIZE=64M

   但是若该系统同时在线数如果连500左右都达不到，那么便没有设置该参数的意义。

   详见：http://hanjian861202.iteye.com/blog/1074196

e.Java池： 
    在安装和使用Java的情况下使用。 

(2)PGA调整 
a.PGA_AGGREGATE_TARGET初始化设置 

    PGA_AGGREGATE_TARGET的值应该基于Oracle实例可利用内存的总量来设置，这个参数可以被动态的修改。假设Oracle实例可分配4GB的物理内存，剩下的内存分配给操作系统和其它应用程序。你也许会分配80％的可用内存给Oracle实例，即3.2G。现在必须在内存中划分SGA和PGA区域。 

在OLTP(联机事务处理)系统中，典型PGA内存设置应该是总内存的较小部分（例如20％），剩下80％分配给SGA。 
OLTP：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20% 

在DSS(数据集)系统中，由于会运行一些很大的查询，典型的PGA内存最多分配70％的内存。 
DSS：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50% 

在这个例子中，总内存4GB，DSS系统，你可以设置PGA_AGGREGATE_TARGET为1600MB，OLTP则为655MB。 

b.配置PGA自动管理 

不用重启DB，直接在线修改。 

SQL> alter system set workarea_size_policy=auto scope=both; 

System altered. 

SQL> alter system set pga_aggregate_target=512m scope=both; 

System altered. 

SQL> show parameter workarea 

NAME                                 TYPE        VALUE 
------------------------------------ ----------- ------------------------------ 
workarea_size_policy                 string      AUTO --这个设置成AUTO 
SQL> show parameter pga 

NAME                                 TYPE        VALUE 
------------------------------------ ----------- ------------------------------ 
pga_aggregate_target                 big integer 536870912 

SQL> 

c.监控自动PGA内存管理的性能 

V$PGASTAT：这个视图给出了一个实例级别的PGA内存使用和自动分配的统计。 

SQL> set lines 256 
SQL> set pages 42 
SQL> SELECT * FROM V$PGASTAT; 

NAME                                                                  VALUE UNIT 
---------------------------------------------------------------- ---------- ------------ 
aggregate PGA target parameter                                    536870912 bytes    --当前PGA_AGGREGATE_TARGET的值 
aggregate PGA auto target                                         477379584 bytes    --当前可用于自动分配了的PGA大小，应该比PGA_AGGREGATE_TARGET 小 
global memory bound                                                26843136 bytes    --自动模式下工作区域的最大大小，Oracle根据工作负载自动调整。 
total PGA inuse                                                     6448128 bytes 
total PGA allocated                                                11598848 bytes    --PGA的最大分配 
maximum PGA allocated                                             166175744 bytes 
total freeable PGA memory                                            393216 bytes    --PGA的最大空闲大小 
PGA memory freed back to OS                                        69074944 bytes 
total PGA used for auto workareas                                         0 bytes    --PGA分配给auto workareas的大小 
maximum PGA used for auto workareas                                 1049600 bytes 
total PGA used for manual workareas                                       0 bytes 
maximum PGA used for manual workareas                                530432 bytes 
over allocation count                                                  1118    --实例启动后，发生的分配次数，如果这个值大于0，就要考虑增加pga的值 
bytes processed                                                   114895872 bytes 
extra bytes read/written                                            4608000 bytes 
cache hit percentage                                                  96.14 percent    --命中率 

16 rows selected. 

--V$PGA_TARGET_ADVICE 

SQL> SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb, 
   ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc, 
   ESTD_OVERALLOC_COUNT 
   FROM v$pga_target_advice; 

The output of this query might look like the following: 

TARGET_MB   CACHE_HIT_PERC ESTD_OVERALLOC_COUNT 
---------- -------------- -------------------- 
63       23          367 
125        24          30 
250        30          3 
375        39          0 
500        58          0 
600        59          0 
700        59          0 
800        60          0 
900        60          0 
1000    61          0 
1500    67          0 
2000    76          0 
3000    83          0 
4000    85          0 

可以看出当TARGET_MB 为375M是ESTD_OVERALLOC_COUNT=0，所以可以将PGA_AGGREGATE_TARGET设置成375M。 

附：oracle SGA与PGA区别： 
SGA：是用于存储数据库信息的内存区，该信息为数据库进程所共享。它包含Oracle 服务器的数据和控制信息,它是在Oracle服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写。 
PGA：包含单个服务器进程或单个后台进程的数据和控制信息，与几个进程共享的SGA 正相反,PGA 是只被一个进程使用的区域，PGA 在创建进程时分配,在终止进程时回收。