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>selectname,bytes/1024/1024fromv$sgainfo;
NAMEBYTES/1024/1024
-----------------------------------------------
FixedSGASize1.27566528
RedoBuffers5.59765625
BufferCacheSize180
SharedPoolSize104
LargePoolSize4
JavaPoolSize4
StreamsPoolSize0
SharedIOPoolSize0
GranuleSize4
MaximumSGASize498.875
StartupoverheadinSharedPool52
NAMEBYTES/1024/1024
-----------------------------------------------
FreeSGAMemoryAvailable200
12rowsselected.
[oracle@node1~]$more/u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_14983.trc
Tracefile/u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_14983.trc
OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production
WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
Systemname:Linux
Nodename:node1
Release:2.6.18-194.el5
Version:#1SMPTueMar1621:52:43EDT
Machine:i686
Instancename:prod
Redothreadmountedbythisinstance:1
Oracleprocessnumber:32
Unixprocesspid:14983,p_w_picpath:oracle@node1(TNSV1-V3)
***-07-0214:58:47.532
***SESSIONID:(32.758)-07-0214:58:47.532
***CLIENTID:()-07-0214:58:47.532
***SERVICENAME:(SYS$USERS)-07-0214:58:47.532
***MODULENAME:(sqlplus@node1(TNSV1-V3))-07-0214:58:47.532
***ACTIONNAME:()-07-0214:58:47.532
KGHLatchDirectoryInformation
ldirstate:2lastallocatedslot:99
Slot[1]Latch:0x200065ecIndex:1Flags:3State:2next:(nil)
Slot[2]Latch:0x3e75571cIndex:1Flags:3State:2next:(nil)
......(省略)
Slot[99]Latch:0x2002616cIndex:1Flags:3State:2next:0x1
******************************************************
HEAPDUMPheapname="sgaheap"desc=0x200010b4
extentsz=0x7ad4alt=124het=32767rec=9flg=-126opc=0
parent=(nil)owner=(nil)nex=(nil)xsz=0x0heap=(nil)
fl2=0x60,nex=(nil)
dsforlatch1:0x2002a9900x2002b5c80x2002c2000x2002ce38
reservedgranulecount0(granulesize4194304)
******************************************************
HEAPDUMPheapname="sgaheap(1,0)"desc=0x2002a990
extentsz=0xfc4alt=124het=32767rec=9flg=-126opc=0
parent=(nil)owner=(nil)nex=(nil)xsz=0x400000heap=(nil)
fl2=0x20,nex=(nil)
latchset1of1
durationsenabledforthisheap
reservedgranulesforroot0(granulesize4194304)
EXTENT0addr=0x3b800000
Chunk3b800038sz=24R-freeable"reservedstoppe"
Chunk3b800050sz=212888R-free""
Chunk3b833fe8sz=24R-freeable"reservedstoppe"
Chunk3b834000sz=3981312perm"perm"alo=3290272
EXTENT1addr=0x3bc00000
Chunk3bc00038sz=24R-freeable"reservedstoppe"
Chunk3bc00050sz=212888R-free""
Chunk3bc33fe8sz=24R-freeable"reservedstoppe"
Chunk3bc34000sz=3510272perm"perm"alo=3510272
Chunk3bf8d000sz=465920perm"perm"alo=465920
Chunk3bffec00sz=5120free""
EXTENT2addr=0x3c000000
Chunk3c000038sz=24R-freeable"reservedstoppe"
Chunk3c000050sz=212888R-free""
Chunk3c033fe8sz=24R-freeable"reservedstoppe"
Chunk3c034000sz=3850176perm"perm"alo=3850176
Chunk3c3dffc0sz=131088perm"perm"alo=131088
Chunk3c3fffd0sz=48free""
EXTENT3addr=0x3c400000
Chunk3c400038sz=24R-freeable"reservedstoppe"
Chunk3c400050sz=212888R-free""
Chunk3c433fe8sz=24R-freeable"reservedstoppe"
Chunk3c434000sz=3980368perm"perm"alo=3980368
Chunk3c7ffc50sz=944free""
EXTENT4addr=0x3c800000
Chunk3c800038sz=24R-freeable"reservedstoppe"
Chunk3c800050sz=212888R-free""
Chunk3c833fe8sz=24R-freeable"reservedstoppe"
Chunk3c834000sz=3672700perm"perm"alo=3672700
Chunk3cbb4a7csz=308240perm"perm"alo=308240
Chunk3cbffe8csz=372free""
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错误FREELISTS:
Bucket0size=16
Bucket1size=20
Bucket2size=24
Bucket3size=28
Bucket4size=32
Bucket5size=36
Bucket6size=40
Bucket7size=44
Bucket8size=48
Chunk3c3fffd0sz=48free""
Chunk3ebfffd0sz=48free""
Bucket9size=52
Bucket10size=56
Bucket11size=60
Bucket12size=64
Bucket13size=68
Bucket14size=72
Bucket15size=76
Bucket16size=80
Bucket17size=84
Bucket18size=88
Chunk3e7fffa8sz=88free""
Bucket19size=92
Bucket20size=96
Bucket21size=100
Bucket22size=104
Bucket23size=108
Bucket24size=112
Bucket25size=116
Bucket26size=120
Bucket27size=124
......
LRU LIST
Lru list主要保存着当前未使用而且可以重建的chunk。当会话在free list中找不到空闲的chunk时，在会在lru list中寻找。UNPINNEDRECREATABLECHUNKS(lrufirst):
Chunk3b1f4000sz=4096recreate"CCUR^3de2d7f5"latch=(nil)
Chunk3a649000sz=4096recreate"CCUR^f5a8106a"latch=(nil)
Chunk383b3000sz=4096recreate"CCUR^2b855a04"latch=(nil)
Chunk383b1000sz=4096recreate"CCUR^f65cc3ed"latch=(nil)
Chunk3ee4c524sz=332recreate"KGLHD"latch=(nil)
Chunk3af76da8sz=4096recreate"SQLA^13df1501"latch=(nil)
Chunk3a72e000sz=4096recreate"CCUR^13df1501"latch=(nil)
Chunk39ff96e0sz=332recreate"KGLHD"latch=(nil)
Chunk3a37e740sz=4096recreate"SQLA^7a9f4a60"latch=(nil)
Chunk38330000sz=4096recreate"CCUR^7a9f4a60"latch=(nil)
Chunk39f8732csz=332recreate"KGLHD"latch=(nil)
Chunk3ab0dec4sz=4096recreate"SQLA^8005cf3c"latch=(nil)
Chunk3a670000sz=4096recreate"CCUR^8005cf3c"latch=(nil)
Chunk3ed28b1csz=1524recreate"KGLHD"latch=(nil)
Chunk39cb0d20sz=332recreate"KGLHD"latch=(nil)
Chunk3aaeb214sz=4096recreate"SQLA^63c15ff"latch=(nil)
Chunk3a646000sz=4096recreate"CCUR^63c15ff"latch=(nil)
Chunk39ff9a38sz=540recreate"KQRPO"latch=0x3ca28438
Chunk3ee4c88csz=540recreate"KQRPO"latch=0x3ca28438
Chunk3ee4caa8sz=540recreate"KQRPO"latch=0x3ca28438
Chunk39fabd2csz=540recreate"KQRPO"latch=0x3ca28438
Chunk39fabf48sz=540recreate"KQRPO"latch=0x3ca28438
Chunk3ee481f8sz=540recreate"KQRPO"latch=0x3ca28438
Chunk3ee48414sz=540recreate"KQRPO"latch=0x3ca28438
Chunk3ee48630sz=540recreate"KQRPO"latch=0x3ca28438
Chunk3a1a1228sz=4096recreate"KGLS^9b4819a8"latch=(nil)
Chunk39b4a504sz=4096recreate"KGLS^6ccccfe"latch=(nil)
Chunk3abf0fb4sz=4096recreate"KGLS^8db54da"latch=(nil)
Chunk3aa65508sz=4096recreate"KGLS^e2d7481e"latch=(nil)
Chunk3a2839d0sz=4096recreate"KGLS^8dd845ff"latch=(nil)
Chunk3abd4f78sz=4096recreate"KGLS^f79d229a"latch=(nil)
RESERVED FREE LIST
Oracle 设置了保留内存区域，该区域用 Reserved free list管理。其大小由参数shared_pool_reserved_size决定(最小为5000字节，最大不能超过shared pool的50%)。RESERVEDFREELISTS:
Reservedbucket0size=16
Chunk3dfffc14sz=980R-free""
Chunk3dbffbe0sz=1032R-free""
Reservedbucket1size=4400
Reservedbucket2size=8204
Reservedbucket3size=8460
Reservedbucket4size=8464
Reservedbucket5size=8468
Reservedbucket6size=8472
Reservedbucket7size=9296
Reservedbucket8size=9300
Reservedbucket9size=12320
Reservedbucket10size=12324
Reservedbucket11size=16396
Reservedbucket12size=32780
Reservedbucket13size=65548
Chunk3b800050sz=212888R-free""
Chunk3bc00050sz=212888R-free""
Chunk3c000050sz=212888R-free""
Chunk3c400050sz=212888R-free""
Chunk3c800050sz=212888R-free""
Chunk3cc00050sz=212888R-free""
Chunk3d3cb8b8sz=214832R-free""
Chunk3d400050sz=212888R-free""
Chunk3e000050sz=212888R-free""
Chunk3e400050sz=212888R-free""
Chunk3e800050sz=212888R-free""
Chunk3f000050sz=212888R-free""
Reservedbucket14size=1990630
Totalreservedfreespace=2558612
UNPINNEDRECREATABLECHUNKS(lrufirst):
Chunk3b1f4000sz=4096recreate"CCUR^3de2d7f5"latch=(nil)
Chunk3a649000sz=4096recreate"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:sharedpoolsubpools=0:javapool
SQL>selectcount(kghluidx)num_pools
2fromx$kghlu
3wherekghlushrpool=1
4/
NUM_POOLS
----------
1
SQL>showparametercpu
NAMETYPEVALUE
-----------------------------------------------------------------------------
cpu_countinteger1
查看sub pool latch信息：SQL>colnamefora40
SQL>setlinesize120
SQL>r
1selectaddr,name,gets,misses,spin_gets
2fromv$latch_children
3*wherename='sharedpool'
ADDRNAMEGETSMISSESSPIN_GETS
------------------------------------------------------------------------------
200A80FCsharedpool2100
200A8098sharedpool2100
200A8034sharedpool2100
200A7FD0sharedpool2100
200A7F6Csharedpool2100
200A7F08sharedpool2100
200A7EA4sharedpool49337870
7rowsselected.
查看sub pool 内存分配：
X$KSMSS([K]ernal [S]torage [M]emory Management [S]GA [S]atistics (lengths of SGA objects)SQL>select
2'sharedpool('||nvl(decode(to_char(ksmdsidx),'0','0-unused',ksmdsidx),'Total')
3||'):'subpool,
4sum(ksmsslen)bytes,round(sum(ksmsslen)/1048576,2)mb
5fromx$ksmss
6whereksmsslen>0
7groupbyrollup(ksmdsidx)orderbysubpoolasc;
SUBPOOLBYTESMB
--------------------------------------------------------------------------
sharedpool(1):121639892116
sharedpool(Total):121639892116
SQL>
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:30SYS@prod>selectname,GETS,MISSESfromv$latchwhereupper(name)like'%LIBRARY%'ORupper(name)like'%SHARE%';
NAMEGETSMISSES
------------------------------------------------------------------------------------
testsharednon-parentl000
ksxpsharedlatch00
kcfisstatssharedlatch00
sharedpool12667661
librarycacheloadlock00
sharedpoolsimulator65760
sharedpoolsimalloc450
SharedB-Tree3020
sharedserverconfiguration60
sharedserverinfo10
运行业务：
17:08:34SCOTT@prod>begin
17:08:382foriin1..100000loop
17:08:523executeimmediate'insertintot1values('||i||')';
17:09:184endloop;
17:09:265end;
17:09:276/
PL/SQLproceduresuccessfullycompleted.
业务运行后：
17:11:05SYS@prod>selectname,GETS,MISSESfromv$latchwhereupper(name)like'%LIBRARY%'ORupper(name)like'%SHARE%'
NAMEGETSMISSES
------------------------------------------------------------------------------------
testsharednon-parentl000
ksxpsharedlatch00
kcfisstatssharedlatch00
sharedpool4526672214
librarycacheloadlock00
sharedpoolsimulator10864370
sharedpoolsimalloc20480
SharedB-Tree3160
sharedserverconfiguration60
sharedserverinfo10
10rowsselected.
17:15:42SYS@prod>selectsid,event,WAIT_TIME,statefromv$session_waitwheresid=42
SIDEVENTWAIT_TIMESTATE
-------------------------------------------------------------------------------------------------------
42latch:sharedpool-1WAITEDSHORTTIME
Elapsed:00:00:00.08
案例2：
业务运行前：
17:18:35SYS@prod>selectsid,EVENT,TOTAL_WAITS,AVERAGE_WAITfromv$session_eventwheresidin(42,46);
SIDEVENTTOTAL_WAITSAVERAGE_WAIT
-------------------------------------------------------------------------------------------------
42DiskfileoperationsI/O4.03
42logfileswitch(privatestrandflushincomplete)110.03
42logfilesync41.76
42dbfilesequentialread385.23
42latch:rowcacheobjects5.44
42latch:sharedpool194.25
42SQL*Netmessagetoclient240
42SQL*Netmessagefromclient235318.9
42SQL*Netbreak/resettoclient2.08
42eventsinwaitclassOther10
46DiskfileoperationsI/O1.03
46dbfilesequentialread33.02
46SQL*Netmessagetoclient130
46SQL*Netmessagefromclient1279.9
14rowsselected.
运行业务：
17:16:39SYS@prod>selectsid,usernamefromv$sessionwhereusernameisnotnull;
SIDUSERNAME
----------------------------------------
1SYS
42SCOTT
46HR
17:17:22SCOTT@prod>begin
17:20:462foriin1..100000loop
17:20:523executeimmediate'insertintot1values('||i||')';
17:20:584endloop;
17:21:025end;
17:21:056/
PL/SQLproceduresuccessfullycompleted.
17:17:42HR@prod>begin
17:21:162foriin1..100000loop
17:21:243executeimmediate'insertintoscott.t1values('||i||')';
17:21:494endloop;
17:21:515end;
17:21:526/
PL/SQLproceduresuccessfullycompleted.
业务运行后：
17:22:32SYS@prod>selectsid,EVENT,TOTAL_WAITS,AVERAGE_WAITfromv$session_eventwheresidin(42,46);
SIDEVENTTOTAL_WAITSAVERAGE_WAIT
-------------------------------------------------------------------------------------------------
42DiskfileoperationsI/O4.03
42latch:cachebufferschains16.18
42bufferbusywaits2.15
42logfileswitch(privatestrandflushincomplete)110.03
42logfilesync41.76
42dbfilesequentialread413.21
42latch:rowcacheobjects58.13
42latch:sharedpool1008.19
42librarycache:mutexX123.33
42SQL*Netmessagetoclient240
42SQL*Netmessagefromclient246044.43
42SQL*Netbreak/resettoclient2.08
42eventsinwaitclassOther87.09
46DiskfileoperationsI/O3.03
46latch:cachebufferschains13.21
46bufferbusywaits1.35
46latch:redocopy11.26
SIDEVENTTOTAL_WAITSAVERAGE_WAIT
-------------------------------------------------------------------------------------------------
46dbfilesequentialread38.02
46enq:HW-contention1.01
46latch:rowcacheobjects58.14
46rowcachelock1.08
46latch:sharedpool666.17
46librarycache:mutexX99.29
46SQL*Netmessagetoclient130
46SQL*Netmessagefromclient13.63
46eventsinwaitclassOther68.14
26rowsselected.
Elapsed:00:00:00.37
17:22:42SYS@prod>
17:22:02SYS@prod>selectsid,event,WAIT_TIME,statefromv$session_waitwheresid=42
17:22:252orsid=46;
SIDEVENTWAIT_TIMESTATE
-------------------------------------------------------------------------------------------------------
42latch:sharedpool-1WAITEDSHORTTIME
46latch:sharedpool-1WAITEDSHORTTIME
----以上部分内容整理自《Oracle DBA实战攻略》 周亮著，感谢作者的辛勤付出！
