关于pga计算的理解

yingyifeng

Liu老大，看了你博客上的文章：
http://www.oracledatabase12g.com/archives/script-listing-memory-used-by-all-sessions.html  有些疑惑
里面的脚本好像是计算会话所占用的PGA的，这和v$process中的sum（PGA_ALLOC_MEM）之和有什么关系么

我检查了一下：
脚本运行结果如下：
。。。。。。。。
。。。。。。
Sun Apr 01                                                             page   23
        PGA = dedicated server processes - UGA = Client machine process
SID Name                          Max Bytes
---- ------------------------- -------------
210 session pga memory max       11,701,768
208 session pga memory max       11,701,768
208 session pga memory           11,701,768
195 session pga memory max       13,798,920
                               -------------
sum                              291,875,648

而
SQL> select sum(pga_alloc_mem)/1024/1024 from v$process;
SUM(PGA_ALLOC_MEM)/1024/1024
----------------------------
                   104.99081
两者相差还是很大么？是算漏了什么吗？

Maclean Liu(刘相兵

PGA = dedicated server processes - UGA = Client machine process

脚本 Script: Listing Memory Used By All Sessions 在  dedicated server  mode 下可能重复计算 PGA和 PGA中的UGA 。

如需计算PGA的使用量，使用一下脚本：

col name format a40 head "Name"
col value format 999,999,999 head "Total"
col unit format a10 head "Units"
col pga_size format a25 head "PGA Size"
col optimal_executions format 999,999,999,999 head "Optimal"
col onepass_executions format 999,999,999,999 head "One-Pass"
col multipasses_executions format 999,999,999,999 head "Multi-Pass"
col optimal_count format 999,999,999,999 head "Optimal Count"
col optimal_perc format 999 head "Optimal|PCT"
col onepass_count format 999,999,999,999 head "One-Pass Count"
col onepass_perc format 999 head "One|PCT"
col multipass_count format 999,999,999,999 head "Multi-Pass Count"
col multipass_perc format 999 head "Multi|PCT"

col sid format 999,999 Head "SID"
col operation format a30 head "Operation"
col esize format 999,999,999 head "Expected Size"
col mem format 999,999,999 head "Actual Mem"
col "MAX MEM" format 999,999,999 head "Maximum Mem"
col pass format 999,999 head "Passes"
col tsize format 999,999,999,999,999 head "Temporary|Segment Size"

spool workareaoverview.out

SELECT  name,  decode(unit, 'bytes', trunc(value/1024/1024), value) value ,
decode(unit, 'bytes', 'MBytes', unit) unit FROM V$PGASTAT
/


Name                                            Total Units
---------------------------------------- ------------ ----------
aggregate PGA target parameter                    385 MBytes
aggregate PGA auto target                         310 MBytes
global memory bound                                77 MBytes
total PGA inuse                                    40 MBytes
total PGA allocated                                93 MBytes
maximum PGA allocated                             127 MBytes
total freeable PGA memory                          13 MBytes
process count                                      24
max processes count                                26
PGA memory freed back to OS                        48 MBytes
total PGA used for auto workareas                   0 MBytes

Name                                            Total Units
---------------------------------------- ------------ ----------
maximum PGA used for auto workareas                 4 MBytes
total PGA used for manual workareas                 0 MBytes
maximum PGA used for manual workareas               0 MBytes
over allocation count                               0
bytes processed                                    88 MBytes
extra bytes read/written                            0 MBytes
cache hit percentage                              100 percent
recompute count (total)                         3,853


total PGA allocated                                93 MBytes
maximum PGA allocated                             127 MBytes


SQL> select sum(pga_alloc_mem)/1024/1024 from v$process;

SUM(PGA_ALLOC_MEM)/1024/1024
----------------------------
                  93.3336782

wzhihua

meclean老大，说说我的理解;
session pga memory 指的是session当前使用的pga，而 session pga memory max指的是该session使用的最大pga，1楼的“sum                              291,875,648”是把他们相加吗？如果是的话不知道是什么含义。
我认为select sum(pga_alloc_mem)/1024/1024 from v$process;
与select sum(se.VALUE) from v$sesstat se,v$statname n
where n.statistic# = se.statistic# and n.name in ('session pga memory')
都表示示例当前分配的所有pga。可是刚才看了下查询结果，实际上是不同的。请meclean老大看下我的理解哪里有问题。
我指的都是dedicated方式连接数据库。

Maclean Liu(刘相兵

ODM TEST:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL>
SQL> select distinct sid from v$mystat;

       SID
----------
       125

SQL> exec proc1;   

PL/SQL procedure successfully completed.

SQL> select se.value,n.name from v$sesstat se,v$statname n
  2  where n.statistic# = se.statistic# and n.name in ('session pga memory','session uga memory')
  3  and se.sid=125;

     VALUE NAME
---------- ----------------------------------------------------------------
   4504280 session uga memory
   5080032 session pga memory

SQL> select spid,pid,pga_used_mem,pga_alloc_mem,pga_max_mem from v$process where addr=(select paddr from v$session where sid=125);

SPID                            PID PGA_USED_MEM PGA_ALLOC_MEM PGA_MAX_MEM
------------------------ ---------- ------------ ------------- -----------
26474                            19      5157864       6400488    60271080

SQL>





wait seconds , process may release freeable memory to OS






SQL> /

SPID                            PID PGA_USED_MEM PGA_ALLOC_MEM PGA_MAX_MEM
------------------------ ---------- ------------ ------------- -----------
26474                            19      1489192       6400488    60271080

SQL> /

SPID                            PID PGA_USED_MEM PGA_ALLOC_MEM PGA_MAX_MEM
------------------------ ---------- ------------ ------------- -----------
26474                            19      1489192       2730472    60271080

SQL> select se.value,n.name from v$sesstat se,v$statname n
  2  where n.statistic# = se.statistic# and n.name in ('session pga memory','session uga memory')
  3  and se.sid=125;

     VALUE NAME
---------- ----------------------------------------------------------------
    836952 session uga memory
   1410016 session pga memory
   
SQL>
SQL> oradebug setospid 26474
Oracle pid: 19, Unix process pid: 26474, image: oracle@nas.oracle.com (TNS V1-V3)
SQL> oradebug dump heapdump 536870917;
Statement processed.
SQL> oradebug tracefile_name;
/s01/orabase/diag/rdbms/g11r23/G11R23/trace/G11R23_ora_26474.trc




[oracle@nas ~]$ egrep "HEAP DUMP heap name|Total heap size|Permanent space" /s01/orabase/diag/rdbms/g11r23/G11R23/trace/G11R23_ora_26474.trc
HEAP DUMP heap name="session heap"  desc=0x2b88808067c8
Total heap size    =   785560
Permanent space    =    48856
HEAP DUMP heap name="PLS UGA hp"  desc=0x2b888081e7e8
Total heap size    =    33568
Permanent space    =      176
HEAP DUMP heap name="koh-kghu sessi"  desc=0x2b8880820c98
Total heap size    =    21576
Permanent space    =       80
HEAP DUMP heap name="Alloc environm"  desc=0x2b888082e090
Total heap size    =     4040
Permanent space    =     4040
HEAP DUMP heap name="koh-kghu sessi"  desc=0x2b88840ce310
Total heap size    =     3200
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x2b8880825688
Total heap size    =     3200
Permanent space    =       80
HEAP DUMP heap name="pga heap"  desc=0xbaf3ca0
Total heap size    =   557120
Permanent space    =   233864
HEAP DUMP heap name="diag pga"  desc=0x2b88802c57e0
Total heap size    =    69576
Permanent space    =    16056
HEAP DUMP heap name="PLS PGA hp"  desc=0x2b888042c500
Total heap size    =    18608
Permanent space    =       80
HEAP DUMP heap name="Alloc environm"  desc=0x2b88804085c8
Total heap size    =    14216
Permanent space    =      432
HEAP DUMP heap name="Alloc server h"  desc=0x2b8880407860
Total heap size    =     9672
Permanent space    =     1152
HEAP DUMP heap name="KFK_IO_SUBHEAP"  desc=0x2b88804371c0
Total heap size    =    10640
Permanent space    =       80
HEAP DUMP heap name="KSFQ heap"  desc=0x2b8880410a28
Total heap size    =     3728
Permanent space    =     1440
HEAP DUMP heap name="top call heap"  desc=0xbaf94e0
Total heap size    =    65512
Permanent space    =    32832
HEAP DUMP heap name="callheap"  desc=0xbaf8678
Total heap size    =      968
Permanent space    =       80
HEAP DUMP heap name="callheap"  desc=0xbaf85c0
Total heap size    =      968
Permanent space    =       80
HEAP DUMP heap name="top uga heap"  desc=0xbaf9700
Total heap size    =   786144
Permanent space    =       80
HEAP DUMP heap name="session heap"  desc=0x2b88808067c8
Total heap size    =   785560
Permanent space    =    48856
HEAP DUMP heap name="PLS UGA hp"  desc=0x2b888081e7e8
Total heap size    =    33568
Permanent space    =      176
HEAP DUMP heap name="koh-kghu sessi"  desc=0x2b8880820c98
Total heap size    =    21576
Permanent space    =       80
HEAP DUMP heap name="Alloc environm"  desc=0x2b888082e090
Total heap size    =     4040
Permanent space    =     4040
HEAP DUMP heap name="koh-kghu sessi"  desc=0x2b88840ce310
Total heap size    =     3200
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x2b8880825688
Total heap size    =     3200
Permanent space    =       80








session heap size   785560 bytes
pga heap size       557120 bytes
top uga heap size   786144 bytes including session heap            
top call heap size  65512  bytes including call heap


top uga heap + overhead = 786144 + overhead = session uga memory
top uga + pga heap  + top call heap + other overhead = 786144 + 557120 + 65512 + other overhead= session pga memory < PGA_USED_MEM

   
实际计算PGA的使用量时 最值得参考的参数仍是 V$PROCESS.pga_alloc_mem 和  V$PGASTAT中的信息

wzhihua

meclean ，从这个深度去理解pga，我就不懂了。我只是看过一些关于pga的概念性的资料。深入的资料从哪里找呀？能否介绍点这方面的资料学习下？比如如何阅读上述dump文件？？pga到底由哪些部分构成？top uga + pga heap  + top call heap + other overhead？这些都是干什么的？

[ 本帖最后由 wzhihua 于 2012-4-3 22:48 编辑 ]

Maclean Liu(刘相兵

《8i service internal》对PGA有较详尽的描述：

Process Memory
In addition to the SGA, or System Global Area, each Oracle process uses three
similar global areas as well:

The Process Global Area (PGA)
The User Global Area (UGA)
The Call Global Area (CGA)


The PGA consists of two component areas, the fixed PGA and the variable PGA,
or PGA heap. The fixed PGA serves a similar purpose to the fixed SGA. It is fixed
in size, and contains several hundred atomic variables, small data structures, and
pointers into the variable PGA.
The variable PGA is a heap. Its chunks are visible to the process in X$KSMPP ,
which has the same structure as X$KSMSP. The PGA heap contains permanent
memory for a number of fixed tables, which are dependent on certain parameter
settings. These include DB_FILES, LOG_FILES (prior to release 8.1), and
CONTROL_FILES. Beyond that, the PGA heap is almost entirely dedicated to its
subheaps, mainly the UGA (if applicable) and the CGA.


PGA heap is almost entirely dedicated to its subheaps, mainly the UGA (if applicable) and the CGA.



UGA


The User Global Area contains information that is specific to a particular session,
including:
The persistent and runtime areas for open cursors
State information for packages, in particular package variables
Java session state
The roles that are enabled
Any trace events that are enabled
The NLS parameters that are in effect
Any database links that are open
The session's mandatory access control (MAC) label for Trusted Oracle


CGA

Unlike the other global areas, the Call Global Area is transient. It only exists for
the duration of a call. A CGA is required for most low-level calls to the instance,
including calls to:
Parse an SQL statement
Execute an SQL statement
Fetch the outputs of a SELECT statement


Oracle heaps grow much more readily than they shrink, but contrary to popular
belief they can and do shrink. The session statistics session uga memory and
session pga memory visible in V$MYSTAT and V$SESSTAT show the current
size of the UGA and PGA heaps respectively, including internal free space. The
corresponding statistics session uga memory max and session pga memory max
show the peak size of the respective heaps during the life of the session.

wzhihua

感谢maclean！！我找来学习下！