1、目錄第1章 性能調整綜述第2章 有效的應用設計第3章 SQL語句處理的過程第4章 ORACLE的優化器第5章 ORACLE的執行計劃 訪問路徑(方法) - access path 表之間的連接 如何產生執行計劃 如何分析執行計劃 如何干預執行計劃 - - 使用hints提示 具體案例分析第6章 其它注意事項附錄第1章 性能調整綜述 Oracle數據庫是高度可調的數據庫產品。本章描述調整的過程和那些人員應與Oracle服務器的調整有關，以及與調整相關聯的操作系統硬件和軟件。本章包括以下方面:l 誰來調整系統?l 什么時候調整?l 建立有效調整的目標l 在設計和開發時的調整l 調整產品系統l 監控

2、產品系統誰來調整系統: 為了有效地調整系統，若干類人員必須交換信息并牽涉到系統調整中，例如:l 應用設計人員必須傳達應用系統的設計，使得每個人都清楚應用中的數據流動.l 應用開發人員必須傳達他們選擇的實現策略，使得語句調整的過程中能快速、容易地識別有問題的應用模塊和可疑的SQL語句.l 數據庫管理人員必須仔細地監控系統活動并提供它們的資料，使得異常的系統性能可被快速得識別和糾正.l 硬件/軟件管理人員必須傳達系統的硬件、軟件配置并提供它們的資料，使得相關人員能有效地設計和管理系統。 簡而言之，與系統涉及的每個人都在調整過程中起某些作用，當上面提及的那些人員傳達了系統的特性并提供了它們的資料，調

3、整就能相對的容易和更快一些。 不幸的是，事實上的結果是：數據庫管理員對調整負有全部或主要的責任。但是，數據庫管理員很少有合適的系統方面的資料，而且，在很多情況下，數據庫管理員往往是在實施階段才介入數據庫，這就給調整工作帶來許多負面的影響，因為在設計階段的缺陷是不能通過DBA的調整而得以解決，而設計階段的缺陷往往對數據庫性能造成極大的影響。 其實，在真正成熟的開發環境下，開發人員作為純代碼編寫人員時，對性能的影響最小，此時大部分的工作應由應用設計人員完成，而且數據庫管理員往往在前期的需求管理階段就介入，為設計人員提供必要的技術支持。調整并不是數據庫管理員的專利，相反大部分應該是設計人員和開發人員

4、的工作，這就需要設計人員和開發人員具體必要的數據庫知識，這樣才能組成一個高效的團隊，然而事實上往往并非如此。什么時候作調整？ 多數人認為當用戶感覺性能差時才進行調整，這對調整過程中使用某些最有效的調整策略來說往往是太遲了。此時，如果你不愿意重新設計應用的話，你只能通過重新分配內存(調整SGA)和調整I/O的辦法或多或少地提高性能。Oracle提供了許多特性，這些特性只有應用到正確地設計的系統中時才能夠很大地提高性能。 應用設計人員需要在設計階段設置應用的性能期望值。然后在設計和開發期間，應用設計人員應考慮哪些Oracle 特性可以對系統有好處，并使用這些特性。通過良好的系統設計，你就可以在應用

5、的生命周期中消除性能調整的代價和挫折。圖1-1圖1-2說明在應用的生命周期中調整的相對代價和收益，正如你見到的，最有效的調整時間是在設計階段。在設計期間的調整能以最低的代價給你最大的收益。圖1-1在應用生命周期中調整的代價圖1-2 在應用生命周期中調整的收益 當然，即使在設計很好的系統中，也可能有性能降低。但這些性能降低應該是可控的和可以預見的。調整目標 不管你正在設計或維護系統，你應該建立專門的性能目標，它使你知道何時要作調整。如果你試圖胡亂地改動初始化參數或SQl 語句，你可能會浪費調整系統的時間，而且無什么大的收益。調整你的系統的最有效方法如下：l 當設計系統時考慮性能l 調整操作系統的

6、硬件和軟件l 識別性能瓶頸l 確定問題的原因l 采取糾正的動作當你設計系統時，制定專門的目標；例如，響應時間小于秒。當應用不能滿足此目標時，識別造成變慢的瓶頸（例如，I/O競爭），確定原因，采取糾正動作。在開發期間，你應測試應用研究，確定在采取應用之前是否滿足設計的性能目標。 當你正在維護生產庫系統時，有多種快速有效的方法來識別性能瓶頸。不管怎樣，調整通常是一系列開銷。一旦你已確定了瓶頸，你可能要犧牲一些其它方面的指標來達到所要的結果。例如，如果I/O有問題，你可能需要更多內存或磁盤。如果不可能買，你可能要限制系統的并發性，來獲取所需的性能。然而，如果你已經明確地定義了性能的目標，那用什么來交

7、換高性能的決策就變的很容易的，因為你已經確定了哪些方面是最重要的，如過我的目標為高性能，可能犧牲一些空間資源。 隨著應用的越來越龐大，硬件性能的提高，全面的調整應用逐漸變成代價高昂的行為，在這樣情況下，要取得最大的投入/效率之比，較好的辦法是調整應用的關鍵部分，使其達到比較高的性能，這樣從總體上來說，整個系統的性能也是比較高的。這也就是有名的20/80原則，調整應用的20%(關鍵部分)，能解決80%的問題。在設計和開發系統時作調整 良好設計的系統可以防止在應用生命周期中產生性能問題。系統設計人員和應用開發人員必須了解Oracle的查詢處理機制以便寫出高效的SQL語句。“第2章 有效的應用設計”

8、討論了你的系統中各種可用的配置，以及每種配置更適合哪種類型的應用。“第5章 優化器”討論了Oracle的查詢優化器，以及如何寫語句以獲取最快的結果。當設計你的系統時，使用下列優化性能的準則：l 消除客戶機服務器應用中不必要的網絡傳輸。- 使用存儲過程。l 使用適合你系統的相應Oracle服務器選件（例如，并行查詢或分布式數據庫）。l 除非你的應用有特殊的需要，否則使用缺省的Oracle鎖。l 利用數據庫記住應用模塊，以便你能以每個模塊為基礎來追蹤性能。l 選擇你的數據塊的最佳大小。 - 原則上來說大一些的性能較好。l 分布你的數據，使得一個節點使用的數據本地存貯在該節點中。調整產品系統 本節描

9、述對應用系統快速、容易地找出性能瓶頸，并決定糾正動作的方法。這種方法依賴于對Oracle服務器體系結構和特性的了解程度。在試圖調整你的系統前，你應熟悉Oracle調整的內容。為調整你已有的系統，遵從下列步驟：l 調整操作系統的硬件和軟件l 通過查詢V $SESSION_WAIT視圖，識別性能的瓶頸，這個動態性能視圖列出了造成會話(session)等待的事件。l 通過分析V $SESSION_WAIT中的數據，決定瓶頸的原因。l 糾正存在的問題。監控應用系統這主要是通過監控oracle的動態視圖來完成。各種有用的動態視圖：如v$session_wait, v$session_event等。 第2

10、章 有效的應用設計 我們通常將最常用的應用分為2種類型：聯機事務處理類型(OLTP)，決策支持系統(DSS)。聯機事務處理(OLTP) 該類型的應用是高吞吐量，插入、更新、刪除操作比較多的系統，這些系統以不斷增長的大容量數據為特征，它們提供給成百用戶同時存取，典型的OLTP系統是訂票系統，銀行的業務系統，訂單系統。OTLP的主要目標是可用性、速度、并發性和可恢復性。 當設計這類系統時，必須確保大量的并發用戶不能干擾系統的性能。還需要避免使用過量的索引與cluster 表，因為這些結構會使插入和更新操作變慢。決策支持(DSS) 該類型的應用將大量信息進行提取形成報告，協助決策者作出正確的判斷。典

11、型的情況是：決策支持系統將OLTP應用收集的大量數據進行查詢。典型的應用為客戶行為分析系統(超市，保險等)。 決策支持的關鍵目標是速度、精確性和可用性。 該種類型的設計往往與OLTP設計的理念背道而馳，一般建議使用數據冗余、大量索引、cluster table、并行查詢等。 近年來，該類型的應用逐漸與OLAP、數據倉庫緊密的聯系在一起，形成的一個新的應用方向。第3章 SQL語句處理的過程 在調整之前我們需要了解一些背景知識，只有知道這些背景知識，我們才能更好的去調整sql語句。本節介紹了SQL語句處理的基本過程，主要包括：· 查詢語句處理· DML語句處理(insert,

12、update, delete)· DDL 語句處理(create . , drop . , alter . , )· 事務控制(commit, rollback)SQL 語句的執行過程(SQL Statement Execution) 圖3-1 概要的列出了處理和運行一個sql語句的需要各個重要階段。在某些情況下，Oracle運行sql的過程可能與下面列出的各個階段的順序有所不同。如DEFINE階段可能在FETCH階段之前，這主要依賴你如何書寫代碼。 對許多oracle的工具來說，其中某些階段會自動執行。絕大多數用戶不需要關心各個階段的細節問題，然而，知道執行的各個階段還是

13、有必要的，這會幫助你寫出更高效的SQL語句來，而且還可以讓你猜測出性能差的SQL語句主要是由于哪一個階段造成的，然后我們針對這個具體的階段，找出解決的辦法。圖 3-1 SQL語句處理的各個階段DML語句的處理 本節給出一個例子來說明在DML語句處理的各個階段到底發生了什么事情。假設你使用Pro*C程序來為指定部門的所有職員增加工資。程序已經連到正確的用戶，你可以在你的程序中嵌入如下的SQL語句：EXEC SQL UPDATE employeesSET salary = 1.10 * salary WHERE department_id = :var_department_id;var_depa

14、rtment_id是程序變量，里面包含部門號，我們要修改該部門的職員的工資。當這個SQL語句執行時，使用該變量的值。每種類型的語句都需要如下階段：· 第1步: Create a Cursor 創建游標· 第2步: Parse the Statement 分析語句· 第5步: Bind Any Variables 綁定變量· 第7步: Run the Statement 運行語句· 第9步: Close the Cursor 關閉游標如果使用了并行功能，還會包含下面這個階段：· 第6步: Parallelize the Statemen

15、t 并行執行語句如果是查詢語句，則需要以下幾個額外的步驟，如圖 3所示：· 第3步: Describe Results of a Query 描述查詢的結果集· 第4步: Define Output of a Query 定義查詢的輸出數據· 第8步: Fetch Rows of a Query 取查詢出來的行下面具體說一下每一步中都發生了什么事情：.第1步: 創建游標(Create a Cursor) 由程序接口調用創建一個游標（cursor）。任何SQL語句都會創建它，特別在運行DML語句時，都是自動創建游標的，不需要開發人員干預。多數應用中，游標的創建是自動

16、的。然而，在預編譯程序(pro*c)中游標的創建，可能是隱含的，也可能顯式的創建。在存儲過程中也是這樣的。第2步:分析語句(Parse the Statement) 在語法分析期間，SQL語句從用戶進程傳送到Oracle，SQL語句經語法分析后，SQL語句本身與分析的信息都被裝入到共享SQL區。在該階段中，可以解決許多類型的錯誤。語法分析分別執行下列操作：l 翻譯SQL語句，驗證它是合法的語句，即書寫正確l 實現數據字典的查找，以驗證是否符合表和列的定義l 在所要求的對象上獲取語法分析鎖，使得在語句的語法分析過程中不改變這些對象的定義l 驗證為存取所涉及的模式對象所需的權限是否滿足l 決定此語

17、句最佳的執行計劃l 將它裝入共享SQL區l 對分布的語句來說，把語句的全部或部分路由到包含所涉及數據的遠程節點 以上任何一步出現錯誤，都將導致語句報錯，中止執行。 只有在共享池中不存在等價SQL語句的情況下，才對SQL語句作語法分析。在這種情況下，數據庫內核重新為該語句分配新的共享SQL區，并對語句進行語法分析。進行語法分析需要耗費較多的資源，所以要盡量避免進行語法分析，這是優化的技巧之一。 語法分析階段包含了不管此語句將執行多少次，而只需分析一次的處理要求。Oracle只對每個SQL語句翻譯一次，在以后再次執行該語句時，只要該語句還在共享SQL區中，就可以避免對該語句重新進行語法分析，也就是

18、此時可以直接使用其對應的執行計劃對數據進行存取。這主要是通過綁定變量(bind variable)實現的，也就是我們常說的共享SQL，后面會給出共享SQL的概念。 雖然語法分析驗證了SQL語句的正確性，但語法分析只能識別在SQL語句執行之前所能發現的錯誤(如書寫錯誤、權限不足等)。因此，有些錯誤通過語法分析是抓不到的。例如，在數據轉換中的錯誤或在數據中的錯（如企圖在主鍵中插入重復的值）以及死鎖等均是只有在語句執行階段期間才能遇到和報告的錯誤或情況。查詢語句的處理 查詢與其它類型的SQL語句不同，因為在成功執行后作為結果將返回數據。其它語句只是簡單地返回成功或失敗，而查詢則能返回一行或許多行數據

19、。查詢的結果均采用表格形式，結果行被一次一行或者批量地被檢索出來。從這里我們可以得知批量的fetch數據可以降低網絡開銷，所以批量的fetch也是優化的技巧之一。 有些問題只與查詢處理相關，查詢不僅僅指SELECT語句，同樣也包括在其它SQL語句中的隱含查詢。例如，下面的每個語句都需要把查詢作為它執行的一部分：INSERT INTO table SELECT.UPDATE table SET x = y WHERE.DELETE FROM table WHERE.CREATE table AS SELECT.具體來說，查詢· 要求讀一致性· 可能使用回滾段作中間處理

20、3; 可能要求SQL語句處理描述、定義和取數據階段第3步: 描述查詢結果(Describe Results of a Query) 描述階段只有在查詢結果的各個列是未知時才需要；例如，當查詢由用戶交互地輸入需要輸出的列名。在這種情況要用描述階段來決定查詢結果的特征（數據類型，長度和名字）。第4步: 定義查詢的輸出數據(Define Output of a Query) 在查詢的定義階段，你指定與查詢出的列值對應的接收變量的位置、大小和數據類型，這樣我們通過接收變量就可以得到查詢結果。如果必要的話，Oracle會自動實現數據類型的轉換。這是將接收變量的類型與對應的列類型相比較決定的。第5步: 綁

21、定變量(Bind Any Variables) 此時，Oracle知道了SQL語句的意思，但仍沒有足夠的信息用于執行該語句。Oracle 需要得到在語句中列出的所有變量的值。在該例中，Oracle需要得到對department_id列進行限定的值。得到這個值的過程就叫綁定變量(binding variables) 此過程稱之為將變量值捆綁進來。程序必須指出可以找到該數值的變量名（該變量被稱為捆綁變量，變量名實質上是一個內存地址，相當于指針）。應用的最終用戶可能并沒有發覺他們正在指定捆綁變量，因為Oracle 的程序可能只是簡單地指示他們輸入新的值，其實這一切都在程序中自動做了。 因為你指定了變

22、量名，在你再次執行之前無須重新捆綁變量。你可以改變綁定變量的值，而Oracle在每次執行時，僅僅使用內存地址來查找此值。 如果Oracle 需要實現自動數據類型轉換的話（除非它們是隱含的或缺省的），你還必須對每個值指定數據類型和長度。關于這些信息可以參考oracle的相關文檔，如Oracle Call Interface Programmer's Guide第6步: 并行執行語句(Parallelize the Statement ) ORACLE 可以在SELECTs, INSERTs, UPDATEs, MERGEs, DELETEs語句中執行相應并行查詢操作，對于某些DDL操作，

23、如創建索引、用子查詢創建表、在分區表上的操作，也可以執行并行操作。并行化可以導致多個服務器進程(oracle server processes)為同一個SQL語句工作，使該SQL語句可以快速完成，但是會耗費更多的資源，所以除非很有必要，否則不要使用并行查詢。第7步: 執行語句(Run the Statement) 到了現在這個時候，Oracle擁有所有需要的信息與資源，因此可以真正運行SQL語句了。如果該語句為SELECT查詢或INSERT語句，則不需要鎖定任何行，因為沒有數據需要被改變。然而，如果語句為UPDATE或DELETE語句，則該語句影響的所有行都被鎖定，防止該用戶提交或回滾之前，別

24、的用戶對這些數據進行修改。這保證了數據的一致性。 對于某些語句，你可以指定執行的次數，這稱為批處理(array processing)。指定執行N次，則綁定變量與定義變量被定義為大小為N的數組的開始位置，這種方法可以減少網絡開銷，也是優化的技巧之一。第8步: 取出查詢的行(Fetch Rows of a Query) 在fetch階段，行數據被取出來，每個后續的存取操作檢索結果集中的下一行數據，直到最后一行被取出來。上面提到過，批量的fetch是優化的技巧之一。第9步: 關閉游標(Close the Cursor) SQL語句處理的最后一個階段就是關閉游標DDL語句的處理(DDL Statem

25、ent Processing) DDL語句的執行不同與DML語句和查詢語句的執行，這是因為DDL語句執行成功后需要對數據字典數據進行修改。對于DDL語句，語句的分析階段實際上包括分析、查找數據字典信息和執行。 事務管理語句、會話管理語句、系統管理語句只有分析與執行階段，為了重新執行該語句，會重新分析與執行該語句。事務控制(Control of Transactions) 一般來說，只有使用ORACLE編程接口的應用設計人員才關心操作的類型，并把相關的操作組織在一起，形成一個事務。一般來說，我門必須定義事務，這樣在一個邏輯單元中的所有工作可以同時被提交或回滾，保證了數據的一致性。一個事務應該由邏

26、輯單元中的所有必須部分組成，不應該多一個，也不應該少一個。· 在事務開始和結束的這段時間內，所有被引用表中的數據都應該在一致的狀態(或可以被回溯到一致的狀態)· 事務應該只包含可以對數據進行一致更改(one consistent change to the data)的SQL語句 例如，在兩個帳號之間的轉帳(這是一個事務或邏輯工作單元)，應該包含從一個帳號中借錢(由一個SQL完成)，然后將借的錢存入另一個帳號(由另一個SQL完成)。這2個操作作為一個邏輯單元，應該同時成功或同時失敗。其它不相關的操作，如向一個帳戶中存錢，不應該包含在這個轉帳事務中。 在設計應用時，除了需要決

27、定哪種類型的操作組成一個事務外，還需要決定使用BEGIN_DISCRETE_TRANSACTIO存儲過程是否對提高小的、非分布式的事務的性能有作用。第4章 ORACLE的優化器 優化器有時也被稱為查詢優化器，這是因為查詢是影響數據庫性能最主要的部分，不要以為只有SELECT語句是查詢。實際上，帶有任何WHERE條件的DML(INSERT、UPDATE、DELETE)語句中都包含查詢要求，在后面的文章中，當說到查詢時，不一定只是指SELECT語句，也有可能指DML語句中的查詢部分。優化器是所有關系數據庫引擎中的最神秘、最富挑戰性的部件之一，從性能的角度看也是最重要的部分，它性能的高低直接關系到數

28、據庫性能的好壞。 我們知道，SQL語句同其它語言(如C語言)的語句不一樣，它是非過程化(non-procedural)的語句，即當你要取數據時，不需要告訴數據庫通過何種途徑去取數據，如到底是通過索引取數據，還是應該將表中的每行數據都取出來，然后再通過一一比較的方式取數據(即全表掃描)，這是由數據庫的優化器決定的，這就是非過程化的含義，也就是說，如何取數據是由優化器決定，而不是應用開發者通過編程決定。在處理SQL的SELECT、UPDATE、INSERT或DELETE語句時，Oracle 必須訪問語句所涉及的數據，Oracle的優化器部分用來決定訪問數據的有效路徑，使得語句執行所需的I/O和處理

29、時間最小。 為了實現一個查詢，內核必須為每個查詢定制一個查詢策略，或為取出符合條件的數據生成一個執行計劃(execution plan)。典型的，對于同一個查詢，可能有幾個執行計劃都符合要求，都能得到符合條件的數據。例如，參與連接的表可以有多種不同的連接方法，這取決于連接條件和優化器采用的連接方法。為了在多個執行計劃中選擇最優的執行計劃，優化器必須使用一些實際的指標來衡量每個執行計劃使用的資源(I/0次數、CPU等)，這些資源也就是我們所說的代價(cost)。如果一個執行計劃使用的資源多，我們就說使用執行計劃的代價大。以執行計劃的代價大小作為衡量標準，優化器選擇代價最小的執行計劃作為真正執行該

30、查詢的執行計劃，并拋棄其它的執行計劃。 在ORACLE的發展過程中，一共開發過2種類型的優化器：基于規則的優化器和基于代價的優化器。這2種優化器的不同之處關鍵在于：取得代價的方法與衡量代價的大小不同。現對每種優化器做一下簡單的介紹：基于規則的優化器 - Rule Based (Heuristic) Optimization(簡稱RBO)： 在ORACLE7之前，主要是使用基于規則的優化器。ORACLE在基于規則的優化器中采用啟發式的方法(Heuristic Approach)或規則(Rules)來生成執行計劃。例如，如果一個查詢的where條件(where clause)包含一個謂詞(pred

31、icate，其實就是一個判斷條件，如”=”, “>”, ”<”等)，而且該謂詞上引用的列上有有效索引，那么優化器將使用索引訪問這個表，而不考慮其它因素，如表中數據的多少、表中數據的易變性、索引的可選擇性等。此時數據庫中沒有關于表與索引數據的統計性描述，如表中有多上行，每行的可選擇性等。優化器也不考慮實例參數，如multi block i/o、可用排序內存的大小等，所以優化器有時就選擇了次優化的計劃作為真正的執行計劃，導致系統性能不高。 如，對于 select * from emp where deptno = 10; 這個查詢來說，如果是使用基于規則的優化器，而且deptno列上有

32、有效的索引，則會通過deptno列上的索引來訪問emp表。在絕大多數情況下，這是比較高效的，但是在一些特殊情況下，使用索引訪問也有比較低效的時候，現舉例說明： 1) emp表比較小，該表的數據只存放在幾個數據塊中。此時使用全表掃描比使用索引訪問emp表反而要好。因為表比較小，極有可能數據全在內存中，所以此時做全表掃描是最快的。而如果使用索引掃描，需要先從索引中找到符合條件記錄的rowid，然后再一一根據這些rowid從emp中將數據取出來，在這種條件下，效率就會比全表掃描的效率要差一些。 2) emp表比較大時，而且deptno = 10條件能查詢出表中大部分的數據如(50%)。如該表共有40

33、00萬行數據，共放在有500000個數據塊中，每個數據塊為8k，則該表共有約4G，則這么多的數據不可能全放在內存中，絕大多數需要放在硬盤上。此時如果該查詢通過索引查詢，則是你夢魘的開始。db_file_multiblock_read_count參數的值200。如果采用全表掃描，則需要500000/db_file_multiblock_read_count=500000/200=2500次I/O。但是如果采用索引掃描，假設deptno列上的索引都已經cache到內存中，所以可以將訪問索引的開銷忽略不計。因為要讀出4000萬x 50% = 2000萬數據，假設在讀這2000萬數據時，有99.9%的

34、命中率，則還是需要20000次I/O,比上面的全表掃描需要的2500次多多了，所以在這種情況下，用索引掃描反而性能會差很多。在這樣的情況下，用全表掃描的時間是固定的，但是用索引掃描的時間會隨著選出數據的增多使查詢時間相應的延長。 上面是枯燥的假設數據，現在以具體的實例給予驗證： 環境: oracle 817 + linux + 陣列柜，表SWD_BILLDETAIL有3200多萬數據； 表的id列、cn列上都有索引 經查看執行計劃，發現執行select count(id) from SWD_BILLDETAIL;使用全表掃描，執行完用了大約1.50分鐘(4次執行取平均，每次分別為1.45 1.

35、51 2.00 1.46)。而執行select count(id) from SWD_BILLDETAIL where cn <'6'卻用了2個小時還沒有執行完，經分析該語句使用了cn列上的索引，然后利用查詢出的rowid再從表中查詢數據。我為什么不使用select count(cn) from SWD_BILLDETAIL where cn <'6'呢？后面在分析執行路徑的索引掃描時時會給出說明。 下面就是基于規則的優化器使用的執行路徑與各個路徑對應的等級： RBO Path 1: Single Row by Rowid(等級最高) RBO Pat

36、h 2: Single Row by Cluster Join RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key RBO Path 4: Single Row by Unique or Primary Key RBO Path 5: Clustered Join RBO Path 6: Hash Cluster Key RBO Path 7: Indexed Cluster Key RBO Path 8: Composite Index RBO Path 9: Single-Column Indexes

37、RBO Path 10: Bounded Range Search on Indexed Columns RBO Path 11: Unbounded Range Search on Indexed Columns RBO Path 12: Sort Merge Join RBO Path 13: MAX or MIN of Indexed Column RBO Path 14: ORDER BY on Indexed Column RBO Path 15: Full Table Scan(等級最低) 上面的執行路徑中，RBO認為越往下執行的代價越大，即等級越低。在RBO生成執行計劃時，如果它

38、發現有等級高的執行路徑可用，則肯定會使用等級高的路徑，而不管任何其它影響性能的元素，即RBO通過上面的路徑的等級決定執行路徑的代價，執行路徑的等級越高，則使用該執行路徑的代價越小。如上面2個例子所述，如果使用RBO，則肯定使用索引訪問表，也就是選擇了比較差的執行計劃，這樣會給數據庫性能帶來很大的負面影響。為了解決這個問題，從ORACLE 7開始oracle引入了基于代價的優化器，下面給出了介紹。基于代價的優化器 - Cost Based Optimization(簡稱CBO) Oracle把一個代價引擎(Cost Engine)集成到數據庫內核中，用來估計每個執行計劃需要的代價，該代價將每個執

39、行計劃所耗費的資源進行量化，從而CBO可以根據這個代價選擇出最優的執行計劃。一個查詢耗費的資源可以被分成3個基本組成部分：I/O代價、CPU代價、network代價。I/O代價是將數據從磁盤讀入內存所需的代價。訪問數據包括將數據文件中數據塊的內容讀入到SGA的數據高速緩存中，在一般情況下，該代價是處理一個查詢所需要的最主要代價，所以我們在優化時，一個基本原則就是降低查詢所產生的I/O總次數。CPU代價是處理在內存中數據所需要的代價，如一旦數據被讀入內存，則我們在識別出我們需要的數據后，在這些數據上執行排序(sort)或連接(join)操作，這需要耗費CPU資源。 對于需要訪問跨節點(即通常說的

40、服務器)數據庫上數據的查詢來說，存在network代價，用來量化傳輸操作耗費的資源。查詢遠程表的查詢或執行分布式連接的查詢會在network代價方面花費比較大。 在使用CBO時，需要有表和索引的統計數據(分析數據)作為基礎數據，有了這些數據，CBO才能為各個執行計劃計算出相對準確的代價，從而使CBO選擇最佳的執行計劃。所以定期的對表、索引進行分析是絕對必要的，這樣才能使統計數據反映數據庫中的真實情況。否則就會使CBO選擇較差的執行計劃，影響數據庫的性能。分析操作不必做的太頻繁，一般來說，每星期一次就足夠了。切記如果想使用CBO，則必須定期對表和索引進行分析。 對于分析用的命令，隨著數據庫版本的

41、升級，用的命令也發生了變換，在oracle 8i以前，主要是用ANALYZE命令。在ORACLE 8I以后，又引入了DBMS_STATS存儲包來進行分析。幸運的是從ORACLE 10G以后，分析工作變成自動的了，這減輕的DBA的負擔，不過在一些特殊情況下，還需要一些手工分析。 如果采用了CBO優化器，而沒有對表和索引進行分析，沒有統計數據，則ORACLE使用缺省的統計數據(至少在ORACLE 9I中是這樣)，這可以從oracle的文檔上找到。使用的缺省值肯定與系統的實際統計值不一致，這可能會導致優化器選擇錯誤的執行計劃，影響數據庫的性能。 要注意的是：雖然CBO的功能隨著ORACLE新版本的推

42、出，功能越來越強，但它不是能包治百病的神藥，否則就不再需要DBA了，那我就慘了！實際上任何一個語句，隨著硬件環境與應用數據的不同，該語句的執行計劃可能需要隨之發生變化，這樣才能取得最好的性能。所以有時候不在具體的環境下而進行SQL性能調整是徒勞的。 在ORACLE8I推出的時候，ORACLE極力建議大家使用CBO，說CBO有種種好處，但是在那是ORACLE開發的應用系統還是使用基于規則的優化器，從這件事上我們可以得出這樣的結論：1) 如果團隊的數據庫水平很高而且都熟悉應用數據的特點，RBO也可以取得很好的性能。2）CBO不是很穩定，但是一個比較有前途的優化器，Oracle極力建議大家用是為了讓

43、大家盡快發現它的BUG，以便進一步改善，但是ORACLE為了對自己開發的應用系統負責，他們還是使用了比較熟悉而且成熟的RBO。從這個事情上給我們的啟發就是：我們在以后的開發中，應該盡量采用我們熟悉并且成熟的技術，而不要一味的采用新技術，一味采用新技術并不一定能開發出好的產品。幸運的是從ORACLE 10G后，CBO已經足夠的強大與智能，大家可以放心的使用該技術，因為ORACLE 10G后，Oracle自己開發的應用系統也使用CBO優化器了。而且ORACLE規定，從ORACLE 10G開始，開始廢棄RBO優化器。這句話并不是指在ORACLE 10G中不能使用RBO，而是從ORACLE 10G開始

44、開始，不再為RBO的BUG提供修補服務。 在上面的第2個例子中，如果采用CBO優化器，它就會考慮emp表的行數，deptno列的統計數據，發現對該列做查詢會查詢出過多的數據，并且考慮db_file_multiblock_read_count參數的設置，發現用全表掃描的代價比用索引掃描的代價要小，從而使用全表掃描從而取得良好的執行性能。 判斷當前數據庫使用何種優化器： 主要是由optimizer_mode初始化參數決定的。該參數可能的取值為：first_rows_1 | 10 | 100 | 1000 | first_rows | all_rows | choose | rule。具體解釋如下：

45、 RULE為使用RBO優化器。 CHOOSE則是根據實際情況，如果數據字典中包含被引用的表的統計數據，即引用的對象已經被分析，則就使用CBO優化器，否則為RBO優化器。 ALL_ROWS為CBO優化器使用的第一種具體的優化方法，是以數據的吞吐量為主要目標，以便可以使用最少的資源完成語句。 FIRST_ROWS為優化器使用的第二種具體的優化方法，是以數據的響應時間為主要目標，以便快速查詢出開始的幾行數據。 FIRST_ROWS_1 | 10 | 100 | 1000 為優化器使用的第三種具體的優化方法，讓優化器選擇一個能夠把響應時間減到最小的查詢執行計劃，以迅速產生查詢結果的前 n 行。該參數為

46、ORACLE 9I新引入的。 從ORACLE V7以來，optimizer_mode參數的缺省設置應是"choose"，即如果對已分析的表查詢的話選擇CBO，否則選擇RBO。在此種設置中，如果采用了CBO，則缺省為CBO中的all_rows模式。 注意：即使指定數據庫使用RBO優化器，但有時ORACLE數據庫還是會采用CBO優化器，這并不是ORACLE的BUG，主要是由于從ORACLE 8I后引入的許多新特性都必須在CBO下才能使用，而你的SQL語句可能正好使用了這些新特性，此時數據庫會自動轉為使用CBO優化器執行這些語句。什么是優化 優化是選擇最有效的執行計劃來執行SQL

47、語句的過程，這是在處理任何數據的語句（SELECT,INSERT,UPDATE或DELETE）中的一個重要步驟。對Oracle來說，執行這樣的語句有許多不同的方法，譬如說，將隨著以什么順序訪問哪些表或索引的不同而不同。所使用的執行計劃可以決定語句能執行得有多快。Oracle中稱之為優化器（Optimizer）的組件用來選擇這種它認為最有效的執行計劃。 由于一系列因素都會會影響語句的執行，優化器綜合權衡各個因素，在眾多的執行計劃中選擇認為是最佳的執行計劃。然而，應用設計人員通常比優化器更知道關于特定應用的數據特點。無論優化器多么智能，在某些情況下開發人員能選擇出比優化器選擇的最優執行計劃還要好的

48、執行計劃。這是需要人工干預數據庫優化的主要原因。事實表明，在某些情況下，確實需要DBA對某些語句進行手工優化。注：從Oracle的一個版本到另一個版本，優化器可能對同一語句生成不同的執行計劃。在將來的Oracle 版本中，優化器可能會基于它可以用的更好、更理想的信息，作出更優的決策，從而導致為語句產生更優的執行計劃。 第5章 ORACLE的執行計劃背景知識： 為了更好的進行下面的內容我們必須了解一些概念性的術語：共享sql語句 為了不重復解析相同的SQL語句(因為解析操作比較費資源，會導致性能下降)，在第一次解析之后，ORACLE將SQL語句及解析后得到的執行計劃存放在內存中。這塊位于系統全局

49、區域SGA(system global area)的共享池(shared buffer pool)中的內存可以被所有的數據庫用戶共享。因此，當你執行一個SQL語句(有時被稱為一個游標)時，如果該語句和之前的執行過的某一語句完全相同，并且之前執行的該語句與其執行計劃仍然在內存中存在，則ORACLE就不需要再進行分析，直接得到該語句的執行路徑。ORACLE的這個功能大大地提高了SQL的執行性能并大大節省了內存的使用。使用這個功能的關鍵是將執行過的語句盡可能放到內存中，所以這要求有大的共享池(通過設置shared buffer pool參數值)和盡可能的使用綁定變量的方法執行SQL語句。 當你向OR

50、ACLE 提交一個SQL語句，ORACLE會首先在共享內存中查找是否有相同的語句。這里需要注明的是，ORACLE對兩者采取的是一種嚴格匹配，要達成共享，SQL語句必須完全相同(包括空格,換行等)。 下面是判斷SQL語句是否與共享內存中某一SQL相同的步驟：1). 對所發出語句的文本串進行hashed。如果hash值與已在共享池中SQL語句的hash值相同，則進行第2步： 2) 將所發出語句的文本串（包括大小寫、空白和注釋）與在第步中識別的所有 已存在的SQL語句相比較。 例如： SELECT * FROM emp WHERE empno = 1000; 和下列每一個都不同 SELECT * f

51、rom emp WHERE empno = 1000; SELECT * FROM EMP WHERE empno = 1000; SELECT * FROM emp WHERE empno = 2000; 在上面的語句中列值都是直接SQL語句中的，今后我們將這類sql成為硬編碼SQL 或字面值SQL 使用綁定變量的SQL語句中必須使用相同的名字的綁定變量(bind variables) ，例如： a. 該2個sql語句被認為相同 select pin , name from people where pin = :blk1.pin; select pin , name from people

52、 where pin = :blk1.pin; b. 該2個sql語句被認為不相同 select pin , name from people where pin = :blk1.ot_ind; select pin , name from people where pin = :blk1.ov_ind; 今后我們將上面的這類語句稱為綁定變量SQL。 3). 將所發出語句中涉及的對象與第步中識別的已存在語句所涉及對象相比較。 例如: 如用戶user1與用戶user2下都有EMP表，則 用戶user1發出的語句：SELECT * FROM EMP; 與 用戶user2發出的語句：SELECT *

53、 FROM EMP; 被認為是不相同的語句， 因為兩個語句中引用的EMP不是指同一個表。 4). 在SQL語句中使用的捆綁變量的捆綁類型必須一致。 如果語句與當前在共享池中的另一個語句是等同的話，Oracle并不對它進行語法分析。而直接執行該語句，提高了執行效率，因為語法分析比較耗費資源。 注意的是，從oracle 8i開始，新引入了一個CURSOR_SHARING參數，該參數的主要目的就是為了解決在編程過程中已大量使用的硬編碼SQL問題。因為在實際開發中，很多程序人員為了提高開發速度，而采用類似下面的開發方法：str_sql string;int_empno int;int_empno = 2000;str_sql = SELECT * FROM emp WHERE empno = + int_empno;int_empno = 1000;str_sql = SELECT * FROM emp WHERE empno = + int_empno; 上面的代碼實際上使用了硬編碼SQL，使我們不能使用共享SQL的功能，結果是數據庫效率不高。但是從上面的2個語句來看，產生的硬編碼SQL只是列值不同，其它部分都是相同的，如果僅僅因為列值不同而導致這2個語句不能共享是很可惜的，為了解決這個問題，引入了CURSOR_S