PGA自动管理的介绍
PGA–Process Global Area,常被称为Program Global Area。
In Oracle8i，DBA需要修改如下参数来调整PGA：
SORT_AREA_SIZE, HASH_AREA_SIZE,
BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE, etc.
Oracle9i开始，使用PGA自动管理，只需要设置 PGA_AGGREGATE_TARGET. 参数。
PGA_AGGREGATE_TARGET的值在实例运行期间可以动态改变。
SQL执行内存自动管理功能是通过设置 WORKAREA_SIZE_POLICY 为AUTO并指定PGA_AGGREGATE_TARGET的值来实现的。这两个参数可以动态设置。
从9I R2开始，可以通过PGA Advisory 来设置更合理的PGA大小。
PGA_AGGREGATE_TARGET 参数并不是设置一个hard limit on pga size，像PL/SQL的工作区它影响不到。
它分为work area和work area之外的部分。
所谓work area，就是session要执行SQL，需要在数据库内部分配的，为了存放中间结果的内存。
比如sort area，为了排序用的内存，比如hash area，为了hash join用的内存，这一部分直接和SQL执行相关，影响SQL执行的效率，比如更大hash area会让hash join更快。
pga_aggregate_target实际上只限制work area的大小。当work area达到pga_aggregate_target的限制，则会产生4030错误。
隐含参数_pga_max_size只是限制单个进程使用的work area大小，也是在pga_aggregate_target的限制之内的更小限制。
而work area之外的内存，不被pga_aggregate_target和_pga_max_size所限制。
所以你经常会看到PGA的大小超过了pga_aggregate_target。这通常是因为PL/SQL中的变量和数组中装入了巨大的数据造成的。
通常的例子是bulk collect，imp/exp，sql loader等工具也会产生类似的问题。
在9iR2, PGA_AGGREGATE_TARGET 只对dedicated server起作用。
在10G，PGA_AGGREGATE_TARGET 对dedicated /ASHARED server都起伤作用。
在11G使用AMM时 PGA_AGGREGATE_TARGET的设置会使AMM设置PGA不会低于此值。
the size of a process没有数据库参数限制，只有通过OS的kernel limits or user shell limits。
参考ORACLE MOS文档：
Automatic PGA Memory Management (文档 ID 223730.1)
FAQ: ORA-4030 [Video] (文档 ID 399497.1)
Keep in mind that the PGA_AGGREGATE_TARGET does not limit the amount of PGA memory usage. It is only a target and is used to dynamically size the process work areas. It does not affect other areas of the PGA that are allowed to grow beyond this limit.
There are certain areas of PGA that cannot be controlled by initialization parameters. Such areas include PL/SQL memory collections such as PL/SQL tables and VARRAYs, and local PL/SQL variables. Depending on the programming code and amount of data being handled these areas can grow very large (up to 20G internal limit on 10) and can consume large amounts of memory. This memory growth can be controlled by good programming practices. As an example, use LIMIT clause with BULK COLLECT.
PGA相关查询脚步
##查AWR快照间隔中PGA的增长情况
SQL> set linesize 180
SQL> SELECT SNAP_ID,INSTANCE_NUMBER,name,value FROM DBA_HIST_PGASTAT where name in(‘max processes count’,’total PGA allocated’) and snap_id between 24943 and 24946;
SNAP_ID INSTANCE_NUMBER NAME VALUE
———- ————— —————————— ————————
24943 1 max processes count 126
24943 1 total PGA allocated 608,856,064
24944 1 max processes count 126
24944 1 total PGA allocated 630,258,688
24945 1 max processes count 126
24945 1 total PGA allocated 678,053,888
24946 1 max processes count 126
24946 1 total PGA allocated 618,394,624
8 rows selected.
SELECT a.SNAP_ID,a.INSTANCE_NUMBER,a.name,a.value,b.BEGIN_INTERVAL_TIME,b.END_INTERVAL_TIME FROM DBA_HIST_PGASTAT a,DBA_HIST_SNAPSHOT b
where a.name in(‘max processes count’,’total PGA allocated’) and a.snap_id=b.snap_id and b.BEGIN_INTERVAL_TIME>sysdate-1;
#####################################################3
2.PGA大小设置与调整
1.PGA设置的规则：
– For OLTP systems PGA_AGGREGATE_TARGET = (physical memory * 80%) * 20% 物理内存*0.16
– For DSS systems PGA_AGGREGATE_TARGET = (physical memory * 80%) * 50% 物理内存*0.4
2.查看PGA效率–
select
trunc (
(sum(case when name like ‘workarea executions – optimal’
then value else 0 end) *100) /
(
sum(case when name like ‘workarea executions – optimal’
then value else 0 end) +
sum(case when name like ‘workarea executions – one pass’
then value else 0 end) +
sum(case when name like ‘workarea executions – multipass’
then value else 0 end)
)
) optimal_percent
from v$sysstat
where name like ‘workarea executions – %’
/
workarea memory allocated 总的PGA KB
workarea executions – optimal 最佳值
workarea executions – onepass 单次不可避免的排序写入磁盘
workarea executions – multipass 多次排序写入磁盘–应该避免
3.实例级别的PGA使用统计信息
SELECT * FROM V$PGASTAT;
ORACLE 9IR2,10G,11G ，PGA命中率算法如下：
total bytes processed * 100
PGA Cache Hit Ratio = ——————————————————
(total bytes processed + total extra bytes read/written)
4.查看PGA中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;
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(MULTIPASSES_EXECUTIONS) multipass_count
FROM v$sql_workarea_histogram
WHERE low_optimal_size > 64*1024); —- for 64 K optimal size
5.查看PGA中有哪些操作–是否写入临时段
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;
6.pga_target_advice视图的使用
– PGA_AGGREGATE_TARGET
– STATISTICS_LEVEL. Set this to TYPICAL (the default) or ALL;
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;
#######################################
1.
TOP 10工作区
Finding top ten work areas requiring the most cache memory:
select *
from
(select workarea_address, operation_type, policy, estimated_optimal_size
from v$sql_workarea
order by estimated_optimal_size DESC)
where ROWNUM <=10;
2. 找出使用内存最多的工作区
Finding the percentage of work areas using maximum memory:
select operation_type, total_executions * 100 / optimal_executions “%cache”
From v$sql_workarea
Where policy=’AUTO’
And optimal_executions > 0
Order By operation_type;
3. 找出当前系统中分配内存最多的工作区
Finding the top ten biggest work areas currently allocated in the system:
select c.sql_text, w.operation_type, top_ten.wasize
From (Select *
From (Select workarea_address, actual_mem_used wasize
from v$sql_workarea_active
Order by actual_mem_used)
Where ROWNUM <=10) top_ten,
v$sql_workarea w,
v$sql c
Where w.workarea_address=top_ten.workarea_address
And c.address=w.address
And c.child_number = w.child_number
And c.hash_value=w.hash_value;
4. Finding the percentage of memory that is over and under allocated:
select total_used,
under*100/(total_used+1) percent_under_use,
over*100/(total_used+1) percent_over_used
From
( Select
sum(case when expected_size > actual_mem_used
then actual_mem_used else 0 end) under,
sum(case when expected_size < actual_mem_used
then actual_mem_used else 0 end) over,
sum(actual_mem_used) total_used
From v$sql_workarea_active
Where policy=’AUTO’) usage;
5.List largest process:
/* Do NOT eliminate all background process because certain background processes do need to be monitored at times */
SELECT pid, spid, substr(username,1,20) “USER”, program, pga_used_mem, pga_alloc_mem, pga_freeable_mem, pga_max_mem
FROM v$process
WHERE pga_alloc_mem = (SELECT max(pga_alloc_mem)
FROM v$process
WHERE program NOT LIKE ‘%LGWR%’);
