我們繼續(xù)把前面的問題展開一下. 其實我們可以從數(shù)據(jù)庫內(nèi)部監(jiān)控shared pool的空間碎片情況. 這涉及到一個內(nèi)部視圖x$ksmsp X$KSMSP的名稱含義為: [K]ernal [S]torage [M]emory Management [S]GA Hea[P] 其中每一行都代表著shared pool中的一個chunk首先記錄一下測試環(huán)境：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i EnterPRise Edition Release 9.2.0.3.0 - ProdUCtion
PL/SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0 Production
TNS for linux: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production
我們看一下x$ksmsp的結(jié)構(gòu):
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)
我們關(guān)注以下幾個字段:
KSMCHCOM是注釋字段,每個內(nèi)存塊被分配以后，注釋會添加在該字段中.
x$ksmsp.ksmchsiz代表塊大小
x$ksmsp.ksmchcls列代表類型，主要有四類，說明如下:
free
Free chunks--不包含任何對象的chunk,可以不受限制的被分配.
recr
Recreatable chunks--包含可以被臨時移出內(nèi)存的對象,在需要的時候，這個對象可以
被重新創(chuàng)建.例如,許多存儲共享sql代碼的內(nèi)存都是可以重建的.
freeabl
Freeable chunks--包含session周期或調(diào)用的對象,隨后可以被釋放.這部分內(nèi)存有時候
可以全部或部分提前釋放.但是注重，由于某些對象是中間過程產(chǎn)生的，這些對象不能
臨時被移出內(nèi)存(因為不可重建).
perm
Permanent memory chunks--包含永久對象.通常不能獨立釋放.
我們可以通過查詢x$ksmsp視圖來考察shared pool中存在的內(nèi)存片的數(shù)量不過注重:Oracle的某些版本(如:10.1.0.2)在某些平臺上(如:HP-UX PA-RISC 64-bit)查詢該視圖可能導(dǎo)致過度的CPU耗用,這是由于bug引起的. 我們看一下測試:
初始啟動數(shù)據(jù)庫,x$ksmsp中存在2259個chunk
SQL> select count(*) from x$ksmsp;
COUNT(*)
----------
2259
執(zhí)行查詢:
SQL> select count(*) from dba_objects;
COUNT(*)
----------
10491
此時shared pool中的chunk數(shù)量增加
SQL> select count(*) from x$ksmsp;
COUNT(*)
----------
2358
這就是由于shared pool中進行sql解析，請求空間，進而導(dǎo)致請求free空間,分配、分割從而產(chǎn)生了更多，更細碎的內(nèi)存chunk
由此我們可以看出，假如數(shù)據(jù)庫系統(tǒng)中存在大量的硬解析，不停請求分配free的shred pool內(nèi)存除了必須的shared pool latch等競爭外，還不可避免的會導(dǎo)致shared pool中產(chǎn)生更多的內(nèi)存碎片(當然，在內(nèi)存回收時，你可能看到chunk數(shù)量減少的情況)我們看以下測試:
首先重新啟動數(shù)據(jù)庫:
SQL> startup force;
ORACLE instance started.
Total System Global Area 47256168 bytes
Fixed Size 451176 bytes
Variable Size 29360128 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
創(chuàng)建一張臨時表用以保存之前x$ksmsp的狀態(tài):
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.
保存當前shared pool狀態(tài):
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
14 /
41 rows created.
執(zhí)行查詢:
SQL> select count(*) from dba_objects;
COUNT(*)
----------
10492
比較前后shared pool內(nèi)存分配的變化:
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
16 /
KSMCHCOM CHUNK SUM CHUNK SUM C_DIFF S_DIFF
---------------- ---------- ---------- ---------- ---------- ----------
KGL handles 313 102080 302 98416 11 3664
KGLS heap 274 365752 270 360424 4 5328
KQR PO 389 198548 377 192580 12 5968
free memory 93 2292076 90 2381304 3 -89228
library cache 1005 398284 965 381416 40 16868
sql
area 287 547452 269 490052 18 57400
6 rows selected.
我們簡單分析一下以上結(jié)果: 首先free memory的大小減少了89228(增加到另外五個組件中),這說明sql解析存儲占用了一定的內(nèi)存空間
而chunk從90增加為93，這說明內(nèi)存碎片增加了. 在下面的部分中,我會著手介紹一下KGL handles, KGLS heap這兩個非常重要的shared pool中的內(nèi)存結(jié)構(gòu).
