oracle dba_seg,Oracle DBA 应知应会 -- 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可以控制独立服务器和共享服务器模式的所有工作区域。

一般情况下，如果使用裸设备，那么对于OLTP系统PGA_AGGREGATE_TARGET用以下公式来分配：

PGA_AGGREGATE_TARGET = ( 80%) 20%

对于OLAP系统，可以按照下面的公式计算：

PGA_AGGREGATE_TARGET = ( 80%) 50%

要注意的是，以上只是一个建议的参数设置，如果要知道你的系统中设置什么样的值比较合理，建议通过STATSPACK报告来进行分析。具体的分析方法，请参照本章的相关章节。通过部分系统视图也可以对PGA的使用情况进行分析，得到响应的优化建议。

通过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

主要指标的含义如下：

aggregate PGA auto target：Oracle可以用于PGA工作区的空间，一般比参数略小

total PGA used for auto workarea：系统实际使用的PGA空间。

maximum PGA used for auto workareas：上次数据库启动后PGA空间达到的最大值

total PGA in used:当前正在使用的PGA，可以从v$process的pga_used_mem字段中获取

over allocation count:当PGA_AGGREGATE_TARGET值过低的时候，扩充的PGA空间。当这个值不是0的时候，大多数情况下应该增大该参数

以下指标在Oracle 9i Release 2以后的版本具有的指标：

over allocation count:如果PGA_AGGRAGATE_TARGET太小，动态扩展PGA空间的次数，如果系统的物理内存足够，那么应该设置一个足够大小的PGA_AGGRAGATE_TARGET，使该统计值为0

cache hit percentage：PGA工作区使用内存的百分比，如果内存足够，那么这个值应该接近或者等于100％

V$SQL_WORKAREA_HISTOGRAM：该视图中存放了PGA的使用情况，通过该视图可以了解PGA工作区域的使用情况，下面的语句可以检查PGA的工作情况：

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

以下是这些数据的含义：

SID:产生排序操作的SESSION ID

Operation：操作的类型

Esize：PGA管理器估计的本次操作需要的总内存数量(单位是KB)

MEM：目前正在使用的内存数量(单位是KB)

MAX MEM：该SESSION曾经使用的最大PGA内存数量(单位是KB)

PASS：0代表内存排序，1代表1-pass排序，M代表M-PASS排序

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会导致操作系统宕机。