会话专有连接模式下，是私有区域，服务器进程和用户进程一一对应，用户进程单独使用PGA
共享服务器会话模式下，一个服务器进程对应多个用户进程，PGA是多个用户进程共享使用

PGA主要用于大规模的排序，PGA的优化将这些大规模的排序操作放在PGA中进行，避免有swap交换分区

9i以前通过sort_area_size来设PGA
如果将sort_area_size加得过大（也就是说每一个server进程的排序区都加大）可能将系统的内存耗光，设置过小又会不
能满足排序的需要

排序区的值：（前提WORKAREA_SIZE_POLICY设为auto）
pga_aggregate_target定义了，所有服务器进程PGA内存的总和；会根据排序区的需要进行自动调整
即：启动PGA排序区的自动管理 ，必须设置参数workarea_size_policy和pga_aggregate_target的值

SQL> select name,value,isdefault
  2  from v$parameter
  3  where name in ('pga_aggregate_target','workarea_size_policy');

NAME                 VALUE                          ISDEFAULT
-------------------- ------------------------------ ---------
pga_aggregate_target 16777216                       FALSE——是需要设置的，不是系统的默认参数
workarea_size_policy AUTO                           TRUE——是默认参数，－如果是MANAUL就要手工设sort_area_size

注意：
pga_aggregate_target的值与SGA之和要小于等于实例可以获得的物理内存
否则oracle一些操作走磁盘的虚拟内存，性能急剧下降；

如何诊断pga_aggregate_target 的设置是否足够，通过 v$pgastat获取在内存排序的百分比
在系统运行了一段时间后，或进行了大规模排序之后再查才准

SQL> select * from v$pgastat where name = 'cache hit percentage';

NAME                                                    VALUE       UNIT
-------------------------------------------------- ---------- ------------
cache hit percentage                               100  percent

SQL> select round(PGA_TARGET_FOR_ESTIMATE/1024/1024) as target_mb,
  2  ESTD_PGA_CACHE_HIT_PERCENTAGE,ESTD_OVERALLOC_COUNT
  3  from v$pga_target_advice
  4  order by target_mb;

 TARGET_MB ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
---------- ----------------------------- --------------------
        12                           100                    1
        16                           100                    1
        19                           100                    1
        22                           100                    1
        26                           100                    1
        29                           100                    1
        32                           100                    1
        48                           100                    1
        64                           100                    0
        96                           100                    0
       128                           100                    0

11 rows selected.
实验：
SQL> create table test1 as select * from dba_objects;

Table created.

SQL> insert into test1 select * from dba_objects;－－进行多次的插入

SQL> select count(*) from test1 group by OBJECT_NAME;——执行大规模的排序
SQL>  select * from v$pgastat where name = 'cache hit percentage';

NAME                                                    VALUE UNIT
-------------------------------------------------- ---------- ------------
cache hit percentage                                      100 percent

SQL> select round(PGA_TARGET_FOR_ESTIMATE/1024/1024) as target_mb,
  2  ESTD_PGA_CACHE_HIT_PERCENTAGE,ESTD_OVERALLOC_COUNT
  3  from v$pga_target_advice
  4  order by target_mb;

 TARGET_MB ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
---------- ----------------------------- --------------------
        12                            90                    3——有所变化，设置12M，PGA排序占了90％，有10％走磁盘临时表空间排序
        16                           100                    3
        19                           100                    3
        22                           100                    3
        26                           100                    3
        29                           100                    3
        32                           100                    3
        48                           100                    1
        64                           100                    0
        96                           100                    0
       128                           100                    0

11 rows selected.