[最佳化]Oracle 內在索引和引數數的調整(轉)

[最佳化]Oracle 內在索引和引數數的調整(轉)[@more@]本文是有關Oracle 10g基於成本的內部最佳化（CBO）和結構化查詢語言（SQL）效能最最佳化的一系列文章（共12部分）中的第10部分。每個技巧類的文章都摘錄自即將由Rampant科技出版社出版的書《Oracle 結構化查詢語言（SQL）和內在索引》，作者是Kimberly Floss。從該系列的主頁上，你能看到其他即將釋出的部分。

如果你不能更改程式碼。。。

雖然系統級效能調優不是結構化查詢語言（SQL）效能調優的最好解決方案，但是，當你不能改變某些SQL語句時（例如，提供商提供的某些包中的SQL語句），使用SQL處理的常規模式來調節系統效能是極其有幫助的。在日常工作經驗的基礎上，Oracle資料庫管理員改變某些最佳化引數來適應庫緩衝區中SQL型別的變化是很常見的事。

一些較為常見的變更：

1、針對Oracle引數的更改。對optimizer_mode，optimizer_index_cost_adj和optimizer_index_caching的改變能對SQL執行計劃產生巨大影響。
2、 針對統計引數的更改。使用dbms_stats包匯入特定的統計引數（針對當然處理模式作了調整的）可對SQL的執行速度產生巨大影響。
3、 使用自動化查詢重新寫入。使用Oracle實體化檢視能夠預先聚集、預先彙總資料，從而減少執行時刻表連線的數量。對於更新比較少的資料庫，也可以透過預先連線表來提高處理速度。

一些對效能調優最重要的Oracle最佳化引數如下：

* optimizer_mode（最佳化模式）－在Oracle 9i中，有許多最佳化模式，都是由引數optimizer_mode的值決定的。這個引數的取值範圍是rule, choose, all_rows, first_rows, first_rows_1, first_rows_10 和 first_rows_100.

我們以定義“最好的”執行計劃作為開始點。在任何給定的時間，庫緩衝區中的所有SQL語句都需要有“最好的”執行計劃（當然，由於在任何給定的時間裡處理需求可能不同，所以這個最優執行計劃可能會經常發生變化）。什麼是“最好的”執行計劃？是返回結果最快的執行計劃還是使用最少計算資源的執行計劃？很明顯，答案依賴於你的資料庫的處理過程，Oracle提供了兩種最佳化模式，允許你選擇你認為的“最好的”執行計劃：

1、 optimizer_mode=first_rows――相對全表掃描訪問，這個最佳化模式更注重索引訪問。當你想要一個查詢以最快的速度返回結果行時，即使它的邏輯輸入輸出總量比全表掃描高，也要使用這個模式線上訪問系統一般都使用這個模式，因為終端使用者想要儘快地看到第一頁查詢結果。

2、optimizer_mode=all_rows――這個最佳化模式更注重全表掃描（特別是併發全表掃描），因為在這種情況下伺服器資源的開銷最小。這個模式一般被用於批處理程式和資料倉儲中，它們的目標都是使伺服器消耗的資源最小化。

3、 optimizer_mode=first_rows_n――從Oracle 9i開始，又有一種新的最佳化模式針對某些返回小結果集的查詢進行最佳化。其取值範圍是first_rows_1, first_rows_10 和 first_rows_100，使用這些引數值可以確保Oracle能夠最佳化這類SQL。

雖然引數optimizer_mode控制了“基於代價的最佳化”的總體行為，還有其他Oracle引數也會對“基於代價的最佳化”產生相當大的影響。Oracle提供了一些重要的引數來控制“基於代價的最佳化”做出的選擇：

1、optimizer_index_cost_adj――這個引數可用來調整“基於代價的最佳化”相對於全表掃描訪問而言，更加傾向於索引訪問的程度。這個值越小，“基於代價的最佳化”就越有可能使用一個可用的索引。

2、 optimizer_index_caching――這個引數告訴Oracle你的索引在記憶體的資料緩衝區中的可能性有多大。對這個引數的設定將會影響到“基於代價的最佳化” 做出的對一個表連線（巢狀迴圈）使用索引還是使用全表掃描選擇。

3、 db_file_multiblock_read_count――當把這個值設定得比較大時（使用更大的伺服器），“基於代價的最佳化”識別出分散的（多塊）讀操作的代價或許比識別順序讀操作的代價更小一些。這就使得“基於代價的最佳化”更加傾向於全表掃描。

但是從Oracle 9.2版本開始，情況不再是這樣了。當計算系統統計表時，它包含了“多塊讀操作記數”（MBRC），這個數字決定了全表掃描的成本。Oracle 10g則更進一步，加入了一些“系統預設值”，這些預設值是非常不合適的。對於Oracle 9.2版本而言，請注意Metalink上的149560.1。

1、 parallel_automatic_tuning――當該引數設定為“開啟”時，對於含有許多CPU的Oracle伺服器，全表掃描併發執行。因為併發全表掃描的速度可以非常快，所以“基於代價的最佳化”對於索引訪問開銷很大，因此更加傾向於使用全表掃描。

2、 hash_area_size（假如不使用pga_aggregate_target的話）――這個引數設定“基於代價的最佳化”相對於使用巢狀迴圈和排序合併表連線來說，更傾向於使用雜湊連線的程度。

3、sort_area_size（只當不使用引數pga_aggregate_target時）――這個引數影響了“基於代價的最佳化”做出的執行索引訪問還是執行對結果集的排序的決定。這個引數值越高，則在記憶體中執行排序（比使用臨時表空間快上千倍）的可能性就越大，同時“基於代價的最佳化”相對於使用預先排序好的索引檢索，更傾向於使用直接排序。