认识PGA
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的值。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28687558/viewspace-1650866/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28687558/viewspace-1650866/