一、12c：官档
Books → Performance Tuning Guide → Part III Tuning Database Memory → 16 Tuning the Program Global Area
二、关于 Program Global Area
程序全局区域(PGA)是一个私有内存区域，它包含一个服务器进程的数据和控制信息。只有服务器进程才能访问PGA。Oracle数据库代表服务器进程在PGA中读取和写入信息。此类信息的一个示例是游标的运行时区域。每当执行一个游标时，都会在服务器进程执行该游标的PGA内存区域中为该游标创建一个新的运行时区域。
Note:在使用共享服务器时，部分运行时区域可以位于共享全局区域(SGA)。
对于复杂的查询(比如决策支持查询)，运行时区域的很大一部分专门用于内存密集型操作符分配的工作区域，包括:
- 基于排序的操作符，如ORDER BY、GROUP BY、ROLLUP和window函数。
- Hash-join
- 位图合并
- 位图创建
- 编写用于批量加载操作的缓冲区。
1 Work Area 大小
Oracle数据库使您能够控制和调整工作区域的大小。一般来说，更大的 Work Area 可以显著改善特定操作员的性能，而代价是更高的内存消耗。现有的工作面积包括：- Optimal
Optimal 大小是指 Work Area 的大小足够大，可以容纳由其关联的SQL操作符分配的输入数据和辅助内存结构。这是 Work Area 的理想尺寸。 - One-pass
One-pass 大小是当 Work Area 的大小低于 Optimal 大小，并在部分输入数据上执行额外传递时。One-pass 大小增加响应时间. - Multi-pass
Multi-pass 大小是指 Work Area 的大小低于 One-pass 阈值，并且需要对输入数据进行多次传递时。使用 Multi-pass 大小，响应时间会显着增加，因为与输入数据大小相比，Work Area 的大小太小。
在对 Work Area 进行大小调整时，目标是使大多数 Work Area 具有 optimal 大小(对于纯oltp系统而言，超过90%，甚至100%)，并且只有少数工作区域以 one-pass 大小(小于10%)运行。应避免 multi-pass 执行，原因如下：
- multi-pass 执行会严重降低性能。
大量的 multi-pass Work Area对其关联的SQL操作符的响应时间有指数级的不利影响。 - 运行 one-pass 执行并不需要大量的内存。
只需要22 MB就可以将1gb的数据排序为 one-pass 大小。
即使DSS系统运行大型排序和hash-join，one-pass 执行的内存需求也相对较小。配置了合理数量的PGA内存的系统不需要对输入数据执行多次传递。
三、使用 AMM 调整 PGA 的大小
自动PGA内存管理简化并改进了PGA内存分配方式。默认情况下，PGA内存管理是启用的。在这种模式下，Oracle数据库会根据SGA内存大小的20%动态调整PGA内存中用于 Work Area 的部分，从而自动调整PGA的大小。最小值为10MB。
Note:
为了向后兼容，可以通过将PGA_AGGREGATE_TARGET初始化参数的值设置为0来禁用自动PGA内存管理。当禁用自动PGA内存管理时，Work Area 的最大大小可以使用关联的_AREA_SIZE参数(如sort_area_size参数)进行调整。
1 配置自动 PGA 内存管理
在自动PGA内存管理模式下运行Oracle数据库时，自动调整所有会话的 Work Area，并且在此模式下运行的所有会话都忽略了*_AREA_SIZE参数。Oracle数据库自动地从PGA_AGGREGATE_TARGET初始化参数中获得活动工作区域可用的PGA内存总量。PGA内存的数量被设置为PGA_AGGREGATE_TARGET的值减去分配给系统其他组件的PGA内存的数量(例如由会话分配的PGA内存)。然后，Oracle数据库根据其特定的内存需求将结果的PGA内存分配给各个活动 Work Area。
Oracle数据库试图通过动态控制分配给 Work Area 的PGA内存的数量来遵循DBA设置的PGA_AGGREGATE_TARGET值。为了实现这一点，Oracle数据库首先尝试最大限度地增加所有内存密集型SQL操作的 optimal Work Area 的数量。Work Area 的其余部分以 one-pass 模式执行，除非DBA（使用PGA_AGGREGATE_TARGET参数）设置的PGA内存限制非常低，因此需要 multi-pass 执行以减少内存消耗，以实现PGA目标限制。在配置新的数据库实例时，很难确定PGA_AGGREGATE_TARGET的适当设置。
配置自动PGA内存管理
- 对PGA_AGGREGATE_TARGET参数的值进行初始估计，如“Setting the Initial Value for PGA_AGGREGATE_TARGET”所描述的那样。
- 在数据库实例上运行一个有代表性的工作负载，并监视它的性能，如“Monitoring Automatic PGA Memory Management”中所描述的。
- 使用Oracle PGA的建议统计数据来调优PGA_AGGREGATE_TARGET参数的值，如“Tuning PGA_AGGREGATE_TARGET”中所描述的那样。
- 《Oracle Database Reference》有关PGA_AGGREGATE_TARGET初始化参数的信息。
2 设置初始化参数 PGA_AGGREGATE_TARGET
根据Oracle数据库实例的可用内存数量，设置PGA_AGGREGATE_TARGET初始化参数的初始值。这个值可以在实例级别上进行调整和动态修改。默认情况下，Oracle数据库使用SGA大小的20%作为这个值。但是，对于大型DSS系统来说，这个设置可能太低了。
设置 PGA_AGGREGATE_TARGET 初始值：
- 确定要为运行在同一系统上的操作系统和其他非Oracle应用程序预留多少物理内存。
例如，您可能决定为操作系统和其他非Oracle应用程序预留全部物理内存的20%，将系统上80%的内存专用于Oracle数据库实例。 - 将剩余可用内存划分为SGA和PGA：
- 对于OLTP系统，PGA内存通常只占可用内存的一小部分，剩下的大部分内存留给SGA。
Oracle建议最初将可用内存的20%用于PGA, 80%用于SGA。因此，OLTP系统的PGA_AGGREGATE_TARGET参数的初始值可以计算为：
PGA_AGGREGATE_TARGET = (total_mem * 0.8) * 0.2，其中total_mem是系统上可用的物理内存总量。 - 对于运行大型内存密集型查询的DSS系统，PGA内存通常可以使用多达70%的可用内存。
Oracle建议最初将50%的可用内存用于PGA, 50%用于SGA。因此，DSS系统的PGA_AGGREGATE_TARGET参数的初始值可以计算为：
PGA_AGGREGATE_TARGET = (total_mem * 0.8) * 0.5，其中total_mem是系统上可用的物理内存总量。
- 对于OLTP系统，PGA内存通常只占可用内存的一小部分，剩下的大部分内存留给SGA。
3 监控自动 PGA 内存管理
在启动优化过程之前，在数据库实例上运行一个有代表性的工作负载并监视其性能。Oracle数据库收集的PGA统计信息使您能够确定最大PGA大小是配置不足还是配置过度。监视这些统计数据使您能够评估自动PGA内存管理的性能，并相应地调优 PGA_AGGREGATE_TARGET 参数的值。3.1 使用 V$PGASTAT 视图
V$PGASTAT视图提供了有关PGA内存使用情况和自动PGA内存管理器的实例级统计信息。
下面的示例显示了这个视图的查询。
SELECT *
FROM V$PGASTAT;该查询的输出可能如下所示：
NAME VALUE UNIT
-------------------------------------------------------- ---------- ------------
aggregate PGA target parameter 41156608 bytes
aggregate PGA auto target 21823488 bytes
global memory bound 2057216 bytes
total PGA inuse 16899072 bytes
total PGA allocated 35014656 bytes
maximum PGA allocated 136795136 bytes
total freeable PGA memory 524288 bytes
PGA memory freed back to OS 1713242112 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 2383872 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 8470528 bytes
over allocation count 291
bytes processed 2124600320 bytes
extra bytes read/written 39949312 bytes
cache hit percentage 98.15 percent表 1：V$PGASTAT 视图统计数据
| 统计数据 | 描述 |
|---|---|
| aggregate PGA target parameter | 这个统计数据显示了PGA_AGGREGATE_TARGET参数的当前值。默认值是SGA大小的20%。将此参数设置为0可禁用自动PGA内存管理。 |
| aggregate PGA auto target | 此统计数据显示，Oracle内存数据库可以用于自动模式下运行的工作区域。这个数量是由PGA_AGGREGATE_TARGET参数的值和当前工作区域的工作负载动态地派生出来的。因此，它是由Oracle数据库不断调整的。如果这个值与PGA_AGGREGATE_TARGET值相比很小，那么其他系统组件(比如PL/SQL或Java)就会使用掉大部分的PGA内存，而剩下的很少一部分则由 Work Area 使用。确保在自动模式下运行的 Work Area 有足够的PGA内存。 |
| global memory bound | 此统计数据显示在自动模式下执行的 Work Area 的最大大小。Oracle数据库不断调整此值，以反映工作区工作负载的当前状态。当系统中活动工作区的数量增加时，全局内存限制通常会减少。根据经验，全局边界的值不应减少到小于1MB。如果是这样的话，则增加PGA_Aggregate_Target参数的值。 |
| total PGA allocated | 此统计数据显示数据库实例分配的当前PGA内存量。Oracle数据库试图将此数字保持在 PGA_AGGREGATE_TARGET 目标值以下。但是，如果 Work Area 工作负载正在迅速增加，或者 PGA_AGGREGATE_TARGET 参数设置为过低的值，则分配的PGA可能会以很小的百分比和较短的时间超过此值。 |
| total freeable PGA memory | 此统计数据表明可以释放多少分配的PGA内存。 |
| total PGA used for auto workareas | 此统计数据表明，在自动模式下运行的 Work Area 目前消耗了多少PGA内存。用这个数字来确定有多少内存是由PGA内存其他用户消耗（如PL/SQL或Java）： |
| over allocation count | 这个统计数据是从实例启动时累积的。如果PGA_AGGREGATE_TARGET值太小，无法容纳PGA其他组件和执行 Work Area 工作负载所需的最小内存，则可能会发生过多分配PGA内存的情况。在这种情况下，Oracle数据库无法满足 PGA_AGGREGATE_TARGET 值的要求，必须分配额外的 PGA 内存。如果出现过度分配，使用 V$PGA_TARGET_ADVICE 视图提供的信息，增加PGA_AGGREGATE_TARGET参数的值，如“Using the V$PGA_TARGET_ADVICE View”所描述的那样。 |
| total bytes processed | 此统计数据表示自实例启动以来由内存密集型SQL运算符处理的字节数。例如，处理的字节数是排序操作的输入大小。此数字用于计算缓存命中率指标。 |
| extra bytes read/written | 当 Work Area 不能 optimal 运行时，将对输入数据执行一个或多个额外的传递。此统计数据表示自实例启动以来这些额外传递过程中处理的字节数。这个数字还用于计算缓存命中率。理想情况下，与处理的总字节相比，它应该很小。 |
| cache hit percentage | 此度量由Oracle数据库计算，以反映PGA内存组件的性能。它是从实例启动开始累积的。值100%意味着系统自实例启动以来执行的所有 Work Area 都使用 optimal 的 PGA 内存。这是理想的，但很少发生，除了纯OLTP系统。通常，一些 Work Area 运行one-pass 甚至 multi-pass，这取决于PGA内存的总体大小。当 Work Area 无法 optimal 运行时，将对输入数据执行一个或多个额外的传递。这将根据输入数据的大小和执行的额外传递数来降低缓存命中率。关于这个度量的计算示例，请参见示例 1。 |
示例 1 计算缓存命中率
已经执行了4个排序操作，3个是小的(1 MB的输入数据)，一个是更大的(100mb的输入数据)。由四个操作处理的字节总数为103 MB，如果其中一个小排序运行一次，则执行超过1MB的输入数据的额外传递。这个1MB值是读/写或EBP的额外字节数。
缓存命中率使用以下公式计算：
BP x 100 / (BP + EBP)在本例中，缓存命中率为99.03%。这个值反映出只有一个小排序操作执行额外的传递，而所有其他排序操作都能够以 optimal 大小运行。因此，缓存命中率几乎为100%，因为超过1MB的额外传递代表了很小的开销。但是，如果较大的排序操作以 one-pass 的大小运行，则EBP为100 MB而不是1MB，缓存命中率降至50.73%，因为额外的传递有更大的影响。
3.2 使用 V$PROCESS 视图
V$PROCESS视图每行包含与数据库实例连接的每个Oracle进程。使用此视图中的下列列监视这些进程的PGA内存使用情况：- PGA_USED_MEM
- PGA_ALLOC_MEM
- PGA_FREEABLE_MEM
- PGA_MAX_MEM
示例 2 显示了这个视图的查询
示例 2：查询 V$PROCESS 视图
SELECT program, pga_used_mem, pga_alloc_mem, pga_freeable_mem, pga_max_mem
FROM V$PROCESS;此查询的输出可能如下所示：
PROGRAM PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
----------------------- ------------ ------------- ---------------- -----------
PSEUDO 0 0 0 0
oracle@examp1690 (PMON) 314540 685860 0 685860
oracle@examp1690 (MMAN) 313992 685860 0 685860
oracle@examp1690 (DBW0) 696720 1063112 0 1063112
oracle@examp1690 (LGWR) 10835108 22967940 0 22967940
oracle@examp1690 (CKPT) 352716 710376 0 710376
oracle@examp1690 (SMON) 541508 948004 0 1603364
oracle@examp1690 (RECO) 323688 685860 0 816932
oracle@examp1690 (q001) 233508 585128 0 585128
oracle@examp1690 (QMNC) 314332 685860 0 685860
oracle@examp1690 (MMON) 885756 1996548 393216 1996548
oracle@examp1690 (MMNL) 315068 685860 0 685860
oracle@examp1690 (q000) 330872 716200 65536 716200
oracle@examp1690 (CJQ0) 533476 1013540 0 11446123.3 使用 V$PROCESS_MEMORY 视图
V$PROCESS_MEMORY 视图通过命名组件类别显示每个Oracle进程的动态PGA内存使用。这个视图包含了每个Oracle进程的最多6行，一行用于：- 每个命名组件类别：
- Java
- PL/SQL
- OLAP
- SQL
- 可自由使用
由操作系统分配给进程的内存，但不属于特定的类别。 - 其它
已分配给类别的内存，但不属于指定类别。
- CATEGORY
- ALLOCATED
- USED
- MAX_ALLOCATED
See Also:
- 《Oracle Database Reference》有关 V$PROCESS_MEMORY 和 V$PROCESS_MEMORY_DETAIL 视图的更多信息。
3.4 使用 V$SQL_WORKAREA_HISTOGRAM 视图
V$SQL_WORKAREA_HISTOGRAM 视图显示了自实例启动以来执行的 optimal、one-pass 和 multi-pass 内存大小的 Work Area 的数量。这个视图中的统计数据被划分为 bucket。这些 bucket 是由 Work Area 的 optimal 内存需求定义的。每个 bucket 都由一系列 optimal 内存需求确定，这些需求由 LOW_OPTIMAL_SIZE 和HIGH_OPTIMAL_SIZE 列的值指定。例如，排序操作可能需要3MB内存才能以 optimal 大小(缓存)运行。有关此排序操作使用的 Work Area 的统计信息放置在由以下人员定义的 bucket 中：
- LOW_OPTIMAL_SIZE = 2097152 (2 MB)
- HIGH_OPTIMAL_SIZE = 4194303 (4 MB minus 1 byte)
统计数据是由 Work Area 大小划分的，因为在 optimal, one-pass 或 multi-pass 大小中运行 Work Area 的性能影响主要取决于 Work Area 的大小。在本例中，有关 Work Area 的统计信息被放置在此 bucket 中，因为3MB位于 optimal 大小范围内。
示例 3 和示例 4 展示了查询此视图的两种方法。
示例 3：查询V$SQL_WORKAREA_HISTOGRAM视图：非空 bucket下面的查询显示了所有非空 bucket 的统计数据：
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在这个示例中，输出显示在1 MB到2 MB的 bucket-551 Work Area 中运行的是 optimal 大小，而16个运行的是 one-pass 大小，没有一个是 multi-pass 大小的。它还显示了1mb以下的所有 Work Area 都能够以 optimal 的大小运行。
示例 4：查询V$SQL_WORKAREA_HISTOGRAM视图：optimal 百分比下面的查询显示了自启动以来，Work Area 在optimal、one-pass 或 multi-pass 大小下执行的百分比。此查询只考虑特定大小的 Work Area，其 optimal 内存需求至少为 64kb：
SELECT optimal_count, ROUND(optimal_count*100/total, 2) optimal_perc,
onepass_count, ROUND(onepass_count*100/total, 2) onepass_perc,
multipass_count, ROUND(multipass_count*100/total, 2) multipass_perc
FROM
(SELECT DECODE(SUM(total_executions), 0, 1, SUM(total_executions)) total,
SUM(optimal_executions) optimal_count,
SUM(onepass_executions) onepass_count,
SUM(multipass_executions) multipass_count
FROM V$SQL_WORKAREA_HISTOGRAM
WHERE low_optimal_size >= 64*1024);该查询的输出可能如下所示：
OPTIMAL_COUNT OPTIMAL_PERC ONEPASS_COUNT ONEPASS_PERC MULTIPASS_COUNT MULTIPASS_PERC
------------- ------------ ------------- ------------ --------------- --------------
2239 81.63 504 18.37 0 0在本例中，输出显示81.63%的 Work Area 能够以 optimal 大小运行。剩下的 Work Area (18.37%)以 one-pass 的大小运行，没有一个工作区域以 multi-pass 的大小运行。
3.5 使用 V$WORKAREA_ACTIVE 视图
V$WORKAREA_ACTIVE 视图显示在数据库实例中活动(或执行)的 Work Area。在此视图中排除小型、主动排序操作(小于64kb)。使用此视图精确地监视所有活动 Work Area 的大小，并确定这些活动 Work Area 是否会溢出到临时区域。
示例 5 显示了此视图的查询。
示例 5 查询 V$WORKAREA_ACTIVE 视图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在本例中，输出显示：
- Session 12 (SID 列)在以one-pass (PASS列)大小运行的 Work Area 中运行一个 hash-join 操作(OPERATION列)。
- PGA 内存管理器期望此 hash-join 操作使用的最大内存量为18044 KB(ESIZE列)。
- Work Area 目前正在使用11406 KB内存(MEM列)。
- 在过去，Work Area 使用了多达 21406 KB的PGA内存(MAX MEM列)。
- Work Area 溢出到120000 KB的临时段(TSIZE列)
3.6 使用 V$SQL_WORKAREA 视图
Oracle数据库为每个加载的游标维护累积的 Work Area 统计数据，其执行计划使用一个或多个工作区域。每当一个 Work Area 被释放时，V$SQL_WORKAREA视图就会被更新为该 Work Area 的执行统计信息。您可以将V$SQL_WORKAREA视图与V$SQL视图连接起来，将 Work Area 与游标关联起来，并将其与V$SQL_PLAN 视图连接起来，以精确确定计划中的哪个操作使用 Work Area 。
示例 6 显示了该视图的三个查询。
示例 6：查询 V$SQL_WORKAREA 视图
下面的查询查找需要最多缓存内存的前10个工作区域：
SELECT *
FROM (SELECT workarea_address, operation_type, policy, estimated_optimal_size
FROM V$SQL_WORKAREA
ORDER BY estimated_optimal_size DESC)
WHERE ROWNUM <= 10;col sql_text format A80 wrap
SELECT sql_text, sum(ONEPASS_EXECUTIONS) onepass_cnt,
sum(MULTIPASSES_EXECUTIONS) mpass_cnt
FROM V$SQL s, V$SQL_WORKAREA wa
WHERE s.address = wa.address
GROUP BY sql_text
HAVING sum(ONEPASS_EXECUTIONS+MULTIPASSES_EXECUTIONS)>0;使用特定游标的 hash 值和地址，以下查询显示游标执行计划，包括有关关联 Work Area 的信息：
col "O/1/M" format a10
col name format a20
SELECT operation, options, object_name name, trunc(bytes/1024/1024) "input(MB)",
TRUNC(last_memory_used/1024) last_mem,
TRUNC(estimated_optimal_size/1024) optimal_mem,
TRUNC(estimated_onepass_size/1024) onepass_mem,
DECODE(optimal_executions, null, null,
optimal_executions||'/'||onepass_executions||'/'||
multipasses_executions) "O/1/M"
FROM V$SQL_PLAN p, V$SQL_WORKAREA w
WHERE p.address=w.address(+)
AND p.hash_value=w.hash_value(+)
AND p.id=w.operation_id(+)
AND p.address='88BB460C'
AND p.hash_value=3738161960; 该查询的输出可能如下所示：
OPERATION OPTIONS NAME input(MB) LAST_MEM OPTIMAL_ME ONEPASS_ME O/1/M
------------ -------- -------- --------- -------- ---------- ---------- ------
SELECT STATE
HASH GROUP BY 4582 8 16 16 16/0/0
HASH JOIN SEMI 4582 5976 5194 2187 16/0/0
TABLE ACCESS FULL ORDERS 51
TABLE ACCESS FUL LINEITEM 1000通过在查询中指定一个模式，您可以从V$SQL视图获取地址和Hash值，如下面的查询所示:
SELECT address, hash_value
FROM V$SQL
WHERE sql_text LIKE '%my_pattern%';4 调优 PGA_AGGREGATE_TARGET
为了帮助您调整PGA_AGGREGATE_TARGET初始化参数的值，Oracle数据库提供了两个PGA性能咨询视图：V$PGA_TARGET_ADVICE 和 V$PGA_TARGET_ADVICE_HISTOGRAM。通过使用这些视图，您不需要使用经验方法来调优PGA_AGGREGATE_TARGET参数的值。相反，您可以使用这些视图来预测PGA_AGGREGATE_TARGET参数的值将如何影响关键的PGA统计数据。4.1 启用自动生成PGA性能咨询视图
Oracle数据库通过记录工作负载历史记录生成 V$PGA_TARGET_ADVICE 和 V$PGA_TARGET_ADVICE_HISTOGRAM 视图，然后对PGA_AGGREGATE_TARGET参数的不同值模拟这段历史。PGA_AGGREGATE_TARGET参数的值来自于其当前值的分数和倍数，以评估可能的更高和更低的值。这些值用于预测，范围从10 MB到最大256 GB。仿真过程发生在后台，不断更新工作负载历史，生成仿真结果。您可以通过查询这些视图来查看结果。启用自动生成PGA性能建议视图：
- 设置PGA_AGGREGATE_TARGET参数以启用自动PGA内存管理。
将此参数设置为0可禁用自动PGA内存管理，不推荐使用。有关设置此参数的信息，请参见“Setting the Initial Value for PGA_AGGREGATE_TARGET”。 - 将 STATISTICS_LEVEL 参数设置为 TYPICAL(默认) 或 ALL。
将此参数设置为 BASIC 禁用PGA性能建议视图的生成，因此不建议。
PGA建议性能视图的内容在实例启动时或在 PGA_AGGREGATE_TARGET 参数的值更改时重置。
V$PGA_TARGET_ADVICE视图预测了如何改变PGA_AGGREGATE_TARGET初始化参数的值将会影响到V$PGASTAT视图中的以下统计数据：
- 缓存命中的百分比
- 超额分配个数
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下图说明如何绘制此查询的结果。
图 1：V$PGA_TARGET_ADVICE的图形表示
该曲线显示了PGA缓存命中率如何随着PGA_AGGREGATE_TARGET参数值的增加而提高。图中的阴影区域代表了超过分配区域，其中ESTD_OVERALLOCATION_COUNT列的值为非零。这个区域表示PGA_AGGREGATE_TARGET参数的值太小，不足以满足PGA的最小内存需求。如果PGA_AGGREGATE_TARGET参数的值设置为超过分配区域内，那么内存管理器将会过度分配内存，而实际使用的PGA内存将超过设置的限制。因此，在该区域中设置PGA_AGGREGATE_TARGET参数值是没有意义的。在这个特殊的例子中，PGA_AGGREGATE_TARGET参数应该设置为至少375 MB。
在超过分配区域的情况下，PGA缓存命中率迅速增加。这是由于 optimal 或 one-pass Work Area 数量的增加和 multi-pass 执行数量的减少。在这个例子中，在某个点，大约有500 MB，曲线上的一个拐点对应于大多数(可能所有的) Work Area 都可以运行在 optimal 的或者至少是 one-pass 的点上。在这一点之外，缓存命中率一直在增加，尽管速度较慢，直到它开始逐渐减少，并且随着 PGA_AGGREGATE_TARGET 参数值的增加，只取得了轻微的改进。在图中，当PGA_AGGREGATE_TARGET达到3 GB时发生这种情况。在这一点上，缓存命中率为83%，仅通过增加1G的PGA内存(增加2%)就能实现轻微的改进。在本例中，3 GB是PGA_AGGREGATE_TARGET参数的 optimal 值。
Note:
尽管PGA缓存命中率的理论最大值为100%，但对 Work Area 的最大大小存在实际限制，即使 PGA_AGGREGATE_TARGET 参数的值进一步增加，也可能会阻止该理论最大值的达到。这应该只发生在大型的DSS系统中，因为最优内存需求很大，并且可能会导致缓存命中率降低到更低的百分比，比如90%。理想情况下，PGA_AGGREGATE_TARGET参数的值应该设置为 optimal 值，或者至少是超出分配区域之外的区域内可能的最大值。根据经验，PGA缓存命中率应该高于60%，因为60%的系统几乎处理了在理想情况下需要处理的字节数的两倍。在本例中，PGA_AGGREGATE_TARGET参数的值应该设置为至少500 MB，并且尽可能接近3 GB。然而，PGA_AGGREGATE_TARGET参数的正确设置取决于可以将多少内存用于PGA组件。通常，添加PGA内存需要减少一些SGA组件的内存，比如 Shared Pool 或 Buffer Cache，因为对数据库实例的整体内存通常是由系统上可用的物理内存数量绑定的。因此，任何增加PGA内存的决定都必须在系统中可用内存的大背景下和各种SGA组件的性能(您可以通过共享池咨询和缓冲区缓存咨询统计信息来监视)中进行。如果不能减少SGA组件的内存，可以考虑向系统添加更多物理内存。
4.3 使用 V$PGA_TARGET_ADVICE_HISTOGRAM 视图
V$PGA_TARGET_ADVICE_HISTOGRAM视图预测如何更改PGA_AGGREGATE_TARGET初始化参数的值将影响V$SQL_WORKAREA_HISTOGRAM视图中的统计信息。使用此视图可以显示关于预测的PGA_AGGREGATE_TARGET值的optimal、one-pass 和 multi-pass Work Area 执行情况的详细信息。V$PGA_TARGET_ADVICE_HISTOGRAM视图与V$SQL_WORKAREA_HISTOGRAM视图相同，另外还有两个列表示用于预测的PGA_AGGREGATE_TARGET值。因此，可以在此视图上使用针对V$SQL_WORKAREA_HISTOGRAM视图执行的任何查询，并使用一个附加谓词来选择PGA_AGGREGATE_TARGET参数的期望值。示例 7 显示了这个视图的一个查询，该查询显示了 PGA_AGGREGATE_TARGET 参数设置为其当前值两倍的值的 V$SQL_WORKAREA_HISTOGRAM 视图的预测内容。
示例 7：查询 V$PGA_TARGET_ADVICE_HISTOGRAM 视图
SELECT low_optimal_size/1024 low_kb, (high_optimal_size+1)/1024 high_kb,
estd_optimal_executions estd_opt_cnt,
estd_onepass_executions estd_onepass_cnt,
estd_multipasses_executions estd_mpass_cnt
FROM V$PGA_TARGET_ADVICE_HISTOGRAM
WHERE pga_target_factor = 2
AND estd_total_executions != 0
ORDER BY 1;此查询的输出可能如下所示：
LOW_KB HIGH_KB ESTD_OPTIMAL_CNT ESTD_ONEPASS_CNT ESTD_MPASS_CNT
------ ------- ---------------- ---------------- --------------
8 16 156107 0 0
16 32 148 0 0
32 64 89 0 0
64 128 13 0 0
128 256 58 0 0
256 512 10 0 0
512 1024 653 0 0
1024 2048 530 0 0
2048 4096 509 0 0
4096 8192 227 0 0
8192 16384 176 0 0
16384 32768 133 16 0
32768 65536 66 103 0
65536 131072 15 47 0
131072 262144 0 48 0
262144 524288 0 23 0在这个示例中，输出显示，将PGA_AGGREGATE_TARGET参数的值增加2倍，将使所有 Work Area 在16 MB以下的情况下，以optimal 的大小执行。
4.4 使用 V$SYSSTAT 和 V$SESSTAT 视图
V$SYSSTAT和V$SESSTAT视图中的统计数据显示了使用optimal、one-pass 和 multi-pass内存大小执行的 Work Area 总数。这些统计数据是自实例或会话启动以来累积的。示例 8 显示了对V$SYSSTAT视图的查询，该视图显示自实例启动以来执行工作区域在这三种大小下的总数和百分比：
示例 8：查询 V$SYSSTAT 视图
SELECT name profile, cnt, DECODE(total, 0, 0, ROUND(cnt*100/total)) percentage
FROM (SELECT name, value cnt, (SUM(value) over ()) total
FROM V$SYSSTAT
WHERE name
LIKE 'workarea exec%');PROFILE CNT PERCENTAGE
----------------------------------- ---------- ----------
workarea executions - optimal 5395 95
workarea executions - onepass 284 5
workarea executions - multipass 0 0在本例中，输出结果显示，5395个 Work Area 执行(或95%)以 optimal 大小执行，284个 Work Area 执行(或5%)以 one-pass 大小执行。
4.5 教程:如何调优PGA_AGGREGATE_TARGET
本教程提供了使用本章讨论的各种视图调优 PGA_AGGREGATE_TARGET 参数值的指导方针。调优PGA_AGGREGATE_TARGET：
- 设置PGA_AGGREGATE_TARGET参数的值以避免内存过度分配。
使用V$PGA_TARGET_ADVICE视图来确保PGA_AGGREGATE_TARGET值不被设置在过度分配区域内，如“Using the V$PGA_TARGET_ADVICE View”所描述的那样。在示例 8 中，PGA_AGGREGATE_TARGET值应该设置为至少375 MB。 - 根据响应时间需求和内存限制，最大化PGA缓存命中率。
使用V$PGA_TARGET_ADVICE视图来确定PGA_AGGREGATE_TARGET参数的 optimal 值，并将其值设置为 optimal 值，或以“Using the V$PGA_TARGET_ADVICE View”描述的最大可能值。
假设在可以分配给PGA的内存中有一个限制X：- 如果极限X高于 optimal 值，则将PGA_AGGREGATE_TARGET参数的值设置为 optimal 值。
在示例 8 中，如果您有10 GB用于PGA，则将PGA_AGGREGATE_TARGET参数的值设置为3 GB，并将剩下的7 GB用于SGA。 - 如果限制X小于最优值，则将PGA_AGGREGATE_TARGET参数的值设置为X。
在示例 8 中，如果您只有2 GB用于PGA，那么将PGA_AGGREGATE_TARGET参数的值设置为2 GB，并接受缓存命中率为75%。
- 如果极限X高于 optimal 值，则将PGA_AGGREGATE_TARGET参数的值设置为 optimal 值。
- 验证PGA_AGGREGATE_TARGET参数的新值将导致所需的 optimal 和 one-pass Work Area 执行数量，并避免任何 multi-pass Work Area 执行。
使用V$PGA_TARGET_ADVICE_HISTOGRAM视图来预测optimal、one-pass 和 multi-pass Work Area 执行的数量，如“Using the V$PGA_TARGET_ADVICE_HISTOGRAM View”所描述的那样。 - 如果需要更多的PGA内存，那么可以通过减少SGA组件的内存或向系统添加更多物理内存来增加PGA内存。
- 在任何时候，确保optimal、one-pass 和 multi-pass Work Area 执行的数量匹配预测，并在必要时调整PGA_AGGREGATE_TARGET参数的值。
使用V$SYSSTAT和V$SESSTAT视图来验证自实例或会话启动以来执行的optimal、one-pass 和 multi-pass 内存大小的 Work Area 总数，如“Using the V$SYSSTAT and V$SESSTAT Views”所描述的那样。
四、通过指定绝对限制对 PGA 进行大小调整
在自动PGA内存管理模式中，Oracle数据库试图通过动态控制分配给 Work Area 的PGA内存量来坚持 PGA_AGGREGATE_TARGET 值。但是，由于以下原因，PGA内存使用率有时可能会超过 PGA_AGGREGATE_TARGET 设置：
- PGA_AGGREGATE_TARGET设置作为一个目标，而不是一个限制。
- PGA_AGGREGATE_TARGET只控制可调内存的分配。
过多的PGA使用会导致高的交换率。当这种情况发生时，系统可能会变得不响应和不稳定。在这种情况下，考虑使用下列任何方法来指定PGA内存使用的绝对限制：
- 使用PGA_AGGREGATE_LIMIT参数设置总PGA内存使用的绝对限制。
参见“Sizing the Program Global Area Using the PGA_AGGREGATE_LIMIT Parameter” - 使用资源管理器过程DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE在特定的使用者组中为每个会话设置一个绝对限制的PGA内存使用。
参见“Sizing the Program Global Area Using the Resource Manager”
1 使用 PGA_AGGREGATE_LIMIT 参数调整 PGA 的大小
PGA_AGGREGATE_LIMIT初始化参数使您能够指定对PGA内存使用的绝对限制。如果超过了PGA_AGGREGATE_LIMIT值，Oracle数据库将中止或终止以以下顺序消耗最不可调PGA内存的会话或进程：- 对消耗最不可调的PGA内存的会话的调用将被中止。
- 如果PGA内存使用仍然超过PGA_AGGREGATE_LIMIT，那么消耗最不可调PGA内存的会话和进程将被终止。
默认情况下，PGA_AGGREGATE_LIMIT参数设置为更大的2 GB, 200%的PGA_AGGREGATE_TARGET值，或3 MB乘以PROCESSES 参数的值。但是，它不会超过物理内存大小的120%减去总SGA大小。默认值被打印到警告日志中。如果系统上的物理内存数量不能确定，则警告日志中会打印警告消息。
设置PGA_AGGREGATE_LIMIT：
- 将PGA_AGGREGATE_LIMIT初始化参数设置为所需的字节数。
值表示为一个数字，后面跟着K(单位为千字节)，M(为兆字节)，或G(为gb)。将值设置为0可禁用PGA内存的硬限制。
- 《Oracle Database Reference》有关PGA_AGGREGATE_LIMIT初始化参数的信息
- 《Oracle Database Reference》有关V$PGASTAT视图的信息
- 《Oracle Database Administrator's Guide》有关Oracle数据库资源管理器和用户组的信息
2 使用资源管理器调整 PGA 的大小
您可以使用Oracle数据库资源管理器的DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE存储过程的SESSION_PGA_LIMIT参数，在特定的使用者组中设置可以分配给每个会话的PGA内存数量的绝对限制。如果会话超过其使用者组设置的PGA内存限制，那么该会话将以ORA-10260错误消息终止。See Also:
- 《Oracle Database Administration Guide》主题
- "Program Global Area (PGA)" 有关限制用户组中每个会话的PGA内存的更多信息
- "Creating Resource Plan Directives" 有关创建资源计划指令的更多信息，请使用DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE 存储过程
- 《Oracle Database PL/SQL Packages and Types Reference》
- DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE 存储过程
扫一扫
7648
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
