关于深入shared pool

周末又看了一下shared pool，在阅读了老白和老盖及dsi后写下这个笔记，如有不对的理解不正确地方还望日后批评指正
版本oracle 10g 2
shared pool 是sga中的重要组成部份，通过shared_pool_size指定大小，如要启用了asmm大小很根据需要动态调整
shared pool 由perm，libary cache,row catch(dictionary catch),reserved area(保留),free area 组成。
perm :process,session,lock(latch),trasaction,resource
lib cache :pl/sql ，parse code,sql explan
row catch :table column definition and grant privs 's infomation
reserved area: reserved large object use，it will be allocate until shared pool not allocate enouth space, default size shared_pool *5%,
free area:it can immediate allocate ,free list manager
dump 出来看看
sys@ANBOB> alter session set events 'immediate trace name heapdump level 2';
sys@ANBOB> oradebug setmypid;
sys@ANBOB> oradebug tracefile_name;
摘自部份trace文件内容

HEAP DUMP heap name="sga heap(1,0)" desc=0x2001a468
 extent sz=0xfc4 alt=108 het=32767 rec=9 flg=-126 opc=0
 parent=(nil) owner=(nil) nex=(nil) xsz=0x400000
EXTENT 0 addr=0x30400000
  Chunk 30400038 sz= 24 R-freeable "reserved stoppe"
  Chunk 30400050 sz= 212888 R-free " "
  Chunk 30433fe8 sz= 24 R-freeable "reserved stoppe"
  Chunk 30434000 sz= 3981312 perm "perm " alo=3742412
EXTENT 1 addr=0x30800000
  Chunk 30800038 sz= 24 R-freeable "reserved stoppe"
  Chunk 30800050 sz= 212888 R-free " "
  Chunk 30833fe8 sz= 24 R-freeable "reserved stoppe"
  Chunk 30834000 sz= 3214744 perm "perm " alo=3214744
  Chunk 30b44d98 sz= 766568 free " "
EXTENT 2 addr=0x30c00000
  Chunk 30c00038 sz= 24 R-freeable "reserved stoppe"
  Chunk 30c00050 sz= 212888 R-free " "
  Chunk 30c33fe8 sz= 24 R-freeable "reserved stoppe"
  Chunk 30c34000 sz= 3924808 perm "perm " alo=3924808
  Chunk 30ff2348 sz= 56504 free " "
EXTENT 3 addr=0x31000000
  Chunk 31000038 sz= 24 R-freeable "reserved stoppe"
  Chunk 31000050 sz= 212888 R-free " "
   省略
EXTENT 5 addr=0x31800000
  Chunk 31800038 sz= 24 R-freeable "reserved stoppe"
  Chunk 31800050 sz= 212888 R-free " "
  Chunk 31833fe8 sz= 24 R-freeable "reserved stoppe"
  Chunk 31834000 sz= 3981244 perm "perm " alo=3981244
  Chunk 31bfffbc sz= 68 free "     
  省略
EXTENT 8 addr=0x32800000
  Chunk 32800038 sz= 24 R-freeable "reserved stoppe"  
  Chunk 32800050 sz= 212888 R-free " "
  Chunk 32833fe8 sz= 24 R-freeable "reserved stoppe"
  Chunk 32834000 sz= 3903820 perm "perm " alo=3903820
  Chunk 32bed14c sz= 32768 perm "perm " alo=32768
  Chunk 32bf514c sz= 43024 perm "perm " alo=43024
  Chunk 32bff95c sz= 1700 free " "  
Total heap size = 37748232    
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
 Bucket 9 size=52
 Bucket 10 size=56
 Bucket 11 size=60
 Bucket 12 size=64
 Bucket 13 size=68
  Chunk 31bfffbc sz= 68 free " " --EXTENT 5 中
 Bucket 14 size=72
 Bucket 15 size=76
  Chunk 317fffb4 sz= 76 free " " --EXTENT 4 中
 Bucket 16 size=80
 Bucket 17 size=84
 Bucket 18 size=88
 Bucket 19 size=92
 。。。
 Bucket 165 size=676
 Bucket 166 size=680
 Bucket 167 size=684
 Bucket 168 size=688
 Bucket 169 size=692
 Bucket 170 size=696
 Bucket 171 size=700
 Bucket 172 size=704
 Bucket 173 size=708
 Bucket 174 size=712
 Bucket 175 size=716-----由4递增改为8
 Bucket 176 size=724
 Bucket 177 size=732
 Bucket 178 size=740
 Bucket 179 size=748
 Bucket 180 size=756
 Bucket 181 size=764
 Bucket 182 size=772
 Bucket 183 size=780
 Bucket 184 size=788
 Bucket 185 size=796
 Bucket 186 size=804
 Bucket 187 size=812----改为64递增
 Bucket 188 size=876
 Bucket 189 size=940
 Bucket 190 size=1004
 Bucket 191 size=1068
 Bucket 192 size=1072
 Bucket 193 size=1076
 Bucket 194 size=1132
 Bucket 195 size=1196
 Bucket 196 size=1260
 Bucket 197 size=1324
 Bucket 198 size=1388
  Chunk 31fffa64 sz= 1436 free " " --在extent 6 中
 Bucket 199 size=1452
 Bucket 200 size=1516
 Bucket 201 size=1580
 Bucket 202 size=1644
  Chunk 32bff95c sz= 1700 free " " --在extent 8 中
 Bucket 203 size=1708
 Bucket 204 size=1772
 Bucket 205 size=1836
 Bucket 206 size=1900
 Bucket 207 size=1964
 Bucket 208 size=2028
 Bucket 209 size=2092
 Bucket 210 size=2156
 Bucket 211 size=2220
 Bucket 212 size=2284
 Bucket 213 size=2348
 Bucket 214 size=2412
 Bucket 215 size=2476
 。。。
 Bucket 237 size=3884
 Bucket 238 size=3948
 Bucket 239 size=4012 -----改为递增84
 Bucket 240 size=4096   
 Bucket 241 size=4100 -----改为递增4
 Bucket 242 size=4108 -----改为递增8
 Bucket 243 size=8204 -----改为递96
 Bucket 244 size=8460 ---256
 Bucket 245 size=8464 ---4
 Bucket 246 size=8468
 Bucket 247 size=8472
 Bucket 248 size=9296 ----824
 Bucket 249 size=9300 ----4
 Bucket 250 size=12320 ----3020
 Bucket 251 size=12324 ----4
 Bucket 252 size=16396 ----4172
 Bucket 253 size=32780 ----16384
  Chunk 30ff2348 sz= 56504 free " " --extent 2中
 Bucket 254 size=65548
  Chunk 30b44d98 sz= 766568 free " " --extent 1中
Total free space = 827216

RESERVED FREE LISTS:
 Reserved bucket 0 size=16
 Reserved bucket 1 size=4400
 Reserved bucket 2 size=8204
 Reserved bucket 3 size=8460
...

初始的,数据库启动以后,shared pool多数是连续内存块，当空间分配使用以后,内存块开始被分割,碎片开始出现，Oracle请求shared pool空间时，首先进入相应的Bucket进行查找。
如果找不到,则转向下一个非空的bucket,获取第一个chunk。分割这个chunk,剩余部分会进入相应的Bucket,进一步增加碎片。
shared_pool free lists 划分了255个bucket,8i前从9i以后并且以开始递增是以4，8，64..的容量递增，以至于后来的无规律跳动，个人觉得应该是内在被分割后的碎片。碎片过多会导致查找free Lists的时间增加，从而使shared pool latch被长时间持有，导致更多的Latch竞争。
我们可以从数据库的内部视图监控,x$ksmsp其中每一行都代表着shared pool中的一个chunk, 也可以关注v$shared_pool_reserved 查看reserved pool使用情况

sys@ANBOB> select component,oper_type,initial_size,final_size,start_time from v$sga_resize_ops where component='shared pool';
COMPONENT            OPER_TYPE                               INITIAL_SIZE FINAL_SIZE START_TIME
-------------------- --------------------------------------- ------------ ---------- -------------------
shared pool          STATIC                                             0   79691776 2011-10-15 15:38:29
shared pool          GROW                                        79691776   83886080 2011-10-15 15:45:20
sys@ANBOB> show parameter shared_pool
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
shared_pool_reserved_size            big integer                       3984588
shared_pool_size                     big integer                       0
sys@ANBOB> select 3984588*20 from dual;
3984588*20
----------
  79691760
差不多验证了shared_pool_reserved_size  是shared_pool_size 20%说法
查看shared_pool大小
sys@ANBOB> SELECT pool,sum(bytes) FROM V$SGASTAT group by pool;
POOL                                 SUM(BYTES)
------------------------------------ ----------
                                      171965040
java pool                               4194304
shared pool                            79696468
large pool                              4194304
表x$kghlu可以查看shared pool中的LRU列表,x$kghlu    One-row summary of LRU statistics for the shared pool 
sys@ANBOB> select
  2  indx,
  3  kghlurcr,
  4  kghlutrn,
  5  kghlufsh,
  6  kghluops,
  7  kghlunfu,
  8  kghlunfs
  9  from
 10  sys.x$kghlu;
      INDX   KGHLURCR   KGHLUTRN   KGHLUFSH   KGHLUOPS   KGHLUNFU   KGHLUNFS
 ---------- ---------- ---------- ---------- ---------- ---------- ----------
         0        217        446          0      32136          0          0
         1        777        981      13448      52258          0          0
         2       3871       5868          0      28571          1        540
         3          0          0          0          0          0          0

oracle server memory is managed by a generic heap manager, when a user asks for a chunk of space , that chunk comes from a particula heap,the user can ask for space that is permanently allocated in the heap or for space that can be freed and reused.
when a chunk of space that may be freed is allocated from a heap,it is possible to specify that the contents of the chunk are recreatable . if this option is specified,then the chunk of space can be explicitly unpinned when not in use.
when a user requests space from a heap and no more space is available , the heap manager can use a callback routine to request that the owner of an unpinned,recreatable chunk of space free the chunk,Unpinned chunks of space are kept on an LRU list so that the heap manager can determine whice one to try to free up first.
a heap is composed of a set of contiguous chunks of space called extents. when the users asks for a chunk of space from a heap , the heap manager looks in the set of extents contained in the heap for an unused piece of space of the requested size.if one canot be found ,then the heap manager uses a callback to request a new extent,and adds it to the heap.
a chunk from ane heap may contain another heap;this is known as a subheap

The total size of the shared pool is determined by the initialization parameter SHARED_POOL_SIZE. The default value of this parameter is 8MB on 32-bit platforms and 64MB on 64-bit platforms.
Oracle also flushes a shared SQL area from the shared pool in these circumstances:
When the ANALYZE statement is used to update or delete the statistics of a table, cluster, or index, all shared SQL areas that contain statements referencing the analyzed schema object are flushed from the shared pool. The next time a flushed statement is run, the statement is parsed in a new shared SQL area to reflect the new statistics for the schema object.
If a schema object is referenced in a SQL statement and that object is later modified in any way, the shared SQL area is invalidated (marked invalid), and the statement must be reparsed the next time it is run.
If you change a database's global database name, all information is flushed from the shared pool.
The administrator can manually flush all information in the shared pool to assess the performance (with respect to the shared pool, not the data buffer cache) that can be expected after instance startup without shutting down the current instance. The statement ALTER SYSTEM FLUSH SHARED_POOL is used to do this.