Oracle內(nèi)在索引和參數(shù)數(shù)的調(diào)整

歡迎進(jìn)入Oracle社區(qū)論壇，與200萬技術(shù)人員互動(dòng)交流 >>進(jìn)入 本文是有關(guān)Oracle 10g基于成本的內(nèi)部優(yōu)化（CBO）和結(jié)構(gòu)化查詢語言（SQL）性能最優(yōu)化的一系列文章（共12部分）中的第10部分。每個(gè)技巧類的文章都摘錄自即將由Rampant科技出版社出版的書《Oracle 結(jié)構(gòu)

歡迎進(jìn)入Oracle社區(qū)論壇，與200萬技術(shù)人員互動(dòng)交流 >>進(jìn)入

　　本文是有關(guān)Oracle 10g基于成本的內(nèi)部優(yōu)化（CBO）和結(jié)構(gòu)化查詢語言（SQL）性能最優(yōu)化的一系列文章（共12部分）中的第10部分。每個(gè)技巧類的文章都摘錄自即將由Rampant科技出版社出版的書《Oracle 結(jié)構(gòu)化查詢語言（SQL）和內(nèi)在索引》，作者是Kimberly Floss。從該系列的主頁上，你能看到其他即將發(fā)布的部分。

　　如果你不能更改代碼。。

　　雖然系統(tǒng)級性能調(diào)優(yōu)不是結(jié)構(gòu)化查詢語言（SQL）性能調(diào)優(yōu)的最好解決方案，但是，當(dāng)你不能改變某些SQL語句時(shí)（例如，提供商提供的某些包中的SQL語句），使用SQL處理的常規(guī)模式來調(diào)節(jié)系統(tǒng)性能是極其有幫助的。在日常工作經(jīng)驗(yàn)的基礎(chǔ)上，Oracle數(shù)據(jù)庫管理員改變某些優(yōu)化參數(shù)來適應(yīng)庫緩沖區(qū)中SQL類型的變化是很常見的事。

　　一些較為常見的變更：

　　1、針對Oracle參數(shù)的更改。對optimizer_mode，optimizer_index_cost_adj和optimizer_index_caching的改變能對SQL執(zhí)行計(jì)劃產(chǎn)生巨大影響。

　　2、 針對統(tǒng)計(jì)參數(shù)的更改。使用dbms_stats包導(dǎo)入特定的統(tǒng)計(jì)參數(shù)（針對當(dāng)然處理模式作了調(diào)整的）可對SQL的執(zhí)行速度產(chǎn)生巨大影響。

　　3、 使用自動(dòng)化查詢重新寫入。使用Oracle實(shí)體化視圖能夠預(yù)先聚集、預(yù)先匯總數(shù)據(jù)，從而減少運(yùn)行時(shí)刻表連接的數(shù)量。對于更新比較少的數(shù)據(jù)庫，也可以通過預(yù)先連接表來提高處理速度。

　　一些對性能調(diào)優(yōu)最重要的Oracle優(yōu)化參數(shù)如下：

　　* optimizer_mode（優(yōu)化模式）－在Oracle 9i中，有許多優(yōu)化模式，都是由參數(shù)optimizer_mode的值決定的。這個(gè)參數(shù)的取值范圍是rule, choose, all_rows, first_rows, first_rows_1, first_rows_10 和 first_rows_100.

　　我們以定義“最好的”執(zhí)行計(jì)劃作為開始點(diǎn)。在任何給定的時(shí)間，庫緩沖區(qū)中的所有SQL語句都需要有“最好的”執(zhí)行計(jì)劃（當(dāng)然，由于在任何給定的時(shí)間里處理需求可能不同，所以這個(gè)最優(yōu)執(zhí)行計(jì)劃可能會(huì)經(jīng)常發(fā)生變化）。什么是“最好的”執(zhí)行計(jì)劃？是返回結(jié)果最快的執(zhí)行計(jì)劃還是使用最少計(jì)算資源的執(zhí)行計(jì)劃？很明顯，答案依賴于你的數(shù)據(jù)庫的處理過程，Oracle提供了兩種優(yōu)化模式，允許你選擇你認(rèn)為的“最好的”執(zhí)行計(jì)劃：

　　1、 optimizer_mode=first_rows??相對全表掃描訪問，這個(gè)優(yōu)化模式更注重索引訪問。當(dāng)你想要一個(gè)查詢以最快的速度返回結(jié)果行時(shí)，即使它的邏輯輸入輸出總量比全表掃描高，也要使用這個(gè)模式在線訪問系統(tǒng)一般都使用這個(gè)模式，因?yàn)榻K端用戶想要盡快地看到第一頁查詢結(jié)果。

　　2、optimizer_mode=all_rows??這個(gè)優(yōu)化模式更注重全表掃描（特別是并發(fā)全表掃描），因?yàn)樵谶@種情況下服務(wù)器資源的開銷最小。這個(gè)模式一般被用于批處理進(jìn)程和數(shù)據(jù)倉庫中，它們的目標(biāo)都是使服務(wù)器消耗的資源最小化。

　　3、 optimizer_mode=first_rows_n??從Oracle 9i開始，又有一種新的優(yōu)化模式針對某些返回小結(jié)果集的查詢進(jìn)行優(yōu)化。其取值范圍是first_rows_1, first_rows_10 和 first_rows_100，使用這些參數(shù)值可以確保Oracle能夠優(yōu)化這類SQL。

　　雖然參數(shù)optimizer_mode控制了“基于代價(jià)的優(yōu)化”的總體行為，還有其他Oracle參數(shù)也會(huì)對“基于代價(jià)的優(yōu)化”產(chǎn)生相當(dāng)大的影響。Oracle提供了一些重要的參數(shù)來控制“基于代價(jià)的優(yōu)化”做出的選擇：

　　1、optimizer_index_cost_adj??這個(gè)參數(shù)可用來調(diào)整“基于代價(jià)的優(yōu)化”相對于全表掃描訪問而言，更加傾向于索引訪問的程度。這個(gè)值越小，“基于代價(jià)的優(yōu)化”就越有可能使用一個(gè)可用的索引。

　　2、 optimizer_index_caching??這個(gè)參數(shù)告訴Oracle你的索引在內(nèi)存的數(shù)據(jù)緩沖區(qū)中的可能性有多大。對這個(gè)參數(shù)的設(shè)置將會(huì)影響到“基于代價(jià)的優(yōu)化” 做出的對一個(gè)表連接（嵌套循環(huán)）使用索引還是使用全表掃描選擇。

　　3、 db_file_multiblock_read_count??當(dāng)把這個(gè)值設(shè)置得比較大時(shí)（使用更大的服務(wù)器），“基于代價(jià)的優(yōu)化”識別出分散的（多塊）讀操作的代價(jià)或許比識別順序讀操作的代價(jià)更小一些。這就使得“基于代價(jià)的優(yōu)化”更加傾向于全表掃描。

　　但是從Oracle 9.2版本開始，情況不再是這樣了。當(dāng)計(jì)算系統(tǒng)統(tǒng)計(jì)表時(shí)，它包含了“多塊讀操作記數(shù)”（MBRC），這個(gè)數(shù)字決定了全表掃描的成本。Oracle 10g則更進(jìn)一步，加入了一些“系統(tǒng)默認(rèn)值”，這些默認(rèn)值是非常不合適的。對于Oracle 9.2版本而言，請注意Metalink上的149560.1。

　　1、 parallel_automatic_tuning??當(dāng)該參數(shù)設(shè)置為“開啟”時(shí)，對于含有許多CPU的Oracle服務(wù)器，全表掃描并發(fā)執(zhí)行。因?yàn)椴l(fā)全表掃描的速度可以非?？?，所以“基于代價(jià)的優(yōu)化”對于索引訪問開銷很大，因此更加傾向于使用全表掃描。

　　2、 hash_area_size（假如不使用pga_aggregate_target的話）??這個(gè)參數(shù)設(shè)置“基于代價(jià)的優(yōu)化”相對于使用嵌套循環(huán)和排序合并表連接來說，更傾向于使用哈希連接的程度。

　　3、sort_area_size（只當(dāng)不使用參數(shù)pga_aggregate_target時(shí)）??這個(gè)參數(shù)影響了“基于代價(jià)的優(yōu)化”做出的執(zhí)行索引訪問還是執(zhí)行對結(jié)果集的排序的決定。這個(gè)參數(shù)值越高，則在內(nèi)存中執(zhí)行排序（比使用臨時(shí)表空間快上千倍）的可能性就越大，同時(shí)“基于代價(jià)的優(yōu)化”相對于使用預(yù)先排序好的索引檢索，更傾向于使用直接排序。

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com