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才會計算出真正一個程式所獨佔的記憶體空間