理解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。保留所有权利。

    两个参数：

[sql] view plaincopyprint?
  1. 11:40:31 sys@ORCL (^ω^) show parameter workarea_size_policy  
  2.   
  3. NAME_COL_PLUS_S TYPE       VALUE_COL_  
  4. --------------- ---------- ----------   
  5. workarea_size_p string     AUTO  
  6. olicy  
  7.   
  8. 11:40:55 sys@ORCL (^ω^) show parameter pga_aggregate_target  
  9.   
  10. NAME_COL_PLUS_S TYPE       VALUE_COL_  
  11. --------------- ---------- ----------   
  12. pga_aggregate_t big intege 194M  
  13. 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工作区：

[sql] view plaincopyprint?
  1. select sid,to_char(sysdate,'mi:ss') time,  
  2.        round(work_area_size/1048576,1) work_area_size_mb,  
  3.        round(max_mem_used/1048576,1) max_work_area_size_mb,  
  4.        number_passes,  --与临时表空间的I/O数   
  5.        nvl(tempseg_size/148576,0) tempseg_size_mb  
  6.   from v$sql_workarea_active  
  7. 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]
             动态设置如下：

[sql] view plaincopyprint?
  1. 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
                  动态设置，会话级或实例级都可：

[sql] view plaincopyprint?
  1. alter system set statistics_level=typical  
alter system set statistics_level=typical


 

[sql] view plaincopyprint?
  1. 17:13:53 sys@ORCL (^ω^) show parameter statistics_level  
  2.   
  3. NAME                                 TYPE       VALUE  
  4. ------------------------------------ ---------- ------------------------------   
  5. statistics_level                     string     TYPICAL  
17:13:53 sys@ORCL (^ω^) show parameter statistics_level

NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
statistics_level                     string     TYPICAL


           b）确认隐藏参数是否为true

[sql] view plaincopyprint?
  1. 17:04:32 sys@ORCL (^ω^) select a.ksppinm name,b.ksppstvl value,a.ksppdesc description  
  2. 17:04:35   2  from x$ksppi a,x$ksppcv b  
  3. 17:04:35   3  where a.indx = b.indx  and  
  4. 17:04:35   4        a.ksppinm like '%_smm_advice_enabled%'  
  5. 17:04:36   5  /  
  6.   
  7. NAME                 VALUE      DESCRIPTION  
  8. -------------------- ---------- ------------------------------   
  9. _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。

[sql] view plaincopyprint?
  1. 18:38:15 sys@ORCL (^ω^) select x.ksppinm name,  
  2. 18:38:18   2         case  
  3. 18:38:18   3         when x.ksppinm like '%pga%'  
  4. 18:38:18   4         then  
  5. 18:38:18   5           to_number(y.ksppstvl)/1024  
  6. 18:38:18   6         else  
  7. 18:38:18   7           to_number(y.ksppstvl)  
  8. 18:38:18   8         end as value,  
  9. 18:38:18   9         x.ksppdesc description  
  10. 18:38:18  10    from x$ksppi x,x$ksppcv y  
  11. 18:38:18  11   where x.inst_id=userenv('Instance') and  
  12. 18:38:18  12         y.inst_id=userenv('Instance') and  
  13. 18:38:18  13         x.indx=y.indx and  
  14. 18:38:18  14         x.ksppinm in ('pga_aggregate_target','_pga_max_size',  
  15. 18:38:18  15                       '_smm_max_size','_smm_px_max_size')  
  16. 18:38:19  16  /  
  17.   
  18. NAME                 VALUE DESCRIPTION  
  19. --------------- ---------- ----------------------------------------   
  20. pga_aggregate_t     198656 Target size for the aggregate PGA memory  
  21. arget                       consumed by the instance  
  22.   
  23. _pga_max_size       204800 Maximum size of the PGA memory for one p  
  24.                            rocess  
  25.   
  26. _smm_max_size        39731 maximum work area size in auto mode (ser  
  27.                            ial)  
  28.   
  29. _smm_px_max_siz      99328 maximum work area size in auto mode (glo  
  30. 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%

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/net2012/p/2886415.html