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, image: 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
......