ITPub博客

首页 > 数据库 > Oracle > pga知识点总结

pga知识点总结

Oracle 作者:germany006 时间:2014-02-23 22:19:49 0 删除 编辑

搜集资料学习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/28371090/viewspace-1086584/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 关于动态语句
请登录后发表评论 登录
全部评论
ORACLE,MYSQL,POSTGRESQL,SQLSERVER

注册时间:2013-03-06

  • 博文量
    766
  • 访问量
    922634