[AD]程序全局区（PGA）

一，PGA介绍
1，PGA值的是程序全局区（Program Global Area），是服务器进程（Server Process）使用的一块包含数据和控制信息的内存区域，PGA是非共享的内存，在服务器进程启动或创建时分配（在系统运行时，排序、连接等操作也可能需要进一步的PGA分配），并为Server Process排它访问。PGA的内容依专用模式和共享模式而不同，但是通常来说，PGA中包含私有SQL区（存放绑定信息、运行时内存结构等）和session信息等内容。所有服务器进程分配的PGA总和通常被称为PGA合计（Aggregated PGA）。
2，在Oracle 9i版本以前的版本中，PGA由一些列的内存区域组成，这些区域包括主要由*_area_size 参数控制。
3，在oracle 8i的环境中，这些参数主要有：Sort_Area_Size；Hash_Area_Size；Bitmap_Area_size；Create_Bitmap_Area_Size；
4，Oracle 9i开始，Oracle提供了一种PGA内存管理的新方法；自动化SQL执行内存管理（Automated SQL Execution Managerment）。使用这个新特性，Oracle可以自动调整SQL内存区，而不用关闭数据库。
二，PGA的自动管理
1，为了实现PGA管理，Oracle引入了几个新的初始化参数。PGA_AGGREGATE_TARGET:此参数用来指定所有session总计可以使用最大PGA内存，这个参数可以被动态的更改，取值范围从10M-（4096G-1）bytes。
WORKAREA_SIZE_POLIY：此参数用于开关PGA内存自动管理功能，该参数有两个选项AUTO和MANUAL，当设置为AUTO时，数据库使用自动管理功能，当设置为MANUAL时，使用手工管理方式。缺省值为AUTO。
2，Oracle 9i中，PGA_AGGREGATE_TARGET参数仅对专有服务器模式下（Dedicated Server）的专属连接有效，但是对共享服务器（Shared Server）连接无效；
3，从Oracle 10g开始，PGA_AGGREGATE_TARGET对专有服务器连接和共享服务器连接同时有效。PGA_AGGREGATE_TAREGET参数同时限制全局PGA分配和私有工作区内存分配。
4，对于串行操作，单个SQL操作能够使用的PGA内存按照以下分配原则分配：MIN(5% PGA_AGGREGATE_TARGET,100MB)
5，对于并行操作： 30%PGA_AGGREGATE_TARGET/DOP (DOP=Degree Of Parallelism 并行度)
6，要理解PGA的自动调整，还需要区分可调整内存（TUNABLE MEMORY SiZE）与不可调整内存（UNTUNABLE MEMORY SIZE）。可调整内存是由SQL工作区使用的。其余部分这不可调整内存。
7，启用了自动PGA调整之后，Oracle仍然需要遵循以下原则：UNTUNABLE MEMORY SIZE + TUNABLE MEMORY SIZE <= PGA_AGGREGATE_TARGET
8，数据库系统只能控制可调整部分的内存分配，如果可调整的部分过小，则Oracle永远也不会强制启用这个等式。另外，PGA_AGGREGATE_TARGET参数在CBO优化器模式下，对于SQL的执行计划会产生影响。Oracle在评估执行计 划时会根据PGA_AGGREGATE_TARGET参数评估在Sort,HASH-JOIN或Bitmap操作时使用的最大或最小内存，从而选择最优的执行计划。
9，对于PGA_AGGREGATE_TAREGT参数的设置，Oracle提供这样的一个建议方案。对于OLTP系统：
PGA_AGGREGATE_TARGET=( *80%)*2O%
10，对于DSS系统：PGA_AGGREGATE_TARGET=( *80%)*50%
11，也就是说，对于一个单纯的数据库服务器，通常需要保留20%的物理内存给操作系统使用，剩余80%可以分配给Oracle使用。Oracle使用的内存分为两个部分SGA和PGA，那么PGA可以占用oracle的内存的20%（OLTP系统） ~50%（DSS系统）。
12，SGA_MAX_SIZE+PGA_AGGREGATE_TARGET <=*80%
注：在某些os上单个进程使用的真实的内存可能远大于在Oracle中看到的PGA大小，如：AIX.
三，v$process
1，在v$process视图中增加了相应的字段来记录进程的PGA耗用。
2，[oracle@rhel ~]$ ps -ef | grep ora
root      2536  2512  0 09:11 ?        00:00:00 hald-addon-storage: polling /dev/hdc
oracle    2839     1  0 09:11 ?        00:00:00 /u01/app/oracle/10.2.0/db_1/bin/tnslsnr LISTENER -inherit
oracle    2865     1  0 09:11 ?        00:00:00 ora_pmon_orcl
oracle    2867     1  0 09:11 ?        00:00:00 ora_psp0_orcl
oracle    2869     1  0 09:11 ?        00:00:00 ora_mman_orcl
oracle    2871     1  0 09:11 ?        00:00:00 ora_dbw0_orcl
oracle    2873     1  0 09:11 ?        00:00:00 ora_lgwr_orcl
oracle    2875     1  0 09:11 ?        00:00:00 ora_ckpt_orcl
oracle    2877     1  0 09:11 ?        00:00:01 ora_smon_orcl
oracle    2879     1  0 09:11 ?        00:00:00 ora_reco_orcl
oracle    2881     1  0 09:11 ?        00:00:00 ora_cjq0_orcl
oracle    2883     1  0 09:11 ?        00:00:01 ora_mmon_orcl
oracle    2885     1  0 09:11 ?        00:00:00 ora_mmnl_orcl
oracle    2887     1  0 09:11 ?        00:00:00 ora_d000_orcl
oracle    2889     1  0 09:11 ?        00:00:00 ora_s000_orcl
oracle    2906     1  0 09:11 ?        00:00:00 ora_arc0_orcl
oracle    2908     1  0 09:11 ?        00:00:00 ora_arc1_orcl
oracle    2913     1  0 09:11 ?        00:00:00 ora_qmnc_orcl
oracle    3060     1  0 09:12 ?        00:00:00 ora_q000_orcl
oracle    3062     1  0 09:12 ?        00:00:00 ora_q001_orcl
root      3096  3072  0 09:12 pts/1    00:00:00 su - oracle
oracle    3097  3096  0 09:12 pts/1    00:00:00 -bash
oracle    3120  3097  0 09:12 pts/1    00:00:00 rlwrap sqlplus / as sysdba
oracle    3121  3120  0 09:12 pts/2    00:00:00 sqlplus   as sysdba
oracle    3122  3121  0 09:12 ?        00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
root      3943  3892  0 10:29 pts/3    00:00:00 su - oracle
oracle    3944  3943  0 10:29 pts/3    00:00:00 -bash
oracle    3971     1  0 10:30 ?        00:00:00 ora_j000_orcl
oracle    3972  3944  0 10:30 pts/3    00:00:00 ps -ef
oracle    3973  3944  0 10:30 pts/3    00:00:00 grep ora

3，SQL> select pid,spid,username,pga_used_mem,pga_alloc_mem,pga_freeable_mem,pga_max_mem
  2  from v$process where spid=2871;

       PID SPID         USERNAME        PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
---------- ------------ --------------- ------------ ------------- ---------------- -----------
         5 2871         oracle                274565       1671469                0     1671469

SQL>
四，SQL的工作区中以3中方式执行
1，Optimal（优化方式）：指所有处理可以在内存中完成。
2，Onepass：大部分操作都可以在内存中完成，但是需要使用到磁盘操作。
3，Multipass：最大操作需要产生磁盘交互，性能极差。
4，通常对于PGA的优化目标，就是使用Optimal的执行尽量高，也就是在内存中完成所有的排序等操作；同时Multipass操作尽量低，也就是使磁盘交互尽量低。也就是期望实现如下目标：
workarea execution -optimal >=90%;
workarea execution -multipass =0%;
5，SQL>  SELECT NAME,VALUE,
  2  100*(VALUE
  3  /DECODE((SELECT SUM(VALUE) FROM v$sysstat
  4  WHERE NAME LIKE 'workarea executions%'),
  5  0,NULL,
  6  (SELECT SUM(VALUE) FROM v$sysstat
  7  WHERE NAME LIKE 'workarea executions%')
  8  )
  9  ) pct
 10  FROM v$sysstat
 11  WHERE NAME LIKE 'workarea executions%';
NAME                                                                  VALUE        PCT
---------------------------------------------------------------- ---------- ----------
workarea executions - optimal                                          9626        100
workarea executions - onepass                                             0          0
workarea executions - multipass                                           0          0
五，v$PGASTAT
1，伴随着PGA调整新特性的引入，Oracle随之引入了一系列新的视图，V$PGASTAT就是其中的一个。在V$PGASTAT中有这样的一个条目gobal memory bound，就该条目记录数据库允许的最高PGA内存的使用量，可以从不同的PGA
参数设置来观察一下Oracle运行的PGA上限。
2，SQL> alter system set pga_aggregate_target=&Nm;
Enter value for nm: 10m
old   1: alter system set pga_aggregate_target=&Nm
new   1: alter system set pga_aggregate_target=10m
System altered.
SQL> set autotrace traceonly
SQL> SELECT DISTINCT * FROM SCOTT.EMP WHERE ROWNUM<500000;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2332671984

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   518 |     4  (25)| 00:00:01 |
|   1 |  HASH UNIQUE        |      |    14 |   518 |     4  (25)| 00:00:01 |
|*  2 |   COUNT STOPKEY     |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<500000)


Statistics
----------------------------------------------------------
        459  recursive calls
          0  db block gets
         83  consistent gets
         11  physical reads
          0  redo size
       1380  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL> SET autotrace off


SQL> SELECT sql_text,operation_type,POLICY,last_memory_used/1024/1024,
  last_execution,last_tempseg_size
  FROM v$sql l,v$sql_workarea a
  WHERE l.hash_value=a.hash_value
  AND sql_text='SELECT DISTINCT * FROM SEASON1';

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!???????????????????????????!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
六，PGA的调整建议
1，伴随自动PGA调整功能的引入，Oracle同时引入相应的动态性能视图用于优化建议PGA的优化建议通过v$pga_target_advice和v$pga_target_histogram提供。v$pga_target_advice视图通过对不同PGA设置进行评估，给出在
不同设置下的PGA命中率和OverAlloc信息。
2，SQL> select PGA_TARGET_FOR_ESTIMATE/1024/1024 PGAMB,PGA_TARGET_FACTOR,ESTD_PGA_CACHE_HIT_PERCENTAGE,ESTD_OVERALLOC_COUNT
  2  from v$pga_target_advice;

     PGAMB PGA_TARGET_FACTOR ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
---------- ----------------- ----------------------------- --------------------
        10                 1                            69                    3
        12               1.2                            69                    3
        14               1.4                            69                    3
        16               1.6                            69                    3
        18               1.8                            69                    3
        20                 2                            69                    3
        30                 3                            69                    3
        40                 4                            69                    3
        60                 6                           100                    0
        80                 8                           100                    0

10 rows selected.
SQL>
3，可以看到，在以上输出中，当PGA设置为60M时，即可消除PGA过载，在PGA设置为60M时，命中率到达100%当前的PGA设置为
10M修改为60M，从而提高性能。
4，SQL> show parameter pga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 10M
SQL>
七，v$pga_target_advice_histogram 视图
1，可以通过对不同的工作区间大小的采样评估提供统计信息供分析使用，其中几个重要的字段
LOW_OPTIMAL_SIZE-Histogram评估区间内Optimal下限（bytes）
HIGH_OPTIMAL_SIZE-Histogram评估区间内Optimal上限（bytes）
ESTD_OPTIMAL_EXECUTIONS-Histogram评估区间内估计Optimal执行次数；
ESTD_ONEPASS_EXECUTIONS-Histogram评估区间内估计Onepass执行次数；
ESTD_MULTIPASSES_EXECUTIONS-Histogram评估区间内估计Multipass执行次数；
ESTD_TOTAL_EXECUTIONS-Histogram评估区间内估计执行总次数；
2，SQL> select pga_target_factor factor,low_optimal_size/1024 low,
ROUND(high_optimal_size/1024) high,
estd_optimal_executions estd_opt,estd_onepass_executions estd_op,
estd_multipasses_executions estd_mp,estd_total_executions estd_exec
FROM v$pga_target_advice_histogram
WHERE pga_target_factor=1.8 AND estd_total_executions >0
/

    FACTOR        LOW       HIGH   ESTD_OPT    ESTD_OP    ESTD_MP  ESTD_EXEC
---------- ---------- ---------- ---------- ---------- ---------- ----------
       1.8       4096       8192          2          2          0          4
       1.8        512       1024         13          0          0         13
       1.8         64        128          1          0          0          1
       1.8          2          4       2277          0          0       2277

注：PGA的新特性使PGA管理得以极大简化。

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

转载于:http://blog.itpub.net/29611940/viewspace-1144944/