oracle PGA（process global area）初探

来源：互联网发布：部落冲突皮卡超人数据编辑：程序博客网时间：2024/06/05 06:30

转载：http://blog.itpub.net/28687558/viewspace-1650866/

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

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相关的参数
2.1. pag_aggregate_target
理论上PGA_AGGREGATE_TARGET参数用来控制实例使用PGA的内存总量，实例尽量保持在PGA_AGGREGATE_TARGET的限制内，但如果实在无法保证，它也不会停止处理，只是要求超过这个阀值。实际上这个PGA_AGGREGATE_TARGET参数是控制工作区的操作。
2.2. WORKAREA_SIZE_POLICY
控制PGA的管理方式，有AUTO和MANUL两个选项，AUTO就是自动管理，MANUL就是手动管理方式
下面通过实验的方式再来了解其他一些PGA相关的参数：
SQL> show parameter pga_aggregate_

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 400M

SQL> show parameter workarea_size_p

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
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=(select userenv('sid') from dual) and p.addr=s.paddr;

SID USED_MEM ALLOCATE_MEM FREE_MEM MAX_MEM
---------- ---------- ------------ ---------- ----------
38 .946094513 2.25030518 1 210.437805
这说明当一个客户端连接到oracle数据库（dedicated），什么这不做，就需要差不多1M的内存
当在这个会话（sid=38）中执行下面需要大量的work area的操作时(具体来说是排序)，我们在另外一个会话中观察sid=38号会话对PGA的使用情况：
SQL> select count(*) from tt;

COUNT(*)
----------
100001

SQL> select count(*) from cbc_t1;

COUNT(*)
----------
100001
SQL> select * from tt,cbc_t1 order by 1,2,3;
在另外一个会话中查询38号会话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=38 and p.addr=s.paddr;

SID USED_MEM ALLOCATE_MEM FREE_MEM MAX_MEM
---------- ---------- ------------ ---------- ----------
38 86.9909554 88.1253052 .875 210.437805
此时38号会话已经使用了87M左右的内存（work area size）
当我终止38号会话的操作，再观察38号会话的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=38 and p.addr=s.paddr;

SID USED_MEM ALLOCATE_MEM FREE_MEM MAX_MEM
---------- ---------- ------------ ---------- ----------
38 .946094513 2.25030518 1 210.437805
这说明当操作完成后，work area会释放之前使用的内存。
我们观察一下下面几个参数：
SQL> show parameter sort_area_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_size integer 209715200
SQL> show parameter hash_area

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hash_area_size integer 419430400
SQL> show parameter bitmap_me

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size integer 1048576
SQL> show parameter sort_area_re

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_retained_size integer 0
这说明，当使用自动的PGA内存管理方式时，单个会话最大能使用PGA内存为PGA_AGGREGATE_TARGET的1/4左右（实际上是由隐含参数决定的，下面会讲到），sort_area_size、hash_area_size、bitmap_merge_size并不生效。
接下来我们使用MANUL方式来管理PGA
SQL> alter system set workarea_size_policy=manual;
System altered.
再在38号会话中做上面的操作：
SQL> select * from tt,cbc_t1 order by 1,2,3;
再在另外一个会话中观察38号会话使用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=38 and p.addr=s.paddr;

       SID   USED_MEM ALLOCATE_MEM   FREE_MEM    MAX_MEM
---------- ---------- ------------ ---------- ----------
        38 208.606464   210.187805      .0625 210.437805
此时单个会话的最大PGA（work area size）的限制就是由sort_area_szie、hash_area_size、bitmap_merge_size这几个参数来控制的。
此时有一个问题，我们在使用AUTO方式管理PGA，单个服务器进程怎么尽可能大的使用PGA？？？
那么首先我们先关注两个隐含的参数：
SQL> @?/rdbms/admin/show_para.sql
Enter value for para_name: _smm_max_size
old   1: select ksppinm name,ksppstvl value,ksppdesc from x$ksppi i,x$ksppcv cv where i.indx=cv.indx and ksppinm like '%¶_name%'
new   1: select ksppinm name,ksppstvl value,ksppdesc from x$ksppi i,x$ksppcv cv where i.indx=cv.indx and ksppinm like '%_smm_max_size%'

NAME                 VALUE                KSPPDESC
-------------------- -------------------- --------------------------------------------------
_smm_max_size        81920                maximum work area size in auto mode (serial)
@?/rdbms/admin/show_para.sql
Enter value for para_name: _pga_max_size
old   1: select ksppinm name,ksppstvl value,ksppdesc from x$ksppi i,x$ksppcv cv where i.indx=cv.indx and ksppinm like '%¶_name%'
new   1: select ksppinm name,ksppstvl value,ksppdesc from x$ksppi i,x$ksppcv cv where i.indx=cv.indx and ksppinm like '%_pga_max_size%'

NAME VALUE KSPPDESC
-------------------- -------------------- --------------------------------------------------
_pga_max_size 209715200 Maximum size of the PGA memory for one process
SQL> select 209715200/1024/1024 from dual;

209715200/1024/1024
-------------------
200
其实这个参数就决定了单个server process最大使用PGA（work area）的大小（AUTO模式），我们修改这个参数的大小
SQL>ALTER SYTEM SET WORKAREA_SIZE_POLICY=MANUAL;
SQL>Alter system set “_pga_max_size”=409715200;
在38号会话中进行下面的操作：
SQL> select * from tt,cbc_t1 order by 1,2,3;
在另外一个会话中观察38号会话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=38 and p.addr=s.paddr;

SID USED_MEM ALLOCATE_MEM FREE_MEM MAX_MEM
---------- ---------- ------------ ---------- ----------
32 261.118088 264.000305 2.6875 264.000305
这个值已经超过了之前使用AUTO模式所能使用的值,接近于_pga_max_size这个隐含参数的值。

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

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     50
session_cached_cursors               integer     50
我们在38号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;
/
在另外一个会话中执行下面的语句，查询38号会话缓存的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的值。

0 0