PGA初探

认识PGA
PGA是特定于进程或者线程的一段内存，换句话说，它是一个操作系统进程或线程专用的内存，不允许系统的其他它进程（线程）访问。PGA一般通过C语言的运行时调用malloc（）或memmap（）来分配，而且可以在运行时动态扩大和缩小，PGA肯定不会在SGA中分配。PGA是由进程或者线程在本地分配的。PGA中的P代表process或者program，是不共享的。PGA是通过内存heap来管理的，它包含专用和共享服务器进程相关的会话信息。
实际上，对你来说，UGA就是你的会话的状态。会话总能访问这部分内存。UGA的位置完全取决于怎么连接oracle。如果是共享模式连接oracle。UGA肯定存储在每个共享服务器能够访问的内存结构中，也即是SGA中。如果使用专用模式连接oracle，则不再需要大家都能访问你的会话状态，UGA几乎成了PGA的同义词，实际上UGA就包含在PGA中。
1. PGA结构
我们先来看看PGA的结构，如下图（来自Oracle联机文档）：
 

PGA包含工作区，会话区，和私有sql区，而sql work areas分为sort area、hash area、bitmap merge area。session memory主要包含一些登陆的信息。Private sql area分为persisten area和runtime area。
1.1. Sql work areas
Work areas是PGA中分配的，用于一些基于内存的操作，例如排序，哈希和位图合并，如果基于内存的操作（sort，hash，merge，group by）的数据超过了work area的空间，oracle自动会将数据分为若干个小的数据片，然后在work area中分别处理每个数据片，其他暂时还没有处理的数据片则存放在temp表空间上，以待稍后处理。当开启PGA自动管理时，数据库可以自动的调整work area的大小，当然了你也可以手动的管理PGA，这就需要你手工的设置一些参数了。一般而言，work area越大，需要消耗大量内存的操作性能越好，如果使用了PGA的自动管理，则oracle会根据要处理的数据量来动态的调整work area的大小，来尽量满足数据所需的内存，尽可能地保证所有的操作在内存中完成。相反如果没有使用PGA自动管理，work area的相关参数的大小相对于输入的数据来说比较小的话，oracle不得不把一些数据缓存在磁盘上，这可能极大地增大了响应时间。

1.2. Private sql area
Private sql area保存了一些解析语句的信息和会话相关的信息，当一个服务器进程执行sql或者pl/sql代码，服务器进程使用private sql area存储绑定变量、查询执行状态信息等信息。当使用shared连接模式，private sql area是在UGA中的，而UGA是在SGA（large pool）中分配的。多个不同会话的private sql area可以指向同一个执行计划。例如20号会话执行了select * from employees，10号会话也执行了同样的语句，这两个会话可以共享相同的执行计划。但是每个private sql area可能包含不同的变量和数据。游标是特定的private sql area的名字或者句柄。你可以认为游标就是客户端指向服务端的一个指针，因为游标和private sql area和游标是紧密联系的，它们有时是可以互换的。
 

Private sql area可以划分为下面几个部分：
The run-time area
  这个区域包含了查询执行的状态信息，例如 run-time area记录了全表扫描在当前时间返回的行数。分配Run-time area是oracle数据库处理执行请求的第一步。对于DML语句，sql语句关闭后run-time area就释放了。
The Persisten area
  这个区域包含了绑定变量。当sql 语句在执行的时候将绑定变量的值赋给语句。只有在游标关闭的时候persisten area才会释放。客户端进程负责管理private sql area。Private sql area的分配和重分配在很大程度上是依赖应用的，但是客户端可以分配的private sql area是受初始化参数open_cursors限制的。

2.PGA 的管理

PGA的管理有两种方式，一种是自动管理，一种是手动管理，下面我们就这两种管理方式展开讨论。workarea_size_policy就是配置PGA是手工管理还是自动管理的参数，如果WORKAREA_SIZE_POLICY=AUTO则PGA就是自动管理，WORKAREA_SIZE_POLICY=MANUL则PGA就是手工管理的。其实我们无论使用自动管理还是手工管理都是针对work area区域的。

2.1.PGA的手工管理

WORKAREA_SIZE_POLICY=MANUL则PGA就是手工管理，这个时候我们就需要手工的配置像SORT_AREA_SIZE，HASH_AREA_SIZE，BITMAP_MERGE_AREA_SIZE,SORT_AREA_RETAINED_SIZE这些参数了，我们每个session能使用的PGA（work area）大小就由这些参数来决定。下面通过实验来验证。

SQL> show parameter workarea_size_policy


NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy     stringAUTO
SQL> alter system set workarea_size_policy=manual scope=spfile;（注意：在设置PGA的手工管理的时候，我们要先把Oracle自动内存管理关闭）
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 3290345472 bytes
Fixed Size    2232840 bytes
Variable Size 1879051768 bytes
Database Buffers 1392508928 bytes
Redo Buffers   16551936 bytes
Database mounted.
Database opened.
SQL> select count(*) from tt;
  COUNT(*)
----------
    603288
SQL> select count(*) from tt1;
  COUNT(*)
----------
    603288
SQL> show parameter sort_area_size
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_size     integer65536
SQL> show parameter hash_area_size
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
hash_area_size     integer131072
SQL> show parameter bitmap_merge_area_size
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size     integer1048576
SQL> show parameter sort_area_retained_size
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_retained_size     integer0
SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
   32
SQL> set termout off
SQL> select * from tt1,tt order by 1,2,3,4,5,6,7;
在另外一个session中监测32号session：
SQL> select n.name,s.value from v$sesstat s,v$statname n where s.statistic#=n.statistic# and s.sid=32 and n.name like '%sort%';
NAME    VALUE
------------------------------ ----------
sorts (memory) 0
sorts (disk) 1
sorts (rows)   603288
SQL> select sid,pga_used_mem/1024/1024 used_mem,pga_alloc_mem/1024/1024 allocate_mem,pga_freeable_mem/1024/1024 free_mem,pga_max_mem/1024/1024 max_mem from v$process p,v$session s where sid=32 and p.addr=s.paddr;
       SID   USED_MEM ALLOCATE_MEM   FREE_MEM MAX_MEM
---------- ---------- ------------ ---------- ----------
32 2.99741364 5.54946136     1.1875 5.54946136

在32号会话一直执行排序操作的sql语句时，我多次执行上面的sql语句发现max_mem在5.5M左右后保持不变了，这说明当使用manual方式管理PGA（workarea）时，它可用的大小就是由上面我们提到那些参数决定的。

2.1.1 手工管理PGA的调优

当使用手工管理的PGA时，优化PGA我们主要使用两个动态性能视图，v$pga_target_advice和v$pga_target_advice_histogram,具体的对这两个动态性能试图的字段介绍我们可以查看Oracle的联机文档。

2.2.PGA的自动管理

当workarea_size_policy=auto时，PGA是自动管理的，这个时候我们不需要手动的设置像sort_area_size这些参数，而只是简单的设置一个pga_aggregate_target参数，具体的PGA内部各个组件的大小由Oracle自身来决定，理论上pga_aggregate_target为所有会话使用pga的一个阀值，但是当所有会话需要的PGA总值超过这个阀值时，Oracle并不会停止工作，而是会超过这个阀值。单个会话可以使用的最大的PGA大约为（默认）pga_aggregate_target*0.2，当这个session中有多个并行的process的时候，那么每个并行的进程可以使用的PGA大约为pga_aggregate_target*0.2/n，其中n为并行的进程数。我来解释一下上一句话中括号里“默认”的含义，其实单个会话可以使用的最大的PGA是由Oracle隐含参数_pga_max_size决定的，而这个参数默认的值就是PGA_AGGREGATE_TARGET*0.2。另外一个隐含参数_smm_max_size是决定在使用自动方式管理PGA的时候，最大的workarea的size，下面我们使用实验来验证：

SQL> show parameter workarea_size_policy
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy     stringAUTO
SQL> show parameter pga_agg
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target     big integer 1G

SQL> select n.ksppinm,v.ksppstvl,n.ksppdesc from x$ksppi n,x$ksppcv v where n.indx=v.indx and n.ksppinm like '_smm_max_size%';
KSPPINM     KSPPSTVLKSPPDESC
-------------------- ---------- ------------------------------------------------------------
_smm_max_size     104850maximum work area size in auto mode (serial)
SQL> select n.ksppinm,v.ksppstvl,n.ksppdesc from x$ksppi n,x$ksppcv v where n.indx=v.indx and n.ksppinm like '_pga_max_size%';
KSPPINM     KSPPSTVLKSPPDESC
-------------------- ---------- ------------------------------------------------------------
_pga_max_size     214732800Maximum size of the PGA memory for one process

SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
   32

在32号会话中我们执行下面的sql：（其中表tt,tt1是我构造的两个表，表中各有进60万条数据）

SQL> select * from tt,tt1 order by 1,2,3;

我们在另外一个会话中监测32号会话，执行下面的语句来查看pga的使用情况：

SQL> select sid,pga_used_mem/1024/1024 used_mem,pga_alloc_mem/1024/1024 allocate_mem,pga_freeable_mem/1024/1024 free_mem,pga_max_mem/1024/1024 max_mem from v$process p,v$session s where sid=34 and p.addr=s.paddr;

       SID   USED_MEM ALLOCATE_MEM   FREE_MEM MAX_MEM
---------- ---------- ------------ ---------- ----------
34 188.844437 190.299461     1.1875 190.299461

可以看出最大的使用最大的pga为190M，基本上是等于隐含参数_pag_max_size的

接下来我们修改_pga_max_size的值，再来观察pga的使用情况

SQL> alter system set "_pga_max_size"=500M scope=spfile;


System altered.


SQL> startup force
ORACLE instance started.


Total System Global Area 2137886720 bytes
Fixed Size    2230072 bytes
Variable Size  503318728 bytes
Database Buffers 1627389952 bytes
Redo Buffers    4947968 bytes
Database mounted.
Database opened.
SQL> select userenv('sid') from dual;


USERENV('SID')
--------------
    1


SQL> select * from tt,tt1 order by 1,2,3,4;

我们在另外一个会话中观察1号会话PGA的使用情况

SQL> select sid,pga_used_mem/1024/1024 used_mem,pga_alloc_mem/1024/1024 allocate_mem,pga_freeable_mem/1024/1024 free_mem,pga_max_mem/1024/1024 max_mem from v$process p,v$session s where sid=1 and p.addr=s.paddr;


       SID   USED_MEM ALLOCATE_MEM   FREE_MEM MAX_MEM
---------- ---------- ------------ ---------- ----------
1 289.336327 295.861961  5.5 295.861961

这个使用单个会话使用的最大PGA已经变成了300M左右。

3.private sql area

接下来我们再关注一下另外两个参数open_cursors和session_cached_cursors，这两个参数都是与private area相关的（非工作区）
SQL> SHOW PARAMETER cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     50
session_cached_cursors               integer     50
我们在1号session中执行下面的匿名块：
declare
 v_str varchar2(200);
begin
 for i in 1 .. 30000000
  loop
   for j in 1 .. 5
    loop
     execute immediate 'select * from tt where id='|| i;
     --execute immediate v_str;
    end loop;
  end loop;
end;
/
在另外一个会话中执行下面的语句，查询1号会话缓存的cursor
SQL> select count(*) from v$open_cursor where cursor_type LIKE '%CACHED%' AND SID=41;

  COUNT(*)
----------
        50
其实查询出来的结果可能不是50，但是基本上是保持在50上下的，这个具体原因还不是很清楚。这个已经可以缓存的cursor数量就是由session_cached_cursors决定的，我们可以修改session_cached_cursors这个参数的值，再来查询已经缓存的cursor数量（注意：单个session，不是全局哦）
SQL> alter system set session_cached_cursors=200 scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2215944 bytes
Variable Size             209719288 bytes
Database Buffers          411041792 bytes
Redo Buffers                3350528 bytes
Database mounted.
Database opened.
SQL> select userenv('sid') from dual;

USERENV('SID')
--------------
            30

SQL> declare
  2  v_str varchar2(200);
  3  begin
  4  for i in 1 .. 30000000
  5  loop
  6  for j in 1 .. 5
  7  loop
  8  execute immediate 'select * from tt where id='||i;
  9  --execute immediate v_str;
 10  end loop;
 11  end loop;
 12  end;
 13  /
在另一个会话中查看此时30号会话的cursor缓存信息：
SQL> select count(*) from v$open_cursor where cursor_type LIKE '%CACHED%' AND SID in (30);

  COUNT(*)
----------
       200
关于PGA中缓存的cursor的知识是关于软软解析的，这里就不展开讨论了。
在30号会话中执行下面的pl/sql匿名块：
declare
 v_sql varchar2(500);
 v_cur number;
 v_stat number;
begin
 for i in 1 .. 300
  loop
   v_cur :=dbms_sql.open_cursor;
   v_sql :='select id from tt where id='||i;
   dbms_sql.parse(v_cur,v_sql,dbms_sql.native);
   v_stat :=dbms_sql.execute(v_cur);
  end loop;
end;
/
马上返回下面的错误：
declare
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded
ORA-06512: at "SYS.DBMS_SQL", line 1017
ORA-06512: at line 8
另一个会话中查询一下30号会话已经打开的cursor数量：
SQL> select count(*) from v$open_cursor where cursor_type LIKE 'OPEN%' AND SID in (30);

  COUNT(*)
----------
        56
这个值基本上就是open_cursors的值
关于报错是因为open_cursers=50，而我们试图打开300个cursor，自然会报错的，我们将open_cursors改为400
SQL> alter system set open_cursors=400 scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2215944 bytes
Variable Size             218107896 bytes
Database Buffers          402653184 bytes
Redo Buffers                3350528 bytes
Database mounted.
Database opened.
SQL> select userenv('sid') from dual;

USERENV('SID')
--------------
            38
SQL> declare
  2  msql varchar2(500);
  3  mcur number;
  4  mstat number;
  5  begin
  6  for i in 1 .. 300
  7  loop
  8  mcur :=dbms_sql.open_cursor;
  9  msql :='select id from tt where id='||i;
 10  dbms_sql.parse(mcur,msql,dbms_sql.native);
 11  mstat :=dbms_sql.execute(mcur);
 12  end loop;
 13  end;
 14  /

PL/SQL procedure successfully completed.
此时上面的匿名pl/sql块可以顺利执行了，我们再查看一下38号会话打开的cursor数：
SQL> select count(*) from v$open_cursor where cursor_type LIKE 'OPEN%' AND SID in (38);

  COUNT(*)
----------
       305
打开的cursor数量基本上等于open_cursors的值。
关于为什么查出的值具体为什么不是准确的等于open_cursors的值，我偶然发现在执行匿名块之前先查询的当前会话已经打开的cursor数，在匿名块执行结束后再查询会话打开的cursor数，然后用后面的查询结果减去前面的查询结果，这样似乎直接等于open_cursors的值。