【文章內(nèi)容簡介】 t(id) from SWD_BILLDETAIL where 39。639。卻用了2個小時還沒有執(zhí)行完，經(jīng)分析該語句使用了列上的索引，然后利用查詢出的rowid再從表中查詢數(shù)據(jù)。我為什么不使用select count() from SWD_BILLDETAIL where 39。639。呢？后面在分析執(zhí)行路徑的索引掃描時時會給出說明。 下面就是基于規(guī)則的優(yōu)化器使用的執(zhí)行路徑與各個路徑對應(yīng)的等級： 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: SingleColumn 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(等級最低) 上面的執(zhí)行路徑中，RBO認(rèn)為越往下執(zhí)行的代價越大，即等級越低。在RBO生成執(zhí)行計劃時，如果它發(fā)現(xiàn)有等級高的執(zhí)行路徑可用，則肯定會使用等級高的路徑，而不管任何其它影響性能的元素，即RBO通過上面的路徑的等級決定執(zhí)行路徑的代價，執(zhí)行路徑的等級越高，則使用該執(zhí)行路徑的代價越小。如上面2個例子所述，如果使用RBO，則肯定使用索引訪問表，也就是選擇了比較差的執(zhí)行計劃，這樣會給數(shù)據(jù)庫性能帶來很大的負(fù)面影響。為了解決這個問題，從ORACLE 7開始o(jì)racle引入了基于代價的優(yōu)化器，下面給出了介紹?；诖鷥r的優(yōu)化器 Cost Based Optimization(簡稱CBO) Oracle把一個代價引擎(Cost Engine)集成到數(shù)據(jù)庫內(nèi)核中，用來估計每個執(zhí)行計劃需要的代價，該代價將每個執(zhí)行計劃所耗費的資源進(jìn)行量化，從而CBO可以根據(jù)這個代價選擇出最優(yōu)的執(zhí)行計劃。一個查詢耗費的資源可以被分成3個基本組成部分：I/O代價、CPU代價、network代價。I/O代價是將數(shù)據(jù)從磁盤讀入內(nèi)存所需的代價。訪問數(shù)據(jù)包括將數(shù)據(jù)文件中數(shù)據(jù)塊的內(nèi)容讀入到SGA的數(shù)據(jù)高速緩存中，在一般情況下，該代價是處理一個查詢所需要的最主要代價，所以我們在優(yōu)化時，一個基本原則就是降低查詢所產(chǎn)生的I/O總次數(shù)（注意這里說的是次數(shù)）。CPU代價是處理在內(nèi)存中數(shù)據(jù)所需要的代價，如一旦數(shù)據(jù)被讀入內(nèi)存，則我們在識別出我們需要的數(shù)據(jù)后，在這些數(shù)據(jù)上執(zhí)行排序(sort)或連接(join)操作，這需要耗費CPU資源。 對于需要訪問跨節(jié)點(即通常說的服務(wù)器)數(shù)據(jù)庫上數(shù)據(jù)的查詢來說，存在network代價，用來量化傳輸操作耗費的資源。查詢遠(yuǎn)程表的查詢或執(zhí)行分布式連接的查詢會在network代價方面花費比較大。 在使用CBO時，需要有表和索引的統(tǒng)計數(shù)據(jù)(分析數(shù)據(jù))作為基礎(chǔ)數(shù)據(jù)，有了這些數(shù)據(jù)做依據(jù)，CBO才能為各個執(zhí)行計劃計算出相對準(zhǔn)確的代價，從而使CBO選擇最佳的執(zhí)行計劃。所以定期的對表、索引進(jìn)行分析是絕對必要的，這樣才能使統(tǒng)計數(shù)據(jù)反映數(shù)據(jù)庫中的真實情況。否則就會使CBO選擇較差的執(zhí)行計劃，影響數(shù)據(jù)庫的性能。分析操作不必做的太頻繁，一般來說，每星期一次就足夠了。切記如果想使用CBO，則必須定期對表和索引進(jìn)行分析。 對于分析用的命令，隨著數(shù)據(jù)庫版本的升級，用的命令也發(fā)生了變換，在oracle 8i以前，主要是用ANALYZE命令。在ORACLE 8I以后，又引入了DBMS_STATS存儲包來進(jìn)行分析。幸運的是從ORACLE 10G以后，分析工作變成自動的了，這減輕的DBA的負(fù)擔(dān)，不過在一些特殊情況下，還需要一些手工分析。 如果采用了CBO優(yōu)化器，而沒有對表和索引進(jìn)行分析，沒有統(tǒng)計數(shù)據(jù)，則ORACLE使用缺省的統(tǒng)計數(shù)據(jù)(至少在ORACLE 9I中是這樣)，這可以從oracle的文檔上找到。使用的缺省值肯定與系統(tǒng)的實際統(tǒng)計值不一致，這可能會導(dǎo)致優(yōu)化器選擇錯誤的執(zhí)行計劃，影響數(shù)據(jù)庫的性能。 要注意的是：雖然CBO的功能隨著ORACLE新版本的推出，功能越來越強(qiáng)，但它不是能包治百病的神藥，否則就不再需要DBA了，那我就慘了！?。嶋H上任何一個語句，隨著硬件環(huán)境與應(yīng)用數(shù)據(jù)的不同，該語句的執(zhí)行計劃可能需要隨之發(fā)生變化，這樣才能取得最好的性能。所以有時候不在具體的環(huán)境下而進(jìn)行SQL性能調(diào)整是徒勞的。 在ORACLE8I推出的時候，ORACLE極力建議大家使用CBO，說CBO有種種好處，但是在那是ORACLE開發(fā)的應(yīng)用系統(tǒng)還是使用基于規(guī)則的優(yōu)化器，從這件事上我們可以得出這樣的結(jié)論：1) 如果團(tuán)隊的數(shù)據(jù)庫水平很高而且都熟悉應(yīng)用數(shù)據(jù)的特點，RBO也可以取得很好的性能。2）CBO不是很穩(wěn)定，但是一個比較有前途的優(yōu)化器，Oracle極力建議大家用是為了讓大家盡快發(fā)現(xiàn)它的BUG，以便進(jìn)一步改善，但是ORACLE為了對自己開發(fā)的應(yīng)用系統(tǒng)負(fù)責(zé)，他們還是使用了比較熟悉而且成熟的RBO。從這個事情上給我們的啟發(fā)就是：我們在以后的開發(fā)中，應(yīng)該盡量采用我們熟悉并且成熟的技術(shù)，而不要一味的采用新技術(shù)，一味采用新技術(shù)并不一定能開發(fā)出好的產(chǎn)品。幸運的是從ORACLE 10G后，CBO已經(jīng)足夠的強(qiáng)大與智能，大家可以放心的使用該技術(shù)，因為ORACLE 10G后，Oracle自己開發(fā)的應(yīng)用系統(tǒng)也使用CBO優(yōu)化器了。而且ORACLE規(guī)定，從ORACLE 10G開始，開始廢棄RBO優(yōu)化器。這句話并不是指在ORACLE 10G中不能使用RBO，而是從ORACLE 10G開始開始，不再為RBO的BUG提供修補(bǔ)服務(wù)。 在上面的第2個例子中，如果采用CBO優(yōu)化器，它就會考慮emp表的行數(shù)，deptno列的統(tǒng)計數(shù)據(jù)，發(fā)現(xiàn)對該列做查詢會查詢出過多的數(shù)據(jù)，并且考慮db_file_multiblock_read_count參數(shù)的設(shè)置，發(fā)現(xiàn)用全表掃描的代價比用索引掃描的代價要小，從而使用全表掃描從而取得良好的執(zhí)行性能。 判斷當(dāng)前數(shù)據(jù)庫使用何種優(yōu)化器： 主要是由optimizer_mode初始化參數(shù)決定的。該參數(shù)可能的取值為：first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows | （前面這些個都是cbo中一種特例，隸屬于cbo，它們會影響到oracle到底采用哪種連接方式）choose | rule。具體解釋如下： RULE為使用RBO優(yōu)化器。 CHOOSE則是根據(jù)實際情況，如果數(shù)據(jù)字典中包含被引用的表的統(tǒng)計數(shù)據(jù)，即引用的對象已經(jīng)被分析，則就使用CBO優(yōu)化器，否則為RBO優(yōu)化器。 ALL_ROWS為CBO優(yōu)化器使用的第一種具體的優(yōu)化方法，是以數(shù)據(jù)的吞吐量為主要目標(biāo)，以便可以使用最少的資源完成語句。 FIRST_ROWS為優(yōu)化器使用的第二種具體的優(yōu)化方法，是以數(shù)據(jù)的響應(yīng)時間為主要目標(biāo)，以便快速查詢出開始的幾行數(shù)據(jù)。 FIRST_ROWS_[1 | 10 | 100 | 1000] 為優(yōu)化器使用的第三種具體的優(yōu)化方法，讓優(yōu)化器選擇一個能夠把響應(yīng)時間減到最小的查詢執(zhí)行計劃，以迅速產(chǎn)生查詢結(jié)果的前 n 行。該參數(shù)為ORACLE 9I新引入的。 從ORACLE V7以來，optimizer_mode參數(shù)的缺省設(shè)置應(yīng)是choose，即如果對已分析的表查詢的話選擇CBO，否則選擇RBO。在此種設(shè)置中，如果采用了CBO，則缺省為CBO中的all_rows模式。 注意：即使指定數(shù)據(jù)庫使用RBO優(yōu)化器，但有時ORACLE數(shù)據(jù)庫還是會采用CBO優(yōu)化器，這并不是ORACLE的BUG，主要是由于從ORACLE 8I后引入的許多新特性都必須在CBO下才能使用，而你的SQL語句可能正好使用了這些新特性，此時數(shù)據(jù)庫會自動轉(zhuǎn)為使用CBO優(yōu)化器執(zhí)行這些語句。什么是優(yōu)化 優(yōu)化是選擇最有效的執(zhí)行計劃來執(zhí)行SQL語句的過程，這是在處理任何數(shù)據(jù)的語句（SELECT,INSERT,UPDATE或DELETE）中的一個重要步驟。對Oracle來說，執(zhí)行這樣的語句有許多不同的方法，譬如說，將隨著以什么順序訪問哪些表或索引的不同而不同。所使用的執(zhí)行計劃可以決定語句能執(zhí)行得有多快。Oracle中稱之為優(yōu)化器（Optimizer）的組件用來選擇這種它認(rèn)為最有效的執(zhí)行計劃。 由于一系列因素都會會影響語句的執(zhí)行，優(yōu)化器綜合權(quán)衡各個因素，在眾多的執(zhí)行計劃中選擇認(rèn)為是最佳的執(zhí)行計劃。然而，應(yīng)用設(shè)計人員通常比優(yōu)化器更知道關(guān)于特定應(yīng)用的數(shù)據(jù)特點。無論優(yōu)化器多么智能，在某些情況下開發(fā)人員能選擇出比優(yōu)化器選擇的最優(yōu)執(zhí)行計劃還要好的執(zhí)行計劃。這是需要人工干預(yù)數(shù)據(jù)庫優(yōu)化的主要原因。事實表明，在某些情況下，確實需要DBA對某些語句進(jìn)行手工優(yōu)化。注：從Oracle的一個版本到另一個版本，優(yōu)化器可能對同一語句生成不同的執(zhí)行計劃。在將來的Oracle 版本中，優(yōu)化器可能會基于它可以用的更好、更理想的信息，作出更優(yōu)的決策，從而導(dǎo)致為語句產(chǎn)生更優(yōu)的執(zhí)行計劃。第5章 ORACLE的執(zhí)行計劃背景知識： 為了更好的進(jìn)行下面的內(nèi)容我們必須了解一些概念性的術(shù)語：共享sql語句 為了不重復(fù)解析相同的SQL語句(因為解析操作比較費資源，會導(dǎo)致性能下降)，在第一次解析之后，ORACLE將SQL語句及解析后得到的執(zhí)行計劃存放在內(nèi)存中。這塊位于系統(tǒng)全局區(qū)域SGA(system global area)的共享池(shared buffer pool)中的內(nèi)存可以被所有的數(shù)據(jù)庫用戶共享。因此，當(dāng)你執(zhí)行一個SQL語句(有時被稱為一個游標(biāo))時，如果該語句和之前的執(zhí)行過的某一語句完全相同，并且之前執(zhí)行的該語句與其執(zhí)行計劃仍然在內(nèi)存中存在，則ORACLE就不需要再進(jìn)行分析（指的是執(zhí)行計劃的選擇過程，至于語法檢查、對象和權(quán)限確認(rèn)還是會做的吧？），直接得到該語句的執(zhí)行路徑。ORACLE的這個功能大大地提高了SQL的執(zhí)行性能并大大節(jié)省了內(nèi)存的使用。使用這個功能的關(guān)鍵是將執(zhí)行過的語句盡可能放到內(nèi)存中，所以這要求有大的共享池(通過設(shè)置shared buffer pool參數(shù)值)和盡可能的使用綁定變量的方法執(zhí)行SQL語句。 當(dāng)你向ORACLE 提交一個SQL語句，ORACLE會首先在共享內(nèi)存中查找是否有相同的語句。這里需要注明的是，ORACLE對兩者采取的是一種嚴(yán)格匹配，要達(dá)成共享，SQL語句必須完全相同(包括空格,換行等)。 下面是判斷SQL語句是否與共享內(nèi)存中某一SQL相同的步驟： 1). 對所發(fā)出語句的文本串進(jìn)行hashed。如果hash值與已在共享池中SQL語句的hash值相同，則進(jìn)行第2步：（hash值相同，語句到不一定相同，還需要具體確認(rèn)） 2)將所發(fā)出語句的文本串（包括大小寫、空白和注釋）與在第１步中識別的所有已存在的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語句被認(rèn)為相同 select pin , name from people where pin = :。 select pin , name from people where pin = :。 b. 該2個sql語句被認(rèn)為不相同 select pin , name from people where pin = :。 select pin , name from people where pin = :。 今后我們將上面的這類語句稱為綁定變量SQL。 3). 將所發(fā)出語句中涉及的對象與第２步中識別的已存在語句所涉及對象（使用對象的全稱，加上模式名）相比較。 例如: 如用戶user1與用戶user2下都有EMP表，則 1