基於參數shared_pool_reserved_size進一步理解共用池shared pool原理

結論 1,與共用池相關的參數為:shared_pool_size,shared_pool_reserved_size,_shared_pool_reserved_pct
  _shared_pool_reserved_min_alloc
2,shared_pool_reserved_size一般預設是shared_pool_size的5%，這個比例由參數_shared_pool_reserved_pct控制
3,_shared_pool_reserved_min_alloc控制保留池中最小的分配大小，預設大小為4400，其取值範圍為4000bytes到60M之間（當然這是基於當前共用池大小）
  否則會報錯資料庫無法重啟
4,調整  shared_pool_reserved_size,_shared_pool_reserved_pct，_shared_pool_reserved_min_alloc全要重啟庫方可
5,調整上述的參數可以是相互獨立，即調整一個參數後，其它參數不會動態進行相應的調整，仍保持原值
6,每個保留列表對應堆HEAP，即多少個堆就有多少個保留列表
  每個保留列表下麵包括14個BUCKET
7，調整上述幾個參數，發現保留列表相關資料沒有發生明顯的變化，當然，也限於我對這塊的理解還不到位
8,x$ksmspr記錄保留區CHUNK的分配情況，CHUNK大小共計2種，第1種為48位元組，第2種為 4529992即4424K ，具體測試見   經過在UE進行尋找分析比對 （在文章進行匹配尋找即可）
 
9，關於x$ksmspr的使用，請見下面測試之 --獲知與CHUNK分配相關的X視圖（在文章進行匹配尋找即可）

10, x$ksmsp是x$ksmspr的父集，即前者包含後者
測試

----oracle version
SQL> select * from v$version where rownum=1;


BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi


--與保留區相關的參數，可見shared_pool_reserved_size=shared_pool_size*_shared_pool_reserved_pct,且_shared_pool_reserved_pct預設值為5%
SQL> show parameter shared_pool


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size            big integer 44M
shared_pool_size                     big integer 0


控制從共用池分配多大比例給保留池，可見為5%
_shared_pool_reserved_pct                          5                                                  percentage memory of the shared pool allocated for
                                                                                                       the reserved area
保留池中最小的分配大小為4400位元組，也就是小於這個大小，不會從保留池分配記憶體，只會從FREE LIST及LRU LIST分配記憶體
_shared_pool_reserved_min_alloc                    4400                                               minimum allocation size in bytes for reserved area
                                                                                                       of shared pool


v$sgainfo或者__shared_pool_size可得到當前共用池的大小
SQL> select name,bytes/1024/1024 as mb,resizeable from v$sgainfo where name='Shared Pool Size';


NAME                                     MB RES
-------------------------------- ---------- ---
Shared Pool Size                        832 Yes


__shared_pool_size                                 872415232                                          Actual size in bytes of shared pool


      


SQL> select 872415232*0.05/1024/1024 mb from dual;


        MB
----------
      41.6                                                                                                                                                                                                            


先研究下參數shared_pool_reserved_size調整對於共用池記憶體配置的影響
----未調整shared_pool_reserved_size前
----共計4個heap
SQL> host more /home/ora10g/admin/ora10g/udump/ora10g_ora_14042.trc|grep -i --color sga
HEAP DUMP heap name="sga heap"  desc=0x60000058
HEAP DUMP heap name="sga heap(1,0)"  desc=0x60034fe0
HEAP DUMP heap name="sga heap(1,1)"  desc=0x60036838
  Chunk        0a3b7bc20 sz=  4190296    recreate  "KSFD SGA I/O b "  latch=(nil)
HEAP DUMP heap name="sga heap(1,2)"  desc=0x60038090
HEAP DUMP heap name="sga heap(1,3)"  desc=0x600398e8


---可見每個heap對應一個保留區的free list,共計4個
SQL> host more /home/ora10g/admin/ora10g/udump/ora10g_ora_14042.trc|grep -i --color "RESERVED FREE LISTS:"
RESERVED FREE LISTS:
RESERVED FREE LISTS:
RESERVED FREE LISTS:
RESERVED FREE LISTS:


--可見每個保留區的free list包含14個bucket,共計56個bucket,並且在RESERVED EXTENTS部分會包含每個保留區free list中bucket中的chunk(大家可以這樣理解，ORACLE是採用BUCKET來管理分配不同大小的記憶體)
SQL> host more /home/ora10g/admin/ora10g/udump/ora10g_ora_14042.trc|grep -i --color RESERVED
 reserved granule count 46 (granule size 16777216)
RESERVED EXTENTS
 reserved granules for root 46 (granule size 16777216)
  Chunk        0a1000058 sz=       48  R-freeable  "reserved stoppe"
  Chunk        0a10ccfd0 sz=       48  R-freeable  "reserved stoppe"
  Chunk        0a2000058 sz=       48  R-freeable  "reserved stoppe"
  Chunk        0a20ccfd0 sz=       48  R-freeable  "reserved stoppe"
  Chunk        0a4000058 sz=       48  R-freeable  "reserved stoppe"
  Chunk        0a40ccfd0 sz=       48  R-freeable  "reserved stoppe"
RESERVED FREE LISTS:
 Reserved bucket 0 size=32
 Reserved bucket 1 size=4400
 Reserved bucket 2 size=8216
 Reserved bucket 3 size=8696
 Reserved bucket 4 size=8704
 Reserved bucket 5 size=8712
 Reserved bucket 6 size=8720
 Reserved bucket 7 size=9368
 Reserved bucket 8 size=9376
 Reserved bucket 9 size=12352
 Reserved bucket 10 size=12360
 Reserved bucket 11 size=16408
 Reserved bucket 12 size=32792
 Reserved bucket 13 size=65560
Total reserved free space   =  2518488
 reserved granules for root 46 (granule size 16777216)
  Chunk        0a3000058 sz=       48  R-freeable  "reserved stoppe"
  Chunk        0a30ccfd0 sz=       48  R-freeable  "reserved stoppe"
RESERVED FREE LISTS:
 Reserved bucket 0 size=32
 Reserved bucket 1 size=4400
 Reserved bucket 2 size=8216
 Reserved bucket 3 size=8696
 Reserved bucket 4 size=8704
 Reserved bucket 5 size=8712
 Reserved bucket 6 size=8720
 Reserved bucket 7 size=9368
 Reserved bucket 8 size=9376
 Reserved bucket 9 size=12352
 Reserved bucket 10 size=12360
 Reserved bucket 11 size=16408
 Reserved bucket 12 size=32792
 Reserved bucket 13 size=65560
Total reserved free space   =   839496
 reserved granules for root 46 (granule size 16777216)
  Chunk        0a0000058 sz=       48  R-freeable  "reserved stoppe"
  Chunk        0a00ccfd0 sz=       48  R-freeable  "reserved stoppe"