PGA就是Oracle服务进程的私有内存区域，包含数据和控制信息。

使用PGA的SQL操作：
1 排序相关的操作：Order By, Group By, Rollup, windows function, and Distinct
2 Hash-Join
3 Bitmap merge
4 Bitmap create
5 Bulk load

Work Area(工作区)

Work Area(工作区)有Sort Area，Hash Area。如果工作区足够大并能容纳输入的数据和SQL操
作分配的辅助的内存。这就是工作区的optimal size。如果工作区比optimal size小，那么输
入的数据需要一个额外的传输。这就是工作区的one-pass size。如果工作区比one-pass size
的阀值还小，输入的数据需要多次额外的传输，这就是multiple-pass size。
最好的情况就是所有的操作都在optimal size范围内运行。或者90%的SQL操作在opimal size
范围中运行，10%是在one-pass size范围内运行。而multiple-pass size应该被避免。

自动PGA内存管理

自动PGA内存管理简化并提高PGA内存分配方式。Oracle可以根据需要自动调整各个工作区的PGA
内存。

PGA_AGGREGATE_TARGET的最佳值

1 首先设置PGA_AGGREGATE_TARGET为默认值：
- 如果是oltp系统 PGA_AGGREGATE_TARGET = ( * 80%) * 20%
- 如果是dss系统 PGA_AGGREGATE_TARGET = ( * 80%) * 50%

2 根据运行情况来调整参数PGA_AGGREGATE_TARGET。

视图v$pgastat提供了实例层次的PGA内存使用情况。

点击(此处)折叠或打开

SELECT * FROM V$PGASTAT;
NAME VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter 1073741824 bytes
aggregate PGA auto target 922438656 bytes
global memory bound 107366400 bytes
total PGA inuse 48802816 bytes
total PGA allocated 75149312 bytes
maximum PGA allocated 188080128 bytes
total freeable PGA memory 17956864 bytes
process count 31
max processes count 37
PGA memory freed back to OS 6466633728 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 12309504 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 273408 bytes
over allocation count 0
bytes processed 5109238784 bytes
extra bytes read/written 0 bytes
cache hit percentage 100 percent
recompute count (total) 76201

aggregate PGA target parameter: PGA_AGGREGATE_TARGET参数大小。
aggregate PGA auto target: work area大小，这部分是可以自动调节的。这是相对于PGA的
其他部分比如PL/SQL和Java memory。
global memory bound：一个进程使用的Work Area的最大内存。在9i和10g中，这个值是
max(%5 * PGA_AGGREGATE_TARGET, 100m)。在11g中，当PGA_AGGREGATE_TARGET>=1g时，为
20%*PGA_AGGREGATE_TARGET。当PGA_AGGREGATE_TARGET<1g时，为10%*PGA_AGGREGATE_TARGET。
total PGA used for auto workarea: 实际使用的work area的PGA内存
maximum PGA used for auto workareas: 系统启动以来达到的最大使用内存。
total PGA in used: 总共使用的PGA内存。更详细的信息可以从v$process.PGA_USED_MEM中获取。
over allocation count: 超额分配PGA内存次数。当PGA_AGGREGATE_TARGET太小不能容纳
work area和不可调优的部分时。额外的PGA内存需要分配。
cache hit percentage: 计算公式:
total bytes processed * 100
PGA Cache Hit Ratio = ------------------------------------------------------
(total bytes processed + total extra bytes read/written)

- V$SQL_WORKAREA_HISTOGRAM
这个视图列出了多少个work area分别在optimal size, one-pass size, multiple-size下执
行的。

点击(此处)折叠或打开

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
---------- ---------- ------------------ ------------------ ----------------------
2 4 706242 0 0
64 128 416 0 0
128 256 150 0 0
256 512 270 0 0
512 1024 3208 0 0
1024 2048 168 0 0
2048 4096 31 0 0
4096 8192 140 0 0

这里列出了所有的work area在8M以下运行在optimal size。

- V$SQL_WORKAREA_ACTIVE
这个视图列出了正在执行的work area。其中work area>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
---------- -------------------- ---------- ---------- ---------- ---------- ----------
18 SORT (v2) 104549 103867 104878 1 564224

正在使用103,867k的内存。过去使用的最大内存为104,878。work area在one-pass模式下运行。有
564,224K使用了临时段。ESIZE是指期望的最大的PGA Work Area的内存。

点击(此处)折叠或打开

SID OPERATION ESIZE MEM MAX MEM PASS TSIZE
---------- -------------------- ---------- ---------- ---------- ---------- ----------
139 SORT (v2) 13392 7445 52462 1 1033216

正在使用7,445k的内存。过去使用的最大内存为52,462。work area在one-pass模式下运行。有
1,033,216K使用了临时段。ESIZE是指期望的最大的PGA Work Area的内存。

3- 看到实际的PGA运行情况，就可以开始调整参数PGA_AGGREGATE_TARGET.

- V$PGA_TARGET_ADVICE
- V$PGA_TARGET_ADVICE_HISTOGRAM

如果修改PGA_AGGREGATE_TARGET参数，关键PGA统计信息的变化。

V$PGA_TARGET_ADVICE view

V$PGA_TARGET_ADVICE view 预测如果修改PGA_AGGREGATE_TARGET参数，缓存的命中率和超额分配计数
的影响。

点击(此处)折叠或打开

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
---------- -------------- --------------------
128 61 1
256 86 0
512 86 0
768 86 0
1024 86 0
1229 86 0
1434 86 0
1638 86 0
1843 86 0
2048 86 0
3072 86 0
4096 86 0
6144 86 0
8192 86 0

从上面的统计信息，当PGA_AGGREGATE_TARGET参数为256m，ESTD_OVERALLOC_COUNT为0，
CACHE_HIT_PERCENT为86%。

ITPub博客

PGA内存管理

最新文章