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

Oracle shared pool可以分为很多内存区域，其中最有代表性的是library cache和row cache。通过查询v$sgastat，可以看到shared pool内存区域的使用情况：

引用

SQL> select count(*) from v$sgastat where pool='shared pool';

  COUNT(*)
----------
       612

进一步，亦可查看在shared pool中分配前20位内存区域：

引用

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

shared pool从结构上可以大体分为以下几类：
1、Permanent area，在实例启动时即分配，与实例的寿命相同，主要包含processes，sessions，segmented arrays等结构体。

引用

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.

2、Segmented Arrays主要包含段的enqueues (locks)、enqueue resources (resources)、transactions、transaction branches。可以通过X$KSMDD查询Segmented Arrays的相关信息。

引用

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
...

3、Row Cache亦叫dictionary cache主要存放数据字典信息，可以通过v$rowcache获得详细信息

引用

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
。。。

4、Reserved Area，主要用于大的连续的内存分配，主要是为了防止shared pool内存分裂，只有当shared pool内存不可用时，才会启用。Reserved Area内存大小主要受以下参数影响

引用

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

亦可以查询数据字典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查询：

引用

SQL> select distinct KSMCHCLS from x$ksmsp;

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

6 rows selected.

我们可以通过dump观察堆的物理结构

引用

SQL> alter session set events 'immediate trace name heapdump level 2';

Session altered

从trace跟踪文件，可以找到以下内容：

引用

******************************************************
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 "  ds=0x3079b328<==副堆指针
...
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

Oracle 在shared pool分配内存的顺序为：检索空闲列-->检索LRU列-->检索内存剩余空间。
这里需要注意的是即使还有足够多的剩余空间，因为内存碎片化，还是可能还会出现ORA-4031错误。
在Oracle10g中允许有多个sub shared pool，由隐含参数_kghdsidx_count决定，当前系统该参数值为

引用

NAME                                          VALUE                PDESC
--------------------------------------------- -------------------- --------------------------------------------------
_kghdsidx_count                               1                    max kghdsidx count

每个sub shared pool使用独立的shared_pool latch：

引用

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

每个sub  shared pool有4个子分区（heap）：

引用

[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

利用以下命令能转储副堆：

引用

SQL> alter session set events 'immediate trace name heapdump_addr addr 0x3079b328';

Session altered.

查看跟踪文件

引用

******************************************************
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

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