浅谈SGA与PGA参数及其优化

oracle SGA与PGA区别：

     SGA：用于存储数据库信息的内存区，该信息为数据库进程所共享。它包含Oracle 服务器的数据和控制信息,它是在Oracle服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写。

     PGA：包含单个服务器进程或单个后台进程的数据和控制信息，与几个进程共享的SGA 正相反,PGA 是只被一个进程使用的区域，PGA 在创建进程时分配,在终止进程时回收。

1.SGA

     SGA：系统全局域（System Global Area）是Oracle Instance的基本组成部分，在实例启动时分配；SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区。

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

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

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

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

     SGA与操作系统、内存大小、cpu、同时登录的用户数有关。可占OS系统物理内存的1/2到1/3，当然，如果服务器上只有oracle的话，可以分配的更大一些，如果还有其他服务，如IIS等，那就要分的小一些。

     1）共享池 Shared Pool：

     查看共享池大小Sql代码：

          SQL> show parameter shared_pool_size;

     查看共享SQL区的使用率Sql代码：

          select(sum(pins-reloads))/sum(pins) "Library cache" from v$librarycache;  

     LIBRARY命中率应该在90％以上，否则需要增加共享池的大小。

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

          select (sum(gets-getmisses-usage-fixed))/sum(gets) "Data dictionary cache" from v$rowcache;

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

     修改共享池大小的Sql代码：

           ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;

     2）缓冲区高速缓存 Database Buffer Cache：

      查看其大小的Sql代码：

              show parameter db_cache_size。

      查看数据库数据缓冲区的使用情况：

              SELECT name,value FROM v$sysstat  WHERE name IN('DB BLOCK GETS','CONSISTENT GETS','PHYSICAL READS') order by name;

              SELECT * FROM V$SYSSTAT WHERE NAME IN ('parse_time_cpu','parse_time_elapsed','parse_count_ hard');

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

              select sum(pins) "请求存取数",sum(reloads) "不命中数",sum(reloads)/sum(pins) from v$librarycache;

     其中，pins,显示在库高速缓存中执行的次数;reload,显示在执行阶段库高速缓存不命中的数目，一般 sum(reloads)/sum(pins)的值应接近于零.如果大于1%就应该增加 shared_pool_size的值, 来提高数据字典高速缓存可用的内存数量,减少不命中数。

     通过动态性能表v$rowcache来查询数据字典高速缓存的活动：

             select sum(gets) "请求存取数",sum(getmisses) "不命中数" from v$rowcache;

     其中，gets,显示请求相应项的总数; getmisses,显示造成高速缓存不命中的数据请求数。

     Hit ratio与wait events：

          select value from v$pgastat where name ='cache hit percentage';

     Hit ratio与wait events是此消彼涨，在执行类似于        

           select col1,col2,.. from tab1 a where exists (

               select 1 from tab2 where a.col3 = b.col3

            )

            where ....

            的语句的时候,如果tab1的记录很多的话,你会发现系统的hit ratio会有很大的提高,wait events是否会改观呢.

     3）日志缓冲区

      查看日志缓冲区使用情况的Sql代码：

             SELECT name, value  FROM v$sysstat WHERE name IN ('redo entries','redo log space requests');

      查询出的结果可以计算出日志缓冲区的申请失败率：  

      申请失败率＝requests/entries，申请失败率应该接近于0，否则说明日志缓冲区开设太小，需要增加ORACLE数据库的日志缓冲区。

     4）大型池

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

      指定Large Pool的大小：

            ALTER SYSTEM SET LARGE_POOL_SIZE=64M;

     5）Java池

     在安装和使用Java的情况下使用。

   2.PGA

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

     1）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%

     在OLAP（在线分析系统）或DSS(决策支持系统)系统中，由于会运行一些很大的查询，典型的PGA内存最多分配70％的内存。

     OLAP/ DSS：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%

     若oracle实例总内存为4G，OLTP系统，可以设置PGA_AGGREGATE_TARGET为655MB，OLAP/DSS系统则为1600MB。

     2）配置PGA自动管理

      不用重启数据库，直接在线修改。             

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

 

ORACLE建议一个数据库服务器，分80%的内存给数据库，20%的内存给操作系统，那怎么给一个数据库服务器配内存呢？

SQL> select * from v$pgastat;

NAME                                                                  VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter                                    104857600 bytes 
-----这个值等于参数PGA_AGGREGATE_TARGET的值，如果此值为0，表示禁用了PGA自动管理。
aggregate PGA auto target                                          75220992 bytes     
-----表示PGA还能提供多少内存给自动运行模式，通常这个值接近pga_aggregate_target-total pga inuse.
global memory bound                                                20971520 bytes
-----工作区执行的最大值，如果这个值小于1M，马上增加PGA大小
total PGA inuse                                                    30167040 bytes
-----当前分配PGA的总大小，这个值有可能大于PGA，如果PGA设置太小.这个值接近select sum(pga_used_mem) from v$process.
total PGA allocated                                                52124672 bytes
-----工作区花费的总大小
maximum PGA allocated                                              67066880 bytes
total freeable PGA memory                                                 0 bytes         ----没有了空闲的PGA
process count                                                            23                    ----当前一个有23个process
max processes count                                                      25
PGA memory freed back to OS                                               0 bytes
total PGA used for auto workareas                                   8891392 bytes
maximum PGA used for auto workareas                                22263808 bytes
total PGA used for manual workareas                                       0 bytes                  ---为0自动管理
maximum PGA used for manual workareas                                     0 bytes            ---为0自动管理
over allocation count                                                     0       
如果PGA设置太小，导致PGA有时大于PGA_AGGREGATE_TARGET的值，此处为0，说明PGA没有扩展大于TARGET的值，如 果此值出现过，那么增加PGA大小。
bytes processed                                                   124434432 bytes
extra bytes read/written                                                  0 bytes
cache hit percentage                                                    100 percent  ---命中率为100%，如果太小增加PGA
recompute count (total)                                                6651
19 rows selected