|
第1章 效能調整綜述
Oracle資料庫是高度可調的資料庫產品。本章描述調整的過程和那些人員應與Oracle伺服器的調整有關，以及與調整相關聯的作業系統硬體和軟體。本章包括以下方面:
l 誰來調整系統?
l 什麼時候調整?
l 建立有效調整的目標
l 在設計和開發時的調整
l 調整產品系統
l 監控產品系統
誰來調整系統:
為了有效地調整系統，若干類人員必須交換資訊並牽涉到系統調整中，例如:
l 應用設計人員必須傳達應用系統的設計，使得每個人都清楚應用中的資料流動.
l 應用開發人員必須傳達他們選擇的實現策略，使得語句調整的過程中能快速、容易地識別有問題的應用模組和可疑的SQL語句.
l 資料庫管理人員必須仔細地監控系統活動並提供它們的資料，使得異常的系統效能可被快速得識別和糾正.
l 硬體/軟體管理人員必須傳達系統的硬體、軟體配置並提供它們的資料，使得相關人員能有效地設計和管理系統。
簡而言之，與系統涉及的每個人都在調整過程中起某些作用，當上面提及的那些人員傳達了系統的特性並提供了它們的資料，調整就能相對的容易和更快一些。
不幸的是，事實上的結果是：資料庫管理員對調整負有全部或主要的責任。但是，資料庫管理員很少有合適的系統方面的資料，而且，在很多情況下，資料庫管理員往往是在實施階段才介入資料庫，這就給調整工作帶來許多負面的影響，因為在設計階段的缺陷是不能透過DBA的調整而得以解決，而設計階段的缺陷往往對資料庫效能造成極大的影響。
其實，在真正成熟的開發環境下，開發人員作為純程式碼編寫人員時，對效能的影響最小，此時大部分的工作應由應用設計人員完成，而且資料庫管理員往往在前期的需求管理階段就介入，為設計人員提供必要的技術支援。
調整並不是資料庫管理員的專利，相反大部分應該是設計人員和開發人員的工作，這就需要設計人員和開發人員具體必要的資料庫知識，這樣才能組成一個高效的團隊，然而事實上往往並非如此。
什麼時候作調整？
多數人認為當使用者感覺效能差時才進行調整，這對調整過程中使用某些最有效的調整策略來說往往是太遲了。此時，如果你不願意重新設計應用的話，你只能透過重新分配記憶體(調整SGA)和調整I/O的辦法或多或少地提高效能。Oracle提供了許多特性，這些特性只有應用到正確地設計的系統中時才能夠很大地提高效能。
應用設計人員需要在設計階段設定應用的效能期望值。然後在設計和開發期間，應用設計人員應考慮哪些Oracle 特性可以對系統有好處，並使用這些特性。
透過良好的系統設計，你就可以在應用的生命週期中消除效能調整的代價和挫折。圖1-1圖1-2說明在應用的生命週期中調整的相對代價和收益，正如你見到的，最有效的調整時間是在設計階段。在設計期間的調整能以最低的代價給你最大的收益。
圖1-1 在應用生命週期中調整的代價
圖1-2 在應用生命週期中調整的收益
當然，即使在設計很好的系統中，也可能有效能降低。但這些效能降低應該是可控的和可以預見的。
調整目標
不管你正在設計或維護系統，你應該建立專門的效能目標，它使你知道何時要作調整。如果你試圖胡亂地改動初始化引數或SQl 語句，你可能會浪費調整系統的時間，而且無什麼大的收益。調整你的系統的最有效方法如下：
l 當設計系統時考慮效能
l 調整作業系統的硬體和軟體
l 識別效能瓶頸
l 確定問題的原因
l 採取糾正的動作
當你設計系統時，制定專門的目標；例如，響應時間小於３秒。當應用不能滿足此目標時，識別造成變慢的瓶頸（例如，I/O競爭），確定原因，採取糾正動作。在開發期間，你應測試應用研究，確定在採取應用之前是否滿足設計的效能目標。
當你正在維護生產庫系統時，有多種快速有效的方法來識別效能瓶頸。
不管怎樣，調整通常是一系列開銷。一旦你已確定了瓶頸，你可能要犧牲一些其它方面的指標來達到所要的結果。例如，如果I/O有問題，你可能需要更多記憶體或磁碟。如果不可能買，你可能要限制系統的併發性，來獲取所需的效能。然而，如果你已經明確地定義了效能的目標，那用什麼來交換高效能的決策就變的很容易的，因為你已經確定了哪些方面是最重要的，如過我的目標為高效能，可能犧牲一些空間資源。
隨著應用的越來越龐大，硬體效能的提高，全面的調整應用逐漸變成代價高昂的行為，在這樣情況下，要取得最大的投入/效率之比，較好的辦法是調整應用的關鍵部分，使其達到比較高的效能，這樣從總體上來說，整個系統的效能也是比較高的。這也就是有名的20/80原則，調整應用的20%(關鍵部分)，能解決80%的問題。
在設計和開發系統時作調整
良好設計的系統可以防止在應用生命週期中產生效能問題。系統設計人員和應用開發人員必須瞭解Oracle的查詢處理機制以便寫出高效的SQL語句。“第2章 有效的應用設計”討論了你的系統中各種可用的配置，以及每種配置更適合哪種型別的應用。“第5章 最佳化器”討論了Oracle的查詢最佳化器，以及如何寫語句以獲取最快的結果。
當設計你的系統時，使用下列最佳化效能的準則：
l 消除客戶機／伺服器應用中不必要的網路傳輸。-- 使用儲存過程。
l 使用適合你係統的相應Oracle伺服器選件（例如，並行查詢或分散式資料庫）。
l 除非你的應用有特殊的需要，否則使用預設的Oracle鎖。
l 利用資料庫記住應用模組，以便你能以每個模組為基礎來追蹤效能。
l 選擇你的資料塊的最佳大小。 -- 原則上來說大一些的效能較好。
l 分佈你的資料，使得一個節點使用的資料本地存貯在該節點中。
調整產品系統
本節描述對應用系統快速、容易地找出效能瓶頸，並決定糾正動作的方法。這種方法依賴於對Oracle伺服器體系結構和特性的瞭解程度。在試圖調整你的系統前，你應熟悉Oracle調整的內容。
為調整你已有的系統，遵從下列步驟：
l 調整作業系統的硬體和軟體
l 透過查詢V $SESSION_WAIT檢視，識別效能的瓶頸，這個動態效能檢視列出了造成會話(session)等待的事件。
l 透過分析V $SESSION_WAIT中的資料，決定瓶頸的原因。
l 糾正存在的問題。
監控應用系統
這主要是透過監控oracle的動態檢視來完成。
各種有用的動態檢視：如v$session_wait, v$session_event等。
第2章 有效的應用設計
我們通常將最常用的應用分為2種型別：聯機事務處理型別(OLTP)，決策支援系統(DSS)。
聯機事務處理(OLTP)
該型別的應用是高吞吐量，插入、更新、刪除操作比較多的系統，這些系統以不斷增長的大容量資料為特徵，它們提供給成百使用者同時存取，典型的OLTP系統是訂票系統，銀行的業務系統，訂單系統。OTLP的主要目標是可用性、速度、併發性和可恢復性。
當設計這類系統時，必須確保大量的併發使用者不能干擾系統的效能。還需要避免使用過量的索引與cluster 表，因為這些結構會使插入和更新操作變慢。
決策支援(DSS)
該型別的應用將大量資訊進行提取形成報告，協助決策者作出正確的判斷。典型的情況是：決策支援系統將OLTP應用收集的大量資料進行查詢。典型的應用為客戶行為分析系統(超市，保險等)。
決策支援的關鍵目標是速度、精確性和可用性。
該種型別的設計往往與OLTP設計的理念背道而馳，一般建議使用資料冗餘、大量索引、cluster table、並行查詢等。
近年來，該型別的應用逐漸與OLAP、資料倉儲緊密的聯絡在一起，形成的一個新的應用方向。
第3章 SQL語句處理的過程 在調整之前我們需要了解一些背景知識，只有知道這些背景知識，我們才能更好的去調整sql語句。
本節介紹了SQL語句處理的基本過程，主要包括：
· 查詢語句處理
· DML語句處理(insert, update, delete)
· DDL 語句處理(create .. , drop .. , alter .. , )
· 事務控制(commit, rollback)
SQL 語句的執行過程(SQL Statement Execution)
圖3-1 概要的列出了處理和執行一個sql語句的需要各個重要階段。在某些情況下，Oracle執行sql的過程可能與下面列出的各個階段的順序有所不同。如DEFINE階段可能在FETCH階段之前，這主要依賴你如何書寫程式碼。
對許多oracle的工具來說，其中某些階段會自動執行。絕大多數使用者不需要關心各個階段的細節問題，然而，知道執行的各個階段還是有必要的，這會幫助你寫出更高效的SQL語句來，而且還可以讓你猜測出效能差的SQL語句主要是由於哪一個階段造成的，然後我們針對這個具體的階段，找出解決的辦法。
圖 3-1 SQL語句處理的各個階段
DML語句的處理
本節給出一個例子來說明在DML語句處理的各個階段到底發生了什麼事情。
假設你使用Pro*C程式來為指定部門的所有職員增加工資。程式已經連到正確的使用者，你可以在你的程式中嵌入如下的SQL語句：
EXEC SQL UPDATE employees
SET salary = 1.10 * salary
WHERE department_id = :var_department_id;
var_department_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 Statement 並行執行語句
如果是查詢語句，則需要以下幾個額外的步驟，如圖 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語句時，都是自動建立遊標的，不需要開發人員干預。多數應用中，遊標的建立是自動的。然而，在預編譯程式(pro*c)中游標的建立，可能是隱含的，也可能顯式的建立。在儲存過程中也是這樣的。
第2步:分析語句(Parse the Statement)
在語法分析期間，SQL語句從使用者程式傳送到Oracle，SQL語句經語法分析後，SQL語句本身與分析的資訊都被裝入到共享SQL區。在該階段中，可以解決許多型別的錯誤。
語法分析分別執行下列操作：
l 翻譯SQL語句，驗證它是合法的語句，即書寫正確
l 實現資料字典的查詢，以驗證是否符合表和列的定義
l 在所要求的物件上獲取語法分析鎖，使得在語句的語法分析過程中不改變這些物件的定義
l 驗證為存取所涉及的模式物件所需的許可權是否滿足
l 決定此語句最佳的執行計劃
l 將它裝入共享SQL區
l 對分佈的語句來說，把語句的全部或部分路由到包含所涉及資料的遠端節點
以上任何一步出現錯誤，都將導致語句報錯，中止執行。
只有在共享池中不存在等價SQL語句的情況下，才對SQL語句作語法分析。在這種情況下，資料庫核心重新為該語句分配新的共享SQL區，並對語句進行語法分析。進行語法分析需要耗費較多的資源，所以要儘量避免進行語法分析，這是最佳化的技巧之一。
語法分析階段包含了不管此語句將執行多少次，而只需分析一次的處理要求。Oracle只對每個SQL語句翻譯一次，在以後再次執行該語句時，只要該語句還在共享SQL區中，就可以避免對該語句重新進行語法分析，也就是此時可以直接使用其對應的執行計劃對資料進行存取。這主要是透過繫結變數(bind variable)實現的，也就是我們常說的共享SQL，後面會給出共享SQL的概念。
雖然語法分析驗證了SQL語句的正確性，但語法分析只能識別在SQL語句執行之前所能發現的錯誤(如書寫錯誤、許可權不足等)。因此，有些錯誤透過語法分析是抓不到的。例如，在資料轉換中的錯誤或在資料中的錯（如企圖在主鍵中插入重複的值）以及死鎖等均是隻有在語句執行階段期間才能遇到和報告的錯誤或情況。
查詢語句的處理
查詢與其它型別的SQL語句不同，因為在成功執行後作為結果將返回資料。其它語句只是簡單地返回成功或失敗，而查詢則能返回一行或許多行資料。查詢的結果均採用表格形式，結果行被一次一行或者批次地被檢索出來。從這裡我們可以得知批次的fetch資料可以降低網路開銷，所以批次的fetch也是最佳化的技巧之一。
有些問題只與查詢處理相關，查詢不僅僅指SELECT語句，同樣也包括在其它SQL語句中的隱含查詢。例如，下面的每個語句都需要把查詢作為它執行的一部分：
INSERT INTO table SELECT...
UPDATE table SET x = y WHERE...
DELETE FROM table WHERE...
CREATE table AS SELECT...
具體來說，查詢
· 要求讀一致性
· 可能使用回滾段作中間處理
· 可能要求SQL語句處理描述、定義和取資料階段
第3步: 描述查詢結果(Describe Results of a Query)
描述階段只有在查詢結果的各個列是未知時才需要；例如，當查詢由使用者互動地輸入需要輸出的列名。在這種情況要用描述階段來決定查詢結果的特徵（資料型別，長度和名字）。
第4步: 定義查詢的輸出資料(Define Output of a Query)
在查詢的定義階段，你指定與查詢出的列值對應的接收變數的位置、大小和資料型別，這樣我們透過接收變數就可以得到查詢結果。如果必要的話，Oracle會自動實現資料型別的轉換。這是將接收變數的型別與對應的列型別相比較決定的。
第5步: 繫結變數(Bind Any Variables)
此時，Oracle知道了SQL語句的意思，但仍沒有足夠的資訊用於執行該語句。Oracle 需要得到在語句中列出的所有變數的值。在該例中，Oracle需要得到對department_id列進行限定的值。得到這個值的過程就叫繫結變數(binding variables)
此過程稱之為將變數值捆綁進來。程式必須指出可以找到該數值的變數名（該變數被稱為捆綁變數，變數名實質上是一個記憶體地址，相當於指標）。應用的終端使用者可能並沒有發覺他們正在指定捆綁變數，因為Oracle 的程式可能只是簡單地指示他們輸入新的值，其實這一切都在程式中自動做了。
因為你指定了變數名，在你再次執行之前無須重新捆綁變數。你可以改變繫結變數的值，而Oracle在每次執行時，僅僅使用記憶體地址來查詢此值。
如果Oracle 需要實現自動資料型別轉換的話（除非它們是隱含的或預設的），你還必須對每個值指定資料型別和長度。關於這些資訊可以參考oracle的相關文件，如Oracle Call Interface Programmer's Guide
第6步: 並行執行語句(Parallelize the Statement )
ORACLE 可以在SELECTs, INSERTs, UPDATEs, MERGEs, DELETEs語句中執行相應並行查詢操作，對於某些DDL操作，如建立索引、用子查詢建立表、在分割槽表上的操作，也可以執行並行操作。並行化可以導致多個伺服器程式(oracle server processes)為同一個SQL語句工作，使該SQL語句可以快速完成，但是會耗費更多的資源，所以除非很有必要，否則不要使用並行查詢。
第7步: 執行語句(Run the Statement)
到了現在這個時候，Oracle擁有所有需要的資訊與資源，因此可以真正執行SQL語句了。如果該語句為SELECT查詢或INSERT語句，則不需要鎖定任何行，因為沒有資料需要被改變。然而，如果語句為UPDATE或DELETE語句，則該語句影響的所有行都被鎖定，防止該使用者提交或回滾之前，別的使用者對這些資料進行修改。這保證了資料的一致性。
對於某些語句，你可以指定執行的次數，這稱為批處理(array processing)。指定執行N次，則繫結變數與定義變數被定義為大小為N的陣列的開始位置，這種方法可以減少網路開銷，也是最佳化的技巧之一。
第8步: 取出查詢的行(Fetch Rows of a Query)
在fetch階段，行資料被取出來，每個後續的存取操作檢索結果集中的下一行資料，直到最後一行被取出來。上面提到過，批次的fetch是最佳化的技巧之一。
第9步: 關閉遊標(Close the Cursor)
SQL語句處理的最後一個階段就是關閉遊標
DDL語句的處理(DDL Statement Processing)
DDL語句的執行不同與DML語句和查詢語句的執行，這是因為DDL語句執行成功後需要對資料字典資料進行修改。對於DDL語句，語句的分析階段實際上包括分析、查詢資料字典資訊和執行。
事務管理語句、會話管理語句、系統管理語句只有分析與執行階段，為了重新執行該語句，會重新分析與執行該語句。
事務控制(Control of Transactions)
一般來說，只有使用ORACLE程式設計介面的應用設計人員才關心操作的型別，並把相關的操作組織在一起，形成一個事務。一般來說，我門必須定義事務，這樣在一個邏輯單元中的所有工作可以同時被提交或回滾，保證了資料的一致性。一個事務應該由邏輯單元中的所有必須部分組成，不應該多一個，也不應該少一個。
· 在事務開始和結束的這段時間內，所有被引用表中的資料都應該在一致的狀態(或可以被回溯到一致的狀態)
· 事務應該只包含可以對資料進行一致更改(one consistent change to the data)的SQL語句
例如，在兩個帳號之間的轉帳(這是一個事務或邏輯工作單元)，應該包含從一個帳號中借錢(由一個SQL完成)，然後將借的錢存入另一個帳號(由另一個SQL完成)。這2個操作作為一個邏輯單元，應該同時成功或同時失敗。其它不相關的操作，如向一個帳戶中存錢，不應該包含在這個轉帳事務中。
在設計應用時，除了需要決定哪種型別的操作組成一個事務外，還需要決定使用BEGIN_DISCRETE_TRANSACTIO儲存過程是否對提高小的、非分散式的事務的效能有作用。
第4章 ORACLE的最佳化器 最佳化器有時也被稱為查詢最佳化器，這是因為查詢是影響資料庫效能最主要的部分，不要以為只有SELECT語句是查詢。實際上，帶有任何WHERE條件的DML(INSERT、UPDATE、DELETE)語句中都包含查詢要求，在後面的文章中，當說到查詢時，不一定只是指SELECT語句，也有可能指DML語句中的查詢部分。最佳化器是所有關聯式資料庫引擎中的最神秘、最富挑戰性的部件之一，從效能的角度看也是最重要的部分，它效能的高低直接關係到資料庫效能的好壞。
我們知道，SQL語句同其它語言(如C語言)的語句不一樣，它是非過程化(non-procedural)的語句，即當你要取資料時，不需要告訴資料庫透過何種途徑去取資料，如到底是透過索引取資料，還是應該將表中的每行資料都取出來，然後再透過一一比較的方式取資料(即全表掃描)，這是由資料庫的最佳化器決定的，這就是非過程化的含義，也就是說，如何取資料是由最佳化器決定，而不是應用開發者透過程式設計決定。在處理SQL的SELECT、UPDATE、INSERT或DELETE語句時，Oracle 必須訪問語句所涉及的資料，Oracle的最佳化器部分用來決定訪問資料的有效路徑，使得語句執行所需的I/O和處理時間最小。
為了實現一個查詢，核心必須為每個查詢定製一個查詢策略，或為取出符合條件的資料生成一個執行計劃(execution plan)。典型的，對於同一個查詢，可能有幾個執行計劃都符合要求，都能得到符合條件的資料。例如，參與連線的表可以有多種不同的連線方法，這取決於連線條件和最佳化器採用的連線方法。為了在多個執行計劃中選擇最優的執行計劃，最佳化器必須使用一些實際的指標來衡量每個執行計劃使用的資源(I/0次數、CPU等)，這些資源也就是我們所說的代價(cost)。如果一個執行計劃使用的資源多，我們就說使用執行計劃的代價大。以執行計劃的代價大小作為衡量標準，最佳化器選擇代價最小的執行計劃作為真正執行該查詢的執行計劃，並拋棄其它的執行計劃。
在ORACLE的發展過程中，一共開發過2種型別的最佳化器：基於規則的最佳化器和基於代價的最佳化器。這2種最佳化器的不同之處關鍵在於：取得代價的方法與衡量代價的大小不同。現對每種最佳化器做一下簡單的介紹：
基於規則的最佳化器 -- Rule Based (Heuristic) Optimization(簡稱RBO)：
在ORACLE7之前，主要是使用基於規則的最佳化器。ORACLE在基於規則的最佳化器中採用啟發式的方法(Heuristic Approach)或規則(Rules)來生成執行計劃。例如，如果一個查詢的where條件(where clause)包含一個謂詞(predicate，其實就是一個判斷條件，如”=”, “>”, ” 如，對於
select * from emp where deptno = 10;
這個查詢來說，如果是使用基於規則的最佳化器，而且deptno列上有有效的索引，則會透過deptno列上的索引來訪問emp表。在絕大多數情況下，這是比較高效的，但是在一些特殊情況下，使用索引訪問也有比較低效的時候，現舉例說明：
1) emp表比較小，該表的資料只存放在幾個資料塊中。此時使用全表掃描比使用索引訪問emp表反而要好。因為表比較小，極有可能資料全在記憶體中，所以此時做全表掃描是最快的。而如果使用索引掃描，需要先從索引中找到符合條件記錄的rowid，然後再一一根據這些rowid從emp中將資料取出來，在這種條件下，效率就會比全表掃描的效率要差一些。
2) emp表比較大時，而且deptno = 10條件能查詢出表中大部分的資料如(50%)。如該表共有4000萬行資料，共放在有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%的命中率，則還是需要20000次I/O,比上面的全表掃描需要的2500次多多了，所以在這種情況下，用索引掃描反而效能會差很多。在這樣的情況下，用全表掃描的時間是固定的，但是用索引掃描的時間會隨著選出資料的增多使查詢時間相應的延長。
上面是枯燥的假設資料，現在以具體的例項給予驗證：
環境: oracle 817 + linux + 陣列櫃，表SWD_BILLDETAIL有3200多萬資料；
表的id列、cn列上都有索引
經檢視執行計劃，發現執行select count(id) from SWD_BILLDETAIL;使用全表掃描，執行完用了大約1.50分鐘(4次執行取平均，每次分別為1.45 1.51 2.00 1.46)。而執行select count(id) from SWD_BILLDETAIL where cn
下面就是基於規則的最佳化器使用的執行路徑與各個路徑對應的等級：
RBO Path 1: Single Row by Rowid(等級最高)
RBO Path 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
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生成執行計劃時，如果它發現有等級高的執行路徑可用，則肯定會使用等級高的路徑，而不管任何其它影響效能的元素，即RBO透過上面的路徑的等級決定執行路徑的代價，執行路徑的等級越高，則使用該執行路徑的代價越小。如上面2個例子所述，如果使用RBO，則肯定使用索引訪問表，也就是選擇了比較差的執行計劃，這樣會給資料庫效能帶來很大的負面影響。為了解決這個問題，從ORACLE 7開始oracle引入了基於代價的最佳化器，下面給出了介紹。
基於代價的最佳化器 -- Cost Based Optimization(簡稱CBO)
Oracle把一個代價引擎(Cost Engine)整合到資料庫核心中，用來估計每個執行計劃需要的代價，該代價將每個執行計劃所耗費的資源進行量化，從而CBO可以根據這個代價選擇出最優的執行計劃。一個查詢耗費的資源可以被分成3個基本組成部分：I/O代價、CPU代價、network代價。I/O代價是將資料從磁碟讀入記憶體所需的代價。訪問資料包括將資料檔案中資料塊的內容讀入到SGA的資料快取記憶體中，在一般情況下，該代價是處理一個查詢所需要的最主要代價，所以我們在最佳化時，一個基本原則就是降低查詢所產生的I/O總次數。CPU代價是處理在記憶體中資料所需要的代價，如一旦資料被讀入記憶體，則我們在識別出我們需要的資料後，在這些資料上執行排序(sort)或連線(join)操作，這需要耗費CPU資源。
對於需要訪問跨節點(即通常說的伺服器)資料庫上資料的查詢來說，存在network代價，用來量化傳輸操作耗費的資源。查詢遠端表的查詢或執行分散式連線的查詢會在network代價方面花費比較大。
在使用CBO時，需要有表和索引的統計資料(分析資料)作為基礎資料，有了這些資料，CBO才能為各個執行計劃計算出相對準確的代價，從而使CBO選擇最佳的執行計劃。所以定期的對錶、索引進行分析是絕對必要的，這樣才能使統計資料反映資料庫中的真實情況。否則就會使CBO選擇較差的執行計劃，影響資料庫的效能。分析操作不必做的太頻繁，一般來說，每星期一次就足夠了。切記如果想使用CBO，則必須定期對錶和索引進行分析。
對於分析用的命令，隨著資料庫版本的升級，用的命令也發生了變換，在oracle 8i以前，主要是用ANALYZE命令。在ORACLE 8I以後，又引入了DBMS_STATS儲存包來進行分析。幸運的是從ORACLE 10G以後，分析工作變成自動的了，這減輕的DBA的負擔，不過在一些特殊情況下，還需要一些手工分析。
如果採用了CBO最佳化器，而沒有對錶和索引進行分析，沒有統計資料，則ORACLE使用預設的統計資料(至少在ORACLE 9I中是這樣)，這可以從oracle的文件上找到。使用的預設值肯定與系統的實際統計值不一致，這可能會導致最佳化器選擇錯誤的執行計劃，影響資料庫的效能。
要注意的是：雖然CBO的功能隨著ORACLE新版本的推出，功能越來越強，但它不是能包治百病的神藥，否則就不再需要DBA了，那我就慘了！！！實際上任何一個語句，隨著硬體環境與應用資料的不同，該語句的執行計劃可能需要隨之發生變化，這樣才能取得最好的效能。所以有時候不在具體的環境下而進行SQL效能調整是徒勞的。
在ORACLE8I推出的時候，ORACLE極力建議大家使用CBO，說CBO有種種好處，但是在那是ORACLE開發的應用系統還是使用基於規則的最佳化器，從這件事上我們可以得出這樣的結論：1) 如果團隊的資料庫水平很高而且都熟悉應用資料的特點，RBO也可以取得很好的效能。2）CBO不是很穩定，但是一個比較有前途的最佳化器，Oracle極力建議大家用是為了讓大家儘快發現它的BUG，以便進一步改善，但是ORACLE為了對自己開發的應用系統負責，他們還是使用了比較熟悉而且成熟的RBO。從這個事情上給我們的啟發就是：我們在以後的開發中，應該儘量採用我們熟悉並且成熟的技術，而不要一味的採用新技術，一味採用新技術並不一定能開發出好的產品。幸運的是從ORACLE 10G後，CBO已經足夠的強大與智慧，大家可以放心的使用該技術，因為ORACLE 10G後，Oracle自己開發的應用系統也使用CBO最佳化器了。而且ORACLE規定，從ORACLE 10G開始，開始廢棄RBO最佳化器。這句話並不是指在ORACLE 10G中不能使用RBO，而是從ORACLE 10G開始開始，不再為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。具體解釋如下：
RULE為使用RBO最佳化器。
CHOOSE則是根據實際情況，如果資料字典中包含被引用的表的統計資料，即引用的物件已經被分析，則就使用CBO最佳化器，否則為RBO最佳化器。
ALL_ROWS為CBO最佳化器使用的第一種具體的最佳化方法，是以資料的吞吐量為主要目標，以便可以使用最少的資源完成語句。
FIRST_ROWS為最佳化器使用的第二種具體的最佳化方法，是以資料的響應時間為主要目標，以便快速查詢出開始的幾行資料。
FIRST_ROWS_[1 | 10 | 100 | 1000] 為最佳化器使用的第三種具體的最佳化方法，讓最佳化器選擇一個能夠把響應時間減到最小的查詢執行計劃，以迅速產生查詢結果的前 n 行。該引數為ORACLE 9I新引入的。
從ORACLE V7以來，optimizer_mode引數的預設設定應是"choose"，即如果對已分析的表查詢的話選擇CBO，否則選擇RBO。在此種設定中，如果採用了CBO，則預設為CBO中的all_rows模式。
注意：即使指定資料庫使用RBO最佳化器，但有時ORACLE資料庫還是會採用CBO最佳化器，這並不是ORACLE的BUG，主要是由於從ORACLE 8I後引入的許多新特性都必須在CBO下才能使用，而你的SQL語句可能正好使用了這些新特性，此時資料庫會自動轉為使用CBO最佳化器執行這些語句。
什麼是最佳化
最佳化是選擇最有效的執行計劃來執行SQL語句的過程，這是在處理任何資料的語句（SELECT,INSERT,UPDATE或DELETE）中的一個重要步驟。對Oracle來說，執行這樣的語句有許多不同的方法，譬如說，將隨著以什麼順序訪問哪些表或索引的不同而不同。所使用的執行計劃可以決定語句能執行得有多快。Oracle中稱之為最佳化器（Optimizer）的元件用來選擇這種它認為最有效的執行計劃。
由於一系列因素都會會影響語句的執行，最佳化器綜合權衡各個因素，在眾多的執行計劃中選擇認為是最佳的執行計劃。然而，應用設計人員通常比最佳化器更知道關於特定應用的資料特點。無論最佳化器多麼智慧，在某些情況下開發人員能選擇出比最佳化器選擇的最優執行計劃還要好的執行計劃。這是需要人工干預資料庫最佳化的主要原因。事實表明，在某些情況下，確實需要DBA對某些語句進行手工最佳化。
注：從Oracle的一個版本到另一個版本，最佳化器可能對同一語句生成不同的執行計劃。在將來的Oracle 版本中，最佳化器可能會基於它可以用的更好、更理想的資訊，作出更優的決策，從而導致為語句產生更優的執行計劃。 |
第5章 ORACLE的執行計劃 背景知識：
為了更好的進行下面的內容我們必須瞭解一些概念性的術語：
共享sql語句
為了不重複解析相同的SQL語句(因為解析操作比較費資源，會導致效能下降)，在第一次解析之後，ORACLE將SQL語句及解析後得到的執行計劃存放在記憶體中。這塊位於系統全域性區域SGA(system global area)的共享池(shared buffer pool)中的記憶體可以被所有的資料庫使用者共享。因此，當你執行一個SQL語句(有時被稱為一個遊標)時，如果該語句和之前的執行過的某一語句完全相同，並且之前執行的該語句與其執行計劃仍然在記憶體中存在，則ORACLE就不需要再進行分析，直接得到該語句的執行路徑。ORACLE的這個功能大大地提高了SQL的執行效能並大大節省了記憶體的使用。使用這個功能的關鍵是將執行過的語句儘可能放到記憶體中，所以這要求有大的共享池(透過設定shared buffer pool引數值)和儘可能的使用繫結變數的方法執行SQL語句。
當你向ORACLE 提交一個SQL語句，ORACLE會首先在共享記憶體中查詢是否有相同的語句。這裡需要註明的是，ORACLE對兩者採取的是一種嚴格匹配，要達成共享，SQL語句必須完全相同(包括空格,換行等)。
下面是判斷SQL語句是否與共享記憶體中某一SQL相同的步驟：
1). 對所發出語句的文字串進行hashed。如果hash值與已在共享池中SQL語句的hash值相同，則進行第2步：
2) 將所發出語句的文字串（包括大小寫、空白和註釋）與在第１步中識別的所有
已存在的SQL語句相比較。
例如：
SELECT * FROM emp WHERE empno = 1000;
和下列每一個都不同
SELECT * from 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 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 * 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_SHARING引數，使這類問題也可以使用共享SQL，從而使這樣的開發也可以利用共享SQL功能。聽起來不錯，ORACLE真為使用者著想，使使用者在不改變程式碼的情況下還可以利用共享SQL的功能。真的如此嗎？天上不會無緣無故的掉一個餡餅的，ORACLE對該引數的使用做了說明，建議在經過實際測試後再改該引數的值(預設情況下，該引數的值為EXACT，語句完全一致才使用共享SQL)。因為有可能該變該值後，你的硬編碼SQL是可以使用共享SQL了，但資料庫的效能反而會下降。 我在實際應用中已經遇到這種情況。所以建議編寫需要穩定執行程式的開發人員最好還是一開始就使用繫結變數的SQL。
Rowid的概念：
rowid是一個偽列，既然是偽列，那麼這個列就不是使用者定義，而是系統自己給加上的。對每個表都有一個rowid的偽列，但是表中並不物理儲存ROWID列的值。不過你可以像使用其它列那樣使用它，但是不能刪除改列，也不能對該列的值進行修改、插入。一旦一行資料插入資料庫，則rowid在該行的生命週期內是唯一的，即即使該行產生行遷移，行的rowid也不會改變。
為什麼使用ROWID
rowid對訪問一個表中的給定的行提供了最快的訪問方法，透過ROWID可以直接定位到相應的資料塊上，然後將其讀到記憶體。我們建立一個索引時，該索引不但儲存索引列的值，而且也儲存索引值所對應的行的ROWID，這樣我們透過索引快速找到相應行的ROWID後，透過該ROWID，就可以迅速將資料查詢出來。這也就是我們使用索引查詢時，速度比較快的原因。
在ORACLE8以前的版本中，ROWID由FILE 、BLOCK、ROW NUMBER構成。隨著oracle8中物件概念的擴充套件，ROWID發生了變化，ROWID由OBJECT、FILE、BLOCK、ROW NUMBER構成。利用DBMS_ROWID可以將rowid分解成上述的各部分，也可以將上述的各部分組成一個有效的rowid。
Recursive SQL概念
有時為了執行使用者發出的一個sql語句，Oracle必須執行一些額外的語句，我們將這些額外的語句稱之為'recursive calls'或'recursive SQL statements'。如當一個DDL語句發出後，ORACLE總是隱含的發出一些recursive SQL語句，來修改資料字典資訊，以便使用者可以成功的執行該DDL語句。當需要的資料字典資訊沒有在共享記憶體中時，經常會發生Recursive calls，這些Recursive calls會將資料字典資訊從硬碟讀入記憶體中。使用者不比關心這些recursive SQL語句的執行情況，在需要的時候，ORACLE會自動的在內部執行這些語句。當然DML語句與SELECT都可能引起recursive SQL。簡單的說，我們可以將觸發器視為recursive SQL。
Row Source(行源)
用在查詢中，由上一操作返回的符合條件的行的集合，即可以是表的全部行資料的集合；也可以是表的部分行資料的集合；也可以為對上2個row source進行連線操作(如join連線)後得到的行資料集合。
Predicate(謂詞)
一個查詢中的WHERE限制條件
Driving Table(驅動表)
該表又稱為外層表(OUTER TABLE)。這個概念用於巢狀與HASH連線中。如果該row source返回較多的行資料，則對所有的後續操作有負面影響。注意此處雖然翻譯為驅動表，但實際上翻譯為驅動行源(driving row source)更為確切。一般說來，是應用查詢的限制條件後，返回較少行源的表作為驅動表，所以如果一個大表在WHERE條件有有限制條件(如等值限制)，則該大表作為驅動表也是合適的，所以並不是只有較小的表可以作為驅動表，正確說法應該為應用查詢的限制條件後，返回較少行源的表作為驅動表。在執行計劃中，應該為靠上的那個row source，後面會給出具體說明。在我們後面的描述中，一般將該表稱為連線操作的row source 1。
Probed Table(被探查表)
該表又稱為內層表(INNER TABLE)。在我們從驅動表中得到具體一行的資料後，在該表中尋找符合連線條件的行。所以該表應當為大表(實際上應該為返回較大row source的表)且相應的列上應該有索引。在我們後面的描述中，一般將該表稱為連線操作的row source 2。
組合索引(concatenated index)
由多個列構成的索引，如create index idx_emp on emp(col1, col2, col3, ……)，則我們稱idx_emp索引為組合索引。在組合索引中有一個重要的概念：引導列(leading column)，在上面的例子中，col1列為引導列。當我們進行查詢時可以使用”where col1 = ? ”，也可以使用”where col1 = ? and col2 = ?”，這樣的限制條件都會使用索引，但是”where col2 = ? ”查詢就不會使用該索引。所以限制條件中包含先導列時，該限制條件才會使用該組合索引。
可選擇性(selectivity)：
比較一下列中唯一鍵的數量和表中的行數，就可以判斷該列的可選擇性。如果該列的”唯一鍵的數量/表中的行數”的比值越接近1，則該列的可選擇性越高，該列就越適合建立索引，同樣索引的可選擇性也越高。在可選擇性高的列上進行查詢時，返回的資料就較少，比較適合使用索引查詢。
有了這些背景知識後就開始介紹執行計劃。為了執行語句，Oracle可能必須實現許多步驟。這些步驟中的每一步可能是從資料庫中物理檢索資料行，或者用某種方法準備資料行，供發出語句的使用者使用。Oracle用來執行語句的這些步驟的組合被稱之為執行計劃。執行計劃是SQL最佳化中最為複雜也是最為關鍵的部分，只有知道了ORACLE在內部到底是如何執行該SQL語句後，我們才能知道最佳化器選擇的執行計劃是否為最優的。執行計劃對於DBA來說，就象財務報表對於財務人員一樣重要。所以我們面臨的問題主要是：如何得到執行計劃；如何分析執行計劃，從而找出影響效能的主要問題。下面先從分析樹型執行計劃開始介紹，然後介紹如何得到執行計劃，再介紹如何分析執行計劃。
舉例：
這個例子顯示關於下面SQL語句的執行計劃。
SELECT ename, job, sal, dname
FROM emp, dept
WHERE emp.deptno = derpt.deptno
AND NOT EXISTS
( SELECT *
FROM salgrade
WHERE emp.sal BETWEEN losal AND hisal );
此語句查詢薪水不在任何建議薪水範圍內的所有僱員的名字，工作，薪水和部門名。
下圖5-1顯示了一個執行計劃的圖形表示：
執行計劃的步驟
執行計劃的每一步返回一組行，它們或者為下一步所使用，或者在最後一步時返回給發出SQL語句的使用者或應用。由每一步返回的一組行叫做行源(row source）。圖5-1樹狀圖顯示了從一步到另一步行資料的流動情況。每步的編號反映了在你觀察執行計劃時所示步驟的順序（如何觀察執行計劃將被簡短地說明）。一般來說這並不是每一步被執行的先後順序。執行計劃的每一步或者從資料庫中檢索行，或者接收來自一個或多個行源的行資料作為輸入：
由紅色字框指出的步驟從資料庫中的資料檔案中物理檢索資料。這種步驟被稱之為存取路徑，後面會詳細介紹在Oracle可以使用的存取路徑：
l 第３步和第６步分別的從EMP表和SALGRADE表讀所有的行。
l 第５步在PK_DEPTNO索引中查詢由步驟３返回的每個DEPTNO值。它找出與DEPT表中相關聯的那些行的ROWID。
l 第４步從DEPT表中檢索出ROWID為第５步返回的那些行。
由黑色字框指出的步驟在行源上操作，如做2表之間的關聯，排序，或過濾等操作，後面也會給出詳細的介紹：
l 第２步實現巢狀的迴圈操作(相當於C語句中的巢狀迴圈)，接收從第３步和第４步來的行源，把來自第３步源的每一行與它第４步中相應的行連線在一起，返回結果行到第１步。
l 第１步完成一個過濾器操作。它接收來自第２步和第６步的行源，消除掉第２步中來的，在第６步有相應行的那些行，並將來自第２步的剩下的行返回給發出語句的使用者或應用。
實現執行計劃步驟的順序
執行計劃中的步驟不是按照它們編號的順序來實現的：Oracle首先實現圖5-1樹結構圖形裡作為葉子出現的那些步驟(例如步驟3、5、6)。由每一步返回的行稱為它下一步驟的行源。然後Oracle實現父步驟。
舉例來說，為了執行圖5-1中的語句，Oracle以下列順序實現這些步驟：
l 首先，Oracle實現步驟３，並一行一行地將結果行返回給第２步。
l 對第３步返回的每一行，Oracle實現這些步驟：
-- Oracle實現步驟５，並將結果ROWID返回給第４步。
-- Oracle實現步驟４，並將結果行返回給第２步。
-- Oracle實現步驟２，將接受來自第３步的一行和來自第４步的一行，並返回
給第１步一行。
-- Oracle實現步驟６，如果有結果行的話，將它返回給第１步。
-- Oracle實現步驟１，如果從步驟６返回行，Oracle將來自第２步的行返回給
發出SQL語句的使用者。
注意Oracle對由第３步返回的每一行實現步驟５，４，２，６一次。許多父步驟在它們能執行之前只需要來自它們子步驟的單一行。對這樣的父步驟來說，只要從子步驟已返回單一行時立即實現父步驟（可能還有執行計劃的其餘部分）。如果該父步驟的父步驟同樣可以透過單一行返回啟用的話，那麼它也同樣被執行。所以，執行可以在樹上串聯上去，可能包含執行計劃的餘下部分。對於這樣的操作，可以使用first_rows作為最佳化目標以便於實現快速響應使用者的請求。
對每個由子步驟依次檢索出來的每一行，Oracle就實現父步驟及所有串聯在一起的步驟一次。對由子步驟返回的每一行所觸發的父步驟包括表存取，索引存取，巢狀的迴圈連線和過濾器。
有些父步驟在它們被實現之前需要來自子步驟的所有行。對這樣的父步驟，直到所有行從子步驟返回之前Oracle不能實現該父步驟。這樣的父步驟包括排序，排序一合併的連線，組功能和總計。對於這樣的操作，不能使用first_rows作為最佳化目標，而可以用all_rows作為最佳化目標，使該中型別的操作耗費的資源最少。
有時語句執行時，並不是象上面說的那樣一步一步有先有後的進行，而是可能並行執行，如在實際環境中，3、5、4步可能並行執行，以便取得更好的效率。從上面的樹型圖上，是很難看出各個操作執行的先後順序，而透過ORACLE生成的另一種形式的執行計劃，則可以很容易的看出哪個操作先執行，哪個後執行，這樣的執行計劃是我們真正需要的，後面會給出詳細說明。現在先來看一些預備知識。
訪問路徑(方法) -- access path
最佳化器在形成執行計劃時需要做的一個重要選擇是如何從資料庫查詢出需要的資料。對於SQL語句存取的任何表中的任何行，可能存在許多存取路徑(存取方法)，透過它們可以定位和查詢出需要的資料。最佳化器選擇其中自認為是最最佳化的路徑。
在物理層，oracle讀取資料，一次讀取的最小單位為資料庫塊(由多個連續的作業系統塊組成)，一次讀取的最大值由作業系統一次I/O的最大值與multiblock引數共同決定，所以即使只需要一行資料，也是將該行所在的資料庫塊讀入記憶體。邏輯上，oracle用如下存取方法訪問資料：
1) 全表掃描（Full Table Scans, FTS）
為實現全表掃描，Oracle讀取表中所有的行，並檢查每一行是否滿足語句的WHERE限制條件。Oracle順序地讀取分配給表的每個資料塊，直到讀到表的最高水線處(high water mark, HWM，標識表的最後一個資料塊)。一個多塊讀操作可以使一次I/O能讀取多塊資料塊(db_block_multiblock_read_count引數設定)，而不是隻讀取一個資料塊，這極大的減少了I/O總次數，提高了系統的吞吐量，所以利用多塊讀的方法可以十分高效地實現全表掃描，而且只有在全表掃描的情況下才能使用多塊讀操作。在這種訪問模式下，每個資料塊只被讀一次。由於HWM標識最後一塊被讀入的資料，而delete操作不影響HWM值，所以一個表的所有資料被delete後，其全表掃描的時間不會有改善，一般我們需要使用truncate命令來使HWM值歸為0。幸運的是oracle 10G後，可以人工收縮HWM的值。
由FTS模式讀入的資料被放到快取記憶體的Least Recently Used (LRU)列表的尾部，這樣可以使其快速交換出記憶體，從而不使記憶體重要的資料被交換出記憶體。
使用FTS的前提條件：在較大的表上不建議使用全表掃描，除非取出資料的比較多，超過總量的5% -- 10%，或你想使用並行查詢功能時。
使用全表掃描的例子：
~~~~~~~~~~~~~~~~~~~~~~~~
SQL> explain plan for select * from dual;
Query Plan
-----------------------------------------
SELECT STATEMENT [CHOOSE] Cost=
TABLE ACCESS FULL DUAL
2) 透過ROWID的表存取（Table Access by ROWID或rowid lookup）
行的ROWID指出了該行所在的資料檔案、資料塊以及行在該塊中的位置，所以透過ROWID來存取資料可以快速定位到目標資料上，是Oracle存取單行資料的最快方法。
為了透過ROWID存取表，Oracle 首先要獲取被選擇行的ROWID，或者從語句的WHERE子句中得到，或者透過表的一個或多個索引的索引掃描得到。Oracle然後以得到的ROWID為依據定位每個被選擇的行。
這種存取方法不會用到多塊讀操作，一次I/O只能讀取一個資料塊。我們會經常在執行計劃中看到該存取方法，如透過索引查詢資料。
使用ROWID存取的方法：
SQL> explain plan for select * from dept where rowid = 'AAAAyGAADAAAAATAAF';
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID DEPT [ANALYZED]
3）索引掃描（Index Scan或index lookup）
我們先透過index查詢到資料對應的rowid值(對於非唯一索引可能返回多個rowid值)，然後根據rowid直接從表中得到具體的資料，這種查詢方式稱為索引掃描或索引查詢(index lookup)。一個rowid唯一的表示一行資料，該行對應的資料塊是透過一次i/o得到的，在此情況下該次i/o只會讀取一個資料庫塊。
在索引中，除了儲存每個索引的值外，索引還儲存具有此值的行對應的ROWID值。索引掃描可以由2步組成：(1) 掃描索引得到對應的rowid值。 (2) 透過找到的rowid從表中讀出具體的資料。每步都是單獨的一次I/O，但是對於索引，由於經常使用，絕大多數都已經CACHE到記憶體中，所以第1步的I/O經常是邏輯I/O，即資料可以從記憶體中得到。但是對於第2步來說，如果表比較大，則其資料不可能全在記憶體中，所以其I/O很有可能是物理I/O，這是一個機械操作，相對邏輯I/O來說，是極其費時間的。所以如果多大表進行索引掃描，取出的資料如果大於總量的5% -- 10%，使用索引掃描會效率下降很多。
如下列所示：
SQL> explain plan for select empno, ename from emp where empno=10;
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX UNIQUE SCAN EMP_I1
注意TABLE ACCESS BY ROWID EMP部分，這表明這不是透過FTS存取路徑訪問資料，而是透過rowid lookup存取路徑訪問資料的。在此例中，所需要的rowid是由於在索引查詢empno列的值得到的，這種方式是INDEX UNIQUE SCAN查詢，後面給予介紹，EMP_I1為使用的進行索引查詢的索引名字。
但是如果查詢的資料能全在索引中找到，就可以避免進行第2步操作，避免了不必要的I/O，此時即使透過索引掃描取出的資料比較多，效率還是很高的，因為這隻會在索引中讀取。所以上面我在介紹基於規則的最佳化器時，使用了select count(id) from SWD_BILLDETAIL where cn
SQL> explain plan for select empno from emp where empno=10; -- 只查詢empno列值
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
INDEX UNIQUE SCAN EMP_I1
進一步講，如果sql語句中對索引列進行排序，因為索引已經預先排序好了，所以在執行計劃中不需要再對索引列進行排序
SQL> explain plan for select empno, ename from emp
where empno > 7876 order by empno;
Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX RANGE SCAN EMP_I1 [ANALYZED]
從這個例子中可以看到：因為索引是已經排序了的，所以將按照索引的順序查詢出符合條件的行，因此避免了進一步排序操作。
根據索引的型別與where限制條件的不同，有4種型別的索引掃描：
索引唯一掃描(index unique scan)
索引範圍掃描(index range scan)
索引全掃描(index full scan)
索引快速掃描(index fast full scan)
(1) 索引唯一掃描(index unique scan)
透過唯一索引查詢一個數值經常返回單個ROWID。如果該唯一索引有多個列組成(即組合索引)，則至少要有組合索引的引導列參與到該查詢中，如建立一個索引：create index idx_test on emp(ename, deptno, loc)。則select ename from emp where ename = ‘JACK’ and deptno = ‘DEV’語句可以使用該索引。如果該語句只返回一行，則存取方法稱為索引唯一掃描。而select ename from emp where deptno = ‘DEV’語句則不會使用該索引，因為where子句種沒有引導列。如果存在UNIQUE 或PRIMARY KEY 約束（它保證了語句只存取單行）的話，Oracle經常實現唯一性掃描。
使用唯一性約束的例子：
SQL> explain plan for
select empno,ename from emp where empno=10;
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX UNIQUE SCAN EMP_I1
(2) 索引範圍掃描(index range scan)
使用一個索引存取多行資料，同上面一樣，如果索引是組合索引，如(1)所示，而且select ename from emp where ename = ‘JACK’ and deptno = ‘DEV’語句返回多行資料，雖然該語句還是使用該組合索引進行查詢，可此時的存取方法稱為索引範圍掃描。在唯一索引上使用索引範圍掃描的典型情況下是在謂詞(where限制條件)中使用了範圍運算子(如>、、>=、<=、between)
使用索引範圍掃描的例子：
SQL> explain plan for select empno,ename from emp
where empno > 7876 order by empno;
Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX RANGE SCAN EMP_I1 [ANALYZED]
在非唯一索引上，謂詞col = 5可能返回多行資料，所以在非唯一索引上都使用索引範圍掃描。
使用index rang scan的3種情況：
(a) 在唯一索引列上使用了range運算子(> < <> >= <= between)
(b) 在組合索引上，只使用部分列進行查詢，導致查詢出多行
(c) 對非唯一索引列上進行的任何查詢。
(3) 索引全掃描(index full scan)
與全表掃描對應，也有相應的全索引掃描。在某些情況下，可能進行全索引掃描而不是範圍掃描，需要注意的是全索引掃描只在CBO模式下才有效。CBO根據統計數值得知進行全索引掃描比進行全表掃描更有效時，才進行全索引掃描，而且此時查詢出的資料都必須從索引中可以直接得到。
全索引掃描的例子：
An Index full scan will not perform. single block i/o's and so it may prove to be inefficient.
e.g.
Index BE_IX is a concatenated index on big_emp (empno, ename)
SQL> explain plan for select empno, ename from big_emp order by empno,ename;
Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT [CHOOSE] Cost=26
INDEX FULL SCAN BE_IX [ANALYZED]
(4) 索引快速掃描(index fast full scan)
掃描索引中的所有的資料塊，與 index full scan很類似，但是一個顯著的區別就是它不對查詢出的資料進行排序，即資料不是以排序順序被返回。在這種存取方法中，可以使用多塊讀功能，也可以使用並行讀入，以便獲得最大吞吐量與縮短執行時間。
索引快速掃描的例子：
BE_IX索引是一個多列索引：big_emp (empno,ename)
SQL> explain plan for select empno,ename from big_emp;
Query Plan
------------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
INDEX FAST FULL SCAN BE_IX [ANALYZED]
只選擇多列索引的第2列：
SQL> explain plan for select ename from big_emp;
Query Plan
------------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
INDEX FAST FULL SCAN BE_IX [ANALYZED]
表之間的連線
Join是一種試圖將兩個表結合在一起的謂詞，一次只能連線2個表，表連線也可以被稱為表關聯。在後面的敘述中，我們將會使用”row source”來代替”表”，因為使用row source更嚴謹一些，並且將參與連線的2個row source分別稱為row source1和row source 2。Join過程的各個步驟經常是序列操作，即使相關的row source可以被並行訪問，即可以並行的讀取做join連線的兩個row source的資料，但是在將表中符合限制條件的資料讀入到記憶體形成row source後，join的其它步驟一般是序列的。有多種方法可以將2個表連線起來，當然每種方法都有自己的優缺點，每種連線型別只有在特定的條件下才會發揮出其最大優勢。
row source(表)之間的連線順序對於查詢的效率有非常大的影響。透過首先存取特定的表，即將該表作為驅動表，這樣可以先應用某些限制條件，從而得到一個較小的row source，使連線的效率較高，這也就是我們常說的要先執行限制條件的原因。一般是在將表讀入記憶體時，應用where子句中對該表的限制條件。
根據2個row source的連線條件的中運算子的不同，可以將連線分為等值連線(如WHERE A.COL3 = B.COL4)、非等值連線(WHERE A.COL3 > B.COL4)、外連線(WHERE A.COL3 = B.COL4(+))。上面的各個連線的連線原理都基本一樣，所以為了簡單期間，下面以等值連線為例進行介紹。在後面的介紹中，都已：
SELECT A.COL1, B.COL2
FROM A, B
WHERE A.COL3 = B.COL4;
為例進行說明，假設A表為Row Soruce1，則其對應的連線操作關聯列為COL 3；B表為Row Soruce2，則其對應的連線操作關聯列為COL 4；
連線型別：
目前為止，無論連線運算子如何，典型的連線型別共有3種：
排序 - - 合併連線(Sort Merge Join (SMJ) )
巢狀迴圈(Nested Loops (NL) )
雜湊連線(Hash Join)
排序 - - 合併連線(Sort Merge Join, SMJ)
內部連線過程：
1) 首先生成row source1需要的資料，然後對這些資料按照連線操作關聯列(如A.col3)進行排序。
2) 隨後生成row source2需要的資料，然後對這些資料按照與sort source1對應的連線操作關聯列(如B.col4)進行排序。
3) 最後兩邊已排序的行被放在一起執行合併操作，即將2個row source按照連線條件連線起來
下面是連線步驟的圖形表示：
MERGE
/ \
SORT SORT
| |
Row Source 1 Row Source 2
如果row source已經在連線關聯列上被排序，則該連線操作就不需要再進行sort操作，這樣可以大大提高這種連線操作的連線速度，因為排序是個極其費資源的操作，特別是對於較大的表。 預先排序的row source包括已經被索引的列(如a.col3或b.col4上有索引)或row source已經在前面的步驟中被排序了。儘管合併兩個row source的過程是序列的，但是可以並行訪問這兩個row source(如並行讀入資料，並行排序).
SMJ連線的例子：
SQL> explain plan for
select /*+ ordered */ e.deptno, d.deptno
from emp e, dept d
where e.deptno = d.deptno
order by e.deptno, d.deptno;
Query Plan
-------------------------------------
SELECT STATEMENT [CHOOSE] Cost=17
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL EMP [ANALYZED]
SORT JOIN
TABLE ACCESS FULL DEPT [ANALYZED]
排序是一個費時、費資源的操作，特別對於大表。基於這個原因，SMJ經常不是一個特別有效的連線方法，但是如果2個row source都已經預先排序，則這種連線方法的效率也是蠻高的。
巢狀迴圈(Nested Loops, NL)
這個連線方法有驅動表(外部表)的概念。其實，該連線過程就是一個2層巢狀迴圈，所以外層迴圈的次數越少越好，這也就是我們為什麼將小表或返回較小row source的表作為驅動表(用於外層迴圈)的理論依據。但是這個理論只是一般指導原則，因為遵循這個理論並不能總保證使語句產生的I/O次數最少。有時不遵守這個理論依據，反而會獲得更好的效率。如果使用這種方法，決定使用哪個表作為驅動表很重要。有時如果驅動表選擇不正確，將會導致語句的效能很差、很差。
內部連線過程：
Row source1的Row 1 -------------- -- Probe -> Row source 2
Row source1的Row 2 -------------- -- Probe -> Row source 2
Row source1的Row 3 -------------- -- Probe -> Row source 2
…….
Row source1的Row n -------------- -- Probe -> Row source 2
從內部連線過程來看，需要用row source1中的每一行，去匹配row source2中的所有行，所以此時保持row source1儘可能的小與高效的訪問row source2(一般透過索引實現)是影響這個連線效率的關鍵問題。這只是理論指導原則，目的是使整個連線操作產生最少的物理I/O次數，而且如果遵守這個原則，一般也會使總的物理I/O數最少。但是如果不遵從這個指導原則，反而能用更少的物理I/O實現連線操作，那儘管違反指導原則吧！因為最少的物理I/O次數才是我們應該遵從的真正的指導原則，在後面的具體案例分析中就給出這樣的例子。
在上面的連線過程中，我們稱Row source1為驅動表或外部表。Row Source2被稱為被探查表或內部表。
在NESTED LOOPS連線中，Oracle讀取row source1中的每一行，然後在row sourc2中檢查是否有匹配的行，所有被匹配的行都被放到結果集中，然後處理row source1中的下一行。這個過程一直繼續，直到row source1中的所有行都被處理。這是從連線操作中可以得到第一個匹配行的最快的方法之一，這種型別的連線可以用在需要快速響應的語句中，以響應速度為主要目標。
如果driving row source(外部表)比較小，並且在inner row source(內部表)上有唯一索引，或有高選擇性非唯一索引時，使用這種方法可以得到較好的效率。NESTED LOOPS有其它連線方法沒有的的一個優點是：可以先返回已經連線的行，而不必等待所有的連線操作處理完才返回資料，這可以實現快速的響應時間。
如果不使用並行操作，最好的驅動表是那些應用了where 限制條件後，可以返回較少行資料的的表，所以大表也可能稱為驅動表，關鍵看限制條件。對於並行查詢，我們經常選擇大表作為驅動表，因為大表可以充分利用並行功能。當然，有時對查詢使用並行操作並不一定會比查詢不使用並行操作效率高，因為最後可能每個表只有很少的行符合限制條件，而且還要看你的硬體配置是否可以支援並行(如是否有多個CPU，多個硬碟控制器)，所以要具體問題具體對待。
NL連線的例子：
SQL> explain plan for
select a.dname,b.sql
from dept a,emp b
where a.deptno = b.deptno;
Query Plan
-------------------------
SELECT STATEMENT [CHOOSE] Cost=5
NESTED LOOPS
TABLE ACCESS FULL DEPT [ANALYZED]
TABLE ACCESS FULL EMP [ANALYZED]
雜湊連線(Hash Join, HJ)
這種連線是在oracle 7.3以後引入的，從理論上來說比NL與SMJ更高效，而且只用在CBO最佳化器中。
較小的row source被用來構建hash table與bitmap，第2個row source被用來被hansed，並與第一個row source生成的hash table進行匹配，以便進行進一步的連線。Bitmap被用來作為一種比較快的查詢方法，來檢查在hash table中是否有匹配的行。特別的，當hash table比較大而不能全部容納在記憶體中時，這種查詢方法更為有用。這種連線方法也有NL連線中所謂的驅動表的概念，被構建為hash table與bitmap的表為驅動表，當被構建的hash table與bitmap能被容納在記憶體中時，這種連線方式的效率極高。
HASH連線的例子：
SQL> explain plan for
select /*+ use_hash(emp) */ empno
from emp, dept
where emp.deptno = dept.deptno;
Query Plan
----------------------------
SELECT STATEMENT [CHOOSE] Cost=3
HASH JOIN
TABLE ACCESS FULL DEPT
TABLE ACCESS FULL EMP
要使雜湊連線有效，需要設定HASH_JOIN_ENABLED=TRUE，預設情況下該引數為TRUE，另外，不要忘了還要設定hash_area_size引數，以使雜湊連線高效執行，因為雜湊連線會在該引數指定大小的記憶體中執行，過小的引數會使雜湊連線的效能比其他連線方式還要低。
總結一下，在哪種情況下用哪種連線方法比較好：
排序 - - 合併連線(Sort Merge Join, SMJ)：
a) 對於非等值連線，這種連線方式的效率是比較高的。
b) 如果在關聯的列上都有索引，效果更好。
c) 對於將2個較大的row source做連線，該連線方法比NL連線要好一些。
d) 但是如果sort merge返回的row source過大，則又會導致使用過多的rowid在表中查詢資料時，資料庫效能下降，因為過多的I/O。
巢狀迴圈(Nested Loops, NL)：
a) 如果driving row source(外部表)比較小，並且在inner row source(內部表)上
有唯一索引，或有高選擇性非唯一索引時，使用這種方法可以得到較好的效率。
b) NESTED LOOPS有其它連線方法沒有的的一個優點是：可以先返回已經
連線的行，而不必等待所有的連線操作處理完才返回資料，
這可以實現快速的響應時間。
雜湊連線(Hash Join, HJ)：
a) 這種方法是在oracle7後來引入的，使用了比較先進的連線理論，
一般來說，其效率應該好於其它2種連線，但是這種連線只能用在
CBO最佳化器中，而且需要設定合適的hash_area_size引數，
才能取得較好的效能。
b) 在2個較大的row source之間連線時會取得相對較好的效率，在一個
row source較小時則能取得更好的效率。
c) 只能用於等值連線中
笛卡兒乘積(Cartesian Product)
當兩個row source做連線，但是它們之間沒有關聯條件時，就會在兩個row source中做笛卡兒乘積，這通常由編寫程式碼疏漏造成(即程式設計師忘了寫關聯條件)。笛卡爾乘積是一個表的每一行依次與另一個表中的所有行匹配。在特殊情況下我們可以使用笛卡兒乘積，如在星形連線中，除此之外，我們要儘量使用笛卡兒乘積，否則，自己想結果是什麼吧！
注意在下面的語句中，在2個表之間沒有連線。
SQL> explain plan for
select emp.deptno,dept,deptno
from emp,dept
Query Plan
------------------------------
SLECT STATEMENT [CHOOSE] Cost=5
MERGE JOIN CARTESIAN
TABLE ACCESS FULL DEPT
SORT JOIN
TABLE ACCESS FULL EMP
CARTESIAN關鍵字指出了在2個表之間做笛卡爾乘積。假如表emp有n行，dept表有m行，笛卡爾乘積的結果就是得到n * m行結果。 |
如何產生執行計劃
要為一個語句生成執行計劃，可以有3種方法：
1)．最簡單的辦法
Sql> set autotrace on
Sql> select * from dual;
執行完語句後，會顯示explain plan 與 統計資訊。
這個語句的優點就是它的缺點，這樣在用該方法檢視執行時間較長的sql語句時，需要等待該語句執行成功後，才返回執行計劃，使最佳化的週期大大增長。
Sql> set autotrace traceonly
這樣，就只會列出執行計劃，而不會真正的執行語句，大大減少了最佳化時間。雖然也列出了統計資訊，但是因為沒有執行語句，所以該統計資訊沒有用處，
如果執行該語句時遇到錯誤，解決方法為：
(1）在要分析的使用者下：
Sqlplus > @ ?\rdbms\admin\utlxplan.sql
(2) 用sys使用者登陸
Sqlplus > @ ?\sqlplus\admin\plustrce.sql
Sqlplus > grant plustrace to user_name; - - user_name是上面所說的分析使用者
2)．用explain plan命令
(1) sqlplus > @ ?\rdbms\admin\utlxplan.sql
(2) sqlplus > explain plan set statement_id =’???’ for select ………………
注意，用此方法時，並不執行sql語句，所以只會列出執行計劃，不會列出統計資訊，並且執行計劃只存在plan_table中。所以該語句比起set autotrace traceonly可用性要差。需要用下面的命令格式化輸出，所以這種方式我用的不多：
set linesize 150
set pagesize 500
col PLANLINE for a120
SELECT EXECORD EXEC_ORDER, PLANLINE
FROM (SELECT PLANLINE, ROWNUM EXECORD, ID, RID
FROM (SELECT PLANLINE, ID, RID, LEV
FROM (SELECT lpad(' ',2*(LEVEL),rpad(' ',80,' '))||
OPERATION||' '|| -- Operation
DECODE(OPTIONS,NULL,'','('||OPTIONS || ') ')|| -- Options
DECODE(OBJECT_OWNER,null,'','OF '''|| OBJECT_OWNER||'.')|| -- Owner
DECODE(OBJECT_NAME,null,'',OBJECT_NAME|| ''' ')|| -- Object Name
DECODE(OBJECT_TYPE,null,'','('||OBJECT_TYPE|| ') ')|| -- Object Type
DECODE(ID,0,'OPT_MODE:')|| -- Optimizer
DECODE(OPTIMIZER,null,'','ANALYZED','', OPTIMIZER)||
DECODE(NVL(COST,0)+NVL(CARDINALITY,0)+NVL(BYTES,0),
0,null,' (COST='||TO_CHAR(COST)||',CARD='||
TO_CHAR(CARDINALITY)||',BYTES='||TO_CHAR(BYTES)||')')
PLANLINE, ID, LEVEL LEV,
(SELECT MAX(ID)
FROM PLAN_TABLE PL2
CONNECT BY PRIOR ID = PARENT_ID
AND PRIOR STATEMENT_ID = STATEMENT_ID
START WITH ID = PL1.ID
AND STATEMENT_ID = PL1.STATEMENT_ID) RID
FROM PLAN_TABLE PL1
CONNECT BY PRIOR ID = PARENT_ID
AND PRIOR STATEMENT_ID = STATEMENT_ID
START WITH ID = 0
AND STATEMENT_ID = 'aaa')
ORDER BY RID, -LEV))
ORDER BY ID;
上面這2種方法只能為在本會話中正在執行的語句產生執行計劃，即我們需要已經知道了哪條語句執行的效率很差，我們是有目的只對這條SQL語句去最佳化。其實，在很多情況下，我們只會聽一個客戶抱怨說現在系統執行很慢，而我們不知道是哪個SQL引起的。此時有許多現成的語句可以找出耗費資源比較多的語句，如：
SELECT ADDRESS,
substr(SQL_TEXT,1,20) Text,
buffer_gets,
executions,
buffer_gets/executions AVG
FROM v$sqlarea
WHERE executions>0
AND buffer_gets > 100000
ORDER BY 5;
從而對找出的語句進行進一步最佳化。當然我們還可以為一個正在執行的會話中執行的所有SQL語句生成執行計劃，這需要對該會話進行跟蹤，產生trace檔案，然後對該檔案用tkprof程式格式化一下，這種得到執行計劃的方式很有用，因為它包含其它額外資訊，如SQL語句執行的每個階段(如Parse、Execute、Fetch)分別耗費的各個資源情況(如CPU、DISK、elapsed等)。
3)．用dbms_system儲存過程生成執行計劃
因為使用dbms_system儲存過程可以跟蹤另一個會話發出的sql語句，並記錄所使用的執行計劃，而且還提供其它對效能調整有用的資訊。因其使用方式與上面2種方式有些不太一樣，所以在附錄中單獨介紹。這種方法是對SQL進行調整比較有用的方式之一，有些情況下非它不可。具體內容參見附錄。
如何分析執行計劃 例1：
假設LARGE_TABLE是一個較大的表，且username列上沒有索引，則執行下面的語句：
SQL> SELECT * FROM LARGE_TABLE where USERNAME = ‘TEST’;
Query Plan
-----------------------------------------
SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14)
TABLE ACCESS FULL LARGE_TABLE [![[轉]透過分析SQL語句的執行計劃最佳化SQL(總結)]() 65001] [ANALYZED]
在這個例子中，TABLE ACCESS FULL LARGE_TABLE是第一個操作，意思是在LARGE_TABLE表上做全表掃描。當這個操作完成之後，產生的row source中的資料被送往下一步驟進行處理，在此例中，SELECT STATEMENT操作是這個查詢語句的最後一步。
ptimizer=CHOOSE 指明這個查詢的optimizer_mode，即optimizer_mode初始化引數指定的值，它並不是指語句執行時真的使用了該最佳化器。決定該語句使用何種最佳化器的唯一方法是看後面的cost部分。例如，如果給出的是下面的形式，則表明使用的是CBO最佳化器，此處的cost表示最佳化器認為該執行計劃的代價:
SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14)
然而假如執行計劃中給出的是類似下面的資訊，則表明是使用RBO最佳化器，因為cost部分的值為空，或者壓根就沒有cost部分。
SELECT STATEMENT Optimizer=CHOOSE Cost=
SELECT STATEMENT Optimizer=CHOOSE
這樣我們從Optimizer後面的資訊中可以得出執行該語句時到底用了什麼樣的最佳化器。特別的，如果Optimizer=ALL_ROWS| FIRST_ROWS| FIRST_ROWS_n，則使用的是CBO最佳化器；如果Optimizer=RULE，則使用的是RBO最佳化器。
cost屬性的值是一個在oracle內部用來比較各個執行計劃所耗費的代價的值，從而使最佳化器可以選擇最好的執行計劃。不同語句的cost值不具有可比性，只能對同一個語句的不同執行計劃的cost值進行比較。
[![[轉]透過分析SQL語句的執行計劃最佳化SQL(總結)]() 65001] 表明該部分查詢是以並行方式執行的。裡面的資料表示這個操作是由並行查詢的一個slave程式處理的，以便該操作可以區別於序列執行的操作。
[ANALYZED] 表明操作中引用的物件被分析過了，在資料字典中有該物件的統計資訊可以供CBO使用。
例2：
假定A、B、C都是不是小表，且在A表上一個組合索引：A(a.col1,a.col2) ，注意a.col1列為索引的引導列。
考慮下面的查詢：
select A.col4
from A , B , C
where B.col3 = 10 and A.col1 = B.col1 and A.col2 = C.col2 and C.col3 = 5
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C'
Statistics
----------------------------------------------------------
0 recursive calls
8 db block gets
6 consistent gets
0 physical reads
0 redo size
551 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
6 rows processed
在表做連線時，只能2個表先做連線，然後將連線後的結果作為一個row source，與剩下的表做連線，在上面的例子中，連線順序為B與A先連線，然後再與C連線：
B
col3=10 col3=5
如果沒有執行計劃，分析一下，上面的3個表應該拿哪一個作為第一個驅動表？從SQL語句看來，只有B表與C表上有限制條件，所以第一個驅動表應該為這2個表中的一個，到底是哪一個呢？
B表有謂詞B.col3 = 10，這樣在對B表做全表掃描的時候就將where子句中的限制條件(B.col3 = 10)用上，從而得到一個較小的row source, 所以B表應該作為第一個驅動表。而且這樣的話，如果再與A表做關聯，可以有效利用A表的索引(因為A表的col1列為leading column)。
當然上面的查詢中C表上也有謂詞(C.col3 = 5)，有人可能認為C表作為第一個驅動表也能獲得較好的效能。讓我們再來分析一下：如果C表作為第一個驅動表，則能保證驅動表生成很小的row source，但是看看連線條件A.col2 = C.col2，此時就沒有機會利用A表的索引，因為A表的col2列不為leading column，這樣nested loop的效率很差，從而導致查詢的效率很差。所以對於NL連線選擇正確的驅動表很重要。
因此上面查詢比較好的連線順序為(B - - > A) - - > C。如果資料庫是基於代價的最佳化器，它會利用計算出的代價來決定合適的驅動表與合適的連線順序。一般來說，CBO都會選擇正確的連線順序，如果CBO選擇了比較差的連線順序，我們還可以使用ORACLE提供的hints來讓CBO採用正確的連線順序。如下所示：
select /*+ ordered */ A.col4
from B,A,C
where B.col3 = 10
and A.col1 = B.col1
and A.col2 = C.col2
and C.col3 = 5
既然選擇正確的驅動表這麼重要，那麼讓我們來看一下執行計劃，到底各個表之間是如何關聯的，從而得到執行計劃中哪個表應該為驅動表：
在執行計劃中，需要知道哪個操作是先執行的，哪個操作是後執行的，這對於判斷哪個表為驅動表有用處。判斷之前，如果對錶的訪問是透過rowid，且該rowid的值是從索引掃描中得來得，則將該索引掃描先從執行計劃中暫時去掉。然後在執行計劃剩下的部分中，判斷執行順序的指導原則就是：最右、最上的操作先執行。具體解釋如下：
得到去除妨礙判斷的索引掃描後的執行計劃：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C'
看執行計劃的第3列，即字母部分，每列值的左面有空格作為縮排字元。在該列值左邊的空格越多，說明該列值的縮排越多，該列值也越靠右。如上面的執行計劃所示：第一列值為6的行的縮排最多，即該行最靠右；第一列值為4、5的行的縮排一樣，其靠右的程度也一樣，但是第一列值為4的行比第一列值為5的行靠上；談論上下關係時，只對連續的、縮排一致的行有效。
從這個圖中我們可以看到，對於NESTED LOOPS部分，最右、最上的操作是TABLE ACCESS (FULL) OF 'B'，所以這一操作先執行，所以該操作對應的B表為第一個驅動表(外部表)，自然，A表就為內部表了。從圖中還可以看出，B與A表做巢狀迴圈後生成了新的row source ，對該row source進行來排序後，與C表對應的排序了的row source(應用了C.col3 = 5限制條件)進行MSJ連線操作。所以從上面可以得出如下事實：B表先與A表做巢狀迴圈，然後將生成的row source與C表做排序—合併連線。
透過分析上面的執行計劃，我們不能說C表一定在B、A表之後才被讀取，事實上，B表有可能與C表同時被讀入記憶體，因為將表中的資料讀入記憶體的操作可能為並行的。事實上許多操作可能為交叉進行的，因為ORACLE讀取資料時，如果就是需要一行資料也是將該行所在的整個資料塊讀入記憶體，而且還有可能為多塊讀。
看執行計劃時，我們的關鍵不是看哪個操作先執行，哪個操作後執行，而是關鍵看錶之間連線的順序(如得知哪個為驅動表，這需要從操作的順序進行判斷)、使用了何種型別的關聯及具體的存取路徑(如判斷是否利用了索引)
在從執行計劃中判斷出哪個表為驅動表後，根據我們的知識判斷該表作為驅動表(就像上面判斷ABC表那樣)是否合適，如果不合適，對SQL語句進行更改，使最佳化器可以選擇正確的驅動表。
對於RBO最佳化器：
在ORACLE文件上說：對於RBO來說，以from 子句中從右到左的順序選擇驅動表，即最右邊的表為第一個驅動表，這是其英文原文：All things being equal RBO chooses the driving order by taking the tables in the FROM clause RIGHT to LEFT。不過，在我做的測試中，從來也沒有驗證過這種說法是正確的。我認為，即使在RBO中，也是有一套規則來決定使用哪種連線型別和哪個表作為驅動表，在選擇時肯定會考慮當前索引的情況，還可能會考慮where 中的限制條件，但是肯定是與where中限制條件的位置無關。
測試：
如果我建立3個表：
create table A(col1 number(4,0),col2 number(4,0), col4 char(30));
create table B(col1 number(4,0),col3 number(4,0), name_b char(30));
create table C(col2 number(4,0),col3 number(4,0), name_c char(30));
create index inx_col12A on a(col1,col2);
執行查詢：
select A.col4
from B, A, C
where B.col3 = 10
and A.col1 = B.col1
and A.col2 = C.col2
and C.col3 = 5;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=RULE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C'
select A.col4
from B, A, C
where A.col1 = B.col1
and A.col2 = C.col2;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=RULE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C'
將A表上的索引inx_col12A刪除後：
select A.col4
from B, A, C
where A.col1 = B.col1
and A.col2 = C.col2;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=RULE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 MERGE JOIN
4 3 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'C'
6 3 SORT (JOIN)
7 6 TABLE ACCESS (FULL) OF 'A'
8 1 SORT (JOIN)
9 8 TABLE ACCESS (FULL) OF 'B'
透過上面的這些例子，使我對oracle文件上的” All things being equal RBO chooses the driving order by taking the tables in the FROM clause RIGHT to LEFT”這句話持懷疑態度。此時，我也不能使用hints來強制最佳化器使用nested loop，如果使用了hints，這樣就自動使用CBO最佳化器，而不是RBO最佳化器了。
對於CBO最佳化器：
CBO根據統計資訊選擇驅動表，假如沒有統計資訊，則在from 子句中從左到右的順序選擇驅動表。這與RBO選擇的順序正好相反。這是英文原文(CBO determines join order from costs derived from gathered statistics. If there are no stats then CBO chooses the driving order of tables from LEFT to RIGHT in the FROM clause. This is OPPOSITE to the RBO) 。我還是沒法證實這句話的正確性。不過經過驗證：“如果用ordered 提示(此時肯定用CBO)，則以from 子句中按從左到右的順序選擇驅動表”這句話是正確的。實際上在CBO中，如果有統計資料(即對錶與索引進行了分析)，則最佳化器會自動根據cost值決定採用哪種連線型別，並選擇合適的驅動表，這與where子句中各個限制條件的位置沒有任何關係。如果我們要改變最佳化器選擇的連線型別或驅動表，則就需要使用hints了，具體hints的用法在後面會給予介紹。
測試：
如果我建立的3個表：
create table A(col1 number(4,0),col2 number(4,0), col4 char(30));
create table B(col1 number(4,0),col3 number(4,0), name_b char(30));
create table C(col2 number(4,0),col3 number(4,0), name_c char(30));
create index inx_col12A on a(col1,col2);
執行查詢：
select A.col4
from B, A, C
where B.col3 = 10
and A.col1 = B.col1
and A.col2 = C.col2
and C.col3 = 5;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=3 Card=1 Bytes=110)
1 0 NESTED LOOPS (Cost=3 Card=1 Bytes=110)
2 1 MERGE JOIN (CARTESIAN) (Cost=2 Card=1 Bytes=52)
3 2 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)
4 2 SORT (JOIN) (Cost=1 Card=1 Bytes=26)
5 4 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)
6 1 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)
select A.col4
from B, A, C
where A.col1 = B.col1
and A.col2 = C.col2;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=5 Card=55 Bytes=4620)
1 0 HASH JOIN (Cost=5 Card=55 Bytes=4620)
2 1 HASH JOIN (Cost=3 Card=67 Bytes=4757)
3 2 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=82 Bytes=1066)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)
5 1 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=82 Bytes=1066)
將A表上的索引inx_col12A刪除後：
select A.col4
from B, A, C
where A.col1 = B.col1
and A.col2 = C.col2;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=5 Card=55 Bytes=4620)
1 0 HASH JOIN (Cost=5 Card=55 Bytes=4620)
2 1 HASH JOIN (Cost=3 Card=67 Bytes=4757)
3 2 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=82 Bytes=1066)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)
5 1 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=82 Bytes=1066)
select /*+ ORDERED */A.col4
from C, A, B
where B.col3 = 10
and A.col1 = B.col1
and A.col2 = C.col2
and C.col3 = 5;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=3 Card=1 Bytes=110)
1 0 NESTED LOOPS (Cost=3 Card=1 Bytes=110)
2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=84)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)
這個查詢驗證了透過ORDERED提示可以正確的提示最佳化器選擇哪個表作為最佳化器。 | |
如何幹預執行計劃 - - 使用hints提示 基於代價的最佳化器是很聰明的，在絕大多數情況下它會選擇正確的最佳化器，減輕了DBA的負擔。但有時它也聰明反被聰明誤，選擇了很差的執行計劃，使某個語句的執行變得奇慢無比。此時就需要DBA進行人為的干預，告訴最佳化器使用我們指定的存取路徑或連線型別生成執行計劃，從而使語句高效的執行。例如，如果我們認為對於一個特定的語句，執行全表掃描要比執行索引掃描更有效，則我們就可以指示最佳化器使用全表掃描。在ORACLE中，是透過為語句新增hints(提示)來實現干預最佳化器最佳化的目的。
hints是oracle提供的一種機制，用來告訴最佳化器按照我們的告訴它的方式生成執行計劃。我們可以用hints來實現：
1) 使用的最佳化器的型別
2) 基於代價的最佳化器的最佳化目標，是all_rows還是first_rows。
3) 表的訪問路徑，是全表掃描，還是索引掃描，還是直接利用rowid。
4) 表之間的連線型別
5) 表之間的連線順序
6) 語句的並行程度
除了”RULE”提示外，一旦使用的別的提示，語句就會自動的改為使用CBO最佳化器，此時如果你的資料字典中沒有統計資料，就會使用預設的統計資料。所以建議大家如果使用CBO或HINTS提示，則最好對錶和索引進行定期的分析。
如何使用hints:
Hints只應用在它們所在sql語句塊(statement block，由select、update、delete關鍵字標識)上，對其它SQL語句或語句的其它部分沒有影響。如：對於使用union操作的2個sql語句，如果只在一個sql語句上有hints，則該hints不會影響另一個sql語句。
我們可以使用註釋(comment)來為一個語句新增hints，一個語句塊只能有一個註釋，而且註釋只能放在SELECT, UPDATE, or DELETE關鍵字的後面
使用hints的語法：
{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */
or
{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...
註解：
1) DELETE、INSERT、SELECT和UPDATE是標識一個語句塊開始的關鍵字，包含提示的註釋只能出現在這些關鍵字的後面，否則提示無效。
2) “+”號表示該註釋是一個hints，該加號必須立即跟在”/*”的後面，中間不能有空格。
3) hint是下面介紹的具體提示之一，如果包含多個提示，則每個提示之間需要用一個或多個空格隔開。
4) text 是其它說明hint的註釋性文字
如果你沒有正確的指定hints，Oracle將忽略該hints，並且不會給出任何錯誤。
使用全套的hints：
當使用hints時，在某些情況下，為了確保讓最佳化器產生最優的執行計劃，我們可能指定全套的hints。例如，如果有一個複雜的查詢，包含多個表連線，如果你只為某個表指定了INDEX提示(指示存取路徑在該表上使用索引)，最佳化器需要來決定其它應該使用的訪問路徑和相應的連線方法。因此，即使你給出了一個INDEX提示，最佳化器可能覺得沒有必要使用該提示。這是由於我們讓最佳化器選擇了其它連線方法和存取路徑，而基於這些連線方法和存取路徑，最佳化器認為使用者給出的INDEX提示無用。為了防止這種情況，我們要使用全套的hints，如：不但指定要使用的索引，而且也指定連線的方法與連線的順序等。
下面是一個使用全套hints的例子，ORDERED提示指出了連線的順序，而且為不同的表指定了連線方法：
SELECT /*+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b)
USE_NL (glcc glf) USE_MERGE (gp gsb) */
b.application_id, b.set_of_books_id ,
b.personnel_id, p.vendor_id Personnel,
p.segment1 PersonnelNumber, p.vendor_name Name
FROM jl_br_journals j, jl_br_balances b,
gl_code_combinations glcc, fnd_flex_values_vl glf,
gl_periods gp, gl_sets_of_books gsb, po_vendors p
WHERE ...
指示最佳化器的方法與目標的hints：
ALL_ROWS -- 基於代價的最佳化器，以吞吐量為目標
FIRST_ROWS(n) -- 基於代價的最佳化器，以響應時間為目標
CHOOSE -- 根據是否有統計資訊，選擇不同的最佳化器
RULE -- 使用基於規則的最佳化器
例子：
SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id
FROM employees
WHERE department_id = 20;
SELECT /*+ CHOOSE */ employee_id, last_name, salary, job_id
FROM employees
WHERE employee_id = 7566;
SELECT /*+ RULE */ employee_id, last_name, salary, job_id
FROM employees
WHERE employee_id = 7566;
指示儲存路徑的hints：
FULL /*+ FULL ( table ) */
指定該表使用全表掃描
ROWID /*+ ROWID ( table ) */
指定對該表使用rowid存取方法，該提示用的較少
INDEX /*+ INDEX ( table [index]) */
使用該表上指定的索引對錶進行索引掃描
INDEX_FFS /*+ INDEX_FFS ( table [index]) */
使用快速全表掃描
NO_INDEX /*+ NO_INDEX ( table [index]) */
不使用該表上指定的索引進行存取，仍然可以使用其它的索引進行索引掃描
SELECT /*+ FULL(e) */ employee_id, last_name
FROM employees e
WHERE last_name LIKE :b1;
SELECT /*+ROWID(employees)*/ *
FROM employees
WHERE rowid > 'AAAAtkAABAAAFNTAAA' AND employee_id = 155;
SELECT /*+ INDEX(A sex_index) use sex_index because there are few
male patients */ A.name, A.height, A.weight
FROM patients A
WHERE A.sex = ’m’;
SELECT /*+NO_INDEX(employees emp_empid)*/ employee_id
FROM employees
WHERE employee_id > 200;
指示連線順序的hints:
ORDERED /*+ ORDERED */
按from 字句中表的順序從左到右的連線
STAR /*+ STAR */
指示最佳化器使用星型查詢
SELECT /*+ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantity
FROM customers c, order_items l, orders o
WHERE c.cust_last_name = :b1
AND o.customer_id = c.customer_id
AND o.order_id = l.order_id;
/*+ ORDERED USE_NL(FACTS) INDEX(facts fact_concat) */
指示連線型別的hints：
USE_NL /*+ USE_NL ( table [,table, ...] ) */
使用巢狀連線
USE_MERGE /*+ USE_MERGE ( table [,table, ...]) */
使用排序- -合併連線
USE_HASH /*+ USE_HASH ( table [,table, ...]) */
使用HASH連線
注意：如果表有alias(別名)，則上面的table指的是表的別名，而不是真實的表名
具體的測試例項：
create table A(col1 number(4,0),col2 number(4,0), col4 char(30));
create table B(col1 number(4,0),col3 number(4,0), name_b char(30));
create table C(col2 number(4,0),col3 number(4,0), name_c char(30));
select A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 MERGE JOIN
4 3 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'B'
6 3 SORT (JOIN)
7 6 TABLE ACCESS (FULL) OF 'A'
8 1 SORT (JOIN)
9 8 TABLE ACCESS (FULL) OF 'C'
select /*+ ORDERED */ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=5 Card=1 Bytes=110)
1 0 HASH JOIN (Cost=5 Card=1 Bytes=110)
2 1 HASH JOIN (Cost=3 Card=1 Bytes=84)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)
select /*+ ORDERED USE_NL (A C)*/ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=4 Card=1 Bytes=110)
1 0 HASH JOIN (Cost=4 Card=1 Bytes=110)
2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=84)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)
建立索引：
create index inx_col12A on a(col1,col2);
select A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C'
select /*+ ORDERED */ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=5 Card=1 Bytes=110)
1 0 HASH JOIN (Cost=5 Card=1 Bytes=110)
2 1 HASH JOIN (Cost=3 Card=1 Bytes=84)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)
select /*+ ORDERED USE_NL (A C)*/ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=4 Card=1 Bytes=110)
1 0 HASH JOIN (Cost=4 Card=1 Bytes=110)
2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=84)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)
select /*+ USE_NL (A C)*/ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
我們這個查詢的意思是讓A、C表做NL連線，並且讓A表作為內表，但是從執行計劃來看，沒有達到我們的目的。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=1 Bytes=110)
1 0 NESTED LOOPS (Cost=3 Card=1 Bytes=110)
2 1 MERGE JOIN (CARTESIAN) (Cost=2 Card=1 Bytes=52)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)
4 2 SORT (JOIN) (Cost=1 Card=1 Bytes=26)
5 4 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)
6 1 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)
對物件進行分析後：
analyze table a compute statistics;
analyze table b compute statistics;
analyze table c compute statistics;
analyze index inx_col12A compute statistics;
select A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=5 Card=8 Bytes=336)
1 0 HASH JOIN (Cost=5 Card=8 Bytes=336)
2 1 MERGE JOIN (CARTESIAN) (Cost=3 Card=8 Bytes=64)
3 2 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)
4 2 SORT (JOIN) (Cost=2 Card=4 Bytes=16)
5 4 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
6 1 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)
select /*+ ORDERED */ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=5 Card=9 Bytes=378)
1 0 HASH JOIN (Cost=5 Card=9 Bytes=378)
2 1 HASH JOIN (Cost=3 Card=30 Bytes=1140)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)
select /*+ ORDERED USE_NL (A C)*/ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=7 Card=9 Bytes=378)
1 0 HASH JOIN (Cost=7 Card=9 Bytes=378)
2 1 NESTED LOOPS (Cost=5 Card=30 Bytes=1140)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)
select /*+ USE_NL (A C)*/ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=7 Card=9 Bytes=378)
1 0 HASH JOIN (Cost=7 Card=9 Bytes=378)
2 1 NESTED LOOPS (Cost=5 Card=30 Bytes=1140)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)
select /*+ ORDERED USE_NL (A B C) */ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=35 Card=9 Bytes=378)
1 0 NESTED LOOPS (Cost=35 Card=9 Bytes=378)
2 1 NESTED LOOPS (Cost=5 Card=30 Bytes=1140)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)
對於這個查詢我無論如何也沒有得到類似下面這樣的執行計劃：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=35 Card=9 Bytes=378)
1 0 NESTED LOOPS (Cost=35 Card=9 Bytes=378)
2 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)
3 1 NESTED LOOPS (Cost=5 Card=30 Bytes=1140)
4 3 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
5 3 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)
從上面的這些例子我們可以看出：透過給語句新增HINTS，讓其按照我們的意願執行，有時是一件很困難的事情，需要不斷的嘗試各種不同的hints。對於USE_NL與USE_HASH提示，建議同ORDERED提示一起使用，否則不容易指定那個表為驅動表。
具體案例分析 環境：oracle 817 + linux + 陣列櫃
swd_billdetail 表5000萬條資料
SUPER_USER 表2800條資料
連線列上都有索引，而且super_user中的一條對應於swd_billdetail表中的很多條記錄
表與索引都做了分析。
實際應用的查詢為：
select a.CHANNEL, B.user_class
from swd_billdetail B, SUPER_USER A
where A.cn = B.cn;
這樣在分析時導致查詢出的資料過多，不方便，所以用count(a.CHANNEL||B.user_class)來代替，而且count(a.CHANNEL||B.user_class)操作本身並不佔用過多的時間，所以可以接受此種替代。
利用索引查詢出SWD_BILLDETAIL表中所有記錄的方法
SQL> select count(id) from SWD_BILLDETAIL;
COUNT(ID)
----------
53923574
Elapsed: 00:02:166.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=18051 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'SYS_C001851' (UNIQUE) (Cost=18051 Card=54863946)
Statistics
----------------------------------------------------------
0 recursive calls
1952 db block gets
158776 consistent gets
158779 physical reads
1004 redo size
295 bytes sent via SQL*Net to client
421 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
利用全表掃描從SWD_BILLDETAIL表中取出全部資料的方法。
SQL> select count(user_class) from swd_billdetail;
COUNT(USER_CLASS)
-----------------
53923574
Elapsed: 00:11:703.07
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=165412 Card=1 Bytes=2)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'SWD_BILLDETAIL' (Cost=165412 Card=54863946 Bytes=109727892)
Statistics
----------------------------------------------------------
0 recursive calls
8823 db block gets
1431070 consistent gets
1419520 physical reads
0 redo size
303 bytes sent via SQL*Net to client
421 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
select count(a.CHANNEL||B.user_class)
from swd_billdetail B, SUPER_USER A
where A.cn = B.cn;
EXEC_ORDER PLANLINE
---------- -----------------------------------------------------------------------------------------------------------
6 SELECT STATEMENT OPT_MODE:CHOOSE (COST=108968,CARD=1,BYTES=21)
5 SORT (AGGREGATE) (COST=,CARD=1,BYTES=21)
4 NESTED LOOPS (COST=108968,CARD=1213745,BYTES=25488645)
1 TABLE ACCESS (FULL) OF 'SWORD.SUPER_USER' (COST=2,CARD=2794,BYTES=27940)
3 TABLE ACCESS (BY INDEX ROWID) OF 'SWORD.SWD_BILLDETAIL' (COST=39,CARD=54863946,BYTES=603503406)
2 INDEX (RANGE SCAN) OF 'SWORD.IDX_DETAIL_CN' (NON-UNIQUE) (COST=3,CARD=54863946,BYTES=)
這個查詢耗費的時間很長，需要1個多小時。
執行後的資訊如下：
COUNT(A.CHANNEL||B.USER_CLASS)
------------------------------
1186387
Elapsed: 01:107:6429.87
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=108968 Card=1 Bytes=21)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=108968 Card=1213745 Bytes=25488645)
3 2 TABLE ACCESS (FULL) OF 'SUPER_USER' (Cost=2 Card=2794Bytes=27940)
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'SWD_BILLDETAIL' (Cost=39 Card=54863946 Bytes=603503406)
5 4 INDEX (RANGE SCAN) OF 'IDX_DETAIL_CN' (NON-UNIQUE) (Cost=3 Card=54863946)
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
1196954 consistent gets
1165726 physical reads
0 redo size
316 bytes sent via SQL*Net to client
421 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
將語句中加入hints，讓oracle的最佳化器使用巢狀迴圈，並且大表作為驅動表，生成新的執行計劃：
select /*+ ORDERED USE_NL(A) */ count(a.CHANNEL||B.user_class)
from swd_billdetail B, SUPER_USER A
where A.cn = B.cn;
EXEC_ORDER PLANLINE
---------- -----------------------------------------------------------------------------------------------------
6 SELECT STATEMENT OPT_MODE:CHOOSE (COST=109893304,CARD=1,BYTES=21)
5 SORT (AGGREGATE) (COST=,CARD=1,BYTES=21)
4 NESTED LOOPS (COST=109893304,CARD=1213745,BYTES=25488645)
1 TABLE ACCESS (FULL) OF 'SWORD.SWD_BILLDETAIL' (COST=165412,CARD=54863946,BYTES=603503406)
3 TABLE ACCESS (BY INDEX ROWID) OF 'SWORD.SUPER_USER' (COST=2,CARD=2794,BYTES=27940)
2 INDEX (RANGE SCAN) OF 'SWORD.IDX_SUPER_USER_CN' (NON-UNIQUE) (COST=1,CARD=2794,BYTES=)
這個查詢耗費的時間較短，才20分鐘，效能比較好。
執行後的資訊如下：
COUNT(A.CHANNEL||B.USER_CLASS)
------------------------------
1186387
Elapsed: 00:20:1208.87
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=109893304 Card=1 Bytes=21)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=109893304 Card=1213745 Bytes=25488645)
3 2 TABLE ACCESS (FULL) OF 'SWD_BILLDETAIL' (Cost=165412 Card=54863946 Bytes=603503406)
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'SUPER_USER' (Cost=2Card=2794 Bytes=27940)
5 4 INDEX (RANGE SCAN) OF 'IDX_SUPER_USER_CN' (NON-UNIQUE) (Cost=1 Card=2794)
Statistics
----------------------------------------------------------
0 recursive calls
8823 db block gets
56650250 consistent gets
1413250 physical reads
0 redo size
316 bytes sent via SQL*Net to client
421 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
總結：
因為上兩個查詢都是採用nested loop迴圈，這時採用哪個表作為driving table就很重要。在第一個sql中，小表(SUPER_USER)作為driving table，符合oracle最佳化的建議，但是由於SWD_BILLDETAIL表中cn列的值有很多重複的，這樣對於SUPER_USER中的每一行，都會在SWD_BILLDETAIL中有很多行，利用索引查詢出這些行的rowid很快，但是再利用這些rowid去查詢SWD_BILLDETAIL表中的user_class列的值，就比較慢了。原因是這些rowid是隨機的，而且該表比較大，不可能快取到記憶體，所以幾乎每次按照rowid查詢都需要讀物理磁碟，這就是該執行計劃比較慢的真正原因。從結果可以得到驗證：查詢出1186387行，需要利用rowid從SWD_BILLDETAIL表中讀取1186387次，而且大部分為從硬碟上讀取。
反其道而行之，利用大表(SWD_BILLDETAIL)作為driving表，這樣大表只需要做一次全表掃描(而且會使用多塊讀功能，每次物理I/O都會讀取幾個oracle資料塊，從而一次讀取很多行，加快了執行效率)，對於讀出的每一行，都與SUPER_USER中的行進行匹配，因為SUPER_USER表很小，所以可以全部放到記憶體中，這樣匹配操作就極快，所以該sql執行的時間與SWD_BILLDETAIL表全表掃描的時間差不多(SWD_BILLDETAIL全表用11分鐘，而此查詢用20分鐘)。
另外：如果SWD_BILLDETAIL表中cn列的值唯一，則第一個sql執行計劃執行的結果或許也會不錯。如果SUPER_USER表也很大，如500萬行，則第2個sql執行計劃執行的結果反而又可能會差。其實，如果SUPER_USER表很小，則第2個sql語句的執行計劃如果不利用SUPER_USER表的索引，查詢或許會更快一些，我沒有對此進行測試。
所以在進行效能調整時，具體問題要具體分析，沒有一個統一的標準。 |
第6章 其它注意事項 1. 不要認為將optimizer_mode引數設為rule，就認為所有的語句都使用基於規則的最佳化器
不管optimizer_mode引數如何設定，只要滿足下面3個條件，就一定使用CBO。
1) 如果使用Index Only Tables(IOTs), 自動使用CBO.
2) Oracle 7.3以後，如果表上的Paralle degree option設為>1，
則自動使用CBO, 而不管是否用rule hints.
3) 除rlue以外的任何hints都將導致自動使用CBO來執行語句
總結一下，一個語句在執行時到底使用何種最佳化器可以從下面的表格中識別出來，從上到下看你的語句到底是否滿足description列中描述的條件：
Description 物件是否被分析 最佳化器的型別
~~~~~~~~~~~ ~~~~~~~~~~~~ ~~~~~~~~~
Non-RBO Object (Eg:IOT) n/a #1
Parallelism > 1 n/a #1
RULE hint n/a RULE
ALL_ROWS hint n/a ALL_ROWS
FIRST_ROWS hint n/a FIRST_ROWS
*Other Hint n/a #1
OPTIMIZER_GOAL=RULE n/a RULE
OPTIMIZER_GOAL=ALL_ROWS n/a ALL_ROWS
OPTIMIZER_GOAL=FIRST_ROWS n/a FIRST_ROWS
OPTIMIZER_GOAL=CHOOSE NO RULE
OPTIMIZER_GOAL=CHOOSE YES ALL_ROWS
#1 表示除非OPTIMIZER_GOAL 被設定為FIRST_ROWS ，否則將使用ALL_ROWS。在PL/SQL中，則一直是使用ALL_ROWS
*Other Hint 表示是指除RULE、ALL_ROWS 和FIRST_ROWS以外的其它提示
2) 當CBO選擇了一個次最佳化的執行計劃時, 不要同CBO過意不去, 先採取如下措施:
a) 檢查是否在表與索引上又最新的統計資料
b) 對所有的資料進行分析，而不是隻分析一部分資料
c) 檢查是否引用的資料字典表，在oracle 10G之前，預設情況下是不對資料字典表進行分析的。
d) 試試RBO最佳化器，看語句執行的效率如何，有時RBO能比CBO產生的更好的執行計劃
e) 如果還不行，跟蹤該語句的執行，生成trace資訊，然後用tkprof格式化trace資訊，這樣可以得到全面的供最佳化的資訊。
3) 假如利用附錄的方法對另一個會話進行trace，則該會話應該為專用連線
4) 不要認為繫結變數(bind variables)的缺點只有書寫麻煩，而優點多多，實際上使用繫結變數雖然避免了重複parse，但是它導致最佳化器不能使用資料庫中的列統計，從而選擇了較差的執行計劃。而使用硬編碼的SQL則可以使用列統計。當然隨著CBO功能的越來越強，這種情況會得到改善。目前就已經實現了在第一次執行繫結變數的sql語句時，考慮列統計。
5) 如果一個row source 超過10000行資料，則可以被認為大row source
6) 有(+)的表不是driving table，注意：如果有外聯接，而且order hint指定的順序與外聯結決定的順序衝突，則忽略order hint
7) 影響CBO選擇execution plan的初始化引數:
這些引數會影響cost值
ALWAYS_ANTI_JOIN
B_TREE_BITMAP_PLANS
COMPLEX_VIEW_MERGING
DB_FILE_MULTIBLOCK_READ_COUNT
FAST_FULL_SCAN_ENABLED
HASH_AREA_SIZE
HASH_JOIN_ENABLED
HASH_MULTIBLOCK_IO_COUNT
OPTIMIZER_FEATURES_ENABLE
OPTIMIZER_INDEX_CACHING
OPTIMIZER_INDEX_COST_ADJ
OPTIMIZER_MODE> / GOAL
OPTIMIZER_PERCENT_PARALLEL
OPTIMIZER_SEARCH_LIMIT
PARTITION_VIEW_ENABLED
PUSH_JOIN_PREDICATE
SORT_AREA_SIZE
SORT_DIRECT_WRITES
SORT_WRITE_BUFFER_SIZE
STAR_TRANSFORMATION_ENABLED
V733_PLANS_ENABLED
CURSOR_SHARING |
| | |