基於引數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"
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 09f000058 sz= 48 R-freeable "reserved stoppe"
Chunk 09f0ccfd0 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
SQL>
---調整shared_pool_reserved_size，由值44M增加到60M
SQL> alter system set shared_pool_reserved_size=60m;
alter system set shared_pool_reserved_size=60m
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set shared_pool_reserved_size=60m scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 1157627904 bytes
Fixed Size 2095800 bytes
Variable Size 1040188744 bytes
Database Buffers 83886080 bytes
Redo Buffers 31457280 bytes
Database mounted.
Database opened.
SQL> show parameter shared_pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 60M
shared_pool_size big integer 0
可見調整shared_pool_reserved_size，引數_shared_pool_reserved_pct不會進行調整
_shared_pool_reserved_pct 5 percentage memory of the shared pool allocated for
the reserved area
可見共享池大小，不然因為保留區大小調整而變化
__shared_pool_size 872415232 Actual size in bytes of shared pool
---僅列舉重點關注的內容，可見和調整shared_pool_reserved_size前沒有變化
SQL> host more /home/ora10g/admin/ora10g/udump/ora10g_ora_14757.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 0a111efd0 sz= 48 R-freeable "reserved stoppe"
Chunk 0a2000058 sz= 48 R-freeable "reserved stoppe"
Chunk 0a211efd0 sz= 48 R-freeable "reserved stoppe"
Chunk 0a4000058 sz= 48 R-freeable "reserved stoppe"
Chunk 0a411efd0 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 = 3526104
reserved granules for root 46 (granule size 16777216)
Chunk 0a3000058 sz= 48 R-freeable "reserved stoppe"
Chunk 0a311efd0 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 = 1175368
reserved granules for root 46 (granule size 16777216)
Chunk 0a0000058 sz= 48 R-freeable "reserved stoppe"
Chunk 0a011efd0 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 = 1175368
reserved granules for root 46 (granule size 16777216)
Chunk 09f000058 sz= 48 R-freeable "reserved stoppe"
Chunk 09f11efd0 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 = 1175368
---換個思路繼續測試，調整_shared_pool_reserved_pct引數，看看保留區變化,可見共享池大小沒有變化，且好像shared_pool_reserved_size沒有變化，因為shared_pool_reserved_size=shared_pool_size*_shared_pool_reserved_pct
SQL> alter system set "_shared_pool_reserved_pct"=15;
alter system set "_shared_pool_reserved_pct"=15
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set "_shared_pool_reserved_pct"=15 scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 1157627904 bytes
Fixed Size 2095800 bytes
Variable Size 1040188744 bytes
Database Buffers 83886080 bytes
Redo Buffers 31457280 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter reserved
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_shared_pool_reserved_pct integer 15
shared_pool_reserved_size big integer 60M
SQL> select 872415232*0.15/1024/1024 mb from dual;
MB
----------
124.8
好像調整_shared_pool_reserved_pct引數後，保留區仍沒有變化或調整
SQL> host more /home/ora10g/admin/ora10g/udump/ora10g_ora_15156.trc|grep -i "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 0a111efd0 sz= 48 R-freeable "reserved stoppe"
Chunk 0a2000058 sz= 48 R-freeable "reserved stoppe"
Chunk 0a211efd0 sz= 48 R-freeable "reserved stoppe"
Chunk 0a4000058 sz= 48 R-freeable "reserved stoppe"
Chunk 0a411efd0 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 = 3526104
reserved granules for root 46 (granule size 16777216)
Chunk 0a3000058 sz= 48 R-freeable "reserved stoppe"
Chunk 0a311efd0 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 = 1175368
reserved granules for root 46 (granule size 16777216)
Chunk 0a0000058 sz= 48 R-freeable "reserved stoppe"
Chunk 0a011efd0 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 = 1175368
reserved granules for root 46 (granule size 16777216)
Chunk 09f000058 sz= 48 R-freeable "reserved stoppe" --對應X$KSMSPR，且R-freeable對應X$KSMSPR的列值R-freea
Chunk 09f11efd0 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 = 1175368
--獲知與CHUNK分配相關的X檢視
SQL> select * from x$kqfta where lower(kqftanam) like '%x$ksmsp%';
ADDR INDX INST_ID KQFTAOBJ KQFTAVER KQFTANAM KQFTATYP KQFTAFLG KQFTARSZ KQFTACOC
---------------- ---------- ---------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ----------
0000000005A592E8 65 1 4294951100 4 X$KSMSP 4 0 88 11
0000000005A59330 66 1 4294951170 3 X$KSMSPR 4 0 88 9
0000000005A594E0 72 1 4294951768 1 X$KSMSP_DSNEW 1 0 6472 8
0000000005A59528 73 1 4294951769 1 X$KSMSP_NWEX 4 0 64 13
可知
X$KSMSPR記錄與保留區相關的chunk分配資訊，下基於此X表進行分析保留區
可見ksmchptr即上述DUMP中的CHUNK的地址
SQL> select * from x$ksmspr where KSMCHSIZ=48 and ksmchptr='00000000A1000058' or ksmchptr='00000000A111EFD0';
ADDR INDX INST_ID KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00002AAB5A233E00 15 1 reserved stoppe 00000000A111EFD0 48 R-freea 0 00
00002AAB5A233D50 17 1 reserved stoppe 00000000A1000058 48 R-freea 0 00
不過DUMP出來的保留區僅6個CHUNK,但如下顯示12個CHUNK，餘下的6個CHUNK為何沒顯示在DUMP中呢，此問題先放在這兒
SQL> select * from x$ksmspr where KSMCHSIZ=48;
ADDR INDX INST_ID KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00002AAB5A234328 0 1 reserved stoppe 000000009F11EFD0 48 R-freea 0 00
00002AAB5A234278 2 1 reserved stoppe 000000009F000058 48 R-freea 0 00
00002AAB5A234220 3 1 reserved stoppe 00000000A011EFD0 48 R-freea 0 00
00002AAB5A234170 5 1 reserved stoppe 00000000A0000058 48 R-freea 0 00
00002AAB5A234118 6 1 reserved stoppe 00000000A311EFD0 48 R-freea 0 00
00002AAB5A234068 8 1 reserved stoppe 00000000A3000058 48 R-freea 0 00
00002AAB5A234010 9 1 reserved stoppe 00000000A411EFD0 48 R-freea 0 00
00002AAB5A233F60 11 1 reserved stoppe 00000000A4000058 48 R-freea 0 00
00002AAB5A233F08 12 1 reserved stoppe 00000000A211EFD0 48 R-freea 0 00
00002AAB5A233E58 14 1 reserved stoppe 00000000A2000058 48 R-freea 0 00
00002AAB5A233E00 15 1 reserved stoppe 00000000A111EFD0 48 R-freea 0 00
ADDR INDX INST_ID KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00002AAB5A233D50 17 1 reserved stoppe 00000000A1000058 48 R-freea 0 00
12 rows selected.
--從另一個維度研究，大家知道保留區也是共享池一部分，正常情況下分配記憶體只會從FREE LIST及LRU LIST分配，如果我調小引數_shared_pool_reserved_min_alloc，分配記憶體更會從保留區分配記憶體，是不是這樣呢
，還是為了研究保留區的記憶體分配一些機制
--注意：僅是為了研究原理與演算法，請在生產中不要這樣調整
__shared_pool_size 872415232 Actual size in bytes of shared pool
shared_pool_reserved_size 62914560 size in bytes of reserved area of shared pool
---可見預設情況下，從保留區分配記憶體最小的記憶體大小為4400位元組
_shared_pool_reserved_min_alloc 4400 minimum allocation size in bytes for reserved area
of shared pool
可見調整引數_shared_pool_reserved_min_alloc必須要重啟庫，且引數必須在4000位元組與60m之間，否則資料庫無法重啟，說明資料庫底層對於一些引數調整有非常複雜的演算法控制的，不是簡單調整一個引數就可以了
SQL> alter system set "_shared_pool_reserved_min_alloc"=10;
alter system set "_shared_pool_reserved_min_alloc"=10
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set "_shared_pool_reserved_min_alloc"=100 scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 62914560
SQL>
基於SPFILE調整PFILE，恢復引數_shared_pool_reserved_min_alloc，然後再次重啟庫
[ora10g@seconary dbs]$ strings spfileora10g.ora|grep reser
*._shared_pool_reserved_min_alloc=100
*._shared_pool_reserved_pct=15
*.shared_pool_reserved_size=62914560
--注意：STRINGS後的檔案要清除產生檔案無關的條目
[ora10g@seconary dbs]$ strings spfileora10g.ora>initora10g.ora
*._shared_pool_reserved_min_alloc=4400
SQL> startup nomount pfile='/home/ora10g/product/10.2.0/db_1/dbs/initora10g.ora'
ORA-32006: PARALLEL_AUTOMATIC_TUNING initialization parameter has been deprecated
ORACLE instance started.
Total System Global Area 1157627904 bytes
Fixed Size 2095800 bytes
Variable Size 1040188744 bytes
Database Buffers 83886080 bytes
Redo Buffers 31457280 bytes
SQL> create spfile from pfile='/home/ora10g/product/10.2.0/db_1/dbs/initora10g.ora'
2 ;
File created.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 1157627904 bytes
Fixed Size 2095800 bytes
Variable Size 1040188744 bytes
Database Buffers 83886080 bytes
Redo Buffers 31457280 bytes
Database mounted.
Database opened.
---可見大小48位元組已在保留區中分配的CHUNK
SQL> select * from x$ksmspr where KSMCHSIZ=48;
ADDR INDX INST_ID KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00002AF0A3240938 0 1 reserved stoppe 000000009C451FD0 48 R-freea 0 00
00002AF0A3240888 2 1 reserved stoppe 000000009C000058 48 R-freea 0 00
00002AF0A3240830 3 1 reserved stoppe 000000009D451FD0 48 R-freea 0 00
00002AF0A3240780 5 1 reserved stoppe 000000009D000058 48 R-freea 0 00
00002AF0A3240728 6 1 reserved stoppe 00000000A3451FD0 48 R-freea 0 00
00002AF0A3240678 8 1 reserved stoppe 00000000A3000058 48 R-freea 0 00
00002AF0A3240620 9 1 reserved stoppe 00000000A4451FD0 48 R-freea 0 00
00002AF0A3240570 11 1 reserved stoppe 00000000A4000058 48 R-freea 0 00
00002AF0A3240518 12 1 reserved stoppe 00000000A2451FD0 48 R-freea 0 00
00002AF0A3240468 14 1 reserved stoppe 00000000A2000058 48 R-freea 0 00
00002AF0A3240410 15 1 reserved stoppe 00000000A1451FD0 48 R-freea 0 00
ADDR INDX INST_ID KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00002AF0A3240360 17 1 reserved stoppe 00000000A1000058 48 R-freea 0 00
00002AF0A3240308 18 1 reserved stoppe 00000000A0451FD0 48 R-freea 0 00
00002AF0A3240258 20 1 reserved stoppe 00000000A0000058 48 R-freea 0 00
00002AF0A3240200 21 1 reserved stoppe 000000009F451FD0 48 R-freea 0 00
00002AF0A3240150 23 1 reserved stoppe 000000009F000058 48 R-freea 0 00
00002AF0A32400F8 24 1 reserved stoppe 000000009E451FD0 48 R-freea 0 00
00002AF0A3240048 26 1 reserved stoppe 000000009E000058 48 R-freea 0 00
18 rows selected.
可以用heapdump 12級進行轉儲，剛好與上述x$ksmspr的條目相同
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump heapdump 12
ORA-00085: current call does not exist
SQL> oradebug dump heapdump 6
Statement processed.
SQL> oradebug tracefile_name
/home/ora10g/admin/ora10g/udump/ora10g_ora_4300.trc
SQL> host more /home/ora10g/admin/ora10g/udump/ora10g_ora_4300.trc|grep reserved
reserved granule count 5 (granule size 16777216)
reserved granules for root 5 (granule size 16777216)
Chunk 09e000058 sz= 48 R-freeable "reserved stoppe"
Chunk 09e451fd0 sz= 48 R-freeable "reserved stoppe"
Chunk 09f000058 sz= 48 R-freeable "reserved stoppe"
Chunk 09f451fd0 sz= 48 R-freeable "reserved stoppe"
Chunk 0a0000058 sz= 48 R-freeable "reserved stoppe"
Chunk 0a0451fd0 sz= 48 R-freeable "reserved stoppe"
Chunk 0a1000058 sz= 48 R-freeable "reserved stoppe"
Chunk 0a1451fd0 sz= 48 R-freeable "reserved stoppe"
Chunk 0a2000058 sz= 48 R-freeable "reserved stoppe"
Chunk 0a2451fd0 sz= 48 R-freeable "reserved stoppe"
Chunk 0a4000058 sz= 48 R-freeable "reserved stoppe"
Chunk 0a4451fd0 sz= 48 R-freeable "reserved stoppe"
Total reserved free space = 27179952
reserved granules for root 5 (granule size 16777216)
Chunk 0a3000058 sz= 48 R-freeable "reserved stoppe"
Chunk 0a3451fd0 sz= 48 R-freeable "reserved stoppe"
Total reserved free space = 4529992
reserved granules for root 5 (granule size 16777216)
Chunk 09d000058 sz= 48 R-freeable "reserved stoppe"
Chunk 09d451fd0 sz= 48 R-freeable "reserved stoppe"
Total reserved free space = 4529992
reserved granules for root 5 (granule size 16777216)
Chunk 09c000058 sz= 48 R-freeable "reserved stoppe"
Chunk 09c451fd0 sz= 48 R-freeable "reserved stoppe"
Total reserved free space = 4529992
我們需要知道
經過在UE進行查詢分析比對，可見
x$ksmspr顯示為標黃的內容，即KSMCHCLS=R-freea ,對應DUMP中的狀態為R-freeable，這部分記憶體對應reserved stoppe
而reserved free list中的chunk對應ksmchlcs=R-free,且這部分記憶體可以自由分配
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
Chunk 09e000088 sz= 4529992 R-free " "
Chunk 09f000088 sz= 4529992 R-free " "
Chunk 0a0000088 sz= 4529992 R-free " "
Chunk 0a1000088 sz= 4529992 R-free " "
Chunk 0a2000088 sz= 4529992 R-free " "
Chunk 0a4000088 sz= 4529992 R-free " "
Total reserved free space = 27179952
--可見4529992位元組對應的CHUNK即未在保理區分配的記憶體CHUNK
SQL> select * from x$ksmspr where KSMCHSIZ=4529992;
ADDR INDX INST_ID KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00002B8C55D385F0 1 1 free memory 000000009C000088 4529992 R-free 0 00
00002B8C55D384E8 4 1 free memory 000000009B000088 4529992 R-free 0 00
00002B8C55D383E0 7 1 free memory 000000009D000088 4529992 R-free 0 00
00002B8C55D382D8 10 1 free memory 00000000A3000088 4529992 R-free 0 00
00002B8C55D381D0 13 1 free memory 00000000A4000088 4529992 R-free 0 00
00002B8C55D380C8 16 1 free memory 00000000A2000088 4529992 R-free 0 00
00002B8C55D37FC0 19 1 free memory 00000000A1000088 4529992 R-free 0 00
00002B8C55D37EB8 22 1 free memory 00000000A0000088 4529992 R-free 0 00
00002B8C55D37DB0 25 1 free memory 000000009F000088 4529992 R-free 0 00
00002B8C55D37CA8 28 1 free memory 000000009E000088 4529992 R-free 0 00
10 rows selected.
可見用於保留區的有2種不同的大小，1個為4529992位元組，1個為48位元組
SQL> select ksmchsiz,count(*) from x$ksmspr group by ksmchsiz;
KSMCHSIZ COUNT(*)
---------- ----------
4529992 10
48 20
而且x$ksmsp是x$ksmspr的父集，即前者包含後者
SQL> select * from x$ksmspr where ksmchcom='free memory';
ADDR INDX INST_ID KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00002B8C55D381D8 1 1 free memory 000000009C000088 4529992 R-free 0 00
00002B8C55D380D0 4 1 free memory 000000009B000088 4529992 R-free 0 00
00002B8C55D37FC8 7 1 free memory 000000009D000088 4529992 R-free 0 00
00002B8C55D37EC0 10 1 free memory 00000000A3000088 4529992 R-free 0 00
00002B8C55D37DB8 13 1 free memory 00000000A4000088 4529992 R-free 0 00
00002B8C55D37CB0 16 1 free memory 00000000A2000088 4529992 R-free 0 00
00002B8C55D37BA8 19 1 free memory 00000000A1000088 4529992 R-free 0 00
00002B8C55D37AA0 22 1 free memory 00000000A0000088 4529992 R-free 0 00
00002B8C55D37998 25 1 free memory 000000009F000088 4529992 R-free 0 00
00002B8C55D37890 28 1 free memory 000000009E000088 4529992 R-free 0 00
10 rows selected.
SQL> select * from x$ksmsp where rownum<=10;
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00002B8C55F75E00 0 1 1 4 sql area 000000009CFFF000 4096 recr 4095 000000009DDD38C8
00002B8C55F75DA8 1 1 1 4 KGLS heap 000000009CFFEBA8 1112 recr 4095 000000009DFFDC48
00002B8C55F75D50 2 1 1 4 sql area 000000009CFFDBA8 4096 recr 4095 000000009DFF5EF0
00002B8C55F75CF8 3 1 1 4 KGLS heap 000000009CFFD750 1112 freeabl 0 000000009DFE5520
00002B8C55F75CA0 4 1 1 4 sql area 000000009CFFC750 4096 recr 4095 000000009DF578B8
00002B8C55F75C48 5 1 1 4 free memory 000000009CFFC6E8 104 free 0 00
00002B8C55F75BF0 6 1 1 4 KGLS heap 000000009CFFC290 1112 freeabl 0 000000009DF91C68
00002B8C55F75B98 7 1 1 4 KGLS heap 000000009CFFBE38 1112 recr 4095 000000009DF9B018
00002B8C55F75B40 8 1 1 4 repository 000000009CFFBBA8 656 freeabl 0 000000009DF72CF0
00002B8C55F75AE8 9 1 1 4 KGLS heap 000000009CFFB750 1112 freeabl 0 000000009DFE36B8
10 rows selected.
SQL> select * from x$ksmsp where rownum<=10 and ksmchptr='000000009C000088';
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00002B8C55F00498 4638 1 1 4 free memory 000000009C000088 4529992 R-free 0 00
SQL> select * from x$ksmsp where rownum<=10 and ksmchptr='000000009F000088';
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00002B8C55D35638 21128 1 1 1 free memory 000000009F000088 4529992 R-free 0 00
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-1844798/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SHARED_POOL_RESERVED_SIZE引數的設定及作用
- _shared_pool_reserved_pct or shared_pool_reserved_size with ASMMASM
- zt_Oracle shared pool internals_共享池_shared_poolOracle
- 深入理解shared pool共享池之library cache系列一
- 深入理解shared pool共享池之library cache系列二
- 如何基於共享伺服器模式shared server mode配置大池large pool之二伺服器模式Server
- 資料庫體系結構-共享池(shared pool)，largepool,Java池，流池資料庫Java
- 使用DBMS_SHARED_POOL包將物件固定到共享池物件
- 共享池的調整與優化(Shared pool Tuning)優化
- 深入理解shared pool共享池之library cache的library cache lock系列四
- 深入理解shared pool共享池之library cache的library cache pin系列三
- 理解Oracle Shared PoolOracle
- 共享池之五：Shared Pool子池與結果集快取技術快取
- 深入理解shared pool共享池空間及library cache分配之ora-4031 系列一
- 共享池之六:shared pool latch/ library cache latch /lock pin 簡介
- zt_eygle大師_shared pool共享池管理機制系列文章
- latch:shared pool的一點理解
- Shared Pool 的基本原理
- SHARED POOL 基礎知識
- oracle 10g在共享伺服器模式shared server如何配置大池large poolOracle 10g伺服器模式Server
- 【Shared Pool】使用DBMS_SHARED_POOL包將PL/SQL大物件儲存到Shared PoolSQL物件
- 共享SQL區在shared pool中釋放的條件SQL
- Oracle shared poolOracle
- 基於Apache元件，分析物件池原理Apache元件物件
- 【實驗】shared_pool的sql命中率--cursor_sharing引數研究SQL
- SHARED POOL總結
- SHARED_POOL解析
- 9i,10g下v$sgastat中共享池的sum值與shared_pool_size的關係。AST
- Oracle基礎包之DBMS_SHARED_POOL(十)Oracle
- 基於HiKariCP元件，分析連線池原理元件
- Request 接收引數亂碼原理解析
- Oracle Shared Pool Memory ManagementOracle
- ORACLE SGA之shared poolOracle
- 你都理解建立執行緒池的引數嗎？執行緒
- 使用DBMS_SHARED_POOL包將PL/SQL大物件儲存到Shared PoolSQL物件
- 基於已有引數檔案進行資料恢復資料恢復
- dbms_shared_pool keep物件到share pool中物件
- 一步一步理解Generator函式的原理函式