Oracle記憶體結構（之三）

標籤：oracle   cga   記憶體   pga   uga   

【深入解析--eygle】 學習筆記

1.2.3 PGA的管理：

[email protected] SQL>show parameter area_size NAME                                 TYPE                   VALUE------------------------------------ ----------------------------------------------------bitmap_merge_area_size               integer                1048576create_bitmap_area_size              integer                8388608hash_area_size                       integer                131072sort_area_size                       integer                65536workarea_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。

[email protected] 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對專用伺服器串連和共用伺服器串連同時生效。

 

1.2.4 參數的設定與記憶體配置

工作區效能期望實現如下目標：

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>[email protected] SQL>select description,dest fromx$messages where description like 'SQL Memory%'; DESCRIPTION                                        DEST-------------------------------------------------- ----------SQL Memory Management Calculation                  CKPT 15:26:04 [email protected] SQL></strong>

[email protected] SQL>select * from v$pgastat;NAME                                          VALUE UNIT---------------------------------------- ---------- ------------------------aggregate PGA target parameter            146800640 bytesaggregate PGA auto target                  22099968 bytesglobal memory bound                        29360128 bytestotal PGA inuse                           122360832 bytestotal PGA allocated                       144107520 bytesmaximum PGA allocated                     163160064 bytestotal freeable PGA memory                  11141120 bytesprocess count                                    32max processes count                              36PGA memory freed back to OS               374669312 bytestotal PGA used for auto workareas                 0 bytesmaximum PGA used for auto workareas         6313984 bytestotal PGA used for manual workareas               0 bytesmaximum PGA used for manual workareas             0 bytesover allocation count                             0bytes processed                           232915968 bytesextra bytes read/written                          0 bytescache hit percentage                            100 percentrecompute count (total)                        600419 rows selected.

1.2.6 PGA調整建議

 

伴隨自動PGA調整功能的引入，Oracle同時引入相應的動態效能檢視用於最佳化建議，PGA的最佳化建議通過v$pga_target_advice  和  v$pga_target_advice_histogra提供。v$pga_target_advice視圖通過對不同PGA設定進行評估，給出在不同設定下的PGA命中率和OverAlloc等資訊。

15:32:23 [email protected] SQL>select PGA_TARGET_FOR_ESTIMATE/1024/1024 PGAMB, PGA_TARGET_FACTOR, 15:37:08   2  ESTD_PGA_CACHE_HIT_PERCENTAGE, ESTD_OVERALLOC_COUNT15: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=0xf7fa7720EXTENT 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=20512EXTENT 19 addr=0x7fadf7fc9958  Chunk     7fadf7fc9968 sz=    16072   perm      "perm           "  alo=16072EXTENT 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=55328EXTENT 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 dual15:50:33 [email protected] SQL>/TO_NUMBER('BAF3CA0','XXXXXXXXXX')---------------------------------                        19603369615:50:33 [email protected] 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地址資訊及空間分配：

[[email protected] ~]$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都是獨立分配的。