·? ? ? ? DDL 語句處理(create .. , drop .. , alter .. , )
? ?? ?? ?? ? 圖3-1 概要的列出了處理和運行一個sql語句的需要各個重要階段。在某些情況下，Oracle運行sql的過程可能與下面列出的各個階段的順序有所不同。如DEFINE階段可能在FETCH階段之前，這主要依賴你如何書寫代碼。
? ?? ?? ?? ?對許多oracle的工具來說，其中某些階段會自動執行。絕大多數用戶不需要關心各個階段的細節問題，然而，知道執行的各個階段還是有必要的，這會幫助你寫出更高效的SQL語句來，而且還可以讓你猜測出性能差的SQL語句主要是由于哪一個階段造成的，然后我們針對這個具體的階段，找出解決的辦法。
下面具體說一下每一步中都發生了什么事情：.
? ?? ???由程序接口調用創建一個游標（cursor）。任何SQL語句都會創建它，特別在運行DML語句時，都是自動創建游標的，不需要開發人員干預。多數應用中，游標的創建是自動的。然而，在預編譯程序(pro*c)中游標的創建，可能是隱含的，也可能顯式的創建。在存儲過程中也是這樣的。
? ?? ? 在語法分析期間，SQL語句從用戶進程傳送到Oracle，SQL語句經語法分析后，SQL語句本身與分析的信息都被裝入到共享SQL區。在該階段中，可以解決許多類型的錯誤。
? ?? ?只有在共享池中不存在等價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...
? ?? ?描述階段只有在查詢結果的各個列是未知時才需要；例如，當查詢由用戶交互地輸入需要輸出的列名。在這種情況要用描述階段來決定查詢結果的特征（數據類型，長度和名字）。
? ?? ? 在查詢的定義階段，你指定與查詢出的列值對應的接收變量的位置、大小和數據類型，這樣我們通過接收變量就可以得到查詢結果。如果必要的話，Oracle會自動實現數據類型的轉換。這是將接收變量的類型與對應的列類型相比較決定的。
? ?? ?此時，Oracle知道了SQL語句的意思，但仍沒有足夠的信息用于執行該語句。Oracle 需要得到在語句中列出的所有變量的值。在該例中，Oracle需要得到對department_id列進行限定的值。得到這個值的過程就叫綁定變量(binding variables)
? ?? ?此過程稱之為將變量值捆綁進來。程序必須指出可以找到該數值的變量名（該變量被稱為捆綁變量，變量名實質上是一個內存地址，相當于指針）。應用的最終用戶可能并沒有發覺他們正在指定捆綁變量，因為Oracle 的程序可能只是簡單地指示他們輸入新的值，其實這一切都在程序中自動做了。
? ?? ? 如果Oracle 需要實現自動數據類型轉換的話（除非它們是隱含的或缺省的），你還必須對每個值指定數據類型和長度。關于這些信息可以參考oracle的相關文檔，如Oracle Call Interface Programmer's Guide
? ???ORACLE 可以在SELECTs, INSERTs, UPDATEs, MERGEs, DELETEs語句中執行相應并行查詢操作，對于某些DDL操作，如創建索引、用子查詢創建表、在分區表上的操作，也可以執行并行操作。并行化可以導致多個服務器進程(oracle server processes)為同一個SQL語句工作，使該SQL語句可以快速完成，但是會耗費更多的資源，所以除非很有必要，否則不要使用并行查詢。
? ?? ?到了現在這個時候，Oracle擁有所有需要的信息與資源，因此可以真正運行SQL語句了。如果該語句為SELECT查詢或INSERT語句，則不需要鎖定任何行，因為沒有數據需要被改變。然而，如果語句為UPDATE或DELETE語句，則該語句影響的所有行都被鎖定，防止該用戶提交或回滾之前，別的用戶對這些數據進行修改。這保證了數據的一致性。
? ???對于某些語句，你可以指定執行的次數，這稱為批處理(array processing)。指定執行N次，則綁定變量與定義變量被定義為大小為N的數組的開始位置，這種方法可以減少網絡開銷，也是優化的技巧之一。
? ???DDL語句的執行不同與DML語句和查詢語句的執行，這是因為DDL語句執行成功后需要對數據字典數據進行修改。對于DDL語句，語句的分析階段實際上包括分析、查找數據字典信息和執行。
? ?? ?一般來說，只有使用ORACLE編程接口的應用設計人員才關心操作的類型，并把相關的操作組織在一起，形成一個事務。一般來說，我門必須定義事務，這樣在一個邏輯單元中的所有工作可以同時被提交或回滾，保證了數據的一致性。一個事務應該由邏輯單元中的所有必須部分組成，不應該多一個，也不應該少一個。
? ?? ?例如，在兩個帳號之間的轉帳(這是一個事務或邏輯工作單元)，應該包含從一個帳號中借錢(由一個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，其實就是一個判斷條件，如”=”, “>”, ”<”等)，而且該謂詞上引用的列上有有效索引，那么優化器將使用索引訪問這個表，而不考慮其它因素，如表中數據的多少、表中數據的易變性、索引的可選擇性等。此時數據庫中沒有關于表與索引數據的統計性描述，如表中有多上行，每行的可選擇性等。優化器也不考慮實例參數，如multi block i/o、可用排序內存的大小等，所以優化器有時就選擇了次優化的計劃作為真正的執行計劃，導致系統性能不高。
? ? ? ? 如，對于
? ? ? ? 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 <'6';卻用了2個小時還沒有執行完，經分析該語句使用了cn列上的索引，然后利用查詢出的rowid再從表中查詢數據。我為什么不使用select count(cn) from SWD_BILLDETAIL where cn <'6';呢？后面在分析執行路徑的索引掃描時時會給出說明。
? ? ? ? 下面就是基于規則的優化器使用的執行路徑與各個路徑對應的等級：
? ? ? ? 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 <'6'，而沒有使用select count(cn) from SWD_BILLDETAIL where cn <'6'。因為在實際情況中，只查詢被索引列的值的情況極為少，所以，如果我在查詢中使用count(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行結果。
[center]如何產生執行計劃[/center]
要為一個語句生成執行計劃，可以有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進行調整比較有用的方式之一，有些情況下非它不可。具體內容參見附錄。
[center]如何分析執行計劃[/center]
例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 [:Q65001] [ANALYZED]
? ? ? ? 在這個例子中，TABLE ACCESS FULL LARGE_TABLE是第一個操作，意思是在LARGE_TABLE表上做全表掃描。當這個操作完成之后，產生的row source中的數據被送往下一步驟進行處理，在此例中，SELECT STATEMENT操作是這個查詢語句的最后一步。
? ?? ?? ?? ?? ? Optimizer=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值進行比較。
[:Q65001] 表明該部分查詢是以并行方式運行的。里面的數據表示這個操作是由并行查詢的一個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 Optimizer=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? ???<---> A <--->? ? C
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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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提示一起使用，否則不容易指定那個表為驅動表。
[center]具體案例分析：[/center]
環境：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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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表的索引，查詢或許會更快一些，我沒有對此進行測試。
? ? ? ? 所以在進行性能調整時，具體問題要具體分析，沒有一個統一的標準。
[center]第6章 其它注意事項[/center]
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