溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
小編給大家分享一下Oracle Share Pool內部管理機制的示例分析，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！
Oracle Share Pool內部管理機制
SHARE POOL利用堆（HEAP)的內存管理方式管理，在物理上由多個內存區（EXTENT)組成，內存區又由多個不同大小的CHUNK組成。而CHUNK又有可重用和空閑之分，并且它們分別有LRU LIST、FREE LIST、RESERVED LIST串聯起來。
堆管理
Shared Pool是利用堆內存管理方式管理的（KGH:Kernel Generic Heap).從Oracle 9i開始，可以有多個最高級堆（TOP-LEVLE HEAP)，最高級堆可以分成多個副堆，副堆下面還擁有子堆。堆和副對結構基本相同。從物理上講，一個堆由多個內存區已link list的形式連接組成。一個內存區物理上使用一個Granule，一個內存區由多個chunk組成，所以chunk是heap的最小內存單位。
Chunk的使用情況可由X$KSMSP內部視圖查看。每個堆頭上則包含了可使用的chunk列表和已使用的chunk列表。通過dump heap命令可以在trace文件中觀察heap和extent的關系。
alter system set events 'immediate trace name heapdump level 2';
案例：
SQL> select name,bytes/1024/1024 from v$sgainfo; NAME BYTES/1024/1024 -------------------------------- --------------- Fixed SGA Size 1.27566528 Redo Buffers 5.59765625 Buffer Cache Size 180 Shared Pool Size 104 Large Pool Size 4 Java Pool Size 4 Streams Pool Size 0 Shared IO Pool Size 0 Granule Size 4 Maximum SGA Size 498.875 Startup overhead in Shared Pool 52 NAME BYTES/1024/1024 -------------------------------- --------------- Free SGA Memory Available 200 12 rows selected. [oracle@node1 ~]$ more /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_14983.trc Trace file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_14983.trc Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1 System name: Linux Node name: node1 Release: 2.6.18-194.el5 Version: #1 SMP Tue Mar 16 21:52:43 EDT 2010 Machine: i686 Instance name: prod Redo thread mounted by this instance: 1 Oracle process number: 32 Unix process pid: 14983, p_w_picpath: oracle@node1 (TNS V1-V3) *** 2014-07-02 14:58:47.532 *** SESSION ID:(32.758) 2014-07-02 14:58:47.532 *** CLIENT ID:() 2014-07-02 14:58:47.532 *** SERVICE NAME:(SYS$USERS) 2014-07-02 14:58:47.532 *** MODULE NAME:(sqlplus@node1 (TNS V1-V3)) 2014-07-02 14:58:47.532 *** ACTION NAME:() 2014-07-02 14:58:47.532 KGH Latch Directory Information ldir state: 2 last allocated slot: 99 Slot [ 1] Latch: 0x200065ec Index: 1 Flags: 3 State: 2 next: (nil) Slot [ 2] Latch: 0x3e75571c Index: 1 Flags: 3 State: 2 next: (nil) ......(省略） Slot [ 99] Latch: 0x2002616c Index: 1 Flags: 3 State: 2 next: 0x1 ****************************************************** HEAP DUMP heap name="sga heap" desc=0x200010b4 extent sz=0x7ad4 alt=124 het=32767 rec=9 flg=-126 opc=0 parent=(nil) owner=(nil) nex=(nil) xsz=0x0 heap=(nil) fl2=0x60, nex=(nil) ds for latch 1: 0x2002a990 0x2002b5c8 0x2002c200 0x2002ce38 reserved granule count 0 (granule size 4194304) ****************************************************** HEAP DUMP heap name="sga heap(1,0)" desc=0x2002a990 extent sz=0xfc4 alt=124 het=32767 rec=9 flg=-126 opc=0 parent=(nil) owner=(nil) nex=(nil) xsz=0x400000 heap=(nil) fl2=0x20, nex=(nil) latch set 1 of 1 durations enabled for this heap reserved granules for root 0 (granule size 4194304) EXTENT 0 addr=0x3b800000 Chunk 3b800038 sz= 24 R-freeable "reserved stoppe" Chunk 3b800050 sz= 212888 R-free " " Chunk 3b833fe8 sz= 24 R-freeable "reserved stoppe" Chunk 3b834000 sz= 3981312 perm "perm " alo=3290272 EXTENT 1 addr=0x3bc00000 Chunk 3bc00038 sz= 24 R-freeable "reserved stoppe" Chunk 3bc00050 sz= 212888 R-free " " Chunk 3bc33fe8 sz= 24 R-freeable "reserved stoppe" Chunk 3bc34000 sz= 3510272 perm "perm " alo=3510272 Chunk 3bf8d000 sz= 465920 perm "perm " alo=465920 Chunk 3bffec00 sz= 5120 free " " EXTENT 2 addr=0x3c000000 Chunk 3c000038 sz= 24 R-freeable "reserved stoppe" Chunk 3c000050 sz= 212888 R-free " " Chunk 3c033fe8 sz= 24 R-freeable "reserved stoppe" Chunk 3c034000 sz= 3850176 perm "perm " alo=3850176 Chunk 3c3dffc0 sz= 131088 perm "perm " alo=131088 Chunk 3c3fffd0 sz= 48 free " " EXTENT 3 addr=0x3c400000 Chunk 3c400038 sz= 24 R-freeable "reserved stoppe" Chunk 3c400050 sz= 212888 R-free " " Chunk 3c433fe8 sz= 24 R-freeable "reserved stoppe" Chunk 3c434000 sz= 3980368 perm "perm " alo=3980368 Chunk 3c7ffc50 sz= 944 free " " EXTENT 4 addr=0x3c800000 Chunk 3c800038 sz= 24 R-freeable "reserved stoppe" Chunk 3c800050 sz= 212888 R-free " " Chunk 3c833fe8 sz= 24 R-freeable "reserved stoppe" Chunk 3c834000 sz= 3672700 perm "perm " alo=3672700 Chunk 3cbb4a7c sz= 308240 perm "perm " alo=308240 Chunk 3cbffe8c sz= 372 free " "
CHUNK
Chunk是以鏈條（chain）的方式存在于內存區，每個chunk包含header和body兩部分，chunk的狀態大體上可以分為free（可立即使用）、recreatable（可再生）、freeable（只有在session或call期間內保存必要的對象狀態）、permanent（永久、不可再生）等，其中free和recreatable狀態下的chunk可以重復使用。
FREE LIST
Free list主要用于管理空閑的chunk，并且是用bucket管理的。從Oracle 9i開始，一個heap總共255個bucket，bucket所包含的free chunk大小隨bucket的編號增加而遞增，個bucket下的free chunk已linked list的形式鏈接。
（1）獲得shared pool latch在free list中查找合適大小的空閑chunk。如果在獲取shared pool latch時發生了爭用，則會出現latch：shared pool等待事件。這時Oracle會一直持有shared pool latch，直到獲得所需的內存為止。所在內存碎片化比較嚴重的shared pool中，進程持有shared pool latch的時間也會相應變長。
（2）如果不存在合適大小的空閑chunk，則在查找到更大的空閑chunk后分割（split）使用，分割后剩下的內存區域則重新登記到free list中。分割內存意味著內存中的碎片開始增多。由于每個cursor所需內存的大小不同，所以shared pool的空閑內存不像buffer cache中的空閑內存一樣具有固定大小。
（3）如果檢索了free list也沒有找到所有合適的空閑chunk，則檢索lru list。lru list上的chunk是重建（recreatable）的，而且是當前不使用的（沒有處于pin狀態）。
（4）如果在lru list上也沒有找到合適的chunk，且所請求的內存還沒有達到隱含參數_shared_pool_reserved_min_alloc的閾值，則追加分配share pool中剩余的內存空間。
（5）如果以上請求的內存均失敗，則出現ORA-4031錯誤
FREE LISTS: Bucket 0 size=16 Bucket 1 size=20 Bucket 2 size=24 Bucket 3 size=28 Bucket 4 size=32 Bucket 5 size=36 Bucket 6 size=40 Bucket 7 size=44 Bucket 8 size=48 Chunk 3c3fffd0 sz= 48 free " " Chunk 3ebfffd0 sz= 48 free " " Bucket 9 size=52 Bucket 10 size=56 Bucket 11 size=60 Bucket 12 size=64 Bucket 13 size=68 Bucket 14 size=72 Bucket 15 size=76 Bucket 16 size=80 Bucket 17 size=84 Bucket 18 size=88 Chunk 3e7fffa8 sz= 88 free " " Bucket 19 size=92 Bucket 20 size=96 Bucket 21 size=100 Bucket 22 size=104 Bucket 23 size=108 Bucket 24 size=112 Bucket 25 size=116 Bucket 26 size=120 Bucket 27 size=124 ......
LRU LIST
Lru list主要保存著當前未使用而且可以重建的chunk。當會話在free list中找不到空閑的chunk時，在會在lru list中尋找。
UNPINNED RECREATABLE CHUNKS (lru first): Chunk 3b1f4000 sz= 4096 recreate "CCUR^3de2d7f5 " latch=(nil) Chunk 3a649000 sz= 4096 recreate "CCUR^f5a8106a " latch=(nil) Chunk 383b3000 sz= 4096 recreate "CCUR^2b855a04 " latch=(nil) Chunk 383b1000 sz= 4096 recreate "CCUR^f65cc3ed " latch=(nil) Chunk 3ee4c524 sz= 332 recreate "KGLHD " latch=(nil) Chunk 3af76da8 sz= 4096 recreate "SQLA^13df1501 " latch=(nil) Chunk 3a72e000 sz= 4096 recreate "CCUR^13df1501 " latch=(nil) Chunk 39ff96e0 sz= 332 recreate "KGLHD " latch=(nil) Chunk 3a37e740 sz= 4096 recreate "SQLA^7a9f4a60 " latch=(nil) Chunk 38330000 sz= 4096 recreate "CCUR^7a9f4a60 " latch=(nil) Chunk 39f8732c sz= 332 recreate "KGLHD " latch=(nil) Chunk 3ab0dec4 sz= 4096 recreate "SQLA^8005cf3c " latch=(nil) Chunk 3a670000 sz= 4096 recreate "CCUR^8005cf3c " latch=(nil) Chunk 3ed28b1c sz= 1524 recreate "KGLHD " latch=(nil) Chunk 39cb0d20 sz= 332 recreate "KGLHD " latch=(nil) Chunk 3aaeb214 sz= 4096 recreate "SQLA^63c15ff " latch=(nil) Chunk 3a646000 sz= 4096 recreate "CCUR^63c15ff " latch=(nil) Chunk 39ff9a38 sz= 540 recreate "KQR PO " latch=0x3ca28438 Chunk 3ee4c88c sz= 540 recreate "KQR PO " latch=0x3ca28438 Chunk 3ee4caa8 sz= 540 recreate "KQR PO " latch=0x3ca28438 Chunk 39fabd2c sz= 540 recreate "KQR PO " latch=0x3ca28438 Chunk 39fabf48 sz= 540 recreate "KQR PO " latch=0x3ca28438 Chunk 3ee481f8 sz= 540 recreate "KQR PO " latch=0x3ca28438 Chunk 3ee48414 sz= 540 recreate "KQR PO " latch=0x3ca28438 Chunk 3ee48630 sz= 540 recreate "KQR PO " latch=0x3ca28438 Chunk 3a1a1228 sz= 4096 recreate "KGLS^9b4819a8 " latch=(nil) Chunk 39b4a504 sz= 4096 recreate "KGLS^6ccccfe " latch=(nil) Chunk 3abf0fb4 sz= 4096 recreate "KGLS^8db54da " latch=(nil) Chunk 3aa65508 sz= 4096 recreate "KGLS^e2d7481e " latch=(nil) Chunk 3a2839d0 sz= 4096 recreate "KGLS^8dd845ff " latch=(nil) Chunk 3abd4f78 sz= 4096 recreate "KGLS^f79d229a " latch=(nil)
RESERVED FREE LIST
Oracle 設置了保留內存區域，該區域用 Reserved free list管理。其大小由參數shared_pool_reserved_size決定（最小為5000字節，最大不能超過shared pool的50%）。
RESERVED FREE LISTS: Reserved bucket 0 size=16 Chunk 3dfffc14 sz= 980 R-free " " Chunk 3dbffbe0 sz= 1032 R-free " " Reserved bucket 1 size=4400 Reserved bucket 2 size=8204 Reserved bucket 3 size=8460 Reserved bucket 4 size=8464 Reserved bucket 5 size=8468 Reserved bucket 6 size=8472 Reserved bucket 7 size=9296 Reserved bucket 8 size=9300 Reserved bucket 9 size=12320 Reserved bucket 10 size=12324 Reserved bucket 11 size=16396 Reserved bucket 12 size=32780 Reserved bucket 13 size=65548 Chunk 3b800050 sz= 212888 R-free " " Chunk 3bc00050 sz= 212888 R-free " " Chunk 3c000050 sz= 212888 R-free " " Chunk 3c400050 sz= 212888 R-free " " Chunk 3c800050 sz= 212888 R-free " " Chunk 3cc00050 sz= 212888 R-free " " Chunk 3d3cb8b8 sz= 214832 R-free " " Chunk 3d400050 sz= 212888 R-free " " Chunk 3e000050 sz= 212888 R-free " " Chunk 3e400050 sz= 212888 R-free " " Chunk 3e800050 sz= 212888 R-free " " Chunk 3f000050 sz= 212888 R-free " " Reserved bucket 14 size=1990630 Total reserved free space = 2558612 UNPINNED RECREATABLE CHUNKS (lru first): Chunk 3b1f4000 sz= 4096 recreate "CCUR^3de2d7f5 " latch=(nil) Chunk 3a649000 sz= 4096 recreate "CCUR^f5a8106a " latch=(nil) ......
Oracle對進入reserved free list的對象大小有限制，即只有大于_shared_pool_reserved_min_alloc隱含參數閾值（默認值4400）的cursor才能進入到reserved free list。
SHARED POOL的SUB POOL技術
從Oracle 9i開始，shared pool可以分為多個sub pool，其數量受一下幾個因素影響：
1、系統的CPU數量，默認情況下，在Oracle中每4個CPU分配一個sub pool，最多不能超過7個。
2、共享池的大小。sub pool的最小容量隨著Oracle版本不同而不同。
9i ----- 128M（Minimum subpool size）
10g<10.2.0.3 ---- 256M
10.2.0.3 and higher ---512M
3、隱含參數_kghdisdx_count值
每個sub pool擁有獨立的free list 、lru list和shared pool latch。從這個角度來講，當系統擁有足夠的內存和CPU時，將shared pool分為多個sub pool時能有效的減少shared pool latch的爭用。
查看sub pool的數量：
kghlushrpool： =1: shared pool subpools =0: java pool SQL> select count(kghluidx) num_pools 2 from x$kghlu 3 where kghlushrpool=1 4 / NUM_POOLS ---------- 1 SQL> show parameter cpu NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cpu_count integer 1
查看sub pool latch信息：
SQL> col name for a40 SQL> set linesize 120 SQL> r 1 select addr,name,gets,misses,spin_gets 2 from v$latch_children 3* where name ='shared pool' ADDR NAME GETS MISSES SPIN_GETS -------- ---------------------------------------- ---------- ---------- ---------- 200A80FC shared pool 21 0 0 200A8098 shared pool 21 0 0 200A8034 shared pool 21 0 0 200A7FD0 shared pool 21 0 0 200A7F6C shared pool 21 0 0 200A7F08 shared pool 21 0 0 200A7EA4 shared pool 493378 7 0 7 rows selected.
查看sub pool 內存分配：
X$KSMSS([K]ernal [S]torage [M]emory Management [S]GA [S]atistics (lengths of SGA objects)
SQL> select
2 'shared pool(' || nvl(decode(to_char(ksmdsidx),'0','0-unused',ksmdsidx),'Total')
3 || '):' subpool,
4 sum(ksmsslen) bytes,round(sum (ksmsslen)/1048576 ,2 ) mb
5 from x$ksmss
6 where ksmsslen >0
7 group by rollup (ksmdsidx) order by subpool asc;
SUBPOOL BYTES MB
------------------------------------------------------ ---------- ----------
shared pool(1): 121639892 116
shared pool(Total): 121639892 116SQL>
Oracle 硬解析
1、獲得shared pool latch ，從free list的bucket 中查找合適大小的free chunk。如果free list中的bucket list過長或者shared pool碎片化嚴重，那么在多個進程同時請求分配內存時，則會發生shared pool latch的爭用。
2、如果不存在大小合適的free chunk，則分割較大的free chunk，分割后的free chunk重新掛載到適當大小的bucket下。如果不存在free chunk，則檢索lru list。若在lru list中也不能獲得合適大小的bucket，則從shared pool的剩余空閑內存中分配。如果cursor大小大于_shared_pool_reserved_min_alloc隱含參數設定的閥值，那么在reserved list中尋找free chunk。若果以上過程均失敗，則出現ORA-4031錯誤。
3、若找到合適大小的chunk，則對cursor相應的handle（library cache handle）以exclusive 模式獲得library cache lock，并創建LCO信息。在創建LCO信息后，library cache lock變換為null模式，然后以exclusive模式獲得library cache pin，并創建執行計劃等信息。硬解析成功后Oracle增加parse count（hard）統計值。
4、對sql cursor已shared模式獲得library cache lock和library cache pin，并執行sql，這個階段稱之為執行階段。
5、sql cursor執行結束后進入fetch階段。在fetch階段，sql cursor將library cache pin變為null模式，并釋放library cache pin。
軟軟解析
由于在軟解析過程中需要獲得library cache latch，所以在高并發軟解析的系統中，依然會出現與latch：library cache相關的等待事件，從而導致性能緩慢。
軟軟解析（Tom Kety）核心原理是通過設置session_cache_cursors參數將某個會話中常用的sql放入UGA的會話緩存區中，當會話發起相同的sql時，可以快速的從UGA取得cursor的信息，從而減少共享池的爭用。當一個cursor被解析3次以上（包括3次）就會被放入到UG會話緩存區中。
案例：shared pool latch 爭用
案例1：
業務運行前：
17:07:30 SYS@ prod>select name,GETS,MISSES from v$latch where upper(name) like '%LIBRARY%' OR upper(name) like '%SHARE%';
NAME GETS MISSES
---------------------------------------------------------------- ---------- ----------
test shared non-parent l0 0 0
ksxp shared latch 0 0
kcfis stats shared latch 0 0
shared pool 126676 61
library cache load lock 0 0
shared pool simulator 6576 0
shared pool sim alloc 45 0
Shared B-Tree 302 0
shared server configuration 6 0
shared server info 1 0
運行業務：
17:08:34 SCOTT@ prod>begin
17:08:38 2 for i in 1..100000 loop
17:08:52 3 execute immediate 'insert into t1 values ('||i||')';
17:09:18 4 end loop;
17:09:26 5 end;
17:09:27 6 /
PL/SQL procedure successfully completed.
業務運行后：
17:11:05 SYS@ prod>select name,GETS,MISSES from v$latch where upper(name) like '%LIBRARY%' OR upper(name) like '%SHARE%'
NAME GETS MISSES
---------------------------------------------------------------- ---------- ----------
test shared non-parent l0 0 0
ksxp shared latch 0 0
kcfis stats shared latch 0 0
shared pool 4526672 214
library cache load lock 0 0
shared pool simulator 1086437 0
shared pool sim alloc 2048 0
Shared B-Tree 316 0
shared server configuration 6 0
shared server info 1 0
10 rows selected.
17:15:42 SYS@ prod>select sid,event,WAIT_TIME,state from v$session_wait where sid=42
SID EVENT WAIT_TIME STATE
---------- ---------------------------------------------------------------- ---------- -------------------
42 latch: shared pool -1 WAITED SHORT TIME
Elapsed: 00:00:00.08
案例2：
業務運行前：
17:18:35 SYS@ prod>select sid,EVENT,TOTAL_WAITS,AVERAGE_WAIT from v$session_event where sid in (42,46);
SID EVENT TOTAL_WAITS AVERAGE_WAIT
---------- ---------------------------------------------------------------- ----------- ------------
42 Disk file operations I/O 4 .03
42 log file switch (private strand flush incomplete) 1 10.03
42 log file sync 4 1.76
42 db file sequential read 385 .23
42 latch: row cache objects 5 .44
42 latch: shared pool 194 .25
42 SQL*Net message to client 24 0
42 SQL*Net message from client 23 5318.9
42 SQL*Net break/reset to client 2 .08
42 events in waitclass Other 1 0
46 Disk file operations I/O 1 .03
46 db file sequential read 33 .02
46 SQL*Net message to client 13 0
46 SQL*Net message from client 12 79.9
14 rows selected.
運行業務：
17:16:39 SYS@ prod>select sid ,username from v$session where username is not null;
SID USERNAME
---------- ------------------------------
1 SYS
42 SCOTT
46 HR
17:17:22 SCOTT@ prod>begin
17:20:46 2 for i in 1..100000 loop
17:20:52 3 execute immediate 'insert into t1 values ('||i||')';
17:20:58 4 end loop;
17:21:02 5 end;
17:21:05 6 /
PL/SQL procedure successfully completed.
17:17:42 HR@ prod>begin
17:21:16 2 for i in 1..100000 loop
17:21:24 3 execute immediate 'insert into scott.t1 values ('||i||')';
17:21:49 4 end loop;
17:21:51 5 end;
17:21:52 6 /
PL/SQL procedure successfully completed.
業務運行后：
17:22:32 SYS@ prod>select sid,EVENT,TOTAL_WAITS,AVERAGE_WAIT from v$session_event where sid in (42,46);
SID EVENT TOTAL_WAITS AVERAGE_WAIT
---------- ---------------------------------------------------------------- ----------- ------------
42 Disk file operations I/O 4 .03
42 latch: cache buffers chains 16 .18
42 buffer busy waits 2 .15
42 log file switch (private strand flush incomplete) 1 10.03
42 log file sync 4 1.76
42 db file sequential read 413 .21
42 latch: row cache objects 58 .13
42 latch: shared pool 1008 .19
42 library cache: mutex X 123 .33
42 SQL*Net message to client 24 0
42 SQL*Net message from client 24 6044.43
42 SQL*Net break/reset to client 2 .08
42 events in waitclass Other 87 .09
46 Disk file operations I/O 3 .03
46 latch: cache buffers chains 13 .21
46 buffer busy waits 1 .35
46 latch: redo copy 1 1.26
SID EVENT TOTAL_WAITS AVERAGE_WAIT
---------- ---------------------------------------------------------------- ----------- ------------
46 db file sequential read 38 .02
46 enq: HW - contention 1 .01
46 latch: row cache objects 58 .14
46 row cache lock 1 .08
46 latch: shared pool 666 .17
46 library cache: mutex X 99 .29
46 SQL*Net message to client 13 0
46 SQL*Net message from client 13 2010.63
46 events in waitclass Other 68 .14
26 rows selected.
Elapsed: 00:00:00.37
17:22:42 SYS@ prod>
17:22:02 SYS@ prod>select sid,event,WAIT_TIME,state from v$session_wait where sid=42
17:22:25 2 or sid=46;
SID EVENT WAIT_TIME STATE
---------- ---------------------------------------------------------------- ---------- -------------------
42 latch: shared pool -1 WAITED SHORT TIME
46 latch: shared pool -1 WAITED SHORT TIME看完了這篇文章，相信你對“Oracle Share Pool內部管理機制的示例分析”有了一定的了解，如果想了解更多相關知識，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
