一些oracle PGA学习资料

程序全局区(PGA)是包含客户端连接服务器所派生的服务器进程的集合，每个服务器进程都拥有存放数据和控制信息的私有内存区域。客户端进程和服务器端进程一一对应，由服务器端进程完成用户的请求，并将数据返回给客户端进程。

   例如，当一个用户连接数据库并调用SQL语句时，这条SQL语句对应的游标就将在PGA中创建一个运行时区域，注意，这个区域是私有的，只针对调用SQL的这个用户进程。PGA就是所有服务器进程的逻辑集合。

 因此PGA和SGA有着质的不同。SGA用于在进程间共享信息，PGA则用于信息私有化。图15-17表示了SGA和PGA内存区域内容的不同。

SGA：数据缓存、共享池、大池、Java池...
PGA:堆栈空间、用户会话数据、游标状态...

PGA的大小直接影响了性能，尤其针对会导致排序的SQL查询。以下几种情况需要提供足够的PGA：

导致排序的操作如 ORDER BY、GROUP BY、 ROLLUP等操作
哈希连接
位图合并
位图创建
批量数据装载

 

在Oracle 10g/11g版本中，PGA的管理被自动化设置，成为自动的PGA内存管理。在该方式中，Oracle将动态调整PGA内存的大小为
SGA内存的20%，最小值是10MB。初始化参数PGA_AGGREGATE_TARGET显示了当前PGA的分配情况。
SQL> show parameters pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 90M
SQL>

 

 

在构建一个新的数据库实例时，PGA_AGGREGATE_TARGET 参数可以首先凭经验进行确定，例如，对应SGA大小20%。然后，
在系统载荷上升的情况下，使用Oracle PGA优化工具，调节 PGA_AGGREAGATE_TARGET参数。Oracle 提供了两个PGA建议
性能视图:V$PGA_TARGET_ADVICE和V$PGA_TARGET_ADVICE_HISTOGRAM。
例如下面的查询列出了在不同PGA设置时预计的命中率:

select round(pga_target_for_estimate/1024/1024) est_target_mb,estd_pga_cache_hit_percentage,estd_overalloc_count,pga_target_factor from v$pga_target_advice order by 1;

EST_TARGET_MB ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
------------- ----------------------------- --------------------
PGA_TARGET_FACTOR
-----------------
           11                            97                    2
             .125

           23                            97                    2
              .25

           45                           100                    1
               .5

EST_TARGET_MB ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
------------- ----------------------------- --------------------
PGA_TARGET_FACTOR
-----------------
           68                           100                    0
              .75

           90                           100                    0
                1

          108                           100                    0
              1.2

EST_TARGET_MB ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
------------- ----------------------------- --------------------
PGA_TARGET_FACTOR
-----------------
          126                           100                    0
              1.4

          144                           100                    0
              1.6

          162                           100                    0
              1.8

EST_TARGET_MB ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
------------- ----------------------------- --------------------
PGA_TARGET_FACTOR
-----------------
          180                           100                    0
                2

          270                           100                    0
                3

          360                           100                    0
                4

EST_TARGET_MB ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
------------- ----------------------------- --------------------
PGA_TARGET_FACTOR
-----------------
          540                           100                    0
                6

          720                           100                    0
                8

14 rows selected.

SQL>

 

||||||||||||||||||||||||||||||||||||||

摘自-老白一个金牌DBA的故事

 
   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_ARREGATE_TARGET参数来限制PGA的大小，而由Oracle内部的智能算法来自动进行 PGA各种工作缓冲区的分配工作。当WORKAREA_SIZE_POLICY设置为AUTO时，PGA自动管理就会起作用。（这两个参数都可以动态修改)

  要注意的是，在Oracle 9i R2版本中，PGA_AGGREGATE_TARGET只能控制独立服务器模式的服务进程的工作区域。共享服务器模式的连接还是由*_area_size 来控制。Oracle 10g可以控制独立服务器和共享服务器模式的所有工作区域。

  与oracle 8i比较，Oracle 9i的工作区管理更加灵活。Oracle 9i提供动态工作区管理的能力。在Oracle 8i下，分配的工作区域将不会归还给操作系统，会导致Oracle对物理内存的过度占用，严重时会导致系统产生换页操作。Oracle 9i的工作区用完后会自动归还，这样减轻了数据库服务程序对物理内存的占用。
  
 一般情况下，如果使用裸设备，那么对于OLTP系统，PGA_AGGREGATE_TARGET 用以下公式来分配:
PGA_AGGREGATE_TARGET=(<Total Physical Memory > * 80%)*20%
对于OLAP系统，可以按照下面的公式计算:
PGA_AGGREAGATE_TARGET=(<Total Physical Memory > *80%) * 50%

要注意的是，以上只是一个建议的参数设置。如果要知道系统中设置什么样的比较合理，建议通过STATSPATCK报告来进行分析。具体的分析方法请参照本章的相关章节。
通过部分系统视图可以对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％

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的设置偏小。

注1：PGA_AGGREGATE_TARGET参数在OpenVms操作系统下无效。
注2：由于BUG问题，在HP-UX 11.0下，如果使用oracle 9.0.1版本，设置PGA_AGGREGATE_TARGET会导致操作系统宕机。