pga自动管理

PGA自动管理 
Process Global Area，也就是常说的Program Global Area，PGA是数据库服务进程的私有空间。包含全局变量数据结构和控制信息，比如一个游标的执行空间，当某个游标执行的时候，都需要在执行该游标的服务进程的PGA里分配一个运行空间。

调整PGA能够提高数据库的性能。总的来说，PGA空间越大，能够得到越快的性能。拿排序来说，如果有足够的PGA排序区（SORT AREA），那么所有的排序操作可以在内存中完成，这样的操作是最优化的，称为Optimal Execs。如果排序空间不足，那么就需要使用临时表空间，将数据存储到临时段中，如果通过一次磁盘操作，完成排序工作，称为1-Pass Execs，这种方式由于要进行磁盘操作，效率大大降低。如果通过一次磁盘操作还不能完成，需要进行多次磁盘交互，那么排序的效率就十分低下了，这种情况称为M-Pass Execs。

Oracle 8i只能通过调整*_area_size参数来调整PGA的性能。Oracle 9i有了革命性的改进。DBA可以通过设置PGA_AGGREGATE_TARGET参数来限制PGA的大小，而由Oracle内部的智能算法来自动进行PGA各种工作缓冲区的分配工作。当WORKAREA_SIZE_POLICY设置为AUTO，PGA自动管理就会起作用（这2个参���都可以动态修改）。

要注意的是，Oracle 9i R2版本，PGA_AGGREGATE_TARGET只能控制独立服务器模式的服务进程的工作区域。共享服务器模式的连接还是由*_Area_size来控制。Oracle 10g可以控制独立服务器和共享服务器模式的所有工作区域。
通过V$PGASTAT视图可以得到PGA的详细情况：

SELECT * FROM V$PGASTAT;

NAME                                               VALUE 
--------------------------------------------------------
aggregate PGA target parameter                     524288000 bytes
aggregate PGA auto target                          463435776 bytes
global memory bound                                25600 bytes
total PGA inuse                                    9353216 bytes
total PGA allocated                                73516032 bytes
maximum PGA allocated                              698371072 bytes
total PGA used for auto workareas                  0 bytes
maximum PGA used for auto workareas                560744448 bytes
total PGA used for manual workareas                0 bytes
maximum PGA used for manual workareas              0 bytes
over allocation count                              0 bytes
total bytes processed                              4.0072E+10 bytes
total extra bytes read/written                     3.1517E+10 bytes
cache hit percentage                               55.97 percent
主要指标的含义如下：
l aggregate PGA auto target：Oracle可以用于PGA工作区的空间，一般比参数略小
l total PGA used for auto workarea：系统实际使用的PGA空间。
l maximum PGA used for auto workareas：上次数据库启动后PGA空间达到的最大值
l total PGA in used:当前正在使用的PGA，可以从v$process的pga_used_mem字段中获取
l over allocation count:当PGA_AGGREGATE_TARGET值过低的时候，扩充的PGA空间。当这个值不是0的时候，大多数情况下应该增大该参数
以下指标在Oracle 9i Release 2以后的版本具有的指标：
l over allocation count:如果PGA_AGGRAGATE_TARGET太小，动态扩展PGA空间的次数，如果系统的物理内存足够，那么应该设置一个足够大小的PGA_AGGRAGATE_TARGET，使该统计值为0
l cache hit percentage：PGA工作区使用内存的百分比，如果内存足够，那么这个值应该接近或者等于100％
SELECT LOW_OPTIMAL_SIZE/1024 low_kb,(HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
       optimal_executions, onepass_executions, multipasses_executions
FROM   v$sql_workarea_histogram
WHERE  total_executions != 0;
LOW_KB HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS
------ ------- ------------------ ------------------ ----------------------
8      16      156255             0                   0
16     32      150                0                   0
32     64      89                 0                   0
64     128     13                 0                   0
128    256     60                 0                   0
256    512     8                  0                   0
512    1024    657                0                   0
1024   2048    551                16                  0
2048   4096    538                26                  0
4096   8192    243                28                  0
8192   16384   137                35                  0
16384  32768   45                 107                 0
32768  65536   0                  153                 0
65536  131072  0                  73                  0
131072 262144  0                  44                  0
262144 524288  0                  22                  0

这个视图可以显示各种工作区的使用情况，OPTIMAL_EXECUTIONS是不需要通过临时表空间交换的操作的次数。ONEPASS_EXECUTIONS是需要进行一次临时表空间交换的操作次数。MULTIPASSES_EXECUTIONS是需要多次临时表空间交换的操作次数。原则上，如果100％的操作都是内存操作，是最佳的选择。如果内存不足，某些大型操作是ONEPASS的，也是可以接受的。出现MULTIPASSES的操作会引起性能的急剧下降。
V$SQL_WORKAREA_ACTIVE

    这个视图显示当前工作区的活动情况。小于64K的小型排序不会被统计。通过该视图可以监控目前系统的工作区使用情况。比如：

SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
       operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE,
       trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM",
       NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2;

SID OPERATION         ESIZE     MEM       MAX MEM    PASS TSIZE
--- ----------------- --------- --------- --------- ----- -------
8   GROUP BY (SORT)   315       280       904         0
8   HASH-JOIN         2995      2377      2430        1   20000
9   GROUP BY (SORT)   34300     22688     22688       0
11  HASH-JOIN         18044     54482     54482       0
12  HASH-JOIN         18044     11406     21406       1   120000

以下是这些数据的含义：
l SID:产生排序操作的SESSION ID
l Operation：操作的类型
l Esize：PGA管理器估计的本次操作需要的总内存数量（单位是KB）
l MEM：目前正在使用的内存数量（单位是KB）
l MAX MEM：该SESSION曾经使用的最大PGA内存数量（单位是KB）
l PASS：0代表内存排序，1代表1-pass排序，M代表M-PASS排序
l TSIZE：转储到临时表空间的大小（单位是KB）
对于ORACLE 9I RELEASE 2版本以后的数据库，提供了2个新的视图：
V$PGA_TARGET_ADVICE：提供PGA使用的建议数据
V$PGA_TARGET_ADVICE_HISTOGRAM：PGA建议直方图
    对于一个正在运行的实际系统，通过这2个视图，可以了解如何配置PGA_AGGREGATE_TARGET是比较合适的。要使用这2个视图，首先要设置statistics_level为TYPICAL或者ALL，如果该参数设置为BASIC，那么这两个视图不会提供任何数据。V$PGA_TARGET_ADVICE视图提供PGA_AGGREGATE_TARGATE和PGA命中率以及PGA  OVERALLOC的数量。比如：

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;

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

在设置PGA_AGGREGATE_TARGET的时候，尽量选择物理内存可以支撑的，命中率比较高，并且ESTD_OVERALLOC_COUNT为0的建议值。在上面的例子里，PGA_AGGREGATE_TARGET取值的最小值是375，低于这个值，OVERALLOC是非零的。如果物理内存充足，设置为4000M或者更高可以提高PGA的性能。
V$PGA_TARGET_ADVICE_HISTOGRAM视图提供PGA使用情况的直方图情况。通过这个统计直方图，可以了解系统中每个大小规模的区域的使用情况，如果某类较小的工作区域产生了M-PASS的操作，那么说明PGA的设置偏小。
和ORACLE 8i比较，Oracle 9i的工作区管理更加灵活。Oracle 9i提供动态工作区管理的能力。Oracle 8i下，分配的工作区域将不会归还给操作系统，会导致Oracle对物理内存的过渡占用，严重的情况会导致系统产生换页操作。Oracle 9i的工作区用完后会自动归还，这样减轻了数据库服务程序对物理内存的占用。
注1：PGA_AGGREGATE_TARGET参数在OpenVms操作系统下无效。
注2：由于BUG问题，在HP-UX 11.0下，如果使用oracle 9.0.1版本，设置PGA_AGGREGATE_TARGET会导致操作系统宕机。

 
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20542911/viewspace-615688/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20542911/viewspace-615688/