PGA自动管理原理深入分析及性能调整（二）

最新推荐文章于 2019-06-28 16:39:04 发布

cpbr82909536

最新推荐文章于 2019-06-28 16:39:04 发布

阅读量181

点赞数

2. PGA自动管理及深入研究

2.1 PGA自动管理概述

在9i之前，我们主要是通过设置sort_area_size、hash_area_size等参数值（通常都叫做*_area_size）来管理PGA的使用，不过严格说来，是对PGA中的UGA中的私有SQL区域进行管理，这块内存区域又有个名称叫做SQL工作区域。但是，这里有个问题，就是这些参数都是针对某个session而言的，也就是说设置的参数值对所有连进来的session都生效。在数据库实际运行过程中，总有些session需要的PGA多，而有些session需要的PGA少。如果都设置一个很小的*_area_size，则会使得某些SQL语句运行时由于需要将临时数据交换到磁盘而导致效率低下。而如果都设置一个很大的值，又有可能一方面浪费空间，另一方面，消耗过多内存可能导致操作系统其他组件所需要的内存短缺，而引起数据库整体性能下降。所以如何设置*_area_size的值一直都是DBA很头疼的一个问题。

而从9i起所引入的一个新的特性可以有效的解决这个问题，这个特性就是自动PGA管理。DBA可以根据数据库的负载情况估计所有session大概需要消耗的PGA的内存总和，然后把该值设置为初始化参数pga_aggregate_target的值即可。Oracle会按照每个session的需要为其分配PGA，同时会尽量维持整个PGA的内存总和不超过该参数所定义的值。这样的话，oracle就能尽量避免整个PGA的内存容量异常增长而影响整个数据库的性能。从而，就有效的解决了设置*_area_size所带来的问题。

不过遗憾的是，9i下的PGA自动管理只对专用连接方式有效，对共享连接方式无效。10g以后对两种连接方式都有效。

启用PGA自动管理是很容易的，只要设置两个初始化参数即可。首先，设置workarea_size_policy参数。该参数为auto（也是缺省值）时，表示启用PGA自动管理；而设置该参数为manual时，则表示禁用PGA自动管理，仍然沿用9i之前的方式，即使用*_area_size对PGA进行管理。其次，就是设置pga_aggregate_target了，该参数可以动态进行调整，范围是从10MB到4096GB – 1个字节。

2.2 PGA自动管理深入

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自动管理机制在内部到底是如何实现的呢？很遗憾，oracle官方并没有给出说明文档。其实这本身也说明了，PGA自动管理的内部算法会随着版本升级而发生变化。不过，知其然而不知其所以然，总是会让诸如我等之类的技术人员感觉如梗在喉。还好，曾经就有一些专门做oracle优化的公司发布的文档中介绍了PGA内部的实现原理，我想这可能是oracle公司透露给这些公司的。这里就做些简单的介绍，不过记住，这里所描述的PGA自动管理的原理并不一定就是将来版本的原理，只能说是截至到9.2的PGA自动管理的原理。

PGA自动管理是采用名为“循环反馈（feedback loop）”的算法来实现的。如下图所示。

上图中，当开始处理一条SQL时，oracle会使用本地内存管理器（local memory manager）对该SQL语句相关的work area profile进行注册。work area profile是一组元数据，描述了该SQL语句所需要的工作区的所有特征，包括该SQL的类型（sort还是hash-join等）、该SQL语句的并行度、所需要的内存等信息。它是SQL语句操作与内存管理器之间唯一的接口。当SQL语句执行完毕时，其对应的work area profile就会被删除。而在SQL语句执行期间，为了反映SQL语句当前已经消耗的内存以及是否被交换到临时表空间了等状态信息，oracle会不断更新其对应的work area profile。所以说，SQL语句的work area profile是有生命周期的，始终能够体现其对应SQL语句的工作区状态。因此，我们可以说，在任何时间点，所有当前活动的work area profile就能够基本体现当前所有session对PGA内存的需要以及当前正在使用的PGA内存。通过查询视图v$sql_workarea_active，可以显示所有当前活动的work area profile的相关信息。

现在，我们需要引入另外一个后台守护进程（background daemon），叫做全局内存管理器（global memory manager）。这个进程每隔3秒会启动一次。每次启动时，都会根据当前所有活动的work area profile的数量以及其他相关信息计算出这个时候的SQL工作区的“内存限度（memory bound）”，也就是每个工作区最大尽量不能超过多大（不过，注意，严格说来应该是尽量不超过。实际上这个最大值是可以被超过的，后面会用个实例来说明）。然后立即发布这个“内存限度”。

最后，本地内存管理器关闭“反馈循环”，并根据当前的“内存限度”以及当前work area profile，从而计算出当前SQL工作区应该具有的内存大小，并为进程分配该大小的内存以执行SQL语句，这个内存的大小尺寸就叫做“期望尺寸（expect size）”，可以从v$sql_workarea_active的expected_size列看到“期望尺寸”的大小。同时，这个“期望尺寸”会定时更新，并据此对SQL工作区进行调整。

Oracle内部对这个“期望尺寸”的大小有如下规则的限制：

Ø “期望尺寸”不能小于最低的内存需求。

Ø “期望尺寸”不能大于optimal尺寸。

Ø 如果“内存限度”介于最低的内存需求和optimal尺寸之间，则使用“内存限度”作为“期望尺寸”的大小，但是排序操作除外。因为排序操作算法的限制，对于分配的内存在optimal尺寸和onepass尺寸之间时，排序操作不会随着内存的增加而更快完成，除非能够为排序操作分配optimal尺寸。所以，如果排序操作的“内存限度”介于onepass尺寸和optimal尺寸之间的话，“期望尺寸”取onepass尺寸。

Ø 如果SQL以并行方式运行，则“期望尺寸”为上面三个规则算出的值乘以并行度。

Ø 非并行模式下，按照通常的说法是“期望尺寸”不能超过min(5%*pga_aggregate_target,100MB)。但实际上，这是在不修改_pga_max_size和_smm_max_size这两个隐藏参数的前提下，可以简单的这么认为。严格说来，应该是不能超过min(5%*pga_aggregate_target,50%*_pga_max_size,_smm_max_size)。对于并行的情况，就更加复杂，可以简单认为不超过30%*pga_aggregate_target。

下面，我们举例（如下图所示）来说明全局内存管理器是如何计算并应用“内存限度”的。比如，

当前系统中有6个活动的work area profile。WP1所需要的onepass内存为7MB，而optimal内存为27MB。WP3是一个并行度为2的hash-join，它需要11MB的onepass内存，以及67MB的optimal的内存。假设pga_aggregate_target设置为133MB，则可以简单的认为全局内存管理器直接将133除以6，也就是大约20MB作为“内存限度”的值。于是该“内存限度”限制了分配给WP1的工作区只能为7MB，也就是onepass的大小，因为WP1是一个排序操作，如果给它分配20MB也不能使它在以optimal的方式完成。而对于20MB的“内存限度”，WP3可以分到40MB的工作区，因为WP3的并行度为2，所以可以分配20MB×2的大小的工作区。

2.3如何设置新数据库的PGA值

我们一旦设置了pga_aggregate_target以后，所有的*_area_size就将被忽略。那么，我们该如何来设置该参数的值呢？这依赖于数据库的用途，如果数据库为OLTP（联机事务处理）应用的，则其应用一般都是小的短的进程，所需要的PGA也相应较少，所以该值该值通常为总共分配给oracle实例的20%，另外的80%则给了SGA；如果数据库为OLAP（DSS）（数据仓库或决策分析）应用的，则其应用一般都是很大的，运行时间很长的进程，因此需要的PGA就多。所以通常为PGA分配50%的内存。而如果数据库为混合类型的，则情况比较复杂，一般会先分配40%的初始值，而后随着数据库的应用，而不断对PGA进行监控，并进行相应的调整。

比如，对于8GB物理内存的数据库服务器来说，按照oracle推荐的，分配给oracle实例的内存为物理内存的80%。那么对于OLTP应用来说，pga_aggregate_target的值大约就是1310MB ((8192 MB× 80%)×20%)。而对于OLAP来说，则该值大约就是3276MB (8192MB×80%)×50%)。