Oracle數(shù)據(jù)庫共享池空間管理調(diào)優(yōu)

摘 要： Oracle數(shù)據(jù)庫是當(dāng)前使用最為廣泛的大型數(shù)據(jù)庫之一。共享池存儲(chǔ)數(shù)據(jù)庫所運(yùn)行的可執(zhí)行程序代碼和相關(guān)數(shù)據(jù)字典提供，是Oracle數(shù)據(jù)庫體系中最復(fù)雜的內(nèi)存組件之一。由于缺乏對(duì)共享池內(nèi)部運(yùn)行機(jī)制的了解，當(dāng)數(shù)據(jù)庫在運(yùn)行過程中顯示共享池中空閑空間較多，報(bào)ORA?4031錯(cuò)誤提示共享池空間不足時(shí)，往往束手無策。通過導(dǎo)出Oracle數(shù)據(jù)庫共享池的內(nèi)存數(shù)據(jù)的方法，總結(jié)并分析共享池空間管理的內(nèi)部管理機(jī)制，找到空閑空間較多而報(bào)ORA?4031錯(cuò)誤的原因，給出了錯(cuò)誤檢測方法和相對(duì)應(yīng)的解決方法。

關(guān)鍵詞： 共享池； 空間管理； 調(diào)優(yōu)； Oracle數(shù)據(jù)庫

中圖分類號(hào)： TN919?34； TP311 文獻(xiàn)標(biāo)識(shí)碼： A 文章編號(hào)： 1004?373X（2015）04?0069?04

0 引 言

內(nèi)存管理是Oracle數(shù)據(jù)庫體系結(jié)構(gòu)中重要的組成部分，也是Oracle數(shù)據(jù)庫性能優(yōu)化的主要方面。共享池是Oracle數(shù)據(jù)庫內(nèi)存管理中最重要的組件之一，如果不了解共享池空間內(nèi)部管理機(jī)制，就不能指導(dǎo)應(yīng)用開發(fā)人員設(shè)計(jì)出高效的SQL語句，也不能設(shè)置正確解決ORA?0431問題。有些DBA當(dāng)發(fā)現(xiàn)共享池出現(xiàn)性能問題時(shí)，只簡單的把共享池設(shè)置的非?！按蟆薄６蚕沓剡^大或過小都會(huì)降低性能，嚴(yán)重的情況下還會(huì)掛起數(shù)據(jù)庫。因此，研究Oracle數(shù)據(jù)庫共享池空間內(nèi)部管理機(jī)制對(duì)于共享池調(diào)優(yōu)是非常必要的。

1 共享池組成

共享池是SGA的一部分，用于緩存不同類型的程序數(shù)據(jù)，例如編譯的SQL和PL/SQL代碼、系統(tǒng)參數(shù)、數(shù)據(jù)字典等，包含Library Cache，Data Dictionary Cache，Reservered Pool，Server Result Cache等組件[1]。從10gR2版本之后，Oracle將共享池的組件分的更加詳細(xì)，很多組件大小是固定的，并且在實(shí)例啟動(dòng)時(shí)就已經(jīng)分配好空間。

使用V$SGASTAT視圖查看共享池所包含的組件和各組件的大?。?/p>

盡管隨著版本的發(fā)展，共享池提供更多的服務(wù)功能。功能越多，空間管理復(fù)雜度就越高。只有清楚共享池空間管理內(nèi)部機(jī)制，才能理解空間不足報(bào)錯(cuò)的原因，進(jìn)而真正解決問題，而不是一味地增加共享池的空間。

2 共享池結(jié)構(gòu)

2.1 子池

為了避免shared pool latch競爭，從9i開始引入了共享池子池（Shared Pool Sub?pool），每個(gè)子池都有freelist和LRU list，這樣減少了很多競爭。通過下面因素自動(dòng)確定子池的個(gè)數(shù)：

每4顆CPU對(duì)應(yīng)一個(gè)子池；

每個(gè)子池至少512 MB（10g以后的版本）；

最多可以有7個(gè)子池。

可以通過查詢X$KGHLU視圖，查看子池個(gè)數(shù)。在11g中，Oracle又把每個(gè)子池分為4個(gè)子子池（sub?sub?pool），也叫Duration。根據(jù)不同類型的請(qǐng)求在相應(yīng)Duration中分配空間，每個(gè)Duration由若干個(gè)extent（extent等于SGA基本單位granule）組成。使用下面的語句可以將共享池的結(jié)構(gòu)導(dǎo)出到跟蹤文件中：

Oradebug setmyid

Oradebug dump heapdump 2

跟蹤文件顯示結(jié)果為：

HEAP DUMP heap name=\"sga heap（1，0）\" desc=380050c30

EXTENT 0 addr=3ae400000

EXTENT 1 addr=3ae800000

………………

HEAP DUMP heap name=\"sga heap（1，1）\" desc=380052488

EXTENT 0 addr=3a5000000

EXTENT 1 addr=3a8000000

………………

sga heap（1，0）代表sub?pool 1的Duration 0，sga heap（1，1）代表sub?pool 1的Duration 1。Duaration地址空間是連續(xù)的，是granule的倍數(shù)。由此可以得到共享池中子池的結(jié)構(gòu)圖，如圖2所示。

Duration 1緩存Data Dictionary Cache；Duration 2緩存游標(biāo)的Heap 0；Duration 3緩存游標(biāo)的執(zhí)行計(jì)劃；Duration 0比其他Duration都大，主要用戶緩存數(shù)據(jù)庫啟動(dòng)時(shí)所需緩存的固定組件，也會(huì)為其分配足夠的extents，當(dāng)其他Duration空間不足時(shí)，會(huì)從此Duration向其他Duration動(dòng)態(tài)分配extent。每個(gè)Duration的大小會(huì)根據(jù)數(shù)據(jù)庫負(fù)載動(dòng)態(tài)調(diào)整，當(dāng)Duration空閑空間不足時(shí)，可以向Duration 0申請(qǐng)。

2.2 Chunk

Extent是SGA向共享池分配內(nèi)存空間的基本單位，而Chunk是共享池向申請(qǐng)者分配內(nèi)存空間的基本單位Extent結(jié)構(gòu)如圖3所示。Chunk包括header和body，header存儲(chǔ)Chunk的描述性信息，body存儲(chǔ)Chunk數(shù)據(jù)。從跟蹤文件中可以看到Chunk header的內(nèi)容：

EXTENT 1 addr=3a8000000

Chunk 3a8000058 sz=48 R?freeable \"reserved stoppe\"

Chunk 3a8000088 sz=212808 R?free \" \"

Chunk 3a8033fd0 sz=48 R?freeable \"reserved stoppe\"

Chunk 3a8034000 sz=184 freeable \"KGLOB \"

Chunk 3a80340b8 sz=160 freeable \"KGLOB \"

Chunk 3a8034158 sz=64 free \" \"

Chunk 3a8034198 sz=160 freeable \"KGLOB \"

…………………………

Chunk header包含下面信息：

第1列：Chunk標(biāo)記；

第2列：Chunk的起始地址；

第3列：Chunk大小；

第4列：Chunk類型；

第5列：Chunk注釋，Chunk的用途。

3 換入和換出

共享池的換入和換出由FREE List和LRU List兩個(gè)數(shù)據(jù)結(jié)構(gòu)管理，每個(gè)Duration都有自己的FREE List，而每個(gè)子池有一個(gè)LRU List。因此，較10g版本之前，減少了對(duì)兩個(gè)數(shù)據(jù)結(jié)構(gòu)的競爭。Chunk分為permanent、recreatable、freeable和free四種類型，作用分別是：

Permanent：包含永久的對(duì)象，比如鏈表、hash表，不能被釋放。

Recreatable：緩存程序數(shù)據(jù)，可以被交換出內(nèi)存。

Freeable：不能單獨(dú)使用，分配完recreatable類型Chunk之后，再次申請(qǐng)空間，將分配此類型Chunk。所對(duì)應(yīng)的recreatable類型Chunk交換出內(nèi)存之后，此類型Chunk也會(huì)交換出內(nèi)存；否則，不會(huì)單獨(dú)交換出內(nèi)存。

Free：空閑Chunk。

3.1 FREE List

管理Free類型Chunk的數(shù)據(jù)結(jié)構(gòu)。根據(jù)應(yīng)用的類型，在不同Duration的FREE List中申請(qǐng)空閑空間。下面是跟蹤文件中的一個(gè)Duration的FREE List結(jié)構(gòu)：

FREE LISTS：

Bucket 0 size=32

Chunk 3adc00078 sz= 0 kghdsx

Bucket 1 size=40

Chunk 3a79d0f00 sz= 40 free

Chunk 3a6d5ad00 sz= 40 free

Bucket 2 size=48

…………………………

Bucket 6 size=80

Chunk 3a2d1ff78 sz= 80 free

…………………………

共享池功能較為復(fù)雜，應(yīng)用在申請(qǐng)空閑空間時(shí)，申請(qǐng)的空間大小不固定。因此，在每個(gè)FREE List中按照空閑空間的大小，使用bucket將相應(yīng)大小的空閑Chunk連接起來，共有255個(gè)bucket。如上面的FREE LIST所示，bucket 0代表所管理的空間為32～39 B的Chunk；如果Chunk超過65 560 B，將連接到bucket 254上。Free List結(jié)構(gòu)如圖4所示。

共享池空間分配算法：

（1） 搜索bucket列表，查找與申請(qǐng)空間大小相匹配的bucket n。

（2） 如果在bucket n中有空閑Chunk，并且“Chunk size – 申請(qǐng)空間”小于32 B，執(zhí)行步驟（5）；如果“Chunk size – 申請(qǐng)空間”大于32 B，執(zhí)行（3）；否則，執(zhí)行（4）。

（3） 將“Chunk size – 申請(qǐng)空間”的剩余空間掛到相應(yīng)的bucket中，執(zhí)行步驟（5）。

（4） 如果bucket n中沒有相應(yīng)空間，執(zhí)行換出算法；再次執(zhí)行步驟（1），如果還沒有分配到合適的Chunk，將報(bào)ORA?4031錯(cuò)誤，異常退出。

（5） 將此Chunk分配給申請(qǐng)者，并正常退出。

3.2 LRU List

管理Creatable類型Chunk的數(shù)據(jù)結(jié)構(gòu)，LRU的管理在子池級(jí)別上，LRU結(jié)構(gòu)在子池的Duration 0中。下面是跟蹤文件中LRU List結(jié)構(gòu)：

UNPINNED RECREATABLE CHUNKS （lru first）：

Chunk 3aad1db10 sz= 4096 recreate

\"SQLA^27baa630 \" latch=0

Chunk 3ab52bc30 sz= 4096 recreate

\"CCUR^27baa630 \" latch=0

Chunk 3ad1d3968 sz= 528 recreate

\"KGLHD \" latch=0

Chunk 3aad1fb10 sz= 4096 recreate

\"SQLA^d55d9a7 \" latch=0

Chunk 3ab52ec30 sz= 4096 recreate

\"CCUR^d55d9a7 \" latch=0

Chunk 3aae808c8 sz= 4096 recreate

\"SQLA^c5417817 \" latch=0

4 空間管理調(diào)優(yōu)

在共享池空間管理中，最主要的錯(cuò)誤就是ORA?4031。錯(cuò)誤提示一般為：ORA?04031： \"unable to allocate n bytes of shared memory （\"shared pool\"， \"object_name\"， \"alloc type（2，0）\" ...）。表示在subpool 2的Duration 0中分配所請(qǐng)求的N字節(jié)空間失敗。

此時(shí)，可以查詢V$SGASTAT視圖，查看shared pool的free空間是否接近于0。查詢語句是：select * from v$sgainfo where pool=′shared pool′ and name=′free memory′。

如果結(jié)果接近于0，表明分配給共享池的內(nèi)存不足，需要增加內(nèi)存。但是，更多時(shí)候結(jié)果顯示有很多free空間，而仍然會(huì)報(bào)ORA?4031錯(cuò)誤。主要有兩個(gè)原因：共享池空閑空間碎片過多。有太多的碎片，當(dāng)申請(qǐng)大空間Chunk時(shí)，每個(gè)碎片都不能滿足應(yīng)用請(qǐng)求空間分配的大小。即使從LRU List中釋放Chunk也不能滿足應(yīng)用請(qǐng)求空間分配的大小。子池之間負(fù)載不均衡。有些子池空間耗盡，有些子池空閑空間較多。

4.1 共享池空閑空間碎片過多

4.1.1 測試方法

執(zhí)行下面的查詢檢測空閑空間的碎片情況：

select′sga heap（′||KSMCHIDX||′，0）′ sga_heap，

ksmchcom ChunkComment，

decode（round（ksmchsiz/1000），0，′0?1K′， 1，′1?2K′， 2，′2?3K′，

3，′3?4K′，4，′4?5K′，5，′5?6k′，6，′6?7k′，7，′7?8k′，8，′8?9k′， 9，′9?10k′，′> 10K′） Size，

count（*），

ksmchcls Status，

sum（ksmchsiz） Bytes

from x$ksmsp

where KSMCHCOM = ′free memory′

group by ′sga heap（′||KSMCHIDX||′，0）′， ksmchcom， ksmchcls，

decode（round（ksmchsiz/1000），0，′0?1K′， 1，′1?2K′， 2，′2?3K′，

3，′3?4K′，4，′4?5K′，5，′5?6k′，6，′6?7k′，7，′7?8k′，8，′8?9k′， 9，′9?10k′，′> 10K′）；

如果1～4 KB的空閑空間數(shù)量較多，5 KB以上的空閑空間數(shù)量較少，說明共享池空閑空間碎片較多。共享池中會(huì)話之間游標(biāo)不能共享，子游標(biāo)較多，是造成此現(xiàn)象的主要原因。

4.1.2 解決方法

（1） 修改初始化參數(shù)SHARED_POOL_RESERVED_SIZE，此參數(shù)默認(rèn)為5%，可以適當(dāng)增大；

（2） 將初始化參數(shù)CURSOR_SHARING設(shè)置為EXACT，不能設(shè)置為SIMILAR或FORCE；

（3） 修改應(yīng)用系統(tǒng)中的查詢語句，盡量多的使用綁定變量。

4.2 子池負(fù)載不均衡

共享池中有多個(gè)子池，Oracle將進(jìn)程分配到某個(gè)子池中，由于此子池中沒有多于空閑空間，分配空間操作失敗。雖然其他子池有很多空閑空間，也不會(huì)響應(yīng)該進(jìn)程的空間分配請(qǐng)求。當(dāng)確定共享池空閑空間較多，并且碎片較少時(shí)，可以確定子池負(fù)載不均衡。每個(gè)進(jìn)程都會(huì)存儲(chǔ)大量的動(dòng)態(tài)初始化參數(shù)設(shè)定。在數(shù)據(jù)庫啟動(dòng)時(shí)，將進(jìn)程分配到每個(gè)子池中。當(dāng)PROCESSES參數(shù)值較大（允許連接數(shù)據(jù)庫的進(jìn)程較多），而實(shí)際的并發(fā)量不大，實(shí)際連接到數(shù)據(jù)庫的進(jìn)程分配的子池不均衡，會(huì)出現(xiàn)此現(xiàn)象。

可以通過下面的方法解決：

（1） 將初始化參數(shù)PROCESSES改小；

（2） 修改隱含參數(shù)_kghdsidx_count，適當(dāng)增加子池?cái)?shù)量。

參考文獻(xiàn)

[1] CYRAN Michele. Oracle database concepts 10 g release 2 （10.2） [EB/OL]. [2014?07?09]. http：//docs.Oracle.com/cd/E11882_01/server.112/e25789/memory.htm#CNCPT1226.

[2] BANSAL Amit. Simplified approach to resolve ORA?4031[EB/OL]. [2008?07?21]. http：//askdba.org/weblog/2008/04/application?design?and?ora?4031.

[3] LEWIS Jonathan. Oracle core essential internals for DBA and developers [M]. [S.l.]： Springer Science+Business Media， 2011.

[4] SHAMSUDEEN Riyaj. A stroll through shared pool heaps [EB/OL]. [2009?01?15]. http：//orainternals.wordpress.com/2009/01/15/a?stroll?through?shared?pool?heaps.

[5] GREEN Russell. Understanding shared pool memory structures [EB/OL]. [2005?09?22]. http：//docs.Oracle.com.

[6] PODER Tanel. ORA?04031 errors and monitoring shared pool subpool memory utilization with Sgastatx SQL [EB/OL]. [2009?06?04]. http：//www.blog.tanelpoder.com.