Oracle 内存区域之shared pool结构解析

Oracle shared pool可以分为很多内存区域，其中最有代表性的是library cache和row cache。通过查询v$sgastat，可以看到shared pool内存区域的使用情况：
[quote]SQL> select count(*) from v$sgastat where pool='shared pool';

COUNT(*)
----------
612[/quote]
进一步，亦可查看在shared pool中分配前20位内存区域：
[quote]SQL> select * from
2 (select name,bytes from v$sgastat where pool='shared pool'
3 order by bytes desc)
4 where rownum<20;

NAME BYTES
-------------------------- ----------
free memory 23073268
sql area 15482384
ASH buffers 6375344
library cache 4438560
KCB Table Scan Buffer 4198400
KSFD SGA I/O b 4190228
row cache 3755444
KGLS heap 3527020
CCursor 3444716
krbmror 2619936
KQR M PO 2241092

NAME BYTES
-------------------------- ----------
kglsim hash table bkts 2097152
PCursor 1945104
PL/SQL MPCODE 1881664
Heap0: KGL 1628636
event statistics per sess 1370880
PL/SQL DIANA 1327028
KTI-UNDO 1235304
private strands 1198080[/quote]

shared pool从结构上可以大体分为以下几类：
1、Permanent area，在实例启动时即分配，与实例的寿命相同，主要包含processes，sessions，segmented arrays等结构体。
[quote]SQL> SELECT ksmchptr, ksmchsiz
2 FROM x$ksmsp
3 WHERE ksmchcls = 'perm';

KSMCHPTR KSMCHSIZ
-------- ----------
32FD8FE0 157812
32867000 7806944
31FF8498 29372
31FED6A8 44528
31F6D634 524404
31867000 7366196
317B0A4C 324368
3179D2DC 79728
31067000 7561948
30FFCC44 11776
30867000 7953476

KSMCHPTR KSMCHSIZ
-------- ----------
30067000 7966424
26FC2000 253736
26800038 8134600

14 rows selected.[/quote]

2、Segmented Arrays主要包含段的enqueues (locks)、enqueue resources (resources)、transactions、transaction branches。可以通过X$KSMDD查询Segmented Arrays的相关信息。
[quote]SQL> select name,heap,sum(numchunks) from X$KSMDD
2 group by name,heap;

NAME HEAP SUM(NUMCHUNKS)
--------------------------------------------- -------- --------------
segmented arrays 2000002C 2
call 2000002C 10
ksir State Object 2000002C 1
UNDO INFO SEGMENTED ARRAY 2000002C 12
KTCN: Hash Table Segmented Arr 2000002C 1
DISPATCHERS INFO 2000002C 1
...[/quote]
3、Row Cache亦叫dictionary cache主要存放数据字典信息，可以通过v$rowcache获得详细信息
[quote]SQL> select parameter,gets,getmisses from v$rowcache
2 order by getmisses desc;

PARAMETER GETS GETMISSES
-------------------------------- ---------- ----------
dc_histogram_defs 55526 22353
dc_histogram_data 29884 8743
dc_segments 112406 4761
dc_objects 27461 2984
dc_object_ids 47894 2911
。。。[/quote]
4、Reserved Area，主要用于大的连续的内存分配，主要是为了防止shared pool内存分裂，只有当shared pool内存不可用时，才会启用。Reserved Area内存大小主要受以下参数影响
[quote]NAME VALUE PDESC
--------------------------------------------- -------------------- --------------------------------------------------
shared_pool_reserved_size 2936012 size in bytes of reserved area of shared pool
_shared_pool_reserved_pct 5 percentage memory of the shared pool allocated for
_shared_pool_reserved_min_alloc 4400 minimum allocation size in bytes for reserved area[/quote]
亦可以查询数据字典V$SHARED_POOL_RESERVED查看内存分配情况。
shared pool内存分配主要通过堆（heap）来管理。简单介绍一下heap的基本知识：
1、一般情况下Oracle shared pool都有一个高级堆，高级堆下面允许有多个副堆，高级堆和副堆结构体大致相同，从堆的观点来说，属于堆的一个chunk起到副堆的指针作用。从Oracle 9i开始允许有多个高级堆。从Oracle 10g开始一个高级堆允许由多个缓冲区。
2、堆由一个或者多个extent组成，每个extent物理上使用一个granule，granule由物理连续分配的内存组成，granule大小可以通过隐含参数_ksmg_granule_size查看。
3、extent由一个或者多个chunks组成，状态主要分为permanent（永久，不可再生），recreatable（可再生），freeable（只有在session或者call期间保存必要的对象），free（可马上使用）。
其状态可以由内部表x$ksmsp查询：
[quote]SQL> select distinct KSMCHCLS from x$ksmsp;

KSMCHCLS
--------
recr
freeabl
R-freea
perm
R-free
free

6 rows selected.[/quote]
我们可以通过dump观察堆的物理结构
[quote]SQL> alter session set events 'immediate trace name heapdump level 2';

Session altered[/quote]
从trace跟踪文件，可以找到以下内容：
[quote]******************************************************
HEAP DUMP heap name="sga heap" desc=0x2000002c
extent sz=0x32c8 alt=108 het=32767 rec=9 flg=-126 opc=0
parent=(nil) owner=(nil) nex=(nil) xsz=0xf60
******************************************************
HEAP DUMP heap name="sga heap(1,0)" desc=0x2001ad40<==高级堆的子缓冲区
extent sz=0xfc4 alt=108 het=32767 rec=9 flg=-126 opc=0
parent=(nil) owner=(nil) nex=(nil) xsz=0x800000
EXTENT 0 addr=0x26800000<==extent，可以看到由多个chunk组成
Chunk 26800038 sz= 8134600 perm "perm " alo=255796<==chunk，可以有多种状态
Chunk 26fc2000 sz= 253736 perm "perm " alo=253736
Chunk 26ffff28 sz= 216 free " "<==free chunk在free list中会显式标记
EXTENT 1 addr=0x30000000
Chunk 30000038 sz= 24 R-freeable "reserved stoppe"
Chunk 30000050 sz= 421784 R-free " "
Chunk 30066fe8 sz= 24 R-freeable "reserved stoppe"
Chunk 30067000 sz= 7966424 perm "perm " alo=7966424
Chunk 307ffed8 sz= 296 free " "
...
EXTENT 5 addr=0x32800000
Chunk 32800038 sz= 24 R-freeable "reserved stoppe"
Chunk 32800050 sz= 421784 R-free " "
Chunk 32866fe8 sz= 24 R-freeable "reserved stoppe"
Chunk 32867000 sz= 7806944 perm "perm " alo=7806944
Chunk 32fd8fe0 sz= 157812 perm "perm " alo=157812
Chunk 32fff854 sz= 1964 free " "
Total heap size = 50331312
FREE LISTS:<==空闲列，可以明显看出bucket大小分配的规律，共有255个buckets，从16bytes到64k，为止。采用此方法分配内存，可以有效的减少内存碎片。每个Bucket之间都用double linked 相互连接，
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
Bucket 9 size=52
Bucket 10 size=56
...
Bucket 50 size=216
Chunk 26ffff28 sz= 216 free " "<==和extent区域对应
...
Bucket 51 size=220
Bucket 52 size=224
Bucket 250 size=12320
Bucket 251 size=12324
Bucket 252 size=16396
Bucket 253 size=32780
Bucket 254 size=65548
Total free space = 6840
RESERVED FREE LISTS:<==预约空闲列
Reserved bucket 0 size=16
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 30000050 sz= 421784 R-free " "
Chunk 30800050 sz= 421784 R-free " "
Chunk 31000050 sz= 421784 R-free " "
Chunk 31800050 sz= 421784 R-free " "
Chunk 32800050 sz= 421784 R-free " "
Chunk 322b9ba8 sz= 4144 freeable "KGSKI schedule " [color=red]ds=0x3079b328[/color]<==副堆指针
...
Total reserved free space = 2108920
UNPINNED RECREATABLE CHUNKS (lru first):《==LRU列：空闲列用尽，则用LRU列
Chunk 267e8b08 sz= 796 recreate "KGL handles " latch=0x308f0de4
Chunk 3211faf8 sz= 284 recreate "KQR SO " latch=0x31615484
Chunk 3211f9dc sz= 284 recreate "KQR SO " latch=0x31615484
Chunk 322443e4 sz= 284 recreate "KQR SO " latch=0x31615484
Chunk 32256980 sz= 284 recreate "KQR SO " latch=0x31615484
Chunk 32256cb8 sz= 284 recreate "KQR SO " latch=0x31615484
...
Unpinned space = 10522800 rcr=4745 trn=5763
PERMANENT CHUNKS:<==permanent chunks
Chunk 26800038 sz= 8134600 perm "perm " alo=255796
Chunk 26fc2000 sz= 253736 perm "perm " alo=253736
Chunk 32fd8fe0 sz= 157812 perm "perm " alo=157812
Chunk 30ffcc44 sz= 11776 perm "perm " alo=11776
Chunk 317b0a4c sz= 324368 perm "perm " alo=324368
Chunk 31ff8498 sz= 29372 perm "perm " alo=29372
Chunk 30067000 sz= 7966424 perm "perm " alo=7966424
Chunk 3179d2dc sz= 79728 perm "perm " alo=79728
Chunk 31fed6a8 sz= 44528 perm "perm " alo=44528
Chunk 30867000 sz= 7953476 perm "perm " alo=7953476
Chunk 31f6d634 sz= 524404 perm "perm " alo=524404
Chunk 31067000 sz= 7561948 perm "perm " alo=7561948
Chunk 31867000 sz= 7366196 perm "perm " alo=7366196
Chunk 32867000 sz= 7806944 perm "perm " alo=7806944
Permanent space = 48215312
[/quote]
Oracle 在shared pool分配内存的顺序为：检索空闲列-->检索LRU列-->检索内存剩余空间。
这里需要注意的是即使还有足够多的剩余空间，因为内存碎片化，还是可能还会出现ORA-4031错误。
在Oracle10g中允许有多个sub shared pool，由隐含参数_kghdsidx_count决定，当前系统该参数值为
[quote]
NAME VALUE PDESC
--------------------------------------------- -------------------- --------------------------------------------------
_kghdsidx_count 1 max kghdsidx count[/quote]
每个sub shared pool使用独立的shared_pool latch：
[quote]SQL> select name,gets,misses,sleeps from v$latch_children
2 where name='shared pool';

NAME GETS MISSES SLEEPS
--------------------------------------------- ---------- ---------- ----------
shared pool 1269146 217 31
shared pool 21 0 0
shared pool 21 0 0
shared pool 21 0 0
shared pool 21 0 0
shared pool 21 0 0
shared pool 21 0 0[/quote]
每个sub shared pool有4个子分区（heap）：
[quote][ora10g@mcprod udump]$ grep "sga heap" mcstar_ora_25506.trc
HEAP DUMP heap name="sga heap" desc=0x2000002c
HEAP DUMP heap name="sga heap(1,0)" desc=0x2001ad40
HEAP DUMP heap name="sga heap(1,1)" desc=0x2001b978
HEAP DUMP heap name="sga heap(1,2)" desc=0x2001c5b0
HEAP DUMP heap name="sga heap(1,3)" desc=0x2001d1e8[/quote]

利用以下命令能转储副堆：
[quote]SQL> alter session set events 'immediate trace name heapdump_addr addr 0x3079b328';

Session altered.[/quote]
查看跟踪文件
[quote]******************************************************
HEAP DUMP heap name="KGSKI schedule" desc=0x3079b328
extent sz=0x1024 alt=32767 het=32767 rec=9 flg=2 opc=0
parent=0x2000002c owner=(nil) nex=(nil) xsz=0x1024<==最高堆指针
EXTENT 0 addr=0x322b9bb4
Chunk 322b9bbc sz= 4124 perm "perm " alo=2740
EXTENT 1 addr=0x322bbb3c
Chunk 322bbb44 sz= 3456 perm "perm " alo=3456
Chunk 322bc8c4 sz= 652 free " "
Total heap size = 8232
FREE LISTS:
Bucket 0 size=0
Chunk 322bc8c4 sz= 652 free " "
Total free space = 652
UNPINNED RECREATABLE CHUNKS (lru first):
PERMANENT CHUNKS:
Chunk 322b9bbc sz= 4124 perm "perm " alo=2740
Chunk 322bbb44 sz= 3456 perm "perm " alo=3456
Permanent space = 7580[/quote]

欲从shared pool分配内存的进程必须获得shared pool latch，如发生硬解析等情况，且必须在分配内存过程中全程拥有，在隐含参数_kghdsidx_count=1的情况下，实际系统可用锁存器只有一个。因此在多个进程在同时获取shared pool内存时，将会引起争用，具体表现为latch:shared pool等待事件。