笔记：PGA(一)

Oracle内存主要有:

用户全局区（UGA）

程序全局区（PGA）

系统全局区（SGA）

调用全局区（call global area）

软件代码区（SCA）

现在探讨下PGA:

PGA作用：

1. 保存回话连接信息，比如会话属性

2. 保存变量绑定信息等。

3. 保存会话sql运行时候的状态信息，例如查询进度。

4. 保存排序，hash连接等结果，如果这里不够，那就用临时表空间。

PGA是一块包含了数据和控制信息的非共享内存区域。操作系统按照进程分配PGA，所以一般进程之间的PGA是排他的。在实例启动的时候，会为各个后台进程分配PGA内存堆（heap），当用户进程连接实例的时候，将要开启服务器进程，分配PGA。

查看所有后台进程和服务器进程的PGA分配情况：

col TERMINAL for a10;

col PROGRAM for a30

col SPID   for a6;

col PROGRAM for a30;

col TRACEFILE for a20;

col USERNAME for a8;

select  pid,spid,pname,username,PROGRAM,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM

from v$process order by pid;

       PID|SPID  |PNAME|USERNAME|PROGRAM                      |PGA_USED_MEM|PGA_ALLOC_MEM|PGA_FREEABLE_MEM|PGA_MAX_MEM

----------|------|-----|--------|------------------------------|------------|-------------|----------------|-----------

         1|     |    |       |PSEUDO                       |          0|           0|              0|         0

         2|1402 |PMON|oracle |oracle@hexel(PMON)          |     756960|      903512|              0|    903512

         3|1404 |PSP0|oracle |oracle@hexel(PSP0)          |     739104|      887096|              0|    887096

         4|1406 |VKTM|oracle |oracle@hexel(VKTM)          |     735864|      887096|              0|    887096

         5|1410 |GEN0|oracle |oracle@hexel(GEN0)          |     743200|      887096|              0|    887096

         6|1412 |DIAG|oracle |oracle@hexel(DIAG)          |     736232|      829928|              0|    829928

         7|1414 |DBRM|oracle |oracle@hexel(DBRM)          |     819656|     1157608|              0|   1157608

         8|1416 |DIA0|oracle |oracle@hexel(DIA0)          |    1206688|     1402696|          65536|   1402696

         9|1418 |MMAN|oracle |oracle@hexel(MMAN)          |     739104|      887096|              0|    887096

        10|1420 |DBW0|oracle |oracle@hexel(DBW0)          |    6882760|     7268296|              0|   7268296

        11|1422 |LGWR|oracle |oracle@hexel(LGWR)          |   11364320|    12085064|              0|  12102120

        12|1424 |CKPT|oracle |oracle@hexel(CKPT)          |     813784|     1051848|              0|   1051848

V$process来源于X$KSUPR表，几个字段解释：

1.Spid，操作系统进程id号

2.Username,操作系统帐号

3.Backgtound:为1表示是后台进程，为空表示服务器进程。

4.Serial#进程序列号

5.PGA_USED_MEM,进程pga使用内存量

6.PGA_ALLOC_MEM，为进程分配的内存空间，一般大于PGA_USED_MEM

7.PGA_FREEABLE_MEM, 代表此进程目前被分配到的PGA内存空间有多少可以被释放

8.PGA_MAX_MEM, 代表此进程曾经被分配到的最大PGA内存空间是多少

V$PROCESS中PGA_USED_MEM、PGA_ALLOC_MEM、PGA_FREEABLE_MEM与PGA_MAX_MEM这4个字段记录了pga分配情况和使用情况，但是这个视图未记录客户端(针对会话)的情况，可以结合v$session视图查看每个会话使用内存的情况：

col MACHINE for a10

col USERNAME for a5

col PROGRAM  for a30

col OSUSER for a10

select s.osuser,

       s.username,

       s.machine,

       s.program,

       p.spid,

       p.pid,

       p.background,

       p.PGA_USED_MEM,

       p.PGA_ALLOC_MEM

from  V$SESSION s,V$PROCESS p

where s.paddr = p.addr

order  by p.pid;

PGA的组成：

客户端向服务器发送连接请求时候，在共享模式下，多个客户端可以共享一个服务器进程，在专有模式下，服务器会为每个客户端连接启用一个服务器进程。

从pga分配和使用上来将，pga一般分为固定pga和可变pga，固定pga可以看作是pga的保留内存，一般包括原子变量，小数据结构和指向可变pga的指针。可变Pga一般是可以动态变化的，我们研究的一般就是这部分pga了。

底层表x$KSMPP记录了可变pga的使用情况：

共享连接和专有连接的区别

分别开启两个专有和共享连接：

SYS >show parameter service;

NAME                                |TYPE       |VALUE

------------------------------------|-----------|------------------------------

service_names                       |string     |SOC

SYS >show parameter dispatcher;

NAME                                |TYPE       |VALUE

------------------------------------|-----------|------------------------------

dispatchers                         |string     |(PROTOCOL=TCP) (SERVICE=orcl_hx)

一个客户端开启两个专有连接：

C:\Users\HuangXing>sqlplussys/talent@191.255.255.253:1523/soc as sysdba

在同一个客户端同时开启两个共享连接：

C:\Users\HuangXing>sqlplussoc_app/soc_app@191.255.255.253:1523/orcl_hx

可以看到，两个专有连接分别开启了两个进程（2490和2463），拥有自己的PGA，而两个共享连接共享了一个进程（2381）的PGA

PGA组成：

1.   会话内存

这部分内存用于存放登录相关信息。对于专有模式的服务器，会话内存是私有的，对于共享式服务器，这部分内存则是共享的。

2.   私有sql区

私有sql区十分重要，它存放了sql语句运行时候的状态（控制信息和数据信息），包括sql语句，变量绑定信息，查询执行状态和查询工作区。

私有sql区分为永久区和运行区：

运行区包含的是一些动态信息，例如，sql语句执行时候的状态信息（此区域包含查询执行状态信息，例如，运行时区域会跟踪到目前为止在全表扫描中检索到的行数）。对于专有服务器模式，私有sql区由pga提供，对于共享模式，这部分内存由sga提供。

永久区自然包含的是sql语句运行时候的变量绑定(bind variable)等信息，他在sql语句运行时候提供变量值，一般在游标关闭时候释放。

游标可以看作是一个客户端端指向服务器端的一个状态指针，操作系统正是通过他来分配私有sql内存的。各种应用程序执行都要显式或者隐式打开游标，数据库活动就是游标的活动，打开游标就需要分配私有工作区。那么，必须要关注用户进程使用的游标情况

私有内存是由用户进程管理的，一般open_cursor参数限制了用户进程可以打开的游标数量，但是内存分配和回收则是依赖据具体的应用程序。

SYS >show parameter open_cursors;

NAME                                |TYPE       |VALUE

------------------------------------|-----------|------------------------------

open_cursors                        |integer    |300

 SYS >selectSADDR,USER_NAME,ADDRESS,SQL_TEXT,CURSOR_TYPE from v$open_cursor whereUSER_NAME='SOC';

SADDR           |USER_NAME |ADDRESS         |SQL_TEXT                                |CURSOR_TYPE

----------------|----------|----------------|----------------------------------------|----------------------------------------------------------------

0000000074284398|SOC_APP   |000000006D796B98|BEGINDBMS_APPLICATION_INFO.SET_MODULE(:|DICTIONARY LOOKUP CURSOR CACHED

                |          |                |1,NULL); END;                           |

0000000074284398|SOC_APP   |000000006C175898|SELECT 44489808 FROMDUAL               |OPEN

3.   Sql工作区（sql workareas）

包括排序（order by/group by/distinct/unions/minus等）,哈希连接，位图连接等消耗的内存区域。对于dml语句，一般是在语句执行完毕后释放sql工作区。对于查询操作，一般是在查询结果返回或者取消后释放。

SOC>select * from student ajoin  sc b on a.sno=b.sno order by b.sno;

       SNO|SNAME       |      SNO|       CNO|CNAME

----------|------------|----------|----------|----------

        10|okokok      |       10|        20|10000

Execution Plan

----------------------------------------------------------

Plan hash value: 1636295263

-------------------------------------------------------------------------------

| Id  | Operation           | Name    | Rows | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |        |     1 |    54 |    8  (25)| 00:00:01 |

|   1 |  SORT ORDER BY      |        |     1 |    54 |    8  (25)| 00:00:01 |

|*  2 |   HASH JOIN         |        |     1 |    54 |    7  (15)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| STUDENT |     1|    21 |     3  (0)| 00:00:01 |

|   4 |   TABLE ACCESS FULL| SC      |     1 |   33 |     3   (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information(identified by operation id):

---------------------------------------------------

   2 -access("A"."SNO"="B"."SNO")

Note

-----

   - dynamic sampling used for this statement(level=2)

Statistics

----------------------------------------------------------

          0 recursive calls

          0 db block gets

         14 consistent gets

          0 physical reads

          0 redo size

        805 bytes sent via SQL*Net to client

        520 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          1 sorts (memory)

          0 sorts (disk)

1          rows processed

查询时候，首先在私有sql区的运行区分配内存段，用于跟踪扫描进度，接着，此回话会在sql工作区的哈希区中开始哈希连接，进行匹配查询；然后，sql工作区中的排序区对结果进行排序。如果排序量不大，排序区就可以完成，如果排序量太大，就需要临时表空间中的临时段了。

通过视图V$PROCESS_MEMORY可以看到用户进程各种类型内存的使用情况。

SYS>select*from v$process_memorywhere pid=(select pidfrom v$processwhere spid=2602);

       PID|  SERIAL#|CATEGORY      | ALLOCATED|     USED|MAX_ALLOCATED

----------|----------|---------------|----------|----------|-------------

        25|       16|SQL           |  3245856|  3240376|     5777768

        25|       16|PL/SQL        |    28296|    21912|       29440

        25|       16|Freeable      |   786432|        0|

        25|       16|Other         |  3314576|         |     3314576

与v$process中的记录进行对比：

OSUSER    |USERN|MACHINE  |PROGRAM          |SPID |BACKGROUND|PGA_USED_MEM|PGA_ALLOC_MEM

HuangXing|SYS |WORKGROUP\|sqlplus.exe      |2622 |         |    4391992|     7375160

7375160=3245856+28296+786432+3314576 。

当执行sql排序等操作时，sql工作区内存（category为sql）会上涨, Freeable(表示操作系统已经分配，但是还未使用的部分)会减少，other表示已经分配给某种类型（具体都有什么？值得关注，是会话内存？私有sql区中的内容?），但是还没有命名的部分。

4. UGA

如果从会话角度来讲，oracle会为每个会话分配内存（UGA），在共享模式下，为每个会话分配的UGA来自共享池或者大型池。

考虑专有模式，PGA服务于一个服务器进程或者后台进程，它包含的是进程相关信息。客户端进行一次连接，服务器器就会给他分配一个进程，那么这个进程占用的内存可以看作是pga。同时，用户的这个进程会产生一个session，那么session使用的内存可以叫做是UGA,也就是说UGA是从会话的角度的来说的。UGA一般会占用pga中的会话内存区和私有sql区以及sql工作区9。

下面这张图很好说明了uga和pga的区别和联系，也说明了uga的内存来源：

UGA也分为固定区域和可变区域。X$ksmup表记录了它的分配状况，UGA包含了如下信息：

1.      私有sql区中游标永久区和运行区，例如变量绑定信息和查询状态

2.      pl/sql区中的包信息以及变量信息，pl/sql运行期间，改变了包中变量信息，也就是改变这部分内存信息

3.      用户权限，角色信息（session memory部分），跟踪事件信息

4.      NLS参数信息

5.      Databaselinks信息

所以，UGA受到一些参数限制,比如：

SYS >showparameter max_enabled_roles;

 

NAME                                |TYPE       |VALUE

------------------------------------|-----------|------------------------------

max_enabled_roles                   |integer   |150

SYS >showparameteropen_links;      

 

NAME                                |TYPE      |VALUE

------------------------------------|-----------|------------------------------

open_links                          |integer   |4

SYS >showparameter open_cursors;

 

NAME                                |TYPE       |VALUE

------------------------------------|-----------|------------------------------

open_cursors                        |integer   |300

PGA的分配

 新特性：_use_realfree_heap

在以前的手动管理中，UGA来源于PGA,一个会话内存分配和回收是通过malloc()和br()函数。会话执行完毕后，内存会释放给PGA，而非释放给操作系统，这造成操作系统内存过度使用。

9iR2以后的_use_realfree_heap用来自动控制PGA管理，oracle为uga单独分配内存，不再来源于pga，内存的分配和回收是通过mmap()调用实现，这种内存分配和回收方式使得会话结束后内存完全释放给了操作系统，这就解决了PGA内存不断上涨的问题。

SYS >start gethidpar

Enter value forpar: _use_real

old   4: and x.ksppinm like '%&par%'

new   4: and x.ksppinm like '%_use_real%'

 

NAME                          |VALUE               |DESCRIB

------------------------------|--------------------|------------------------------------------------------------

_use_realfree_heap            |TRUE                |use real-free based allocatorfor PGA memory

Elapsed:00:00:00.02

查看pga累计释放给操作系统的内存：

SQL> select* from v$pgastat where name='PGA memory freed back to OS';

NAME                                          VALUEUNIT

-------------------------------------------------- ------------

PGA memory freed back to OS             3221028864 bytes

 PGA管理机制

ORACLE PGA内存管理针对的主要还是sql工作区，因为他们占据了pga中的大部分。在oracle9i以前，pga对sql工作区的内存管理是通过手动调整一些参数进行的。但是那时候，由于内存不能释放给操作系统，这会造成sql工作区内存不断上涨，影响性能。即时内存可以及时释放，对于不同会话，需要的sql工作区内存量是不同的：设置太大可能造成浪费，太少造成空间不够，过多在临时表空间排序，影响效率。因此，手动SQL工作区管理方式很少会用。

SYS>alter system set sort_area_size=100 scope=spfile;

SYS >altersystemset hash_area_size=100scope=spfile;

SYS >showparameter pga_aggregate_target;

 

NAME                                |TYPE      |VALUE

------------------------------------|-----------|------------------------------

pga_aggregate_target                |big integer|0   --为0表示可以未打开自动pga管理。

SYS >showparameter area_size;

NAME                                |TYPE      |VALUE

------------------------------------|-----------|------------------------------

bitmap_merge_area_size              |integer   |100   --每个会话使用位图合并连接时的内存工作区域的最大值

create_bitmap_area_size             |integer   |100  --每个会话创建位图时可以使用的内存工作区域的最大值

hash_area_size                      |integer   |100       --每个会话可以用于hash连接的内存空间最大值

sort_area_size                      |integer   |100      --每个会话可以用于内存排序的空间最大值

workarea_size_policy                |string    |MANUAL    --必须要是manual，否则*_area_size的设置值将会被忽略掉

SYS >showparameter retained;  --保留值(UGA保留分配)

 

NAME                                |TYPE      |VALUE

------------------------------------|-----------|------------------------------

sort_area_retained_size             |integer   |100

 

Oracle 9i开始提供了PGA自动内存管理技术—自动化sql执行管理技术（automated sql execution Memory Management）,当参数workarea_size_policy设置成auto时候，并且给pga_aggregate_target指定一个值，这时候启用自动管理技术，这时候，*_area_size参数不再生效，可以通过参数pga_aggregate_target限制所有会话的pga内存总和，这个值范围是10M~（4096G-1）。

Property Description

Parameter type                     Big integer

Syntax                                     PGA_AGGREGATE_TARGET =  integer [K| M | G]

Default value                       10 MB or 20% of the size of the SGA, whichever is greater

Modifiable                             ALTER SYSTEM

Range of values                   Minimum: 10 MB

Maximum:                               4096 GB - 1

Basic                                         Yes

按照上面的建议，在oltp系统中，pga_aggregate_target的默认大小一般是10m或者sga的20%，如果sga正好是物理内存的80%，那么这个参数就是物理内存的16%,下面结果反应了这个规律：

SYS >showparameter area_size_policy;

NAME                                |TYPE      |VALUE

------------------------------------|-----------|------------------------------

workarea_size_policy                |string    |AUTO

SYS >showparametersga;

NAME                                |TYPE      |VALUE

------------------------------------|-----------|------------------------------

lock_sga                            |boolean   |FALSE

pre_page_sga                        |boolean   |FALSE

sga_max_size                        |big integer|332M

sga_target                          |big integer|332M

SYS >showparameter pga

NAME                                |TYPE      |VALUE

------------------------------------|-----------|------------------------------

pga_aggregate_target                |big integer|69625446

SYS >select69625446/1024/1024/332from dual;

 

69625446/1024/1024/332

----------------------

            .199999999

来看看oracle是怎么给每个会话分配内存的

SYS>select * from dba_objects a,dba_objects b order by a.object_id; --让会话耗尽sql工作区，使用临时表空间排序

SYS>start session_pga

OSUSER    |USERN|MACHINE   |PROGRAM                       |SPID  |BACKGROUND|PGA_USED_MEM|PGA_ALLOC_MEM

----------|-----|----------|------------------------------|------|----------|------------|-------------

oracle    |SYS |hexel     |sqlplus@hexel (TNSV1-V3)     |62356 |          |   19933320|    21940888

oracle    |    |hexel     |oracle@hexel (SMCO)           |62358 |1         |     735864|       887096

oracle    |    |hexel     |oracle@hexel(W000)           |62419 |1         |     847640|      1018168

SYS >select 21940888/69625446 from dual;

 

21940888/69625446

-----------------

      .315127432

占用差不多31.5%，但是从这里看到的会话内存使用量和参数pga_aggregate_target 没啥特别的关系。由于sql操作占用了大部分的pga，那么我们可以研究单个sql操作可能占用的内存量。为了防止单个sql操作滥用内存，oracle使用参数_smm_max_size, _pga_max_size进行sql工作区内存限制

_smm_max_size可以确定单个会话工作区可以占用的内存量，如果没有手动设置_smm_max_size的值，一旦设置_pga_max_size或者PGA_AGGREGATE_TARGET，Oracle就会自动计算出_smm_max_size的值，如果手动设置过_smm_max_size则以手动设置的为准。参数_pga_max_size表示单个进程可以占用的内存大小，查看这两个值的的方法如下:

SYS >col name for a30

SYS >col value for a20

SYS >col describfor a60

SYS >select x.ksppinmname, y.ksppstvlvalue, x.ksppdesc describ

        from sys.x$ksppi x,sys.x$ksppcv y

        where x.indx = y.indx

       and  ksppinm in('_pga_max_size','_smm_max_size');

 

NAME                          |VALUE               |DESCRIB

------------------------------|--------------------|------------------------------------------------------------

_pga_max_size                 |209715200          |Maximumsizeofthe PGA memory foroneprocess

_smm_max_size                 |13598              |maximumwork areasizeinautomode(serial)

SYS>select value/1024 from v$pgastat where name='globalmemory bound'; --这个值就是单个会话工作区可以占用的内存大小。

VALUE/1024

----------

     13598

 

SYS >select 13598*1024/69625446 from dual;

13598*1024/69625446

-------------------

         .199989412

关于_pga_max_size :

这个参数默认是200m，如果增大p_a_g，这个参数会有变化，当p_a_g大于1G时，_pga_max_size约等于0.2*P_A_G，从而超过200m。

关于_smm_max_size：

Oracle 11g和oracle10gR2中，考虑串行查询，_smm_max_size参数由_pga_max_size和P_A_G共同决定。不手动设置_smm_max_size，_smm_max_size=min(0.2* P_a_g, 0.5*_pga_max_size)，上面的结果已经反应了这个规律.

在上面中，_pga_max_size为200m，而P_A_G为60M左右，所以_smm_max_size就是13m左右。

当P_A_G大于1G时候，由于_pga_max_size跟着上涨，那么_smm_max_size 就等于0.5*_pga_max_size=0.1*P_G_A,_smm_max_size也就大于100m了。P_A_G上涨到5g以后，由于*_pga_max_size不跟着涨，那么_smm_max_size也不会再涨了

SQL> show parameter pga;

 

NAME                                 TYPE        VALUE

----------------------------------------------- ------------------------------

pga_aggregate_target                 big integer2G

 

colnamefor a30

col value for a20

coldescrib for a60

select x.ksppinmname, y.ksppstvlvalue, x.ksppdesc describ

        from sys.x$ksppi x,sys.x$ksppcv y

        where x.indx = y.indx

       and ksppinm in('_pga_max_size','_smm_max_size');

 

NAME                          |VALUE               |DESCRIB

------------------------------|--------------------|------------------------------------------------------------

_pga_max_size                 |429486080          |Maximumsizeofthe PGA memory foroneprocess

_smm_max_size                 |209710              |maximumwork areasizeinautomode(serial)

 

SYS >select429486080/1024/209710from dual;

 

429486080/1024/209710

---------------------

                    2

没有使用并行查询时，单个Session SQL工作区内存使用最大值为P=min( 20%*PGA_AGGREGATE_TARGET , 50%*_pga_max_size , _smm_max_size)。P值就是前面提到的”globalmemory bound”。

结论：要想让单个session可以使用更大的sql工作区内存，在不手动修改隐含参数的前提下，最根本的办法就是增大P_G_A，因为它会使得上面几个数一起增大。但是，如果我们手动修改了_pga_max_size 和 _smm_max_size，那么上面的关系不再成立，这时候要以实际修改的_smm_max_size为主。

如果PGA_AGGREGATE_TARGET值较小， _smm_max_size达不到理想的状态，可以调高_pga_max_size和 _smm_max_size，达到预期效果的。

对于并行查询，可以使用的内存按照如下规则：

50%P_A_G/DOP(DOP是并行度)，当并行度超过5时候参数_smm_px_max_size生效。