在Oracle 9i之前,我们主要是通过设置sort_area_size、hash_area_size等参数值(通常都叫做*_area_size)来管理PGA的使用,不过严格说来,是对PGA中的UGA进行管理。但是,这里有个问题,就是这些参数都是针对某个session而言的,也就是说设置的参数值对所有登录到数据库的session都生效。在数据库实际运行过程中,总有些session需要的PGA多,而有些session需要的PGA少。如果都设置一个很小的*_area_size,则会使得某些SQL语句运行时由于需要将临时数据交换到磁盘而导致效率低下。而如果都设置一个很大的值,又有可能一方面浪费空间;另一方面,消耗过多内存可能导致操作系统其他组件所需要的内存短缺,而引起数据库整体性能下降。所以如何设置*_area_size的值一直都是DBA很头疼的一个问题。
而从Oracle 9i起(当然也包括Oracle 10g)所引入的一个新的特性可以有效的解决这个问题,这个特性就是自动PGA管理。
首先,设置workarea_size_policy参数。该参数为auto(也是默认值)时,表示启用PGA自动管理;而设置该参数为manual时,则表示禁用PGA自动管理,仍然沿用Oracle 9i之前的方式,也就是使用*_area_size对PGA进行管理。
然后,DBA可以根据数据库的负载情况估计所有session大概需要消耗的PGA的内存总和,然后把该值设置为初始化参数pga_aggregate_target的值即可。Oracle会按照每个session的需要为其分配PGA,同时会尽量维持整个PGA的内存总和不超过该参数所定义的值。这样的话,Oracle就能尽量避免整个PGA的内存容量异常增长而影响整个数据库的性能。从而,就有效的解决了设置*_area_size所带来的问题。
不过遗憾的是,Oracle 9i下的PGA自动管理只对专用连接方式有效,对共享连接方式无效。Oracle 10g以后对两种连接方式都有效。
在PGA中,对性能影响最大的就是SQL工作区了。通常来说,SQL工作区越大则对于SQL语句的执行的效率就高,从而对于用户的响应时间就越少。理想情况下,SQL工作区应该可以容纳SQL执行过程中所涉及的所有输入数据和控制信息。当然,这只是理想情况,现实往往总是不能尽如人意,很多情况下SQL工作区是不能容纳执行SQL所需要的内存空间的,从而不得不交换到临时表空间里。为了衡量执行SQL所需要的内存与实际分配给该SQL的SQL工作区之间的契合程度,Oracle将所分配的SQL工作区大小分成以下三种类型。
optimal尺寸:SQL语句能够完全在所分配的SQL工作区内完成所有的操作。这时的性能最佳。
onepass尺寸:SQL语句需要与磁盘上的临时表空间交互一次才能够在所分配的SQL工作区中完成所有的操作。
multipass尺寸:由于SQL工作区过小,从而导致SQL语句需要与磁盘上的临时表空间交互多次才能完成所有的操作。这个时候的性能将急剧下降。
当系统整体负载不大时,Oracle倾向于为每个session的PGA分配optimal尺寸大小的SQL工作区。
而随着负载上升,比如连接的session逐渐增多导致同时执行的SQL语句越来越多时,Oracle就会倾向于为每个session的PGA分配onepass尺寸大小的SQL工作区,甚至是multipass尺寸的SQL工作区了。
我们一旦设置了pga_aggregate_target以后,所有的*_area_size就将被忽略。那么,我们该如何来设置该参数的值呢?这依赖于数据库的用途,如果数据库为OLTP(联机事务处理)应用的,则其应用一般都是小的短的进程,所需要的PGA也相应较少,所以该值该值通常为总共分配给Oracle实例的20%,另外的80%则给了SGA;如果数据库为OLAP(DSS)(数据仓库或决策分析)应用的,则其应用一般都是很大的,运行时间很长的进程,因此需要的PGA就多。所以通常为PGA分配50%的内存。而如果数据库为混合类型的,则情况比较复杂,一般会先分配40%的初始值,而后随着数据库的应用,而不断对PGA进行监控,并进行相应的调整。
查看本栏目更多精彩内容:http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/