Oracle數(shù)據(jù)庫參數(shù)優(yōu)化

1、Oracle數(shù)據(jù)庫參數(shù)優(yōu)化許多開發(fā)維護(hù)人員并沒有真正理解Oracle數(shù)據(jù)庫相關(guān)參數(shù)在系統(tǒng)運(yùn)行中的作用，導(dǎo)致數(shù)據(jù)庫系統(tǒng)的執(zhí)行效率低下。因此，如何設(shè)計(jì)合理的Oracle數(shù)據(jù)庫初始化參數(shù)就顯得非常重要。為了訪問數(shù)據(jù)庫中的數(shù)據(jù)，Oracle數(shù)據(jù)庫為所有用戶提供一組后臺進(jìn)程，并且，有一些存儲結(jié)構(gòu)專門用來存儲最近的有關(guān)對數(shù)據(jù)庫訪問的數(shù)據(jù)。這些存儲區(qū)域可以通過減少對數(shù)據(jù)庫文件的I/O次數(shù)來改善數(shù)據(jù)庫性能。數(shù)據(jù)庫實(shí)例就是用來訪問一個(gè)數(shù)據(jù)庫文件集的一個(gè)存儲結(jié)構(gòu)以及后臺進(jìn)程的集合。它使一個(gè)單獨(dú)的數(shù)據(jù)庫可以被多個(gè)實(shí)例訪問（這是Oracle的并行服務(wù)器方式 ）。決定實(shí)例的組成以及大小的參數(shù)存儲在文件init.ora中

2、。這個(gè)文件在實(shí)例啟動(dòng)時(shí)候需要裝載，也可以在運(yùn)行中被DBA裝載 。instance的init.ora文件通常包含instance 的名字，比如一個(gè)instance叫ORA9，其init.ora將被起名為initORA9.ora。本文結(jié)合筆者實(shí)際工作中的Oracle數(shù)據(jù)庫使用經(jīng)驗(yàn)討論其相關(guān)參數(shù)的調(diào)整策略。通常需要設(shè)置的參數(shù)： DB_BLOCK_BUFFERS該參數(shù)決定了數(shù)據(jù)庫緩沖區(qū)的大小，這部分內(nèi)存的作用主要是在內(nèi)存中緩存從數(shù)據(jù)庫中讀取的數(shù)據(jù)塊，數(shù)據(jù)庫緩沖區(qū)越大，為用戶已經(jīng)在內(nèi)存里的共享數(shù)據(jù)提供的內(nèi)存就越大，這樣可以減少所需要的磁盤物理讀寫次數(shù)。在Oracle 9i以前版本數(shù)據(jù)庫中，緩沖是由db_

3、block_buffers*db_block_size來決定，db_block_size參數(shù)是在創(chuàng)建數(shù)據(jù)庫時(shí)設(shè)置的，它決定數(shù)據(jù)庫里每個(gè)塊的大小，9i以前不能改變已經(jīng)存在的數(shù)據(jù)庫的塊大小，因此應(yīng)該在最初創(chuàng)建數(shù)據(jù)庫時(shí)就確保設(shè)置適當(dāng)?shù)臄?shù)據(jù)庫塊大小。在創(chuàng)建數(shù)據(jù)庫時(shí)設(shè)置為8k，db_block_buffers*db_block_size的乘積占物理內(nèi)存的20%30%，物理內(nèi)存在1G以內(nèi)，可以占20%，1G2G之間可占25%，物理內(nèi)存在2G以上，可以占30%，甚至更高。說明：Oracle 8i以后的數(shù)據(jù)緩沖區(qū)(data buffer)實(shí)際可由三部分組成，分別是默認(rèn)池、保留池(buffer keep)和回收

4、池(buffer recycle)。保留池(buffer keep)可用于存放需要極快訪問的小表，回收池(buffer recycle)可用于存放需要全表掃描的大表。保留池和回收池的大小分別由參數(shù)buffer_pool_keep和buffer_pool_recycle決定。這兩個(gè)參數(shù)通常在做性能調(diào)整時(shí)才設(shè)置，因此不建議設(shè)置這兩個(gè)參數(shù)。 shared_pool_sizeShared pool由三部分組成，分別是Dictionary cache(包括數(shù)據(jù)字典的定義，如表結(jié)構(gòu)、權(quán)限等)、Library cache（包括共享的sql游標(biāo)、sql原代碼以及執(zhí)行計(jì)劃、存儲過程和會話信息）和Control

5、structure。它的大小由初始化參數(shù)shared_pool_size控制，它的作用是緩存已經(jīng)被解析過的SQL，使其能被重用，不用再解析。SQL的解析非常消耗CPU的資源，如果一條SQL在Shared pool中已經(jīng)存在，則進(jìn)行的僅是軟解析（在Shared pool中尋找相同SQL），這將大大提高數(shù)據(jù)庫的運(yùn)行效率。當(dāng)然，這部分內(nèi)存也并非越大越好，太大的Shared pool，Oracle數(shù)據(jù)庫為了維護(hù)共享結(jié)構(gòu)，將付出更大的管理開銷。這個(gè)參數(shù)的設(shè)置建議在150M500M之間。如果系統(tǒng)內(nèi)存為1G，該值可設(shè)為150M200M;如果為2G，該值設(shè)為250M300M；每增加1G內(nèi)存，該值增加100M；

6、但該值最大不應(yīng)超過500M。（Shared pool不足，Oracle將報(bào)4031錯(cuò)）。 shared_pool_reserved_size它的作用是在shared pool中保留一塊區(qū)域以放置一些大對象（如大型軟件包），如不設(shè)置此參數(shù)，系統(tǒng)缺省保留5%的shared pool空間用于放置大對象，不建議設(shè)置該參數(shù)，讓系統(tǒng)自動(dòng)分配即可。 Sort_area_size該參數(shù)是當(dāng)查詢需要排序的時(shí)候，Oracle將使用這部分內(nèi)存做排序，當(dāng)內(nèi)存不足時(shí)，使用臨時(shí)表空間做排序。這個(gè)參數(shù)是針對會話(session)設(shè)置的，不是針對整個(gè)數(shù)據(jù)庫。即如果應(yīng)用有170個(gè)數(shù)據(jù)庫連接（session），假設(shè)這些sessi

7、on都做排序操作，則Oracle會分配8×170等于1360M內(nèi)存做排序，而這些內(nèi)存是在Oracle的SGA區(qū)之外分配的，即如果SGA區(qū)分配了1.6G內(nèi)存，Oracle還需要額外的1.3G內(nèi)存做排序。建議該值設(shè)置不超過3M，當(dāng)物理內(nèi)存為1G時(shí)，該值宜設(shè)為1M或更低（如512K）；2G時(shí)可設(shè)為2M;但不論物理內(nèi)存多大，該值也不應(yīng)超過3M。 sort_area_retained_size這個(gè)參數(shù)的含義是當(dāng)排序完成后至少為session繼續(xù)保留的排序內(nèi)存的最小值，該值最大可設(shè)為等于Sort_area_size。這樣設(shè)置的好處是可以提高系統(tǒng)性能，因?yàn)橄麓卧僮雠判虿僮鲿r(shí)不需要再臨時(shí)申請內(nèi)存，缺

8、點(diǎn)是如果Sort_ara_retained_size設(shè)得過大并且session數(shù)很多時(shí)，將導(dǎo)致系統(tǒng)內(nèi)存不足。建議該值設(shè)為Sort_area_size的10%20%左右，或者不設(shè)置（缺省為0）。 Log_bufferLog_buffer是重做日志緩沖區(qū)，對數(shù)據(jù)庫的任何修改都按順序被記錄在該緩沖，然后由LGWR進(jìn)程將它寫入磁盤。LGWR的寫入條件是：用戶提交、有1/3重做日志緩沖區(qū)未被寫入磁盤、有大于1M重做日志緩沖區(qū)未被寫入磁盤、超時(shí)、DBWR需要寫入的數(shù)據(jù)的SCN 號大于LGWR記錄的SCN 號，DBWR 觸發(fā)LGWR寫入。從中可以看出，大于1M的log buffer值意義并不大，建議不論物理

9、內(nèi)存多大，該值統(tǒng)一設(shè)為1M。 Large_pool_sizeOracle的large_pool用于MTS、并行查詢和RMAN。如果使用了MTS或RMAN，large_pool特別有用，它可以降低用戶對shared_pool的爭用，這個(gè)參數(shù)不推薦設(shè)置。（或設(shè)為1M）。 Java_pool_sizeOracle 8i以后版本中，Oracle數(shù)據(jù)庫內(nèi)置了對Java的支持，短消息系統(tǒng)并未使用這些功能，因此java_pool_size的值可以減少到1M。但如果數(shù)據(jù)庫安裝時(shí)選擇了JServer組件，則這個(gè)值可以設(shè)置為20M30M之間。（可以查詢v$option動(dòng)態(tài)視圖，如果java那一項(xiàng)值為true，則表

10、示安裝了JServer，為false，表示未安裝）。 SESSION_CACHED_CURSOR該參數(shù)指定要高速緩存的會話游標(biāo)的數(shù)量。對同一SQL語句進(jìn)行多次語法分析后，它的會話游標(biāo)將被移到該會話的游標(biāo)高速緩存中。這樣可以縮短語法分析的時(shí)間，因?yàn)橛螛?biāo)被高速緩存，無需被重新打開。設(shè)置該參數(shù)有助于提高系統(tǒng)的運(yùn)行效率，建議無論在任何平臺都應(yīng)被設(shè)為50。 re_page_sga該參數(shù)表示將把所有SGA裝載到內(nèi)存中，以便使該實(shí)例迅速達(dá)到最佳性能狀態(tài)。這將增加例程啟動(dòng)和用戶登錄的時(shí)間，但在內(nèi)存充足的系統(tǒng)上能減少缺頁故障的出現(xiàn)。建議在2G以上（含2G）內(nèi)存的系統(tǒng)都將該值設(shè)為true。 ML_LOCKS該參數(shù)

11、表示所有用戶獲取的表鎖的最大數(shù)量。對每個(gè)表執(zhí)行 DML 操作均需要一個(gè) DML鎖。例如，如果3個(gè)用戶修改 2 個(gè)表，就要求該值為 6。該值過小可能會引起死鎖問題。這個(gè)參數(shù)同transaction參數(shù)有關(guān)系，缺省為4倍的transaction大小。建議該參數(shù)不應(yīng)該低于600，可以檢查系統(tǒng)的當(dāng)前值，若比600小，則將transaction參數(shù)改為150以上，dml_locks參數(shù)會自動(dòng)變?yōu)閠ransaction的4倍。 OB_QUEUE_PROCESS該參數(shù)指定每個(gè)例程的SNP作業(yè)隊(duì)列進(jìn)程的數(shù)量。當(dāng)使用job或復(fù)制時(shí)，一定要設(shè)置該參數(shù)，推薦設(shè)為3或4。并行查詢中需要設(shè)置的參數(shù)并行查詢僅當(dāng)在多CPU

12、處于空閑狀態(tài)且數(shù)據(jù)分布在不同磁盤時(shí)才會對某些查詢（有全表掃描操作）的性能產(chǎn)生有益的影響，而且即使在這種情況下，并行查詢服務(wù)器的最大數(shù)量最好也不要超過CPU的數(shù)量。這里面應(yīng)該修改的參數(shù)如下。 DB_FILE_MULTIBLOCK_READ_COUNT該參數(shù)主要同全表掃描有關(guān)。當(dāng)Oracle在請求大量連續(xù)數(shù)據(jù)塊的時(shí)候，該參數(shù)控制塊的讀入速率。DB_FILE_MULTIBLOCK_READ_COUNT參數(shù)能對系統(tǒng)性能產(chǎn)生較大的影響，它和DB_BLOCK_SIZE參數(shù)之間有重要關(guān)系。因?yàn)樵赨nix物理層上，Oracle總是以最小64K的數(shù)據(jù)庫塊進(jìn)行讀入，因此應(yīng)該使這2個(gè)參數(shù)的乘積為64K。即如果DB_

13、BLOCK_SIZE為8192，則DB_FILE_MULTIBLOCK_READ_COUNT應(yīng)設(shè)為8。 DB_BLOCK_LRU_LATCHES在多CPU機(jī)器上通過初始化參數(shù)DB_BLOCK_LRU_LATCHES允許多個(gè)LRU鎖存器。當(dāng)DBWR和服務(wù)器進(jìn)程掃描數(shù)據(jù)塊緩沖Cache時(shí)，它們需要獲取LRU鎖存器。這種鎖存器對于避免緩沖區(qū)變臟以及避免被其他進(jìn)程改變都是必要的，這也避免了掃描時(shí)返回不一致的結(jié)果。如果沒有使用鎖存器，某個(gè)掃描自由緩沖區(qū)的進(jìn)程可能會發(fā)現(xiàn)一個(gè)自由緩沖區(qū)，但是立刻就可能被其他進(jìn)程使用。每一個(gè)LRU 鎖存器至少保護(hù)50個(gè)數(shù)據(jù)塊緩沖區(qū)。所有緩沖區(qū)都被Oracle基于Hash算法分

14、配到特定的LRU鎖存器之上。如果該值太小，在數(shù)據(jù)庫活動(dòng)量很大時(shí)就會潛在地導(dǎo)致競爭。在多CPU機(jī)器之上這種競爭會高一些，這是因?yàn)槎鄠€(gè)服務(wù)器進(jìn)程可能排隊(duì)以獲取一個(gè)LRU Cache。該參數(shù)默認(rèn)值為CPU個(gè)數(shù)的一半，在多CPU系統(tǒng)中，推薦設(shè)為等于CPU數(shù)目或CPU數(shù)目的2倍3倍（在Oracle 9i中，該參數(shù)已經(jīng)變?yōu)橐粋€(gè)隱含參數(shù)）。 OPEN_CURSORS指定一個(gè)會話一次可以打開的游標(biāo) (環(huán)境區(qū)域) 的最大數(shù)量，并且限制PL/SQL使用的PL/SQL游標(biāo)高速緩存的大小，以避免用戶再次執(zhí)行語句時(shí)重新進(jìn)行語法分析。請將該值設(shè)置得足夠高，這樣才能防止應(yīng)用程序耗盡打開的游標(biāo)。此值建議設(shè)置為250300。

15、LOG_CHECKPOINT_INTERVAL該參數(shù)同檢查點(diǎn)有關(guān)，檢查點(diǎn)由ckpt 進(jìn)程執(zhí)行，檢查點(diǎn)發(fā)生時(shí)Oracle會同步數(shù)據(jù)文件、控制文件和redo文件。該參數(shù)指定當(dāng)寫入重做日志文件中的OS 塊 (而不是數(shù)據(jù)庫塊) 的數(shù)量達(dá)到設(shè)定值時(shí)，強(qiáng)制執(zhí)行一次檢查點(diǎn)。該值較低可以縮短例程恢復(fù)所需的時(shí)間，但可能導(dǎo)致磁盤操作過量。在Oracle 8i中該值缺省為100000。當(dāng)值為0時(shí)，表示此參數(shù)不起作用。該參數(shù)的設(shè)定需要考慮的因素較多，建議使用缺省值。 LOG_CHECKPOINT_TIMEOUT該參數(shù)仍然同檢查點(diǎn)有關(guān)。它指定距下一個(gè)檢查點(diǎn)出現(xiàn)的最大時(shí)間間隔(秒數(shù))。將該時(shí)間值指定為0，將禁用以時(shí)間為基

16、礎(chǔ)的檢查點(diǎn)。較低的值可以縮短例程恢復(fù)的時(shí)間，但可能導(dǎo)致磁盤操作過量。在8i中該值缺省為1800。如果想強(qiáng)制某一時(shí)間段后執(zhí)行檢查點(diǎn)，則用此選項(xiàng)。不建議修改該值。Oracle 8i OPS的特殊參數(shù)設(shè)置OPS是指Oracle并行服務(wù)器（Oracle Parallel Server），它允許多個(gè)實(shí)例在同一時(shí)刻訪問單一的數(shù)據(jù)庫。OPS增強(qiáng)了數(shù)據(jù)庫的可用性，單個(gè)節(jié)點(diǎn)的故障并不會使整個(gè)數(shù)據(jù)庫不可用。而且如果應(yīng)用分割合理，OPS也可以提高系統(tǒng)的處理性能。但由于OPS本身的復(fù)雜性，如果對并行操作的使用不當(dāng)，經(jīng)常會出現(xiàn)系統(tǒng)操作性能比單節(jié)點(diǎn)還慢的結(jié)果。在OPS中，Oracle通過集成分布式鎖管理器（Integra

17、ted Distributed Lock Manager，IDLM）來協(xié)調(diào)資源的使用，防止發(fā)生沖突。一旦某個(gè)節(jié)點(diǎn)上的實(shí)例啟動(dòng)后，它就可以獲取各種各樣的DLM鎖，這些鎖主要?jiǎng)澐譃镻CM（Parallel Cache Management，并行Cache管理）鎖和非PCM鎖。并行Cache管理是一種技術(shù)，這種技術(shù)允許多個(gè)實(shí)例同時(shí)訪問共享資源。它允許一個(gè)節(jié)點(diǎn)訪問位于另一個(gè)節(jié)點(diǎn)的緩沖區(qū)Cache中的數(shù)據(jù)。PCM鎖用于鎖住數(shù)據(jù)庫塊，非PCM鎖用于鎖住所有其他的共享DLM資源（例如共享池中的數(shù)據(jù)文件和對象）。Oracle 9i中主要參數(shù)設(shè)置 undo_managementOracle 9i中新引入了und

18、o tablespace，它可以自動(dòng)管理Oracle的回滾，不再需要手工建立回滾段，但9i也支持繼續(xù)使用手工建立的回滾段。由Undo_management參數(shù)決定是使用undo tablespace還是使用手工建立的回滾段。如果該參數(shù)為auto，則表示使用undo tablespace。為manual，表示使用手工管理回滾段，這時(shí)同8i一樣，也需要建立回滾表空間，且要建立回滾段。在9i R2（9i R2指Oracle 9.2.X.X，下同）中，該參數(shù)缺省值為auto。建議繼續(xù)使用該缺省值，即使用undo tablespace，也不用手工建立回滾段。 undo_retention該參數(shù)的單位為秒

19、，當(dāng)undo_management參數(shù)為auto時(shí)，undo_retention表示在undo_tablespace中保留多長時(shí)間的回滾信息，Oracle 9i的flash_back功能同該值有關(guān)。該值設(shè)為多大同undo_tablespace的大小有關(guān)，在9i R2中缺省值為10800，一般情況下已經(jīng)夠了，不建議修改該參數(shù)。 sga_max_sizeOracle 9i中該值決定了SGA區(qū)的最大值。只要SGA區(qū)不大于該值，就可以動(dòng)態(tài)調(diào)整數(shù)據(jù)庫緩沖區(qū)和share pool的大小。建議該值取物理內(nèi)存的50%。 db_cache_size在Oracle 9i中，數(shù)據(jù)庫緩沖區(qū)的大小由db_cache_size決定，Oracle 8i中的db_block_buffers被取消。db_cache_size的單位是字節(jié)，它直接決定了數(shù)據(jù)庫緩沖區(qū)的大小，而不再是塊的數(shù)量。