[AD]共享池历程概述

一，Shared Pool 的基本原理
1，SharedPool是Oracle SGA设置中最复杂也是最重要的一部分内容，Oracle通过Shared Pool来实现SQL共享、减少代码硬解析，从而提高数据库的性能。在某些版本中，如果设置不当，SharedPool可能会极大地影响数据库的正常运行。
2，在Oracle 7i之前，Shared Pool并不存在，每个Oracle连接都有一个独立的Server进程与之相关联，Server进程负责解析、优化所有SQL和PL/SQL代码。典型地，在OLTP环境中，很多代码具有相同或类似的结构，反复的独立解析浪费了大量的时间和资源，Oracle最终认识到这个问题，并且从PL/SQL开始尝试把这部分可共享的内容进行独立存储和管理，于是SharedPool作为一个独立的SGA组件开始被引入，并且其功能和作用被逐渐的完善和发展起来。
3，在这里注意到：SharedPool最初被引入的目的，也就是它的本质功能是实现共享。如果你的系统代码完全异构（假如你的代码从不绑定变量，从不反复执行），那么你会发现，这个时候SharedPool完全成为了一个负担，它在徒劳无功地进行无谓的努力：保存代码、执行计划等期待重用，并且客户端要不停地获取Latch，试图寻找共享代码，却始终一无所获。如果真是如此，那这是我们最不愿看到的情况，SharedPool变得有害无益。当然这是极端，可是我们在性能优化中我们发现，大多数性能低下的系统都存在这样的通病：代码极少共享，缺乏或从不实行变量绑定。优化这些系统的根本方法就是优化代码，使代码（在保证 性能的前提下）可以充分共享，减少无谓的反复硬/软解析。
4，实际上，Oracle引入Shared Pool就是为了帮助用户实现代码共享和重用。了解这一点之后，开发人员在应用开发过程中，也应该有意识提高自己的代码水平，以期减少数据库的压力。
二，Shared Pool的设置说明
1，Shared Pool的大小通过初始化参数shared_pool_size 设置。对于SharedPool的设置，一直以来是最有争议的一部分内容。一方面很多人建议可以把SharedPool设置稍大，以充分Cache代码和避免ORA-04031错误的出现；另一方面又有很多人建议不能把Shared Pool设置过大，因为过大可能会带来管理上的额外负担，从而会影响数据库的性能。
2，可以通过如下命令转储Shared Pool共享内存的内容。
SQL> alter session set events 'immediate trace name heapdump level 2';
Session altered.
SQL> @gettrcname
TRACE_FILE_NAME
--------------------------------------------------
/u01/app/oracle/admin/orcl/udump/orcl_ora_3152.trc
Shared Pool通过Free Lists管理Free内存块（chunk），free的内存块（chunk）按不同size被划分到不同的部分（Bucket）进行管理。结合Dump文件，可以通过图对SharedPool的Free List管理进行说明。



3，在oralce 10g中，不同Bucket管理的内存块的size范围如下
[oracle@rhel ~]$ cat /u01/app/oracle/admin/orcl/udump/orcl_ora_3152.trc | grep Bucket
 Bucket 0 size=16
 Bucket 1 size=20
 Bucket 2 size=24
 Bucket 3 size=28
         .
         .
         .
 Bucket 172 size=704
 Bucket 173 size=708
 Bucket 174 size=712
 Bucket 175 size=716
 Bucket 176 size=724
 Bucket 177 size=732
 Bucket 178 size=740
 Bucket 179 size=748
4，在这里小于16bytes的块位于Bucket 0上，如果大于16bytes小于20bytes在Bucket1上。
20-16=4
24-20=4
724-716=8
748-740=8
可以看到以4bytes递增，8bytes递增......
5,初始时，数据库启动以后，SharedPool多数是连续的内存块。但是当空间分配使用后，内存块开始被分割，碎片开始出现，Bucket列表开始变长。Oracle请求SharedPool空间时，首先进入相应的Bucket进行查找。如果找不到，则转向下一个非空的Bucket，获取第一个Chunk，分割这个Chunk，剩余部分会进入相应的Bucket，进一步增加碎片。
6,最终结果是，由于不停分割，每个Bucket上的内存块会越来越多，越来越小。通常Bucket 0 的问题会最为显著，在这个测试的小型数据库上，Bucket 0上的碎片已经达到9030个，而shared_pool_size设置仅为150MB。
7,通常如果每个Bucket上的Chunk多于2000个，就被认为是SharedPool碎片过多。而SharedPool的碎片过多，是SharedPool产生性能问题的主要原因。
8,碎片过多会导致搜索Free List的时间过长，而Free Lists的管理和搜索都需要获得和持有一个非常重要的Latch，就是Shared Pool Latch 。Latch是Oracle数据库内部提供的一种低级锁，通常串行机制保护共享内存不被并发更新/修改所损坏。Latch的持有通常都非常短暂（通常微秒级），但是对于一个繁忙的数据库，这个串行机制往往会成为极大的性能瓶颈。
9,如果Free List链表过长，搜索这个Free Lists的时间会变长，从而可能导致Shared Pool Latch被长时间持有，在一个繁忙的系统中，这会引起严重的Shared Pool Latch竞争。在Oracle 9i之前，这个重要的Shared Pool Latch只有一个，所以长时间持有将会导致严重的性能问题。而在大多数情况下，用户请求的都是相对小的内存块（chunk），这样搜索Bucket 0往往消耗了大量的时间以及资源，Latch的争用此时就会成为一个非常严重的问题。
10,对比Oracle 8i，在Oracle 9i中shared pool管理最为显著的变化是，对于数量众多的Chunk，Oracle增加了更多的Bucket来管理（这与之前讲过的Buffer Cache的增强非常相似）。0~199共200个Bucket，size以4为步递增，200~249共50个Bucket，size以64递增，这样每个Bucket中容纳的chunk数量大大减少，查找的效率得以提高。所以，在Oracle 9i之前，如果盲目地增大shared_pool_size 或者设置过大的shared_pool_size，往往会适得其反。这就是也许你曾经听
说过的“过大的Shared_pool会带来管理上的负担”。
11,而且在Oracle 9i中，为了增加对于大共享池的支持，Shared Pool Latch从原来的一个增加到现在的7个，如果用户的系统有4个或4个以上的CPU，并且shared_pool_size大于250MB，那么Oracle可以把sharedPool分割为多个子缓冲池进行管理，每个subpool都拥有独立的结构、LRU和Shared Pool Latch。
四，子缓冲池
1，SQL> select addr,name,gets,misses,spin_gets from v$latch_children where name='shared pool';
ADDR     NAME                                                     GETS     MISSES  SPIN_GETS
-------- -------------------------------------------------- ---------- ---------- ----------
20095BF4 shared pool                                            336139          5          0
20095C58 shared pool                                                10          0          0
20095CBC shared pool                                                10          0          0
20095D20 shared pool                                                10          0          0
20095D84 shared pool                                                10          0          0
20095DE8 shared pool                                                10          0          0
20095E4C shared pool                                                10          0          0
7 rows selected.
2，子缓冲的数量有一个新引入的隐含参数设置_KGHDSIDX_COUNT。可以手工调整该参数（仅限于实验环境研究用）。以观察共享池管理的变化。
SQL> alter system set "_kghdsidx_count" =2 scope=spfile;
System altered.
3，SQL> startup force;
ORACLE instance started.
Total System Global Area  314572800 bytes
Fixed Size                  1219184 bytes
Variable Size             109053328 bytes
Database Buffers          201326592 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL>
4，SQL> select addr,name,gets,misses,spin_gets from v$latch_children where name='shared pool';
ADDR     NAME                                                     GETS     MISSES  SPIN_GETS
-------- -------------------------------------------------- ---------- ---------- ----------
20095BF4 shared pool                                             38914          0          0
20095C58 shared pool                                              3618          0          0
20095CBC shared pool                                                 6          0          0
20095D20 shared pool                                                 6          0          0
20095D84 shared pool                                                 6          0          0
20095DE8 shared pool                                                 6          0          0
20095E4C shared pool                                                 6          0          0
7 rows selected.
五，了解X$KSMSP视图
1，Shared Pool的空间分配和使用情况，可以通过一个内部视图来观察，这个视图就是X$KSMSP。X$KSMSP的名称的含义为[K]ernal [S]torge [M]emory [M]anagement [S]GA Hea[P]其中的每一行都代表着SharedPool中的一个chunk。
2，SQL> desc x$ksmsp
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- --------

----------------------------------------------------------------------------
 ADDR                                                                                                                       RAW(4)
 INDX                                                                                                                       NUMBER
 INST_ID                                                                                                                    NUMBER
 KSMCHIDX                                                                                                                   NUMBER
 KSMCHDUR                                                                                                                   NUMBER
 KSMCHCOM                                                                                                                   VARCHAR2(16)
 KSMCHPTR                                                                                                                   RAW(4)
 KSMCHSIZ                                                                                                                   NUMBER
 KSMCHCLS                                                                                                                   VARCHAR2(8)
 KSMCHTYP                                                                                                                   NUMBER
 KSMCHPAR                                                                                                                   RAW(4)
SQL>
3，这里主要关注的几个字段
a,x$ksmsp.ksmchcom,是注释字段，每个内存块被分配以后，注释会添加在该字段中。
b,x$ksmsp.ksmchsiz,代表块大小。
c,x$ksmsp.ksmchcls,列代表类型，主要有4中类，说明如下
c1，Free ---Free Chunks：不包含任何对象的chunk，可以不受限制的被自由分配。
c2，recreat---Recreate Chunks：包含可以被临时移出内存的对象，在需要的时候，这个对象可以被重新创建。例如：许多存储共享SQL代码的内存都是可以重建的。
c3，Freeable---Freeable Chunks：包含session周期或调用的对象，随后可以释放。这部分内存有时候可以全部或部分提前释放，但是注意，由于某些对象是中间过程产生的，这些对象不能临时释放出内存（因为不可重建）。
c4，Perm ---Permanent Memory Chunks：包含永久对象，通常不能释放。
4，可以通过查询X$KSMSP视图来考察SharedPool中存在的内存片的数量。不过要注意，Oracle（如：10.1.0.2）版本
在某些平台上（如：HP-UX PA-RISC 64-bit）查询该视图时可能会导致过度的CPU耗用，这是由Bug引起的。
5，SQL> select count(*) from x$ksmsp;
  COUNT(*)
----------
     15690
SQL> select count(*) from dba_objects;
  COUNT(*)
----------
     50396
SQL> select count(*) from x$ksmsp;
  COUNT(*)
----------
     15709
SQL>
6，这是由于SharedPool中进行SQL解析，请求空间，进而导致请求free空间分配、分割、从而产生了更多、更碎的内存Chunk。由此可以看出，如果数据库系统中存在大量的硬解析，不停请求分配Free的SharedPool内存，除了必须的Shared Pool Latch竞争外，还不可避免会导SharedPool中产生更多的内存碎片（当然在内存回收时，可能看到Chunk数量减少的情况）。
六，实验一（查询前后SharedPool的变化）
1，创建一张临时表用以保存之前X$KSMSP的状态：
SQL> CREATE GLOBAL TEMPORARY TABLE e$ksmsp on COMMIT PRESERVE ROWS AS
  2  SELECT a.ksmchcom,
  3  SUM(a.CHUNK) CHUNK,
  4  SUM(a.recr) recr,
  5  SUM(a.freeabl) freeabl,
  6  SUM(a.SUM) SUM
  7  FROM (SELECT ksmchcom,COUNT(ksmchcom) CHUNK,
  8  DECODE (ksmchcls,'recr',SUM(ksmchsiz),NULL) recr,
  9  DECODE (ksmchcls,'freeabl',SUM(ksmchsiz),NULL) freeabl,
 10  SUM(ksmchsiz) SUM
 11  FROM x$ksmsp GROUP BY ksmchcom,ksmchcls) a
 12  where 1=0
 13  GROUP BY a.ksmchcom;
Table created.
2，插入数据
SQL> INSERT INTO E$KSMSP
  2  SELECT a.ksmchcom,
  3  SUM(a.CHUNK) CHUNK,
  4  SUM(a.recr) recr,
  5  SUM(a.freeabl) freeabl,
  6  SUM(a.SUM) SUM
  7  FROM (SELECT ksmchcom,COUNT(ksmchcom) CHUNK,
  8  DECODE(ksmchcls,'recr',SUM(ksmchsiz),NULL) recr,
  9  DECODE(ksmchcls,'freeabl',SUM(ksmchsiz),NULL) freeabl,
 10  SUM(ksmchsiz) SUM
 11  FROM x$ksmsp
 12  GROUP BY ksmchcom,ksmchcls) a
 13  GROUP BY a.ksmchcom;
82 rows created.
3，比较前后SharedPool内存分配的变化；
SQL> select a.ksmchcom,a.chunk,a.sum,b.chunk,b.sum,(a.chunk-b.chunk) c_diff,(a.sum-b.sum) s_diff
  2  from
  3  (SELECT a.ksmchcom,
  4  SUM(a.CHUNK) CHUNK,
  5  SUM(a.recr) recr,
  6  SUM(a.freeabl) freeabl,
  7  SUM(a.SUM) SUM
  8  FROM (SELECT ksmchcom,COUNT(ksmchcom) CHUNK,
  9  DECODE(ksmchcls,'recr',SUM(ksmchsiz),NULL) recr,
 10  DECODE(ksmchcls,'freeabl',SUM(ksmchsiz),NULL) freeabl,
 11  SUM(ksmchsiz) SUM
 12  FROM x$ksmsp
 13  GROUP BY ksmchcom,ksmchcls) a
 14  GROUP BY a.ksmchcom) a,e$ksmsp b
 15  where a.ksmchcom = b.ksmchcom and (a.chunk-b.chunk)<>0;
KSMCHCOM              CHUNK        SUM      CHUNK        SUM     C_DIFF     S_DIFF
---------------- ---------- ---------- ---------- ---------- ---------- ----------
obj htab chun            20     143680         19     136496          1       7184
free memory             223   12545964        196   13194916         27    -648952
Heap0: KGL              595     638828        498     534844         97     103984
KGL handles            3127    1530848       2616    1278288        511     252560
modification             13      26780         12      24720          1       2060
trigger defini           10       4988          7       2784          3       2204
type object de          176     185172        179     190888         -3      -5716
KGLS heap               775    1023852        995    1260848       -220    -236996
obj stat memo           290      88160        256      77824         34      10336
KQR SO                  332     208976        248     156192         84      52784
repository               45      40964         30      28112         15      12852

KSMCHCOM              CHUNK        SUM      CHUNK        SUM     C_DIFF     S_DIFF
---------------- ---------- ---------- ---------- ---------- ---------- ----------
table definiti            9       1924          2        168          7       1756
sql area:PLSQL           41     169704         44     182128         -3     -12424
PCursor                 707     758056        618     662648         89      95408
CCursor                1070    1161156       1055    1145076         15      16080
library cache          1075     103264       1053     101128         22       2136
sql area                862    3531860        888    3638236        -26    -106376
PL/SQL DIANA            295    1208320        282    1155072         13      53248
KQR PO                 2062    1035688       1775     890436        287     145252
PL/SQL MPCODE           455    1945408        398    1713508         57     231900
CURSOR STATS             40     165760         35     145040          5      20720
21 rows selected.
SQL>
4，分析
free memory             223   12545964        196   13194916         27    -648952
简单分析结果，首先free memory的大小减少了648952，这说明SQL解析存储占用了一定的内存空间，而chunk数从196增加到223，说明内存碎片增加了，而碎片增加是共享池性能下降的开始。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29611940/viewspace-1147475/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29611940/viewspace-1147475/