在過去的十年中 Oracle 已經成為世界上最專業的數據庫之一對於 IT 專家來說就是要確保利用 Oracle 的強大特性來提高他們公司的生產力最有效的方法之一是通過 Oracle 調優它有大量的調整參數和技術來改進你的 Oracle 數據庫的性能
Oracle 調優是一個復雜的主題關於調優可以寫整整一本書不過為了改善 Oracle 數據庫的性能有一些基本的概念是每個 Oracle DBA 都應該遵從的
在這篇簡介中我們將簡要地介紹以下的 Oracle 主題—— 外部調整我們應該記住 Oracle 並不是單獨運行的因此我們將查看一下通過調整 Oracle 服務器以得到高的性能
——Row resequencing 以減少磁盤 I/O 我們應該懂得 Oracle 調優最重要的目標是減少 I/O ——Oracle SQL 調整 Oracle SQL 調整是 Oracle 調整中最重要的領域之一只要通過一些簡單的 SQL 調優規則就可以大幅度地提升 SQL 語句的性能這是一點都不奇怪的
—— 調整 Oracle 排序排序對於 Oracle 性能也是有很大影響的
—— 調整 Oracle 的競爭表和索引的參數設置對於 UPDATE 和 INSERT 的性能有很大的影響
我們首先從調整 Oracle 外部的環境開始如果內存和 CPU 的資源不足的話任何的 Oracle 調整都是沒有幫助的
外部的性能問題
Oracle 並不是單獨運行的 Oracle 數據庫的性能和外部的環境有很大的關系這些外部的條件包括有 CPU——CPU 資源的不足令查詢變慢當查詢超過了 Oracle 服務器的 CPU 性能時你的數據庫性能就受到 CPU 的限制內存 —— 可用於 Oralce 的內存數量也會影響 SQL 的性能特別是在數據緩沖和內存排序方面網絡 —— 大量的 Net 通信令 SQL 的性能變慢
許多新手都錯誤的認為應該首先調整 Oracle 數據庫而不是先確認外部資源是否足夠實際上如果外部環境出現瓶頸再多的 Oracle 調整都是沒有幫助的
在檢查 Oracle 的外部環境時有兩個方面是需要注意的 當運行隊列的數目超過服務器的 CPU 數量時服務器的性能就會受到 CPU 的限制補救的方法是為服務器增加額外的 CPU 或者關閉需要很多處理資源的組件例如 Oracle Parallel Query 內存分頁當內存分頁時內存容量已經不足而內存頁是與磁盤上的交換區進行交互的補救的方法是增加更多的內存減少 Oracle SGA 的大小或者關閉 Oracle 的多線程服務器
可以使用各種標准的服務器工具來得到服務器的統計數據例如 vmstatglancetop 和 sar DBA 的目標是確保數據庫服務器擁有足夠的 CPU 和內存資源來處理 Oracle 的請求
以下讓我們來看一下 Oracle 的 rowresequencing 是如何能夠極大地減少磁盤 I/O 的
Rowresequencing （行的重新排序）
就象我們上面提到的有經驗的 Oracle DBA 都知道 I/O 是響應時間的最大組成部分其中磁盤 I/O 特別厲害因為當 Oracle 由磁盤上的一個數據文件得到一個數據塊時讀的進程就必須等待物理 I/O 操作完成磁盤操作要比數據緩沖慢 倍因此如果可以令 I/O 最小化或者減少由於磁盤上的文件競爭而帶來的瓶頸就可以大大地改善 Oracle 數據庫的性能
如果系統響應很慢通過減少磁盤 I/O 就可以有一個很快的改善如果在一個事務中通過按一定的范圍搜索 primarykey 索引來訪問表那麼重新以 CTAS 的方法組織表將是你減少 I/O 的首要策略通過在物理上將行排序為和 primarykey 索引一樣的順序就可以加快獲得數據的速度
就象磁盤的負載平衡一樣行的重新排序也是很簡單的而且也很快通過與其它的 DBA 管理技巧一起使用就可以在高 I/O 的系統中大大地減少響應的時間
在高容量的在線事務處理環境中（ online transaction processing OLTP ）數據是由一個 primary 索引得到的重新排序表格的行就可以令連續塊的順序和它們的 primary 索引一樣這樣就可以在索引驅動的表格查詢中減少物理 I/O 並且改善響應時間這個技巧僅在應用選擇多行的時候有用或者在使用索引范圍搜索和應用發出多個查詢來得到連續的 key 時有效對於隨機的唯一 primarykey （主鍵）的訪問將不會由行重新排序中得到好處
讓我們看一下它是如何工作的考慮以下的一個 SQL 的查詢它使用一個索引來得到 行selectsalaryfromemployeewherelast_name like B%這個查詢將會使用 last_name_index 搜索其中的每一行來得到目標行這個查詢將會至少使用 次物理磁盤的讀取因為 employee 的行存放在不同的數據塊中
不過如果表中的行已經重新排序為和 last_name_index 的一樣同樣的查詢又會怎樣處理呢？我們可以看到這個查詢只需要三次的磁盤 I/O 就讀完全部 個員工的資料（一次用作索引的讀取兩次用作數據塊的讀取）減少了 次的塊讀取
重新排序帶來的性能改善的程度在於在你開始的時候行的亂序性如何以及你需要由序列中訪問多少行至於一個表中的行與索引的排序鍵的匹配程度可以查看數據字典中的 dba_indexes 和 dba_tables 視圖得到
在 dba_indexes 的視圖中查看 clustering_factor 列如果 clustering_factor 的值和表中的塊數目大致一樣那麼你的表和索引的順序是一樣的不過如果 clustering_factor 的值接近表中的行數目那就表明表格中的行和索引的順序是不一樣的
行重新排序的作用是不可以小看的在需要進行大范圍的索引搜索的大表中行重新排序可以令查詢的性能提高三倍
一旦你已經決定重新排序表中的行你可以使用以下的工具之一來重新組織表格 使用 Oracle 的 Create Table As Select （CTAS） 語法來拷貝表格 Oraclei 自帶的表格重新組織工具
以下我們來看以下 SQL 語句的調優
SQL 調優Oracle 的 SQL 調優是一個復雜的主題甚至是需要整本書來介紹 Oracle SQL 調優的細微差別不過有一些基本的規則是每個 Oracle DBA 都需要跟從的這些規則可以改善他們系統的性能 SQL 調優的目標是簡單的 消除不必要的大表全表搜索不必要的全表搜索導致大量不必要的 I/O 從而拖慢整個數據庫的性能調優專家首先會根據查詢返回的行數目來評價 SQL 在一個有序的表中如果查詢返回少於 % 的行或者在一個無序的表中返回少於 % 的行那麼這個查詢都可以調整為使用一個索引來代替全表搜索對於不必要的全表搜索來說最常見的調優方法是增加索引可以在表中加入標准的 B 樹索引也可以加入 bitmap 和基於函數的索引要決定是否消除一個全表搜索你可以仔細檢查索引搜索的 I/O 開銷和全表搜索的開銷它們的開銷和數據塊的讀取和可能的並行執行有關並將兩者作對比在一些情況下一些不必要的全表搜索的消除可以通過強制使用一個 index 來達到只需要在 SQL 語句中加入一個索引的提示就可以了 在全表搜索是一個最快的訪問方法時將小表的全表搜索放到緩存中調優專家應該確保有一個專門的數據緩沖用作行緩沖在 Oracle 中你可以使用 alter table xxx cache 語句在 Oracle 或以上小表可以被強制為放到 KEEP 池中緩沖 確保最優的索引使用 對於改善查詢的速度這是特別重要的有時 Oracle 可以選擇多個索引來進行查詢調優專家必須檢查每個索引並且確保 Oracle 使用正確的索引它還包括 bitmap 和基於函數的索引的使用 確保最優的 JOIN 操作有些查詢使用 NESTED LOOP join 快一些有些則是 HASH join 快一些另外一些則是 sortmerge join 更快
這些規則看來簡單不過它們占 SQL 調優任務的 % 並且它們也無需完全懂得 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） 那麼就會根據 initora 中 sort_area_size 參數的大小在內存中分配一個 Program Global Area （PGA） 如果連接是通過多線程服務器建立的那麼排序的空間就在 large_pool 中分配不幸的是對於所有的 session 用做排序的內存量都必須是一樣的我們不能為需要更大排序的操作分配額外的排序區域因此設計者必須作出一個平衡在分配足夠的排序區域以避免發生大的排序任務時出現磁盤排序（ disk sorts ）的同時對於那些並不需要進行很大排序的任務就會出現一些浪費當然當排序的空間需求超出了 sort_area_size 的大小時這時將會在 TEMP 表空間中分頁進行磁盤排序磁盤排序要比內存排序大概慢 倍
上面我們已經提到私有排序區域的大小是有 initora 中的 sort_area_size 參數決定的每個排序所占用的大小由 initora 中的 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 unlinks 的（即將塊由 freelists 中移除）設置 pctfree= 意味著每個塊都保留 % 的空間用作行擴展 pctused 參數是控制 freelist relinks 的設置 pctused= 意味著只有在塊的使用低於 % 時才會回到表格的 freelists 中
許多新手對於一個塊重新回到 freelists 後的處理都有些誤解其實一旦由於一個刪除的操作而令塊被重新加入到 freelist 中它將會一直保留在 freelist 中即使空間的使用超過了 % 只有在到達 pctfree 時才會將數據塊由 freelist 中移走
表格和索引存儲參數設置的要求總結以下的一些規則是用來設置 freelists freelist groups 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 將在擴展表來得到一個空的塊之前企圖讀取 個 dead 的 free block 表格中 chained rows 的出現意味著 pctfree 太低或者是 db_block_size 太少在很多情況下 RAW 和 LONG RAW 列都很巨大以至超過了 Oracle 的最大塊的大小這時 chained rows 是不可以避免的如果一個表有同時插入的 SQL 語句那麼它需要有同時刪除的語句運行單一個一個清除的工作將會把全部的空閒塊放到一個 freelist 中而沒有其它包含有任何空閒塊的 freelists 出現 freelist 參數應該設置為表格同時更新的最大值例如如果在任何時候某個表最多有 個用戶執行插入的操作那麼該表的參數應該設置為 freelists= 應記住的是 freelist groups 參數的值只是對於 Oracle Parallel Server 和 Real Application Clusters 才是有用的對於這類 Oracle freelist groups 應該設置為訪問該表格的 Oracle Parallel Server 實例的數目
From:http://tw.wingwit.com/Article/program/Oracle/201311/17989.html
