理解PGA（2）pga_aggregate_target详解

注：
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

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

调整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;

2）打开pga建议功能
打开此功能前有两个参数需要确认：
a）确认参数statistics_level是否为typical或者all
在参数文件里面的设置方式：
statistics_level=all | typical | basic
动态设置，会话级或实例级都可：

alter system set statistics_level=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  

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)

注释：
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%