PGA管理和调整

        在Oracle 9i以后
        支持两种PGA 管理支持两种管理模式， 一重就是手动管理模式， 另一种就是自动管理模式。

1.PGA 手动管理模式
        所谓手动管理模式就是手动设置PGA里的各个模块（sort_area_size/ hash_area_size/bitmap_merge_are_size 等)的大小值， 这些值不会根据负荷弹性变化， 而且一旦手动设置为手动模式。 则应用到服务器中每1个server process, 也就是说每个server process所占的PGA将会是一样的。

          1.1 设置方法
     

alter system set workarea_size_policy=manual scope=both;           alter system set sort_area_size=2m scope=both;           alter system set hash_area_size=2m scope=both;           alter system set bitmap_merge_area_size scope=both;

           上面的语句就将pga设为手动管理模式， 而且sort_area设成2m, hash_area设成2m. bitmap_area设成1m。

           1.2 pga手动管理模式的缺点
           上面提到, 一旦将pga管理模式设为手动，那么里面的参数是不会变化的， 如上面的例子，sort_area是2m， 这是这个session突然收到1个很负责的排序sql语句。 所需的排序空间为4m。

            这种情况下pga里面的2m sort area 就不够用了， 这时会server process会向临时表空间申请一块2m的空间，与原来的pga2m空间一起使用， 就够2m了。  但是我们知道pga空间实际上就是内存空间， 而临时表空间对应1个或多个dbf，是1个物理磁盘空间。

             我们可以用如下语句来查看临时表空间的数据文件：

              既然临时表空间实际上是磁盘空间， 那么server process往里面写入数据用于排序。 就肯定执行了1次物理写动作了。其实pga排序也可以分成3种:
              1) 完全内存排序， 也就是说pga里的sort_area 足够用
              2) 一次写磁盘排序，  sort_area 不够用。 需要申请临时表空间并执行一次写动作
              3) 多次写磁盘排序，  排序动作很负责。 需要多次向临时表空间执行写入动作。

              而磁盘写动作是很慢的， 所以一旦执行第2 和第3种情况， 那么对应session执行的sql语句就变得很慢了。
 
              hash_area 同理

              为了避免这个情况， 假如我们手动将pga 的 sort_area_size 设成10m..
              虽然可以避免一部分的磁盘排序或磁盘hash连接， 但是一些负荷很小的server process也同样会得到10m的sort_area 空间,造成了内存使用浪费。

               所以9i 以后，绝大部分情况下，强烈建议使用pga自动管理模式。

2.PGA 自动管理模式
               自动管理模式设置方便得多， 无需为pga里的sort_area/ hash_area/ bitmap_merge_area 等部分设置具体大小。而是在内存里划出了1个空间作为1个总的公共SGA。
               但是这个公共SGA也不是完全地由所有server process共享的， 一旦一个server process需要使用SGA，则会在公共SGA里划出一块空间单独使用， 但使用完成后， 会自动把空间释放回公共SGA里， 这样， 负荷小的server process就不占用多余的SGA空间， 显得很弹性化， 比较合理了。
                但当1个负荷很重的process 需要大量的SGA空间时。 公共SGA也不会把全部的空间都划给这个SGA的，因为一旦全部划给它。 则其他的 server process有可能同时都产生磁盘排序或磁盘哈希连接了。  而是只给1个上限值给这个高负荷的server process，让它单独一个磁盘排序..  不影响其他server process

                 2.1 设置方法
                

 alter system set workarea_size_policy=auto scope=both;      //默认就是auto alter system set pga_aggregate_target=512m scope=both;   //将公共SGA大小上限设为512M

                
                  2.2 应该设为多大才合适
                  在OLTP系统中， 典型PGA内存设置应该是内存的较小部分（例如15%), 剩下的80%交给SGA
                  在DSS系统(数据仓库）中， 因为会运行依稀很大的查询， PGA内存可以设大一点。 例如50%
                 
                  2.3 pga_aggregate_target 其实并不是1个硬性指标， 而只是1个临界值。
                  怎么说呢， 举个例子：
                   例如1个数据库中pga_aggregate_target 设成60MB, 这时有3个server process, 第一个需要10MB， 那么PGA会按需给它10M, 第二个需要20MB, PGA给它20M. 第3个需要30M， 那么PGA把剩下的30M给它..

                   这是PGA所有的内存都被占用了。这时来了第4条session， 服务器建立1个新的server process 需要10M PGA空间，但是公共PGA空间已经全部占用了，这时公共PGA会向内存再申请10M空间， 也就是说公共PGA到达pga_aggreate_target后也会自动扩容的， 区别就是这是公共PGA空间不会按需分配给第4条server process 10M 空间， 只会给1部分， 例如5MB.. 以后的第6条，第7条server process同样道理， 智慧得到一部分所需要的PGA 空间， 除非SGA里的被占用空间被释放回来。

                   也就是说PGA空间占用到达pga_aggeratee_target时， 则肯定会发生磁盘排序或磁盘哈希连接， 但是并不是完全的磁盘内操作， 但是PGA其实也会自动扩容， 而且会给每1个新的server process所需的一部分pga内存空间。以避免它执行完全的磁盘排序或磁盘哈希连接。

                 2.4 每个session最大能分配多少sga空间
                  上面都提过了，实际上1个server process不能独占公共PGA中的所有空间， Oracle中存在1个_pga_max_size, 指定1个session 最多能获得多少pga空间。

                   我们可以用下面语句来查询这个值
                  

                       执行结果：

 

                    如上图， 这个值是200M， 也就是说任意1个process pga上限是200M啦

             
              2.5 查看当前session占用Pga情况
                    其实我们也可以根据数据库大概的session数量来决定公共PGA的大小.
                          可以用如下语句来察看当前各session 的pga使用情况:



执行结果:


                   只有3个session啦.   其中PGA_MAX_MEM意思是这个session占用过PGA的最大值.   

                  2.6 利用$pga_advice_target 来决定公共PGA大小   
                  执行如下如句:
                     


                       结果:
         


          其中Cache_HIT_PREC 就是内存命中率,  EDTD_OVERALLOC_COUNT就是硬盘IO次数, 上图中设为108MB就足够了.

                     
                       2.7 数据库当前内存排序和磁盘排序的次数
                       执行如下如句:
                             select * from V$SYSSTATwhere name like '%sort%';
                       
                        执行结果:
                       

                          可以见到磁盘排序次数为0, 当然我用的只是符合很小的学习数据库啦.
                          
       但是在11g中, 使用的是AMM（Auto Memory Management)管理模式，pga的管理更加方便了， pga_aggregate_target 默认是0, 也就是说支持自动管理，会动态地增加和减少，但是大小不会超过memory_max_target 这个值.