【深入解析--eygle】 學習筆記
sys@felix SQL>show parameter area_size NAME TYPE VALUE ------------------------------------ ---------------------------------------------------- bitmap_merge_area_size integer 1048576 create_bitmap_area_size integer 8388608 hash_area_size integer 131072 sort_area_size integer 65536 workarea_size_policy string AUTO
自動化SQL執行內存管理（Automated SQL Execution Memory Management），也稱為自動PGA管理，使用這個新特性，Oracle可以在一個總體PGA使用限制下自動管理和調整SQL內存區，從而大大簡化了DBA的工作，同時也?高了數據庫的性能。
為實現自動的PGA管理，Oracle引入了幾個新的初始化參數：
（1）PGA_AGGREGATE_TARGET 此參數用來指定所有session 總計可以使用最大PGA內存。這個參數可以被動態的更改,取值范圍從10M ～（4096G-1 ）bytes。
（2）WORKAREA_SIZE_POLICY此參數用於開關PGA內存自動管理功能，該參數有兩個選項：AUTO 和 MANUAL，當 設 置為AUTO時，數據庫使用自動PGA管理功能，當設置為MANUAL時，則仍然使用之前手工管理的方式。
缺省的，WORKAREA_SIZE_POLICY參數被設置為AUTO。
sys@felix SQL>show parameterWORKAREA_SIZE_POLIC NAME TYPE VALUE ---------------------------------------------------------- -------- workarea_size_policy string AUTO
此外需要注意的是，在不同版本中，自動PGA管理的范疇不同：
（1）在Oracle9i中，PGA_AGGREGATE_TARGET參數僅對專用服務器模式下（DedicatedServer）的專屬連接有效，但是對共享服務器（Shared Server）連接無效
（2）從Oracle10g開始PGA_AGGREGATE_TARGET對專用服務器連接和共享服務器連接同時生效。
工作區性能期望實現如下目標：
workarea execution - optimal >= 90%
workarea execution - multipass = 0%
生產系統的PGA性能指標腳本:
SELECT NAME,
VALUE,
100 *
(VALUE / DECODE((SELECT SUM(VALUE)
FROM v$sysstat
WHERE NAME LIKE'workarea executions%'),
0,
NULL,
(SELECT SUM(VALUE)
FROM v$sysstat
WHERE NAME LIKE'workarea executions%'))) pct
FROMv$sysstat
WHERE NAMELIKE 'workarea executions%';
<strong>sys@felix SQL>select description,dest fromx$messages where description like 'SQL Memory%'; DESCRIPTION DEST -------------------------------------------------- ---------- SQL Memory Management Calculation CKPT 15:26:04 sys@felix SQL></strong>
sys@felix SQL>select * from v$pgastat; NAME VALUE UNIT ---------------------------------------- ---------- ------------------------ aggregate PGA target parameter 146800640 bytes aggregate PGA auto target 22099968 bytes global memory bound 29360128 bytes total PGA inuse 122360832 bytes total PGA allocated 144107520 bytes maximum PGA allocated 163160064 bytes total freeable PGA memory 11141120 bytes process count 32 max processes count 36 PGA memory freed back to OS 374669312 bytes total PGA used for auto workareas 0 bytes maximum PGA used for auto workareas 6313984 bytes total PGA used for manual workareas 0 bytes maximum PGA used for manual workareas 0 bytes over allocation count 0 bytes processed 232915968 bytes extra bytes read/written 0 bytes cache hit percentage 100 percent recompute count (total) 6004 19 rows selected.
伴隨自動PGA調整功能的引入，Oracle同時引入相應的動態性能視圖用於優化建議，PGA的優化建議通過v$pga_target_advice 和 v$pga_target_advice_histogra提供。v$pga_target_advice視圖通過對不同PGA設置進行評估，給出在不同設置下的PGA命中率和OverAlloc等信息。
15:32:23 sys@felix SQL>select PGA_TARGET_FOR_ESTIMATE/1024/1024 PGAMB, PGA_TARGET_FACTOR,
15:37:08 2 ESTD_PGA_CACHE_HIT_PERCENTAGE, ESTD_OVERALLOC_COUNT
15:37:08 3 from v$pga_target_advice;
PGAMB PGA_TARGET_FACTOR ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
---------- ----------------- ----------------------------- --------------------
17.5 .125 85 4
35 .25 85 4
70 .5 85 4
105 .75 85 4
140 1 100 0
168 1.2 100 0
196 1.4 100 0
224 1.6 100 0
252 1.8 100 0
280 2 100 0
420 3 100 0
560 4 100 0
840 6 100 0
1120 8 100 0
v$pga_target_advice_histogram 視圖可以通過對不同工作區大小的采樣評估?供統計信息
供分析使用。其中幾個重要字段有：
（1）LOW_OPTIMAL_SIZE-Histogram評估區間內Optimal下限 (bytes)
（2）HIGH_OPTIMAL_SIZE-Histogram評估區間內Optimal上限 (bytes)
（3）ESTD_OPTIMAL_EXECUTIONS-Histogram評估區間內估計optimal執行次數
（4）ESTD_ONEPASS_EXECUTIONS-Histogram評估區間內估計onepass執行次數
（5）ESTD_MULTIPASSES_EXECUTIONS-Histogram評估區間內估計multipass執行次數
（6）ESTD_TOTAL_EXECUTIONS-Histogram評估區間內估計執行總次數
SELECT pga_target_factor factor,
low_optimal_size / 1024 low,
ROUND(high_optimal_size / 1024) high,
estd_optimal_executions estd_opt,
estd_onepass_executions estd_op,
estd_multipasses_executions estd_mp,
estd_total_executions estd_exec
FROM v$pga_target_advice_histogram
WHERE pga_target_factor = 0.25
AND estd_total_executions > 0;
</pre><pre name="code" class="html"><img src="http://img.blog.csdn.net/20140727143007260?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvcGFuZmVsaXg=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast" alt="" />
<span style="font-family: Arial, Helvetica, sans-serif;"> </span>
<span style="font-family: Arial, Helvetica, sans-serif;"> heap name="pga heap" desc=0xbaf3ca0 --注意這裡</span></strong></span>
extentsz=0x20c0 alt=216 het=32767 rec=0 flg=3 opc=2 parent=(nil)owner=(nil) nex=(nil) xsz=0xfff0 heap=(nil) fl2=0x60,nex=(nil), dsxvers=1, dsxflg=0x0 dsx firstext=0xf7fa7720 EXTENT 0 addr=0x7fadf7c72010 Chunk 7fadf7c72020 sz= 65504 free " " EXTENT 1 addr=0x7fadf7d00010 Chunk 7fadf7d00020 sz= 28920 perm "perm " alo=9384 Chunk 7fadf7d07118 sz= 7656 free " " Chunk 7fadf7d08f00 sz= 4224 freeable "diag pga " ds=0x7fadf82157e0 Chunk 7fadf7d09f80 sz= 4224 freeable "diag pga " ds=0x7fadf82157e0 /heap Chunk 7fadf7fd28d0 sz= 7608 perm "perm " alo=7608 Chunk 7fadf7fd4688 sz= 40 free " " Chunk 7fadf7fd46b0 sz= 80 freeable "dbgdInitEventGr" Chunk 7fadf7fd4700 sz= 184 freeable "sdbgrf: iosb " Chunk 7fadf7fd47b8 sz= 80 freeable "skgfzctx " Chunk 7fadf7fd4808 sz= 376 freeable "PLS cca hpdesc" EXTENT 18 addr=0x7fadf7fcd860 Chunk 7fadf7fcd870 sz= 20512 perm "perm " alo=20512 EXTENT 19 addr=0x7fadf7fc9958 Chunk 7fadf7fc9968 sz= 16072 perm "perm " alo=16072 EXTENT 20 addr=0x7fadf7fc7868 Chunk 7fadf7fc7878 sz= 3240 perm "perm " alo=3240 Chunk 7fadf7fc8520 sz= 3184 perm "perm " alo=3184 Chunk 7fadf7fc9190 sz= 1088 perm "perm " alo=1088 Chunk 7fadf7fc95d0 sz= 168 perm "perm " alo=168 Chunk 7fadf7fc9678 sz= 48 free " " Chunk 7fadf7fc96a8 sz= 72 freeable "koh-kghu callh" Chunk 7fadf7fc96f0 sz= 568 freeable "joxp heap " EXTENT 21 addr=0x7fadf7fc5778 Chunk 7fadf7fc5788 sz= 4848 perm "perm " alo=4848 Chunk 7fadf7fc6a78 sz= 440 freeable "krbabrPgaRespMs" Chunk 7fadf7fc6c30 sz= 56 freeable "krbabrPgaReqCtx" Chunk 7fadf7fc6c68 sz= 424 freeable "krbabrPgaReqMsg" Chunk 7fadf7fc6e10 sz= 2600 freeable "kjztprq struct" EXTENT 22 addr=0x7fadf7fc3688 Chunk 7fadf7fc3698 sz= 7720 perm "perm " alo=7720 Chunk 7fadf7fc54c0 sz= 160 freeable "regheapd_kdlwpg" Chunk 7fadf7fc5560 sz= 88 freeable "KCFIS GCTX " Chunk 7fadf7fc55b8 sz= 400 freeable "krbabrPgaStbyRe" EXTENT 23 addr=0x7fadf7fb5e28 Chunk 7fadf7fb5e38 sz= 55328 perm "perm " alo=55328 EXTENT 24 addr=0x7fadf7fb3d38 Chunk 7fadf7fb3d48 sz= 4888 perm "perm " alo=4888 Chunk 7fadf7fb5060 sz= 2992 recreate "KSFQ heap " latch=(nil)
進一步的，可以將某個具體的數據存儲結構轉儲出來（DS），如 以 上 的ds dcd00c0其空間使用的大小為 sz= 14036，首先對空間地址進行一下轉換：
select to_number('baf3ca0','xxxxxxxxxx') from dual
15:50:33 sys@felix SQL>/
TO_NUMBER('BAF3CA0','XXXXXXXXXX')
---------------------------------
196033696
15:50:33 sys@felix SQL>
使用如下命令轉儲固定地址空間的內容：
alter session set events'immediate trace name heapdump_addr level 1, addr n';
以上計算的地址空間可以通過如下命令轉儲：
SQL> ALTER SESSIONSET EVENTS 'immediate trace name heapdump_addr level 1, addr 196033696';
Session altered.
獲取轉儲文件的腳本：
SELECT a.VALUE || b.symbol || c.instance_name || '_ora_' || d.spid ||
'.trc' trace_file
FROM (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') a,
(SELECT SUBSTR(VALUE, -6, 1) symbol
FROM v$parameter
WHERE NAME = 'user_dump_dest') b,
(SELECT instance_name FROM v$instance) c,
(SELECT spid
FROM v$session s, v$process p, v$mystat m
WHERE s.paddr = p.addr
AND s.SID = m.SID
AND m.statistic# = 0) d;
檢查這個進程轉儲文件，可以發現如下Heap地址信息及空間分配：
[oracle@felix ~]$grep heap /u01/app/oracle/diag/rdbms/felix/felix/trace/felix_ora_6443.trc <strong><span style="color:#ff0000;">HEAP DUMP heap name="pga heap" desc=0xbaf3ca0</span></strong> parent=(nil) owner=(nil) nex=(nil) xsz=0xfff0 heap=(nil) Chunk 7fadf7fc96f0 sz= 568 freeable "joxp heap " Chunk 7fadf7fc54c0 sz= 160 freeable "regheapd_kdlwpg" Chunk 7fadf7fb5060 sz= 2992 recreate "KSFQ heap " latch=(nil) Chunk 7fadf7fb5c10 sz= 160 freeable "KSFQ heap descr" Chunk 7fadf7fb0790 sz= 232 freeable "iovecheapd_kdlw" Chunk 7fadf7fb0878 sz= 304 freeable "bcheapd_kdlwpga" Chunk 7fadf7fb09a8 sz= 208 freeable "sioheapd_kdlwpg" Chunk 7fadf7fb0ad8 sz= 160 freeable "KSZ pga subheap" Total heap size = 499952 <strong><span style="color:#ff0000;">HEAP DUMP heap name="top call heap" desc=0xbaf94e0</span></strong> parent=(nil) owner=(nil) nex=(nil) xsz=0xfff8 heap=(nil) Chunk 7fadf7ccb7d8 sz= 1032 recreate "callheap " latch=(nil) Chunk 7fadf7cceb78 sz= 4224 freeable "callheap " ds=0xbaf85c0 Chunk 7fadf7ccfbf8 sz= 1032 recreate "callheap " latch=(nil) Total heap size = 524096
<strong><span style="color:#ff0000;">HEAP DUMP heap name="top uga heap" desc=0xbaf9700</span></strong> parent=(nil) owner=(nil) nex=(nil) xsz=0xfff8 heap=(nil) Chunk 7fadf7c62018 sz= 65512 freeable "session heap " ds=0x7fadf7cd5be0 Chunk 7fadf7d10018 sz= 65512 freeable "session heap " ds=0x7fadf7cd5be0 Chunk 7fadf7cf0018 sz= 65512 freeable "session heap " ds=0x7fadf7cd5be0 Chunk 7fadf7ce0068 sz= 65432 recreate "session heap " latch=(nil) Total heap size = 262048 <strong><span style="color:#ff0000;">HEAP DUMP heap name="pga heap" desc=0xbaf3ca0</span></strong> parent=(nil) owner=(nil) nex=(nil) xsz=0x1fff0 heap=(nil) Chunk 7fadf7fc96f0 sz= 568 freeable "joxp heap " Chunk 7fadf7fc54c0 sz= 160 freeable "regheapd_kdlwpg" Chunk 7fadf7fb5060 sz= 2992 recreate "KSFQ heap " latch=(nil) Chunk 7fadf7fb5c10 sz= 160 freeable "KSFQ heap descr" Chunk 7fadf7fb0790 sz= 232 freeable "iovecheapd_kdlw" Chunk 7fadf7fb0878 sz= 304 freeable "bcheapd_kdlwpga" Chunk 7fadf7fb09a8 sz= 208 freeable "sioheapd_kdlwpg" Chunk 7fadf7fb0ad8 sz= 160 freeable "KSZ pga subheap" Total heap size = 827504
由上面紅色字體可見，在自動管理模式下，PGA,CGA,UGA都是獨立分配的。
