Oracle PGA

 

PGA(Process Global Area)，是server process一段私有内存区，它包含有全局变量，数据结构和一些控制信息。

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性能调整

在Oracle8i 中，PGA调整非常复杂，要调整SORT_AREA_SIZE、HASH_AREA_SIZE、BITMAP_MERGE_AREA_SIZE、 CREATE_BITMAP_AREA_SIZE等参数。在ORACLE9I中，只需要调整 PGA_AGGREGATE_TARGET。

--PGA_AGGREGATE_TARGET初始化设置

PGA_AGGREGATE_TARGET的值应该基于Oracle实例可利用内存的总量来设置，这个参数可以被动态的修改。假设Oracle实例可分配4GB的物理内存，剩下的内存分配给操作系统和其它应用程序。你也许会分配80％的可用内存给Oracle实例，即3.2G。现在必须在内存中划分SGA和PGA区域。

在OLTP系统中，典型PGA内存设置应该是总内存的较小部分（例如20％），剩下80％分配给SGA。 
OLTP：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%

在DSS系统中，由于会运行一些很大的查询，典型的PGA内存最多分配70％的内存。
DSS：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%

在这个例子中，总内存4GB，DSS系统，你可以设置PGA_AGGREGATE_TARGET为1600MB，OLTP则为655MB。

--配置PGA自动管理

不用重启DB，直接在线修改。

SQL> alter system set workarea_size_policy=auto scope=both;

System altered.

SQL> alter system set pga_aggregate_target=512m scope=both;

System altered.

SQL> show parameter workarea

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy                 string      AUTO --这个设置成AUTO
SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 536870912

SQL>

--监控自动PGA内存管理的性能

V$PGASTAT：这个视图给出了一个实例级别的PGA内存使用和自动分配的统计。

SQL> set lines 256
SQL> set pages 42
SQL> SELECT * FROM V$PGASTAT;

NAME                                                                  VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter                                    536870912 bytes    --当前PGA_AGGREGATE_TARGET的值 
aggregate PGA auto target                                         477379584 bytes    --当前可用于自动分配了的PGA大小，不应该比PGA_AGGREGATE_TARGET 小 
global memory bound                                                26843136 bytes    --自动模式下工作区域的最大大小，Oracle根据工作负载自动调整。 
total PGA inuse                                                     6448128 bytes
total PGA allocated                                                11598848 bytes    --PGA的最大分配 
maximum PGA allocated                                             166175744 bytes
total freeable PGA memory                                            393216 bytes    --PGA的最大空闲大小 
PGA memory freed back to OS                                        69074944 bytes
total PGA used for auto workareas                                         0 bytes    --PGA分配给auto workareas的大小 
maximum PGA used for auto workareas                                 1049600 bytes
total PGA used for manual workareas                                       0 bytes
maximum PGA used for manual workareas                                530432 bytes
over allocation count                                                  1118    --实例启动后，发生的分配次数，如果这个值大于0，就要考虑增加pga的值 
bytes processed                                                   114895872 bytes
extra bytes read/written                                            4608000 bytes
cache hit percentage                                                  96.14 percent    --命中率

16 rows selected.

--V$PGA_TARGET_ADVICE

SQL> SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb, 
   ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc, 
   ESTD_OVERALLOC_COUNT
   FROM v$pga_target_advice;

The output of this query might look like the following:

TARGET_MB   CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
63       23          367
125        24          30
250        30          3
375        39          0
500        58          0
600        59          0
700        59          0
800        60          0
900        60          0
1000    61          0
1500    67          0
2000    76          0
3000    83          0
4000    85          0

可以看出当TARGET_MB 为375M是ESTD_OVERALLOC_COUNT=0，所以我们可以将PGA_AGGREGATE_TARGET设置成375M。