PGA管理和调整

向右看齐 2022-03-19 13:22 256阅读 0赞

在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个物理磁盘空间。  
  
             我们可以用如下语句来查看临时表空间的数据文件：

![PGA管理和调整 - 饥民 - 饥民2011][PGA_ - _ - _2011]  
  
              既然临时表空间实际上是磁盘空间， 那么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空间。  
  
                   我们可以用下面语句来查询这个值

![PGA管理和调整 - 饥民 - 饥民2011][PGA_ - _ - _2011 1]  
  
                       执行结果：

![PGA管理和调整 - 饥民 - 饥民2011][PGA_ - _ - _2011 2]

**                  ** 如上图， 这个值是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 这个值.  
  
                      
                       
                   **

[PGA_ - _ - _2011]: http://img0.ph.126.net/0z9znCTIz7I5oP7_lvt_qA==/6597726175099220715.png
[PGA_ - _ - _2011 1]: http://img2.ph.126.net/EnAZah8igggOroyoNFcHIg==/6598193467540819973.png
[PGA_ - _ - _2011 2]: http://img1.ph.126.net/nejT9Ihuklf7jJlw4ahblw==/6597467789866249444.png