關于shared pool的深入探討(二)
sunday, 2004-08-22 21:23 eygle
link:
http://www.eygle.com/internal/shared_pool-2.htm
我們繼續把前面的問題展開一下.
其實我們可以從數據庫內部監控shared pool的空間碎片情況.
這涉及到一個內部視圖x$ksmsp
x$ksmsp的名稱含義為: [k]ernal [s]torage [m]emory management [s]ga hea[p]
其中每一行都代表著shared pool中的一個chunk
首先記錄一下測試環境：
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的結構:
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)
我們關注以下幾個字段:
ksmchcom是注釋字段,每個內存塊被分配以后，注釋會添加在該字段中.
x$ksmsp.ksmchsiz代表塊大小
x$ksmsp.ksmchcls列代表類型，主要有四類，說明如下:
free
free chunks--不包含任何對象的chunk,可以不受限制的被分配.
recr
recreatable chunks--包含可以被臨時移出內存的對象,在需要的時候，這個對象可以
被重新創建.例如,許多存儲共享sql代碼的內存都是可以重建的.
freeabl
freeable chunks--包含session周期或調用的對象,隨后可以被釋放.這部分內存有時候
可以全部或部分提前釋放.但是注意，由于某些對象是中間過程產生的，這些對象不能
臨時被移出內存(因為不可重建).
perm
permanent memory chunks--包含永久對象.通常不能獨立釋放.
我們可以通過查詢x$ksmsp視圖來考察shared pool中存在的內存片的數量
不過注意:oracle的某些版本(如:10.1.0.2)在某些平臺上(如:hp-ux pa-risc 64-bit)查
詢該視圖可能導致過度的cpu耗用,這是由于bug引起的.
我們看一下測試:
初始啟動數據庫,x$ksmsp中存在2259個chunksql> select count(*) from x$ksmsp; count(*)---------- 2259執行查詢:sql> select count(*) from dba_objects; count(*)---------- 10491此時shared pool中的chunk數量增加sql> select count(*) from x$ksmsp; count(*)---------- 2358
這就是由于shared pool中進行sql解析，請求空間，進而導致請求free空間,分配、分割
從而產生了更多，更細碎的內存chunk
由此我們可以看出，如果數據庫系統中存在大量的硬解析，不停請求分配free的shred pool內存
除了必須的shared pool latch等競爭外，還不可避免的會導致shared pool中產生更多的內存碎片
(當然，在內存回收時，你可能看到chunk數量減少的情況)
我們看以下測試:
首先重新啟動數據庫:sql> startup force;oracle instance started.total system global area 47256168 bytesfixed size 451176 bytesvariable size 29360128 bytesdatabase buffers 16777216 bytesredo buffers 667648 bytesdatabase mounted.database opened.創建一張臨時表用以保存之前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.保存當前shared pool狀態: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.執行查詢:sql> select count(*) from dba_objects; count(*)---------- 10492 比較前后shared pool內存分配的變化: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 3664kgls heap 274 365752 270 360424 4 5328kqr po 389 198548 377 192580 12 5968free memory 93 2292076 90 2381304 3 -89228library cache 1005 398284 965 381416 40 16868sql area 287 547452 269 490052 18 574006 rows selected.
我們簡單分析一下以上結果:
首先free memory的大小減少了89228(增加到另外五個組件中),這說明sql解析存儲占用了一定的內存空間
而chunk從90增加為93，這說明內存碎片增加了.
在下面的部分中,我會著手介紹一下kgl handles, kgls heap這兩個非常重要的shared pool中的內存結構.
