【文章內(nèi)容簡(jiǎn)介】 izer_mode初始化參數(shù)決定的。該參數(shù)可能的取值為：first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows | 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ì)劃。第3章 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)行分析，直接得到該語(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步： 2) 將所發(fā)出語(yǔ)句的文本串（包括大小寫(xiě)、空白和注釋）與在第１步中識(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。 17 3). 將所發(fā)出語(yǔ)句中涉及的對(duì)象與第２步中識(shí)別的已存在語(yǔ)句所涉及對(duì)象相比較。 例如: 如用戶user1與用戶user2下都有EMP表，則 用戶user1發(fā)出的語(yǔ)句：SELECT * FROM EMP。 與 用戶user2發(fā)出的語(yǔ)句：SELECT * FROM EMP。 被認(rèn)為是不相同的語(yǔ)句， 因?yàn)閮蓚€(gè)語(yǔ)句中引用的EMP不是指同一個(gè)表。 4). 在SQL語(yǔ)句中使用的捆綁變量的捆綁類型必須一致。 如果語(yǔ)句與當(dāng)前在共享池中的另一個(gè)語(yǔ)句是等同的話，Oracle并不對(duì)它進(jìn)行語(yǔ)法分析。而直接執(zhí)行該語(yǔ)句，提高了執(zhí)行效率，因?yàn)檎Z(yǔ)法分析比較耗費(fèi)資源。 注意的是，從oracle 8i開(kāi)始，新引入了一個(gè)CURSOR_SHARING參數(shù)，該參數(shù)的主要目的就是為了解決在編程過(guò)程中已大量使用的硬編碼SQL問(wèn)題。因?yàn)樵趯?shí)際開(kāi)發(fā)中，很多程序人員為了提高開(kāi)發(fā)速度，而采用類似下面的開(kāi)發(fā)方法： 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。 上面的代碼實(shí)際上使用了硬編碼SQL，使我們不能使用共享SQL的功能，結(jié)果是數(shù)據(jù)庫(kù)效率不高。但是從上面的2個(gè)語(yǔ)句來(lái)看，產(chǎn)生的硬編碼SQL只是列值不同，其它部分都是相同的，如果僅僅因?yàn)榱兄挡煌鴮?dǎo)致這2個(gè)語(yǔ)句不能共享是很可惜的，為了解決這個(gè)問(wèn)題，引入了CURSOR_SHARING參數(shù)，使這類問(wèn)題也可以使用共享SQL，從而使這樣的開(kāi)發(fā)也可以利用共享SQL功能。聽(tīng)起來(lái)不錯(cuò)，ORACLE真為用戶著想，使用戶在不改變代碼的情況下還可以利用共享SQL的功能。真的如此嗎？天上不會(huì)無(wú)緣無(wú)故的掉一個(gè)餡餅的，ORACLE對(duì)該參數(shù)的使用做了說(shuō)明，建議在經(jīng)過(guò)實(shí)際測(cè)試后再改該參數(shù)的值(缺省情況下，該參數(shù)的值為EXACT，語(yǔ)句完全一致才使用共享SQL)。因?yàn)橛锌赡茉撟冊(cè)撝岛?，你的硬編碼SQL是可以使用共享SQL了，但數(shù)據(jù)庫(kù)的性能反而會(huì)下降。 我在實(shí)際應(yīng)用中已經(jīng)遇到這種情況。所以建議編寫(xiě)需要穩(wěn)定運(yùn)行程序的開(kāi)發(fā)人員最好還是一開(kāi)始就使用綁定變量的SQL。 Rowid的概念： rowid是一個(gè)偽列，既然是偽列，那么這個(gè)列就不是用戶定義，而是系統(tǒng)自己給加上的。對(duì)每個(gè)表都有一個(gè)rowid的偽列，但是表中并不物理存儲(chǔ)ROWID列的值。不過(guò)你可以像使用其它列那樣使用它，但是不能刪除改列，也不能對(duì)該列的值進(jìn)行修改、插入。一旦一行數(shù)據(jù)插入數(shù)據(jù)庫(kù)，則rowid在該行的生命周期內(nèi)是唯一的，即即使該行產(chǎn)生行遷移，行的rowid也不會(huì)改變。 為什么使用ROWID rowid對(duì)訪問(wèn)一個(gè)表中的給定的行提供了最快的訪問(wèn)方法，通過(guò)ROWID可以直接定位 18 到相應(yīng)的數(shù)據(jù)塊上，然后將其讀到內(nèi)存。我們創(chuàng)建一個(gè)索引時(shí)，該索引不但存儲(chǔ)索引列的值，而且也存儲(chǔ)索引值所對(duì)應(yīng)的行的ROWID，這樣我們通過(guò)索引快速找到相應(yīng)行的ROWID后，通過(guò)該ROWID，就可以迅速將數(shù)據(jù)查詢出來(lái)。這也就是我們使用索引查詢時(shí)，速度比較快的原因。 在ORACLE8以前的版本中，ROWID由FILE 、BLOCK、ROW NUMBER構(gòu)成。隨著oracle8中對(duì)象概念的擴(kuò)展，ROWID發(fā)生了變化，ROWID由OBJECT、FILE、BLOCK、ROW NUMBER構(gòu)成。利用DBMS_ROWID可以將rowid分解成上述的各部分，也可以將上述的各部分組成一個(gè)有效的rowid。 Recursive SQL概念 有時(shí)為了執(zhí)行用戶發(fā)出的一個(gè)sql語(yǔ)句，Oracle必須執(zhí)行一些額外的語(yǔ)句，我們將這些額外的語(yǔ)句稱之為39。recursive calls39。或39。recursive SQL statements39。如當(dāng)一個(gè)DDL語(yǔ)句發(fā)出后，ORACLE總是隱含的發(fā)出一些recursive SQL語(yǔ)句，來(lái)修改數(shù)據(jù)字典信息，以便用戶可以成功的執(zhí)行該DDL語(yǔ)句。當(dāng)需要的數(shù)據(jù)字典信息沒(méi)有在共享內(nèi)存中時(shí)，經(jīng)常會(huì)發(fā)生Recursive calls，這些Recursive calls會(huì)將數(shù)據(jù)字典信息從硬盤(pán)讀入內(nèi)存中。用戶不比關(guān)心這些recursive SQL語(yǔ)句的執(zhí)行情況，在需要的時(shí)候，ORACLE會(huì)自動(dòng)的在內(nèi)部執(zhí)行這些語(yǔ)句。當(dāng)然DML語(yǔ)句與SELECT都可能引起recursive SQL。簡(jiǎn)單的說(shuō)，我們可以將觸發(fā)器視為recursive SQL。 Row Source(行源) 用在查詢中，由上一操作返回的符合條件的行的集合，即可以是表的全部行數(shù)據(jù)的集合；也可以是表的部分行數(shù)據(jù)的集合；也可以為對(duì)上2個(gè)row source進(jìn)行連接操作(如join連接)后得到的行數(shù)據(jù)集合。 Predicate(謂詞) 一個(gè)查詢中的WHERE限制條件 Driving Table(驅(qū)動(dòng)表) 該表又稱為外層表(OUTER TABLE)。這個(gè)概念用于嵌套與HASH連接中。如果該row source返回較多的行數(shù)據(jù)，則對(duì)所有的后續(xù)操作有負(fù)面影響。注意此處雖然翻譯為驅(qū)動(dòng)表，但實(shí)際上翻譯為驅(qū)動(dòng)行源(driving row source)更為確切。一般說(shuō)來(lái)，是應(yīng)用查詢的限制條件后，返回較少行源的表作為驅(qū)動(dòng)表，所以如果一個(gè)大表在WHERE條件有有限制條件(如等值限制)，則該大表作為驅(qū)動(dòng)表也是合適的，所以并不是只有較小的表可以作為驅(qū)動(dòng)表，正確說(shuō)法應(yīng)該為應(yīng)用查詢的限制條件后，返回較少行源的表作為驅(qū)動(dòng)表。在執(zhí)行計(jì)劃中，應(yīng)該為靠上的那個(gè)row source，后面會(huì)給出具體說(shuō)明。在我們后面的描述中，一般將該表稱為連接操作的row source 1。 Probed Table(被探查表) 該表又稱為內(nèi)層表(INNER TABLE)。在我們從驅(qū)動(dòng)表中得到具體一行的數(shù)據(jù)后，在該表中尋找符合連接條件的行。所以該表應(yīng)當(dāng)為大表(實(shí)際上應(yīng)該為返回較大row source的表)且相應(yīng)的列上應(yīng)該有索引。在我們后面的描述中，一般將該表稱為連接操作的row source 2。 組合索引(concatenated index) 由多個(gè)列構(gòu)成的索引，如create index idx_emp on emp(col1, col2, col3, ……)，則我 19 們稱idx_emp索引為組合索引。在組合索引中有一個(gè)重要的概念：引導(dǎo)列(leading column)，在上面的例子中，col1列為引導(dǎo)列。當(dāng)我們進(jìn)行查詢時(shí)可以使用”where col1 = ? ”，也可以使用”where col1 = ? and col2 = ?”，這樣的限制條件都會(huì)使用索引，但是”where col2 = ? ”查詢就不會(huì)使用該索引。所以限制條件中包含先導(dǎo)列時(shí)，該限制條件才會(huì)使用該組合索引。 可選擇性(selectivity)： 比較一下列中唯一鍵的數(shù)量和表中的行數(shù)，就可以判斷該列的可選擇性。如果該列的”唯一鍵的數(shù)量/表中的行數(shù)”的比值越接近1，則該列的可選擇性越高，該列就越適合創(chuàng)建索引，同樣索引的可選擇性也越高。在可選擇性高的列上進(jìn)行查詢時(shí)，返回的數(shù)據(jù)就較少，比較適合使用索引查詢。 有了這些背景知識(shí)后就開(kāi)始介紹執(zhí)行計(jì)劃。為了執(zhí)行語(yǔ)句，Oracle可能必須實(shí)現(xiàn)許多步驟。這些步驟中的每一步可能是從數(shù)據(jù)庫(kù)中物理檢索數(shù)據(jù)行，或者用某種方法準(zhǔn)備數(shù)據(jù)行，供發(fā)出語(yǔ)句的用戶使用。Oracle用來(lái)執(zhí)行語(yǔ)句的這些步驟的組合被稱之為執(zhí)行計(jì)劃。執(zhí)行計(jì)劃是SQL優(yōu)化中最為復(fù)雜也是最為關(guān)鍵的部分，只有知道了ORACLE在內(nèi)部到底是如何執(zhí)行該SQL語(yǔ)句后，我們才能知道優(yōu)化器選擇的執(zhí)行計(jì)劃是否為最優(yōu)的。執(zhí)行計(jì)劃對(duì)于DBA來(lái)說(shuō)，就象財(cái)務(wù)報(bào)表對(duì)于財(cái)務(wù)人員一樣重要。所以我們面臨的問(wèn)題主要是：如何得到執(zhí)行計(jì)劃；如何分析執(zhí)行計(jì)劃，從而找出影響性能的主要問(wèn)題。下面先從分析樹(shù)型執(zhí)行計(jì)劃開(kāi)始介紹，然后介紹如何得到執(zhí)行計(jì)劃，再介紹如何分析執(zhí)行計(jì)劃。 舉例： 這個(gè)例子顯示關(guān)于下面SQL語(yǔ)句的執(zhí)行計(jì)劃。 SELECT ename, job, sal, dname FROM emp, dept WHERE = AND NOT EXISTS ( SELECT * FROM salgrade WHERE BETWEEN losal AND hisa