书名书名书名书名书名书名书名书名书名书名书名书名书名书名
内存。这个参数可以被动态的更改,取值范围从10M ～（4096G-1 ）bytes。 u? WORKAREA_SIZE_POLICY-此参数用于开关PGA内存自动管理功能，该参数有两
个选项：AUTO 和 MANUAL，当设置为AUTO时，数据库使用自动PGA管理功能，当设置为MANUAL时，则仍然使用之前手工管理的方式。 缺省的，WORKAREA_SIZE_POLICY参数被设置为AUTO。 此外需要注意的是，在不同版本中，自动PGA管理的范畴不同：
u? 在Oracle9i中，PGA_AGGREGATE_TARGET参数仅对专用服务器模式下（Dedicated
Server）的专属连接有效，但是对共享服务器（Shared Server）连接无效
u? 从Oracle10g开始PGA_AGGREGATE_TARGET对专用服务器连接和共享服务器连
接同时生效
5.2.4 参数的设置与内存分配
PGA_AGGREGATE_TARGET （通常可以缩写为P_A_T）参数同时限制全局PGA分配和私有工作区内存分配。
在Oracle9i以及Oracle10gR1中，单个SQL操作内存使用存在如下限制： 1．对于串行操作，单个SQL操作能够使用的PGA内存按照以下原则分配：
MIN（5%*PGA_AGGREGATE_TARGET，100MB）
此处5%*P_A_T实际上是由一个内部参数_smm_max_size决定的，该参数限制自动工作区模式下最大的工作区使用（maximum work area size in auto mode -serial） 2．对于并行操作
30% PGA_AGGREGATE_TARGET /DOP （DOP=Degree Of Parallelism 并行度）
对于Oracle10gr2以及Oracle11g存在如下限制：
1.对于串行操作，单个SQL操作能够使用的PGA内存按照以下原则分配：
如果P_A_T <= 500MB，则_smm_max_size = 20%*P_A_T
如果P_A_T 在 500MB 和1000MB 之间，_smm_max_size = 100M
如果P_A_T 介于1001MB 和 2560MB (2.5GB)之间，_smm_max_size = 10%* P_A_T 如果P_A_T > 2560MB (2,5GB)则 _smm_max_size = 262,060 MB (~0,25GB) 2．对于并行操作
50% PGA_AGGREGATE_TARGET /DOP （DOP=Degree Of Parallelism 并行度） 但是注意，当DOP <=5 时，_smm_max_size限制生效，并行度超过5时另外一个限制并行的参数 _smm_px_max_size才会生效。
从Oracle10g开始的新PGA管理算法受一个新增的隐含参数_newsort_enabled影响，如果将该参数设置为False，则数据库会使用之前Oracle9iR2中的算法规则：
SQL> @GetHidPar
Enter value for name: newsort_enabled old 4: AND x.ksppinm LIKE '%&name%' ·6·
第1章 章名章名章名章名章名
new 4: AND x.ksppinm LIKE '%newsort_enabled%' NAME VALUE
-------------------------------------------------- -------------------- _newsort_enabled TRUE
要理解PGA的自动调整，还需要区分可调整内存（TUNABLE MEMORY SIZE）与不可调整内存（UNTUNABLE MEMORY SIZE）。可调整内存是由SQL工作区使用的，其余部分是不可调整内存。
启用了自动PGA调整之后， Oracle仍然需要遵循以下原则：
UNTUNABLE MEMORY SIZE + TUNABLE MEMORY SIZE <= PGA_AGGREGATE_TARGET
数据库系统只能控制可调整部分的内存分配，如果可调整的部分过小，则Oracle永远也不会强制启用这个等式。
另外，PGA_AGGREGATE_TARGET参数在CBO优化器模式下，对于SQL的执行计划会产生影响。Oracle在评估执行计划时会根据PGA_AGGREGATE_TARGET参数评估在Sort，HASH-JOIN或Bitmap操作时能够使用的最大或最小内存，从而选择最优的执行计划。
对于PGA_AGGREGATE_TARGET参数的设置，Oracle提供这样一个建议方案 1．对于OLTP系统
PGA_AGGREGATE_TARGET = (
PGA_AGGREGATE_TARGET = (
注意：在某些os上单个进程使用的真实内存可能远大于在Oracle中看到的PGA大小，如
AIX。在AIX上通常建议Oracle使用内存不超过物理内存的70%。
这只是一个建议设置，更进一步的应该根据数据库的具体性能指标来调整和优化PGA的使用。伴随这个新特性的引入V$PROCESS视图增加了相应字段用来记录进程的PGA耗用，选择一个Oracle用户进程：
SQL> ! ps -ef|grep LOCAL|head -1
oracle 2803 1 0 Jul13 ? 00:00:46 oracleeygle (LOCAL=NO)
其相关的PGA使用现在可以从v$process视图获得：
SQL> select * from v$version where rownum <2; BANNER
---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
SQL> select pid,spid,username,pga_used_mem,pga_alloc_mem,pga_freeable_mem,pga_max_mem 2 from v$process where spid=2803;
PID SPID USERNAME PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM ---- ------------ ---------- ------------ ------------- ---------------- -----------
·7·
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
18 2803 oracle 6037917 7217777 917504 12460657
而通过V$PROCESS_MEMORY视图还可以进一步知道PGA内存消耗在什么地方：
SQL> SELECT p.program,p.spid,pm.category,pm.allocated,pm.used,pm.max_allocated 2 FROM V$PROCESS p, V$PROCESS_MEMORY pm 3 WHERE p.pid = pm.pid AND p.spid = 2803;
PROGRAM SPID CATEGORY ALLOCATED USED MAX_ALLOCATED ----------- ------ ------- ---------- ---------- ---------- ------------- oracleeygle@eygle 2803 SQL 137208 47028 1679336 oracleeygle@eygle 2803 PL/SQL 98528 33528 123196 oracleeygle@eygle 2803 Freeable 917504 0
oracleeygle@eygle 2803 Other 6064537 9740621
SQL在工作区中以三种方式执行：
u? 优化方式（Optimal）-指所有处理可以在内存中完成
u? onepass - 大部分操作可以在内存中完成，但是需要使用到磁盘操作 u? multipass - 大量操作需要产生磁盘交互，性能极差 下图显示了在不同方式下响应时间与内存分配曲线：
通常我们对于PGA的优化目标就是使得Optimal的执行尽量高，也就是尽量在内存中完成所有排序等操作；同时使multipass操作尽量低，也就是要使磁盘交互尽量低。
工作区性能期望实现如下目标：
workarea execution - optimal >= 90% workarea execution - multipass = 0% 以下是一个生产系统的PGA性能指标：
SQL> SELECT NAME, VALUE, 2 100 * ( VALUE
3 / DECODE ((SELECT SUM (VALUE) FROM v$sysstat ·8·
第1章 章名章名章名章名章名
4 WHERE NAME LIKE 'workarea executions%'), 0, NULL, 5 (SELECT SUM (VALUE) FROM v$sysstat
6 WHERE NAME LIKE 'workarea executions%'))) pct 7 FROM v$sysstat WHERE NAME LIKE 'workarea executions%';
NAME VALUE PCT --------------------------------- ---------- ----------- workarea executions - optimal 22478 97.9433551 workarea executions - onepass 397 1.72984749 workarea executions - multipass 75 .326797386
5.2.5 自动PGA管理实现原理
自动PGA管理采用反馈环（Feedback Loop）算法实现，其原理如下图所示。当活动进程开始执行SQL语句时，首先会通过Local Memory Manager注册一个Active Workarea Profile，工作区Profile是进程与内存管理器之间通讯的唯一接口，Profile是包含了这个Workarea的一系列属性（如类型、执行所需要的minimum、one-pass and optimal内存大小等）的元数据。
工作区活动Profile集通过Local Memory Manager维护，存储在SGA之中，由于Porfile经常被更新，所以所有Active Profile基本可以反应出当前PGA内存需要和当前正在使用的内存。有了这些Profile信息，后台的Global Memory Manager就可以计算出一个既能限制内存使用、又能提供较好性能的Global Memory Bound，这个值用于限制单个进程使用的PGA内存上限；Global Memory Manager每隔3秒更新一次Memory bound，Local Memory Manager得到Memory Bound后会计算出每个Active Statement所需要分配的PGA内存大小，在这里被称为Expect Size，然后每个Active Statement将会在自己所分配到的Expect Size内存中进行运算：
注意，在以上流程中，Global Memory Manager并不直接参与PGA内存的分配，但是通过
·9·
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
其计算得出的Global Memory Bound将影响所有进程的PGA分配。
Global Memory Manager由CKPT后台进程实现。通过底层表x$messages可以发现如下记录：
SQL> select description,dest from x$messages where description like 'SQL Memory%'; DESCRIPTION DEST ---------------------------------------- ---------- SQL Memory Management Calculation CKPT
以下查询来自Oracle11g数据库，通过x$trace表同样可以发现这样的信息：
SQL> set linesize 120
SQL> column time format 99999999999999999 SQL> column data format a80 SQL> SELECT TIME time,data
2 FROM x$trace WHERE data LIKE '%SQL Memory%' ORDER BY seq#; TIME DATA
------------------ ------------------------------------------------------------ 1215510948066330 KSBCTI: (CKPT) : (timeout action) : acnum=[178] comment=[S QL Memory Management Calculation]
1215510951087639 KSBCTI: (CKPT) : (timeout action) : acnum=[178] comment=[S QL Memory Management Calculation]
1215510954109207 KSBCTI: (CKPT) : (timeout action) : acnum=[178] comment=[S QL Memory Management Calculation]
在Oracle10gR2以及Oracle11g中，可以通过v$sgastat视图来查询工作区的管理内存分配，这部分内存在Shared Pool中分配：
SQL> select * from v$version where rownum <2; BANNER
-------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production SQL> select * from v$sgastat where name like 'work area%'; POOL NAME BYTES ------------ -------------------------- ---------- shared pool work area tab 265320
伴随自动PGA调整新特性的引入，Oracle随之引入了一系列新的视图,V$PGASTAT就是其中之一，Global Memory Bound就记录在该视图之中，以下可以从不同的PGA参数设置来观察一下Oracle运行的PGA上限（测试来自Oracle9iR2环境）：
SQL> alter system set pga_aggregate_target=10m; System altered.
SQL> SELECT DISTINCT * FROM t WHERE ROWNUM < 500000; 20000 rows selected. Elapsed: 00:03:04.12 ·10·
05内存管理
