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 设置方法
上面的语句就将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](http://img0.ph.126.net/0z9znCTIz7I5oP7_lvt_qA==/6597726175099220715.png)
既然临时表空间实际上是磁盘空间, 那么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 设置方法
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](http://img2.ph.126.net/EnAZah8igggOroyoNFcHIg==/6598193467540819973.png)
执行结果:
如上图, 这个值是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 管理支持两种管理模式, 一重就是手动管理模式, 另一种就是自动管理模式。
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](http://img0.ph.126.net/0z9znCTIz7I5oP7_lvt_qA==/6597726175099220715.png)
既然临时表空间实际上是磁盘空间, 那么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](http://img2.ph.126.net/EnAZah8igggOroyoNFcHIg==/6598193467540819973.png)
执行结果:
![PGA管理和调整 - 饥民 - 饥民2011 PGA管理和调整 - 饥民 - 饥民2011](http://img1.ph.126.net/nejT9Ihuklf7jJlw4ahblw==/6597467789866249444.png)
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 这个值.