pga知识点总结

搜集资料学习pga
 平时学习的时候，每次都看pga，但很多知识点基本记不住，看了就忘，忘了再看，最近遇到系统宕机的情况，考虑可能是由于分给memory_target的值过大，导致剩余物理内存不足，进而导致被踢出集群，在每次机器被重启之前，都会发现pga很大，比如我们有一次是13点被重启，随后我收取了12-13点的awr发现，这个实例的pga从12点时的2g增长到13点时的15g。查看osw也发现了几个占1G pga内存的进程。
 pga，用户在访问db时，oracle会专门给其分配一块私有的区域，用来存储这个链接的相关会话信息，当链接端口后，系统会回收相应的内存。
查看pga释放会os的内存：
SQL> select inst_id,name,value/1024/1024 from gv$pgastat where name like '%OS';
 
   INST_ID NAME                                                             VALUE/1024/1024
---------- ---------------------------------------------------------------- ---------------
         1 PGA memory freed back to OS                                            372180.25
         2 PGA memory freed back to OS                                              1054013
查看命中率：
SQL> select name,value,100*( value
  2  /decode((select sum(value) from v$sysstat
  3  where name like 'workarea executions%'),0,null,
  4  (select sum(value) from v$sysstat
  5  where name like 'workarea executions%'))) pct
  6  from v$sysstat where name like 'workarea executions%';
 
NAME                                                                  VALUE        PCT
---------------------------------------------------------------- ---------- ----------
workarea executions - optimal                                       3655133 99.9975924---内存中完成
workarea executions - onepass                                            88 0.00240751---磁盘
workarea executions - multipass                                           0          0
查看消耗pga高的sql
select p.program,p.spid,pm.category,pm.allocated,pm.used,pm.max_allocated,l.sql_text,s.sid
 from v$process p, v$process_memory pm,v$session s ,v$sqlarea l
 where p.pid=pm.pid and  s.paddr=p.addr and s.sql_hash_value=l.hash_value 
 and s.USERNAME='ZKF'

当在11g中设定memory_target和memory_max_target的时候，如果我们的物理内存是64g，swap是128g，当db启动的时候，如果我们memory_target设定为50g，那此时db启动，系统不会马上分配50g的内存给oracle，可能只分配10g，待oracle再需要内存资源的时候，才会再分配。对于pga肯定是用的时候才分配，但sga呢，这个还不是很清楚。而且给oracle分配的时候，应该说是物理内存加上swap，50g中不一定都是物理内存的，有可能有比如25%是swap的。如果我们设定memory_target和memory_max_target不为0，而且也设定了sga_max_size，但没有设定pga_aggregate_target，那pga_aggregate_target=memory_target-sga_max_size。其实如果设定了pga_aggregate_target的值如果为5G，但有时候也限制不住，pga的使用也有可能到10g或者更多。
SQL> select name,round(value/1024/1024/1024) from V$pgastat order by value desc;
 
NAME                                                             ROUND(VALUE/1024/1024/1024)
---------------------------------------------------------------- ---------------------------
bytes processed 执行sql时所需要的空间值，实例启动累加值                                      955
PGA memory freed back to OS                                                              459
extra bytes read/written代表SQL工作区无法在最佳状态运行时就需要进行这个额外的内存空间处理    36
aggregate PGA target parameter目前pga_aggregate_target的值，如为0则代表pga自动管理功能关闭           13
aggregate PGA auto target代表pga中sql工作区的数据，随时变化。可以pga_agg有关，和系统负载有关       10
maximum PGA allocated曾分配pga最大大小                                                      10
maximum PGA used for auto workareas                                                        7
total PGA allocated目前给pga分配大小，尽量会在pga_agg内，但也可能超过                          3
total PGA inuse现pga使用量                                                      2
global memory bound可用sql工作区                                                1
total freeable PGA memory-pga最大可用空间                                                    1
total PGA used for auto workareas有多少内存分配给sql工作区使用，我这里是118k                   0
maximum PGA used for manual workareas                                                      0
recompute count (total)                                                                    0
max processes count                                                                        0
process count                                                                              0
cache hit percentage                                                                       0
over allocation count 超出pga_agg的值                                                      0

---cache hit percentage：此数值是自当前实例启动后，PGA中的SQL工作区是否都分配有最佳的内存空间比例，当SQL工作区无法在最佳状态下运行时，会降低cache hit percentage的数值，此数值可以视为目前PGA的命中率(Hit Ratio)。

查看sql工作区情况：
SELECT distinct v$sql_workarea.sql_id,
                total_executions,
                estimated_optimal_size,
                operation_type,
                LAST_TEMPSEG_SIZE,
                v$sql.sql_text workarea_address,
                policy
  FROM V$SQL_WORKAREA, v$sql
 where v$sql_workarea.sql_id = v$sql.sql_id
 ORDER BY LAST_TEMPSEG_SIZE;
查看当前sql工作区使用：
select * from V$sql_Workarea_Active;
将使用pga超过1M的sql列出：
select a.osuser,
         a.username,
         a.machine,
         substr(a.program, 1, 20) "program",
         b.PGA_USED_MEM/ 1024 / 1024,
         b.PGA_ALLOC_MEM/ 1024 / 1024
   from V$SESSION a, V$PROCESS b
  where a.paddr = b.addr
     and b.PGA_USED_MEM / 1024 / 1024 > 1
  order by b.PGA_USED_MEM desc ;
通过sysstat查看排序：
SQL> select * from V$SYSSTAT    where name like '%sort%';
 
STATISTIC# NAME                                                                  CLASS      VALUE    STAT_ID
---------- ---------------------------------------------------------------- ---------- ---------- ----------
       447 sorts (memory)                                                           64    5090112 2091983730
       448 sorts (disk)                                                             64        307 2533123502
       449 sorts (rows)被排序的行数                                                       64 8907356748 3757672740
查看当前系统各个进程使用pga情况：
select pid,spid,program,round(pga_used_mem/1024/1024),round(pga_alloc_mem/1024/1024) from v$process  order by pga_used_mem desc
查看某进程使用pga，uga情况：
select sid,name,value from v$sesstat,v$statname where v$sesstat.STATISTIC#=v$statname.STATISTIC# and name like '%ga%'
and sid=2612
order by value desc
查看pga建议：
SQL> select pga_target_for_estimate/1024/1024,pga_target_factor,estd_time,round(estd_extra_bytes_rw/1024/1024),estd_pga_cache_hit_percentage from v$pga_target_advice;
 
PGA_TARGET_FOR_ESTIMATE/1024/1 PGA_TARGET_FACTOR  ESTD_TIME ROUND(ESTD_EXTRA_BYTES_RW/1024 ESTD_PGA_CACHE_HIT_PERCENTAGE
------------------------------ ----------------- ---------- ------------------------------ -----------------------------
                          1600             0.125   19055602                         784448                            57
                          3200              0.25   12447954                         151443                            87
                          6400               0.5   12429007                         149628                            87
                          9600              0.75   12429007                         149628                            87
                         12800                 1   11061229                          18597                            98
                         15360               1.2   11057491                          18238                            98
                         17920               1.4   11057491                          18238                            98
                         20480               1.6   11057491                          18238                            98
                         23040               1.8   11057491                          18238                            98
                         25600                 2   11057491                          18238                            98
                         38400                 3   11057491                          18238                            98
                         51200                 4   11057491                          18238                            98
                         76800                 6   11057491                          18238                            98
                        102400                 8   11057491                          18238                            98
 
14 rows selected
可以发现当pga大小为12800M时，基本就可以了

在Solaris/unix上，可以使用pmap -x 。
 为什么不直接用pmap（pmap pid会快很多），而用pmap -x?
 因为pmap -x才会计算出真正一个进程所独占的内存空间

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24500180/viewspace-1062902/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24500180/viewspace-1062902/