注：
1）pga_aggregate_target以下简称PAT
2）我的环境：
11:42:10 sys@ORCL (^ω^) select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。
两个参数：
- 11:40:31 sys@ORCL (^ω^) show parameter workarea_size_policy
- NAME_COL_PLUS_S TYPE VALUE_COL_
- --------------- ---------- ----------
- workarea_size_p string AUTO
- olicy
- 11:40:55 sys@ORCL (^ω^) show parameter pga_aggregate_target
- NAME_COL_PLUS_S TYPE VALUE_COL_
- --------------- ---------- ----------
- pga_aggregate_t big intege 194M
- arget r
11:40:31 sys@ORCL (^ω^) show parameter workarea_size_policy NAME_COL_PLUS_S TYPE VALUE_COL_ --------------- ---------- ---------- workarea_size_p string AUTO olicy 11:40:55 sys@ORCL (^ω^) show parameter pga_aggregate_target NAME_COL_PLUS_S TYPE VALUE_COL_ --------------- ---------- ---------- pga_aggregate_t big intege 194M arget r
workarea_size_policy设置为auto，pga_aggregate_target有一个非0值，就会引入自动pga内存管理。
PGA内存分配涉及很多方面，但其中只有工作区（工作区：sort_area和hash_area）在数据库实例的控制之下。PGA内存是按需分配。不管是否设置了PAT，整个内存对单个会话是透明的。对于一般的作业，每个会话可用的PGA内存公式为MIN(pga_aggregate_target*5%,_pga_max_size/2)。其中，pga_aggregate_target∈[10M,4G]。
PAT只是一个目标和请求，而不是明确地指定要分配多少空间。这意味着，在高负荷下实际消耗的内存量是连续的或者至少间歇性地比目标值高。对于需要大内存空间来执行sql语句，oracle会尽力将其作业保持在optimal size类型的sql工作区。也可以说，PAT是控制私有sql区中sql工作区的大小。下列语句可以检测sql工作区：
- select sid,to_char(sysdate,'mi:ss') time,
- round(work_area_size/1048576,1) work_area_size_mb,
- round(max_mem_used/1048576,1) max_work_area_size_mb,
- number_passes, --与临时表空间的I/O数
- nvl(tempseg_size/148576,0) tempseg_size_mb
- from v$sql_workarea_active
- order by sid
select sid,to_char(sysdate,'mi:ss') time,
round(work_area_size/1048576,1) work_area_size_mb,
round(max_mem_used/1048576,1) max_work_area_size_mb,
number_passes, --与临时表空间的I/O数
nvl(tempseg_size/148576,0) tempseg_size_mb
from v$sql_workarea_active
order by sid
调整PAT的步骤:
1）按照oracle文档简单估算PAT的大小：
于OLTP型：PAT=(物理内存*80%）*20%
于OLAP型：PAT=(物理内存*80%）*50%
PAT的设置方式：
pga_aggregate_target=int [k | m | g]
动态设置如下：
- alter system set pga_aggregate_target=**G;
alter system set pga_aggregate_target=**G;
2）打开pga建议功能
打开此功能前有两个参数需要确认：
a）确认参数statistics_level是否为typical或者all
在参数文件里面的设置方式：
statistics_level=all | typical | basic
动态设置，会话级或实例级都可：
- alter system set statistics_level=typical
alter system set statistics_level=typical
- 17:13:53 sys@ORCL (^ω^) show parameter statistics_level
- NAME TYPE VALUE
- ------------------------------------ ---------- ------------------------------
- statistics_level string TYPICAL
17:13:53 sys@ORCL (^ω^) show parameter statistics_level NAME TYPE VALUE ------------------------------------ ---------- ------------------------------ statistics_level string TYPICAL
b）确认隐藏参数是否为true
- 17:04:32 sys@ORCL (^ω^) select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
- 17:04:35 2 from x$ksppi a,x$ksppcv b
- 17:04:35 3 where a.indx = b.indx and
- 17:04:35 4 a.ksppinm like '%_smm_advice_enabled%'
- 17:04:36 5 /
- NAME VALUE DESCRIPTION
- -------------------- ---------- ------------------------------
- _smm_advice_enabled TRUE if TRUE, enable v$pga_advice
17:04:32 sys@ORCL (^ω^) select a.ksppinm name,b.ksppstvl value,a.ksppdesc description 17:04:35 2 from x$ksppi a,x$ksppcv b 17:04:35 3 where a.indx = b.indx and 17:04:35 4 a.ksppinm like '%_smm_advice_enabled%' 17:04:36 5 / NAME VALUE DESCRIPTION -------------------- ---------- ------------------------------ _smm_advice_enabled TRUE if TRUE, enable v$pga_advice
3）借助v$pgastat和v$pga_target_advice来收集统计数据
其实，PAT的值会投射到三个隐藏参数，我们设置了PAT，在oracle内部，是通过三个隐藏参数来反应我们的PAT。
- 18:38:15 sys@ORCL (^ω^) select x.ksppinm name,
- 18:38:18 2 case
- 18:38:18 3 when x.ksppinm like '%pga%'
- 18:38:18 4 then
- 18:38:18 5 to_number(y.ksppstvl)/1024
- 18:38:18 6 else
- 18:38:18 7 to_number(y.ksppstvl)
- 18:38:18 8 end as value,
- 18:38:18 9 x.ksppdesc description
- 18:38:18 10 from x$ksppi x,x$ksppcv y
- 18:38:18 11 where x.inst_id=userenv('Instance') and
- 18:38:18 12 y.inst_id=userenv('Instance') and
- 18:38:18 13 x.indx=y.indx and
- 18:38:18 14 x.ksppinm in ('pga_aggregate_target','_pga_max_size',
- 18:38:18 15 '_smm_max_size','_smm_px_max_size')
- 18:38:19 16 /
- NAME VALUE DESCRIPTION
- --------------- ---------- ----------------------------------------
- pga_aggregate_t 198656 Target size for the aggregate PGA memory
- arget consumed by the instance
- _pga_max_size 204800 Maximum size of the PGA memory for one p
- rocess
- _smm_max_size 39731 maximum work area size in auto mode (ser
- ial)
- _smm_px_max_siz 99328 maximum work area size in auto mode (glo
- e bal)
18:38:15 sys@ORCL (^ω^) select x.ksppinm name,
18:38:18 2 case
18:38:18 3 when x.ksppinm like '%pga%'
18:38:18 4 then
18:38:18 5 to_number(y.ksppstvl)/1024
18:38:18 6 else
18:38:18 7 to_number(y.ksppstvl)
18:38:18 8 end as value,
18:38:18 9 x.ksppdesc description
18:38:18 10 from x$ksppi x,x$ksppcv y
18:38:18 11 where x.inst_id=userenv('Instance') and
18:38:18 12 y.inst_id=userenv('Instance') and
18:38:18 13 x.indx=y.indx and
18:38:18 14 x.ksppinm in ('pga_aggregate_target','_pga_max_size',
18:38:18 15 '_smm_max_size','_smm_px_max_size')
18:38:19 16 /
NAME VALUE DESCRIPTION
--------------- ---------- ----------------------------------------
pga_aggregate_t 198656 Target size for the aggregate PGA memory
arget consumed by the instance
_pga_max_size 204800 Maximum size of the PGA memory for one p
rocess
_smm_max_size 39731 maximum work area size in auto mode (ser
ial)
_smm_px_max_siz 99328 maximum work area size in auto mode (glo
e bal)
注释：
1）_pga_max_size是个动态参数，而_smm_max_size和_smm_px_max_size是静态的，若是到参数文件将其修改，则无论我们对PAT作什么修改，他俩木然以对。
2）增加_pga_max_size,sql工作区有效大小也可以增加，而不用扩展整个实例的可用内存
3）_smm_max_size限制了单个进程下一个单独工作区的最大大小
_pga_max_size限制了单个进程下所有工作区的最大大小
_smm_px_max_size始终设置为PAT的50%