深入解析oracle：中的内存管理讲解

内存管理
Oracle 数据库实例启动时，就需要分配共享内存，启动后台进程，如何分配和设置共享内
存参数，对于 Oracle 来说是非常重要的。不当的内存分配轻则影响性能，重则导致数据库故
障，在生产实际中不容忽视。
Oracle 数据库所使用的内存主要涉及两个方面：PGA 和 SGA。本章就 Oracle 的内存管理
问题进行探讨。
5.1 PGA 管理
PGA 指的是程序全局区（Program Global Area），是服务器进程（Server Process）使用的
一块包含数据和控制信息的内存区域，PGA 是非共享的内存，在服务器进程启动或创建时分
配（在系统运行时，排序、连接等操作也可能需要进一步的 PGA 分配），并为 Server Process
排他访问，所以 PGA 中的数据结构并不需要通过 Latch 来保护。
5.2.1 什么是 PGA
进程的创建通常有两种模式：专用服务器模式（Dedicated Server）及共享服务器模式
（Shared Server）。在专用服务器模式下，Oracle 会为每个会话启动一个 Oracle 进程；而在共享
服务器模式下，通常在服务器端启动一定数量的服务器进程，然后由多个客户端请求共享同一
个 Oracle 服务进程。通常数据库都应当运行在专用服务器模式下。PGA 的内容依专用模式和
共享服务器模式而有所不同，但是通常来说，PGA 中包含私有 SQL 区(存放绑定信息、运行时
内存结构等)、Session 信息等内容。
从内存分配与使用上 PGA 可以被区分为两个区域：
u 固定 PGA（Fixed PGA）- 固定 PGA 和固定 SGA 类似，包含了大量原子变量、小的
数据结构和指向可变 PGA 的指针，这些变量在源码中定义，在编译时分配，可以被
认为是 PGA 的保留内存。
u 可变 PGA（Variable PGA）- 可变 PGA 通过具体的内存 Heap 分配来实现，其空间分
配与使用时可以变化的，通过内部视图 X$KSMPP（[K]ernel [S]ervice [M]emory [P]GA
hea[P]）可以查询可变 PGA 内存的分配和使用情况。PGA 的可变区中主要包含会话
内存及私有 SQL 区等。
下图简要说明了 PGA 的创建过程，当客户端向服务器发送连接请求，服务器监听到客户
端请求，在专用服务器模式下，会在服务器端衍生一个 Server Process 来代理用户的请求，服
务器进程进而向实例发起连接，创建会话（CREATE SESSION），而 PGA 就为 Server Proces
所分配和使用：
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·2·
可变 PGA 部分实际上是我们最为关注的 PGA 部分。虽然 PGA 的内容对于专用和共享模
式会有所不同，但是通常来说，可变 PGA 又进一步的由以下两部分组成：
u 会话内存 - Session Memory：用于存放会话的登录信息以及其他相关信息，对于共享
服务器模式，这部分内存是共享而非私有的。
u 私有的 SQL 区 - Private SQL Area：Private SQL Area 包含绑定变量信息、查询执行
状态信息以及查询工作区等。每个发出 SQL 查询的会话都拥有一块私有 SQL 区，对
于专用服务器模式，这部分内存在 PGA 中分配，对于共享服务器模式，这部分内存
在 SGA 中分配。
在这里还需要了解的一个概念是游标（Cursor）。Oracle 的应用程序或用户的应用程序执
行时，都可能显示或隐式的打开游标（Open Cursor）来进行任务处理，打开游标就需要分配
SQL Area。管理私有 SQL 区是用户进程的责任，而分配和回收则依赖于具体的应用程序，为
了防止过度的 SQL 工作区分配，Oracle 通过 OPEN_CURSORS 参数来限制每个用户进程能够
同时打开的游标数量。一个私有 SQL 区在 Cursor 打开时分配，当执行结束游标关闭时释放。
简单来说，用户进程的任务执行以及 Cursor 的使用是 PGA 内存的主要消耗者，也是我们
在进行数据库性能优化时最关心的内容，实际上数据库的活动主要就是 Cursor 的活动。
进一步的，私有 SQL 区又由以下两部分组成：
u 永久区域 - Persistent Area：这个区域包含绑定变量等信息，这部分内存只有在游标
被关闭时才会被释放。
u 运行时区域 – Runtime Area：这个区域存放了 SQL 语句运行时所需要的信息，在执
行请求时首先创建，其中包含了查询执行的状态信息（如对于全表扫᧿，则记录全
表扫᧿的进度等）、SQL work areas（这部分区域在内存密集型请求下分配，如 Sort
或者 Hash-Join 等，对于 DML 语句来说，SQL 语句执行完毕就释放该区域，对于查
询语句则是在记录返回后或查询取消时释放）
下图简要说明了 PGA 的整体结构，图示中包含了固有 SGA 部分，也包含了游标运行时
示意：
第 1 章 章名章名章名章名章名
·3·
5.2.2 UGA 与 CGA
在上图 PGA 的介绍中，注意到存在一块成为 UGA（User Global Area -用户全局区）的内
存区域，这也是可以经常见到的一个名词。UGA 由用户会话数据、游标状态和索引区组成。
在共享服务器模式下，一个共享服务进程被多个用户进程共享，此时 UGA 是 Shared Pool 或
Large Pool 的一部分，而在专用服务器模式下，UGA 则是 PGA 的一部分。
不考虑 Shared Server 模式，在 Dedicated 模式下，PGA 与 UGA 关系，就如同 Process 和
Session 的关系，PGA 是服务于进程的内存结构，包含进程信息；而 UGA 是服务于会话的，
它包含的是会话的信息。UGA 中包含如下信息：
u 打开游标的永久区和运行区；
u 包的状态信息以及变量信息；
u Java 会话的状态信息；
u 启用角色信息、跟踪事件；
u 起作用的 NLS 参数；
u 所有打开的 database links；
u 会话访问控制信息等
和 PGA 一样，UGA 也由两组区组成，固定 UGA 和可变 UGA（或者说 UGA 堆）。固定
UGA 包含了大概 70 个原子变量、小的数据结构以及指向 UGA 堆的指针。
UGA 中的内存分配可以通过内部表 X$KSMUP（X$KSMUP - [K]ernel [S]ervice [M]emory
[U]GA Hea[P]）查询得到。UGA 堆包含了存储一些固定表（X$表）的永久内存（依赖于特定
参数的设置，如 OPEN_CURSORS，OPEN_LINKS 和 MAX_ENABLED_ROLES）。除此以外，
大部分的 UGA 用于私有 SQL 区和 PL/SQL 区。
从 Oracle9iR2 开始，有一系列新的隐含参数被引入用于控制自动的 PGA 管理，这其中有
一个关键的参数是_use_realfree_heap，当设置这个参数为 true 时，Oracle 会为 CGA、UGA 单
独分配堆，而不从 PGA 中分配。它的默认值为 false，而当设置了 pga_aggregate_target 后，它
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·4·
的值自动被改为 true：
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%';
Enter value for par: realfree
NAME VALUE DESCRIB
------------------------------ -------- ------------------------------------------------
_realfree_heap_max_size 32768 minimum max total heap size, in Kbytes
_realfree_heap_free_threshold 4194303 threshold for performing real-free, in Kbytes
_realfree_heap_mode 0 mode flags for real-free heap
_use_realfree_heap TRUE use real-free based allocator for PGA memory
_use_realfree_heap 是自动管理 PGA 技术的关键技术变化，realfree 代表着实时释放。
Oracle9i 之前手工管理的 PGA 的主要问题在于，UGA 缺省的在 PGA 中分配，当会话执行了
诸如排序、HASH-JOIN 等操作，耗用了大量 PGA 内存，而当会话执行完毕之后，内存会释放
给 PGA 而不是 OS，在很多时候这会导致过度的 PGA 内存使用（在以前版本 PGA 内存分配
和回收是通过 malloc()以及 brk()调用来完成的）；从 Oracle9iR2 开始，自动的 PGA 内存管理当
_use_realfree_heap 为 true 时，PGA 的内存分配将会通过 mmap()调用来实现，这样当调用结束
时将不必将内存返回给进程而直接返回给 OS，从而实现了更好的 PGA 内存分配与使用。
通过 V$PGASTAT 视图可以查询 PGA 累计释放回 OS 的内存空间：
SQL> select name,value from v$pgastat where name like '%OS';
NAME VALUE
---------------------------------------- ----------------------
PGA memory freed back to OS 39824916480
下图是 UGA 的结构示意图：
在 PGA 的示意图中，还涉及了另外一块内存区域被称为 CGA（Call Global Area）-调用
第 1 章 章名章名章名章名章名
·5·
全局区。与其他的全局区不同，CGA 的存在是瞬间的，只存在于调用过程中，而且无论 UGA
存在于 PGA 还是 SGA，CGA 都是 PGA 的 SubHeap。对于实例的一些低层次的调用（Low-Level
Call）需要 CGA，包括分析 SQL 语句、执行 SQL 语句以及获取查询结果都需要使用 CGA，
在 SQL 执行过程中的每个递归调用需要一个独立的 CGA，在 SQL 的解析过程中，查询数据
字典信息、对 SQL 进行语法以及语义的解析、SQL 的优化以及不同执行计划的评估都需要使
用 CGA。
当然，调用并不是只通过 CGA 中的数据结构来工作，实际上调用所需要的大部分的重要
数据结构都来自于 UGA（如 SQL AREA, PL/SQL AREA，Sort Area 都存放在 UGA 中，因为
这些结构在调用期间需要一直可用），CGA 中只包含了那些调用结束后可以被释放的数据。例
如，CGA 中包含了 Direct I/O BUFFER、递归调用信息、表达式评估的堆栈信息等，此外 Java
调用内存也在 CGA 中分配。
5.2.3 PGA 管理技术的变迁
在 Oracle 9i 以前的版本中，PGA 由一系列的内存区域组成，这些区域包括主要由
*_area_size 参数控制。在 Oracle8i 的环境中，这些参数主要有：sort_area_size、hash_area_size、
bitmap_merge_size、create_bitmap_area_size。
可以从数据库中得到这些参数设置的当前值，在 Oracle8i 中可以通过手工修改
sort_area_size、hash_area_size 等参数值来控制 PGA 的使用：
SQL> select * from v$version where rownum <2;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
SQL> show parameter area_size
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
hash_area_size integer 131072
sort_area_size integer 65536
这种独立管理的方式存在一个极大的弊端，以 SORT 操作为例，如果我们为了使特定的
排序操作能够在内存中完成，可能需要设置较大的 sort_area_size，但是由于进程的独立 PGA
内存难于回收和共享，这样可能导致过度的 PGA 内存消耗，所以合理设置和调整 PGA 在
Oracle9i 之前是一件比较复杂的事情；从 Oracle9i 开始，Oracle ᨀ供了一种新的 PGA 内存管
理方法：自动化 SQL 执行内存管理（Automated SQL Execution Memory Management），也称为
自动 PGA 管理，使用这个新特性，Oracle 可以在一个总体 PGA 使用限制下自动管理和调整
SQL 内存区，从而大大简化了 DBA 的工作，同时也ᨀ高了数据库的性能。
为实现自动的 PGA 管理，Oracle 引入了几个新的初始化参数：
u PGA_AGGREGATE_TARGET-此参数用来指定所有 session 总计可以使用最大 PGA
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·6·
内存。这个参数可以被动态的更改,取值范围从 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%'
第 1 章 章名章名章名章名章名
·7·
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 = (<Total Physical Memory > * 80%) * 20%
2．对于 DSS 系统
PGA_AGGREGATE_TARGET = (<Total Physical Memory > * 80%) * 50%
也就是说，对于一个单纯的数据库服务器，通常我们需要保留 20%的物理内存给操作系
统使用，剩余 80%可以分配给 Oracle 使用。Oracle 使用的内存分为两部分 SGA 和 PGA，那么
PGA 可以占用 Oracle 消耗总内存的 20%（OLTP 系统）至 50%（DSS 系统）
注意：在某些 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
---- ------------ ---------- ------------ ------------- ---------------- -----------
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·8·
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
第 1 章 章名章名章名章名章名
·9·
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 内存的分配，但是通过
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·10·
其计算得出的 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
第 1 章 章名章名章名章名章名
·11·
SQL> SELECT sql_text, operation_type, POLICY, last_memory_used / 1024 / 1024,
2 last_execution, last_tempseg_size
3 FROM v$sql l, v$sql_workarea a WHERE l.hash_value = a.hash_value
4 AND sql_text = 'SELECT DISTINCT * FROM t WHERE ROWNUM < 500000';
OPERATION_TYPE POLIC LAST_MEMORY_USED/1024/1024 LAST_EXE LAST_TEMPSEG_SIZE
------------------ ------ ------------------------- -------- -----------------
GROUP BY (SORT) AUTO .548828125206 PASSES 62914560
SQL> SELECT NAME, VALUE / 1024 / 1024 MB FROM v$pgastat
2 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 10
global memory bound .5
SQL> alter system set pga_aggregate_target=30M;
System altered.
SQL> SELECT DISTINCT * FROM t WHERE ROWNUM < 500000;
20000 rows selected.
Elapsed: 00:00:53.30
SQL> SET autotrace off
SQL> SELECT operation_type, POLICY, last_memory_used / 1024 / 1024,
2 last_execution, last_tempseg_size
3 FROM v$sql l, v$sql_workarea a WHERE l.hash_value = a.hash_value
4 AND sql_text = 'SELECT DISTINCT * FROM t WHERE ROWNUM < 500000';
OPERATION_TYPE POLIC LAST_MEMORY_USED/1024/1024 LAST_EXECUTION LAST_TEMPSEG_SIZE
------------------ ----- --------------------------- -------------- -----------------
GROUP BY (SORT) AUTO 1.480468756 PASSES 57671680
SQL> SELECT NAME, VALUE / 1024 / 1024 MB FROM v$pgastat
2 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 30
global memory bound 1.5
在 Oracle9iR2 中，通过以上测试可以注意到，PGA 的 global memory bound 会一直处在 5%
的PGA_AGGREGATE_TARGET参数设置，直到5%*PGA_AGGREGATE_TARGET超过100M,
然后 global memory bound 被限制为 100M。
修改 PGA_AGGREGATE_TARGET 参数可以使用类似如下命令:
alter system set pga_aggregate_target=4096M ;
修改参数后,通常需要执行一些排序操作才能看到视图信息的变化，以下通过一些不同设
置输出继续来看一下 PGA_AGGREGATE_TARGET 与 global memory bound 的关系：
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·12·
SQL> SELECT NAME, VALUE / 1024 / 1024 MB FROM v$pgastat
2 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 10
global memory bound .5
SQL> SELECT NAME, VALUE / 1024 / 1024 MB FROM v$pgastat
2 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 40
global memory bound 2
SQL> SELECT NAME, VALUE / 1024 / 1024 MB FROM v$pgastat
2 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 1024
global memory bound 51.1992188
SQL> SELECT NAME, VALUE / 1024 / 1024 MB FROM v$pgastat
2 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 4096
global memory bound 100
实际上这个 100M 的上限是受到了另外一个隐含参数的控制，该参数为_pga_max_size，
在 Oracle9iR2 中该参数的缺省值为 200M，单进程串行操作 PGA 的上限不能超过该参数的 1/2.
SQL> @GetHidPar
Enter value for par: pga_max
old 6: AND x.ksppinm LIKE '%&par%'
new 6: AND x.ksppinm LIKE '%pga_max%'
NAME VALUE DESCRIB
--------------------------------------------- ---------------- ---
_pga_max_size 209715200 Maximum size of the PGA memory for one
process
如果修改该参数， global memory bound 将可以突破 100M 的上限:
SQL> alter system set "_pga_max_size"=400M;
System altered.
……………………
SQL> SELECT NAME, VALUE / 1024 / 1024 MB FROM v$pgastat
第 1 章 章名章名章名章名章名
·13·
2 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 4096
global memory bound 200
从 Oracle10gR2 开始，_pga_max_size 的设置和 PGA_AGGREGATE_TARGET 相关，在不
同 P_A_T 参数设置下，_pga_max_size 会自动调整以ᨀ供更好的性能需要。通过一些简单测试
可以得出 Oracle10gR2 下两者的关系：
_pga_max_size PGA_AGGREGATE_TARGET 比例关系
209715200 800M 0.25
214732800 1G 0.2
429486080 2G 0.2
644239360 3G 0.2
858992640 4G 0.2
939180032 5G 0.2
939180032 6G 0.15
注意到当 P_A_T 设置大于 5G 之后，_pga_max_size 不再变化，在 1G ~ 5G 范围，
_pga_max_size 按照 20%*P_A_T 设置增长。当 PGA 增大，单进程能够使用的最大内存应当随
之增加，这种增强是极其有益的。
对于 PGA 的控制,还有一系列的内部参数,列举如下,仅供参考:
NAME VALUE DESCRIB
------------------------ ----- ----------------------------------------------------------
_smm_auto_min_io_size 56 Minimum IO size (in KB) used by sort/hash-join in auto mode
_smm_auto_max_io_size 248 Maximum IO size (in KB) used by sort/hash-join in auto mode
_smm_auto_cost_enabled TRUE if TRUE, use the AUTO size policy cost functions
_smm_control 0 provides controls on the memory manager
_smm_trace 0 Turn on/off tracing for SQL memory manager
_smm_min_size 128 minimum work area size in auto mode
_smm_max_size 2560 maximum work area size in auto mode (serial)
_smm_px_max_size 15360 maximum work area size in auto mode (global)
_smm_bound 0 overwrites memory manager automatically computed bound
_smm_advice_log_size 0 overwrites default size of the PGA advice workarea history log
_smm_advice_enabled TRUE if TRUE, enable v$pga_advice
5.2.6 PGA 的调整建议
伴随自动 PGA 调整功能的引入，Oracle 同时引入相应的动态性能视图用于优化建议，PGA
的优化建议通过 v$pga_target_advice 和 v$pga_target_advice_histogram ᨀ供。
v$pga_target_advice 视图通过对不同 PGA 设置进行评估，给出在不同设置下的 PGA 命中
率和 OverAlloc 等信息。
SQL> select PGA_TARGET_FOR_ESTIMATE/1024/1024 PGAMB, PGA_TARGET_FACTOR,
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
·14·
2 ESTD_PGA_CACHE_HIT_PERCENTAGE, ESTD_OVERALLOC_COUNT
3 from v$pga_target_advice;
PGAMB PGA_TARGET_FACTOR ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
---------- ----------------- ----------------------------- --------------------
37.5 0.125 35 391
75 0.25 43 58
150 0.5 61 0
225 0.75 70 0
300 1 70 0
360 1.2 71 0
420 1.4 72 0
480 1.6 72 0
540 1.8 72 0
600 2 72 0
900 3 72 0
1200 4 72 0
1800 6 72 0
2400 8 72 0
14 rows selected
可以看到，在以上输出中，当 PGA 设置为 150M 时，即可消除 PGA 过载；在 PGA 设置
为 420M 时，可以达到最高命中率 72%；当前 PGA 设置为 300M。根据这些信息，我们可以
调整 PGA 为 420M，从而ᨀ高性能