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