Oracle優化的五個方面

在dba_indexes的檢視中，檢視clustering_factor列。如果clustering_factor的值和表中的塊數目大致一樣，那麼你的表和索引的順序是一樣的。不過，如果clustering_factor的值接近表中的行數目，那就表明表格中的行和索引的順序是不一樣的。

行重新排序的作用是不可以小看的。在需要進行大範圍的索引搜尋的大表中，行重新排序可以令查詢的效能提高三倍。

一旦你已經決定重新排序表中的行，你可以使用以下的工具之一來重新組織表格。

◆使用Oracle的Create Table As Select (CTAS)語法來拷貝表格

◆Oracle9i自帶的表格重新組織工具

以下，我們來看以下SQL語句的調優。

SQL調優

Oracle的SQL調優是一個複雜的主題，甚至是需要整本書來介紹Oracle SQL調優的細微差別。不過有一些基本的規則是每個Oracle DBA都需要跟從的，這些規則可以改善他們系統的效能。SQL調優的目標是簡單的：

◆消除不必要的大表全表搜尋：不必要的全表搜尋導致大量不必要的I/O，從而拖慢整個資料庫的效能。調優專家首先會根據查詢返回的行數目來評價SQL。在一個有序的表中，如果查詢返回少於40%的行，或者在一個無序的表中，返回少於7%的行，那麼這個查詢都可以調整為使用一個索引來代替全表搜尋。對於不必要的全表搜尋來說，最常見的調優方法是增加索引。可以在表中加入標準的B樹索引，也可以加入bitmap和基於函式的索引。要決定是否消除一個全表搜尋，你可以仔細檢查索引搜尋的I/O開銷和全表搜尋的開銷，它們的開銷和資料塊的讀取和可能的並行執行有關，並將兩者作對比。在一些情況下，一些不必要的全表搜尋的消除可以通過強制使用一個index來達到，只需要在SQL語句中加入一個索引的提示就可以了。

◆在全表搜尋是一個最快的訪問方法時，將小表的全表搜尋放到快取中，調優專家應該確保有一個專門的資料緩衝用作行緩衝。在Oracle7中，你可以使用alter table xxx cache語句，在Oracle8或以上，小表可以被強制為放到KEEP池中緩衝。

◆確保最優的索引使用：對於改善查詢的速度，這是特別重要的。有時Oracle可以選擇多個索引來進行查詢，調優專家必須檢查每個索引並且確保Oracle使用正確的索引。它還包括bitmap和基於函式的索引的使用。

◆確保最優的JOIN操作：有些查詢使用NESTED LOOP join快一些，有些則是HASH join快一些，另外一些則是sort-merge join更快。

這些規則看來簡單，不過它們佔SQL調優任務的90%，並且它們也無需完全懂得Oracle SQL的內部運作。以下我們來簡單概覽以下Oracle SQL的優化。

我們首先簡要檢視Oracle的排序，並且看一看排序操作是如何影響效能的。

調整Oracle的排序操作

排序是SQL語法中一個小的方面，但很重要，在Oracle的調整中，它常常被忽略。當使用create index、ORDER BY或者GROUP BY的語句時，Oracle資料庫將會自動執行排序的操作。通常，在以下的情況下Oracle會進行排序的操作：

使用Order by的SQL語句

使用Group by的SQL語句在建立索引的時候進行table join時，由於現有索引的不足而導致SQL優化器呼叫MERGE SORT。

當與Oracle建立起一個session時，在記憶體中就會為該session分配一個私有的排序區域。如果該連線是一個專用的連線(dedicated connection)，那麼就會根據init.ora中sort_area_size引數的大小在記憶體中分配一個Program Global Area (PGA)。如果連線是通過多執行緒伺服器建立的，那麼排序的空間就在large_pool中分配。不幸的是，對於所有的session，用做排序的記憶體量都必須是一樣的，我們不能為需要更大排序的操作分配額外的排序區域。因此，設計者必須作出一個平衡，在分配足夠的排序區域以避免發生大的排序任務時出現磁碟排序（disk sorts）的同時，對於那些並不需要進行很大排序的任務，就會出現一些浪費。當然，當排序的空間需求超出了sort_area_size的大小時，這時將會在TEMP表空間中分頁進行磁碟排序。磁碟排序要比記憶體排序大概慢14,000倍。

上面我們已經提到，私有排序區域的大小是有init.ora中的sort_area_size引數決定的。每個排序所佔用的大小由init.ora中的sort_area_retained_size引數決定。當排序不能在分配的空間中完成時，就會使用磁碟排序的方式，即在Oracle例項中的臨時表空間中進行。

磁碟排序的開銷是很大的，有幾個方面的原因。首先，和記憶體排序相比較，它們特別慢；而且磁碟排序會消耗臨時表空間中的資源。Oracle還必須分配緩衝池塊來保持臨時表空間中的塊。無論什麼時候，記憶體排序都比磁碟排序好，磁碟排序將會令任務變慢，並且會影響Oracle例項的當前任務的執行。還有，過多的磁碟排序將會令free buffer waits的值變高，從而令其它任務的資料塊由緩衝中移走。

接著，讓我們看一下Oracle的競爭，並且看一下表的儲存引數的設定是如何影響SQL UPDATE和INSERT語句的效能的。

調整Oracle的競爭

Oracle的其中一個優點時它可以管理每個表空間中的自由空間。Oracle負責處理表和索引的空間管理，這樣就可以讓我們無需懂得Oracle的表和索引的內部運作。不過，對於有經驗的Oracle調優專家來說，他需要懂得Oracle是如何管理表的extent和空閒的資料塊。對於調整擁有高的insert或者update的系統來說，這是非常重要的。

要精通物件的調整，你需要懂得freelists和freelist組的行為，它們和pctfree及pctused引數的值有關。這些知識對於企業資源計劃（ERP）的應用是特別重要的，因為在這些應用中，不正確的表設定通常是DML語句執行慢的原因。

對於初學者來說，最常見的錯誤是認為預設的Oracle引數對於所有的物件都是最佳的。除非磁碟的消耗不是一個問題，否則在設定表的pctfree和pctused引數時，就必須考慮平均的行長和資料庫的塊大小，這樣空的塊才會被有效地放到freelists中。當這些設定不正確時，那些得到的freelists也是“dead”塊，因為它們沒有足夠的空間來儲存一行，這樣將會導致明顯的處理延遲。

Freelists對於有效地重新使用Oracle表空間中的空間是很重要的，它和pctfree及pctused這兩個儲存引數的設定直接相關。

通過將pctused設定為一個高的值，這時資料庫就會盡快地重新使用塊。不過，高效能和有效地重新使用表的塊是對立的。在調整Oracle的表格和索引時，需要認真考慮究竟需要高效能還是有效的空間重用，並且據此來設定表的引數。以下我們來看一下這些freelists是如何影響Oracle的效能的。

當有一個請求需要插入一行到表格中時，Oracle就會到freelist中尋找一個有足夠的空間來容納一行的塊。你也許知道，freelist串是放在表格或者索引的第一個塊中，這個塊也被稱為段頭（segment header）。pctfree和pctused引數的唯一目的就是為了控制塊如何在freelists中進出。雖然freelist link和unlink是簡單的Oracle功能，不過設定freelist link(pctused)和unlink(pctfree)對Oracle的效能確實有影響。

由DBA的基本知識知道，pctfree引數是控制freelist un-links的（即將塊由freelists中移除）。設定pctfree=10意味著每個塊都保留10%的空間用作行擴充套件。pctused引數是控制freelist re-links的。設定pctused=40意味著只有在塊的使用低於40%時才會回到表格的freelists中。

許多新手對於一個塊重新回到freelists後的處理都有些誤解。其實，一旦由於一個刪除的操作而令塊被重新加入到freelist中，它將會一直保留在freelist中即使空間的使用超過了60%，只有在到達pctfree時才會將資料塊由freelist中移走。

表格和索引儲存引數設定的要求總結

以下的一些規則是用來設定freelists,freelistgroups,pctfree和pctused儲存引數的。你也知道，pctused和pctfree的值是可以很容易地通過alter table命令修改的，一個好的DBA應該知道如何設定這些引數的最佳值。

有效地使用空間和高效能之間是有矛盾的，而表格的儲存引數就是控制這個方面的矛盾：

◆對於需要有效地重新使用空間，可以設定一個高的pctused值，不過副作用是需要額外的I/O。一個高的pctused值意味著相對滿的塊都會放到freelist中。因此，這些塊在再次滿之前只可以接受幾行記錄，從而導致更多的I/O。

◆追求高效能的話，可以將pctused設定為一個低的值，這意味著Oracle不會將資料塊放到freelists中直到它幾乎是空的。

那麼塊將可以在滿之前接收更多的行，因此可以減少插入操作的I/O。要記住Oracle擴充套件新塊的效能要比重新使用現有的塊高。對於Oracle來說，擴充套件一個表比管理freelists消耗更少的資源。

讓我們來回顧一下設定物件儲存引數的一些常見規則：

◆經常將pctused設定為可以接收一條新行。對於不能接受一行的free blocks對於我們來說是沒有用的。如果這樣做，將會令Oracle的效能變慢，因為Oracle將在擴充套件表來得到一個空的塊之前，企圖讀取5個“dead”的free block 。

◆表格中chained rows的出現意味著pctfree太低或者是db_block_size太少。在很多情況下，RAW和LONG RAW列都很巨大，以至超過了Oracle的最大塊的大小，這時chained rows是不可以避免的。

◆如果一個表有同時插入的SQL語句，那麼它需要有同時刪除的語句。執行單一個一個清除的工作將會把全部的空閒塊放到一個freelist中，而沒有其它包含有任何空閒塊的freelists出現。

◆freelist引數應該設定為表格同時更新的最大值。例如，如果在任何時候，某個表最多有20個使用者執行插入的操作，那麼該表的引數應該設定為freelists=20。

應記住的是freelist groups引數的值只是對於Oracle Parallel Server和Real Application Clusters才是有用的。對於這類Oracle，freelist groups應該設定為訪問該表格的Oracle Parallel Server例項的數目。