ORACLE性能優(yōu)化之SQL優(yōu)化-優(yōu)化器

1、Oracle9i優(yōu)化器介紹By DavisE-Mail: todavisBlog:davis.blogchi 選擇合適的優(yōu)化器目標(biāo)默認(rèn)情況下，CB0以最佳吞吐量為目標(biāo)，這意味著Oracle使用盡可能少的資源去處理被語句訪問到的所有行；當(dāng)然CBO也可以用最快的響應(yīng)速度來優(yōu)化SQL，這意味著 Oracle用盡可能少的資源去處理被語句訪問到的第一行或前面少數(shù)行，當(dāng)然這種情況對于整個語句 來說可能消耗更多的資源。優(yōu)化器產(chǎn)生的執(zhí)行計劃會因 優(yōu)化器目標(biāo)I的不同而不同。如果以最佳吞吐量為目標(biāo)， 結(jié)果更傾向于使用全表掃描而不是索引掃描，或者使用排序合并連接而不是嵌套循環(huán)連接； 如果以最快的響應(yīng)速度為目標(biāo)，其結(jié)

2、果則通常傾向于使用索引掃描和嵌套循環(huán)連接。例如，假使你有一個語句既能運行于嵌套循環(huán)連接又能運行于排序合并連接，排序合并 連接能夠較快的返回全部查詢結(jié)果，而嵌套循環(huán)能快速的返回第一行或前面少數(shù)行結(jié)果。如 果你是以提高吞吐量為優(yōu)化器目標(biāo)，優(yōu)化器就會傾向于選擇排序合并連接；如果你的優(yōu)化器 目標(biāo)是提高響應(yīng)速度，則優(yōu)化器傾向于選擇嵌套循環(huán)連接。選擇優(yōu)化器目標(biāo)要以你的應(yīng)用為基礎(chǔ)，一般規(guī)則是：1、 對于批處理應(yīng)用，以最佳吞吐量為優(yōu)化目標(biāo)為好。例如Oracle報表應(yīng)用程序。2、 對于交互式應(yīng)用，以最快響應(yīng)速度為優(yōu)化目標(biāo)為好。例如SQLPLUS的查詢。 影響優(yōu)化器優(yōu)化目標(biāo)的因素主要有：1、OPTIMIZER_M

3、ODE 初始化參數(shù)。2、數(shù)據(jù)字典中的 CBO統(tǒng)計數(shù)據(jù)。3、用來改變 CBO優(yōu)化目標(biāo)的 Hints。OPTIMIZER_MODE 初始化參數(shù)這個初始化參數(shù)用來規(guī)定實例的默認(rèn)優(yōu)化方法。其值列表及說明如下：ValueDescriptionCHOOSE此為缺省值。優(yōu)化器既可以使用基于成本的優(yōu)化方法(CBO)，也可以使用基于規(guī)則的優(yōu)化方法(RBO)，其決定于是否有可用的統(tǒng)計信息。1、如果在被訪問的表中，至少有一個表在數(shù)據(jù)字典中有可用的統(tǒng)計 信息存在，則優(yōu)化器使用基于成本的方法。2、如果在被訪問的表中，只有部分表在數(shù)據(jù)字典中有可用的統(tǒng)計信息，優(yōu)化器仍然會使用基于成本的方法，但是優(yōu)化器必須為無統(tǒng)計信息的表利

4、用一些內(nèi)部信息去嘗試其他的統(tǒng)計，比如分配給這些表的數(shù)據(jù)塊的數(shù)量等，這可能會導(dǎo)致產(chǎn)生不理想的執(zhí)行計劃。3、如果在被訪問的表中，沒有一個表在數(shù)據(jù)字典中有統(tǒng)計信息，則 優(yōu)化器使用基于規(guī)則的方法。ALL_ROWS不論是否有統(tǒng)計信息存在，優(yōu)化器都使用基于成本的方法，并以最佳吞1吐量為優(yōu)化日標(biāo)。FIRST_ROWS_ n不論是否有統(tǒng)計信息存在，優(yōu)化器都使用基于成本的方法，并以最快的 速度返回前n行數(shù)據(jù)集，n可以是1,10,100,1000。FIRST_ROWS優(yōu)化器使用成本與試探法混合的方式，去尋找一個可以最快返回前面少 數(shù)行的執(zhí)行計劃。注：CBO使用試探法產(chǎn)生的執(zhí)行計劃，其成本可能會比不使用試探法要 大

5、。FIRST ROWS可用于向后兼容和計劃穩(wěn)定性。RULE不論是否有統(tǒng)計信息存在，優(yōu)化器都會使用基于規(guī)則的方法。你可以在 SESSION 中改變 CBO 優(yōu)化目標(biāo)：ALTER SESSION SET OPTIMIZER_MODE 。 例如：1在初始化參數(shù)文件中加入如下語句，可以在實例級改變CBO優(yōu)化目標(biāo)：OPTIMIZER_MODE=FIRST_ROWS_12、下面的語句可以改變當(dāng)前 SESSION的CBO優(yōu)化目標(biāo)：ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS_1可以改變CBO優(yōu)化目標(biāo)的Hints使用如下Hints可以單獨為具體的 SQL指定CBO優(yōu)化

6、目標(biāo)，SQL語句中Hints能夠覆 蓋OPTIMIZER_MODE 初始化參數(shù)。FIRST_ROWS(n)，n為任意正整數(shù)。FIRST_ROWSALL_ROWSCHOOSERULE數(shù)據(jù)字典中的CBO統(tǒng)計信息CBO使用的統(tǒng)計信息存放于數(shù)據(jù)字典中，你可以使用DBMS_STATS包或ANALYZE語句以精確的方式或估算的方式來統(tǒng)計對象的物理存儲特征和數(shù)據(jù)分布情況。Oracle公司建議使用DBMS_STATS包來代替 ANALYZE 語句收集統(tǒng)計信息。DBMS_STATS包可以并行的收集統(tǒng)計信息，可以為分區(qū)對象收集全局統(tǒng)計信息，以及使用 其他方式優(yōu)化收集操作。但是，收集和基于成本優(yōu)化器無關(guān)的信息必須用

7、ANALYZE 而不是DBMS_STATS，比如：使用 VALIDATE 或 LIST CHAINED ROWS 子句。收集freelist塊的信息。CBO 如何對SQL做最快響應(yīng)的優(yōu)化OPTIMIZER_MODE 被設(shè)置成 FIRST_ROWS_n 、 FIRST_ROWSS ，或者 SQL 語句中使 用了 FIRST_ROWS(n) 、FIRST_ROWS 提示， CBO 都會對 SQL 做最快響應(yīng)的優(yōu)化。這非常適用于聯(lián)機用戶，像通過 Oracle Forms 或 Web 訪問的用戶。聯(lián)機用戶的特點是 只對前面少數(shù)行感興趣，很少看整個查詢的結(jié)果，特別是在查詢結(jié)果巨大的情況下。對于這 樣的用

8、戶，優(yōu)化 SQL 使前面少數(shù)行盡可能快速的返回是有意義的，即使產(chǎn)生整個查詢結(jié)果 的時間并不理想。CBO 在做這種優(yōu)化時，會產(chǎn)生一個處理第一行或前面少數(shù)行消耗成本最低的執(zhí)行計劃。 CBO 有兩種用來產(chǎn)生最快響應(yīng)速度的方法，一個是舊方法一個是新方法。舊的方法就是用 FIRST_ROWS 提示或初始化參數(shù)，這種方法 CBO 會使用成本和規(guī)則混合的方式來產(chǎn)生一個 計劃。 Oracle 保留這種方法是為了向后兼容。新方法 FIRST_ROWS_n 或 FIRST_ROWS(n) 提示，是完全基于成本的。如果 n 值較小， CBO 傾向于產(chǎn)生一個包含嵌套循環(huán)連接和索引查詢的執(zhí)行計劃；如果 n 值較大，則

9、CBO 傾 向于產(chǎn)生一個包含散列連接和全表掃描的執(zhí)行計劃。理解基于成本的優(yōu)化器CBO 根據(jù)可用的訪問路徑和表、索引等對象的統(tǒng)計信息來確定當(dāng)前 SQL 的哪個執(zhí)行計 劃是最高效的或成本最低的；同時 CBO 也會考慮 Hints 的建議。CBO 執(zhí)行下列步驟：1、優(yōu)化器根據(jù)可用的訪問路徑和Hints 為 SQL 語句產(chǎn)生一組潛在的執(zhí)行計劃。2、優(yōu)化器根據(jù)數(shù)據(jù)字典的統(tǒng)計信息評估每個計劃的成本。成本就是一個評估值，它與SQL語句按照某個計劃執(zhí)行所消耗的計算機資源是成正比的。優(yōu)化器基于對計算機資源 (I/O、CPU、內(nèi)存)的評估，計算訪問路徑和連接順序 的成本。3、優(yōu)化器對比執(zhí)行計劃的成本，從而選擇一個

10、成本最低的執(zhí)行計劃。CBO 包含下列組件： 查詢變換器( Query Transformer ) 評估器( Estimator) 計劃生成器( Plan Generator)如下圖所示：I Fa侶百計I (from Paiscr)J(基于成本的優(yōu)化器組件)查詢變換器被解析器解析過的查詢語句進(jìn)入查詢變換器，表現(xiàn)出來的是一組查詢塊(query這些查詢塊之間是相互關(guān)聯(lián)的或者是嵌套的，查詢的形式?jīng)Q定這些查詢塊相互之間如何被關(guān) 聯(lián)。查詢變換器的主要目的就是決定改變查詢的形式是否有利于產(chǎn)生一個好的執(zhí)行計劃。查 詢變換器使用四種不同的查詢變換技術(shù)：視圖合并(View Merging)謂詞推進(jìn)(Predica

11、te Pushing)非嵌套子查詢(Subquery Unnesting) 物化視圖的查詢重寫(Query Rewrite with Materialized Views )最終應(yīng)用于查詢的也可以是以上四種變換技術(shù)的任意組合。視圖合并查詢中的每個視圖都會被解析器擴展到一個獨立的查詢塊中，這個查詢塊本質(zhì)上是用來 描述視圖定義的，是視圖的結(jié)果。優(yōu)化器的一個任務(wù)就是去分析這個獨立視圖查詢塊 query block)并產(chǎn)生一個視圖子計劃 (subplan)，然后優(yōu)化器在產(chǎn)生整個查詢執(zhí)行計劃的同時使 用視圖子計劃來處理剩余的查詢部分。由于視圖是被獨立在整個查詢之外被優(yōu)化的，因此這 種技術(shù)常常會導(dǎo)致一個不

12、良執(zhí)行計劃的產(chǎn)生。查詢變換器通過將視圖查詢塊合并到查詢塊中從而消除這種不良執(zhí)行計劃。絕大多數(shù)類 型的視圖是可以被合并的。在一個視圖被合并后，它原有的視圖查詢塊被包含到查詢塊中， 也就是說視圖查詢塊不存在了，因此也不再需要產(chǎn)生一個子計劃。block)，(view謂詞推進(jìn)4對于那些不能合并的視圖，查詢變換器能夠?qū)⑾嚓P(guān)的謂詞從查詢塊中推進(jìn)到視圖查詢塊 中。由于被推進(jìn)的謂詞能夠用來訪問索引或者用于過濾，這個技術(shù)通?？梢愿倪M(jìn)那些不能被 合并的視圖子計劃。非嵌套的子查詢和視圖一樣，子查詢也是用一個獨立的查詢塊來代表的。子查詢是被嵌套在主查詢或其 他子查詢之中的，計劃產(chǎn)生器在找到一個成本最低的執(zhí)行計劃之前被

13、迫要試驗所有可能的計 劃。由嵌套子查詢產(chǎn)生的限制可以在轉(zhuǎn)換為非嵌套的子查詢和連接之后消除，經(jīng)由查詢轉(zhuǎn)換 器過濾之后絕大多數(shù)的子查詢都會被轉(zhuǎn)換為非嵌套的，然后這些非嵌套的子查詢產(chǎn)生獨立的 子計劃，這些子計劃按照一種高效的方式進(jìn)行排列，從而提高了整個查詢計劃的執(zhí)行速度。物化視圖的查詢重寫物化視圖就是把一個查詢的結(jié)果事先固化存儲在一個表里，當(dāng)發(fā)現(xiàn)和物化視圖一致的查 詢語句就將相應(yīng)的項用物化視圖來重寫。由于絕大多數(shù)的查詢結(jié)果都事先計算好了，因此這 種技術(shù)可以極大的提高查詢速度。查詢轉(zhuǎn)換器負(fù)責(zé)查找和用戶查詢相關(guān)的所有物化視圖，用 其中的一個或多個來重寫查詢。利用物化視圖來重寫查詢也是基于成本的，如果不使

14、用物化 視圖的成本更低一些，則不會去使用物化視圖。評估器評估器會產(chǎn)生下列三個度量值： 選擇性( Selectivity ) 基數(shù)( Cardinality ) 成本( Cost) 這些值是相互關(guān)聯(lián)的，一個值由其他值導(dǎo)出，評估器的最終目標(biāo)是評估計劃的總體成本。 如果有統(tǒng)計信息可用，評估器使用統(tǒng)計信息來計算這些值，統(tǒng)計信息可以提高其精確度。選擇性這里的第一個度量值 選擇性，表示所選擇的行與行集的比值。所謂行集可以是表、 視圖，或者是一個連接或 GROUP BY 操作的中間結(jié)果。選擇性與查詢中的謂詞有關(guān)，比如Iast_name= Smith或者一個聯(lián)合謂詞 last_name= Smith and

15、job_type=。一個謂詞充當(dāng)著一個過濾器的角色，在行集中過濾了一定量的行，謂詞的選擇性是一個比值，它表示一 個行集經(jīng)過謂詞的過濾后剩下的行占原有行集的比例。其值在 0.0 和 1.0 之間， 0.0 表示在行 集中沒有行被選擇； 1.0 表示行集中的所有行都被選擇了。如果沒有可用的統(tǒng)計信息，評估器為選擇性賦予一個內(nèi)部的缺省值，這個內(nèi)部缺省值隨著謂詞的不同而不同。例如：等式謂詞(last_name= Smith的內(nèi)部缺省值低于范圍謂詞(last_name Smith ；評估器會假定等式謂詞返回的行數(shù)小于范圍謂詞。當(dāng)存在可用的統(tǒng)計信息，評估器將使用統(tǒng)計信息來估算選擇性。例如：對于一個等式謂詞

16、(last_name= Smith)', 選擇性的值是distinet last_name 的倒數(shù)即：( 1/count(distinctlast_name)。但是如果在 last_name字段上存在直方圖(histogram)，則選擇性值為：count(Iast_name) where last_name= Smith / coUntst_name) where last_name is notnull?？梢娫跀?shù)據(jù)傾斜的字段上應(yīng)用直方圖能夠幫助 CBO 進(jìn)行準(zhǔn)確的選擇性評估?；鶖?shù)基數(shù)就是行集中行的數(shù)量。基數(shù)分為：基礎(chǔ)基數(shù)( Base cardinality )：就是基表中的行數(shù)?；A(chǔ)

17、基數(shù)在表分析期間獲得。如 果表沒有可用的統(tǒng)計信息，則評估器利用表中區(qū)(exte nts)的數(shù)量來估算基礎(chǔ)基數(shù)。有效基數(shù)( Effective cardinality )：就是從基表中選擇的行數(shù)。有效基數(shù)與具體的謂 詞和字段有關(guān)。有效基數(shù)是根據(jù)基礎(chǔ)基數(shù)和作用于該表的所有謂詞的選擇性得出 的，如果沒有謂詞作用于該表，則有效基數(shù)就等于基礎(chǔ)基數(shù)。連接基數(shù)( Join cardinality )：就是兩個行集在連接之后產(chǎn)生的行數(shù)。連接就是由兩 個行集產(chǎn)生的笛卡爾積，再由連接謂詞過濾結(jié)果。因此，連接基數(shù)是兩個行集基數(shù) 與連接謂詞選擇性的乘積。Distinct 基數(shù)( Distinct cardinalit

18、y )：就是一個行集的字段 distinct 之后的行數(shù)。一個 行集的 distinct 基數(shù)是基于字段中的數(shù)據(jù)的。例如：一個擁有 100 行的行集，如果 一個字段 distinct 之后還剩下 20 行，則 distinct 基數(shù)就為 20。Group基數(shù)(Group cardinality ):就是一個行集在應(yīng)用GROUP BY之后產(chǎn)生行的數(shù)量。 Group 基數(shù)依賴于每個組中字段的 distinct 基數(shù)和行集的行數(shù)。GROUP 基數(shù)例子:假如對一個有 100 行的行集 group by colx， colx 字段的 distinct 基數(shù)是 30，則 Group 基 數(shù)為 30。但是如

19、果 group by colx,coly 呢？ coly 字段的 distinct 基數(shù)是 60，這種情況下 Group 基數(shù)大于 max(colx distinet 基數(shù)，coly distinct 基數(shù))，而小于 min(colx distinet 基數(shù) *coly distinct 基數(shù)，行集的行數(shù) )，用公式表示出來如下:group cardinality lies betweenmax ( dist. card. colx , dist. card. coly )andmin ( (dist. card. colx * dist. card. coly) , num rows in

20、row set )對于上面的例子 Group 基數(shù)大于 max(30， 60)而小于 min(30*60， 100)，也就是 Group 基數(shù)位于 60 和 100 之間。成本成本是用來描述工作單元或資源使用的。 CBO是用磁盤I/O、 CPU 和內(nèi)存的使用情況來作為工作單元的，因此 CBO 使用的成本可以描述為，在一次操作的執(zhí)行過程中所用的磁 盤 I/O 數(shù)量以及 CPU 和內(nèi)存的總使用量。這里的操作可以是掃描一張表、通過索引訪問表、 連接兩個表、或者一個行集的排序。一個查詢計劃的成本就是運行這個查詢并產(chǎn)生結(jié)果的同 時需要的工作單元的數(shù)量。訪問路徑(access path決定著在基表中獲得數(shù)

21、據(jù)所需要的工作單元數(shù)量。訪問路徑可以是表掃描(table scan)、快速全索引掃描(fast full index scan )、索引掃描(index scan)等。在表掃描或快速全索引掃描期間，多個塊可以在一次 I/O 中獲得，因此表掃描或快速全索引 掃描的成本依賴于被掃描的塊數(shù)和多塊讀取的數(shù)量。索引掃描的成本依賴于 B 樹的深度、 被掃描的索引頁塊數(shù)量、和用 ROWID 獲取的行數(shù)，使用 ROWID 獲取行的成本倚賴于索引 聚集因子( clustering factor )。盡管聚集因子是索引的一個屬性，它實際也關(guān)系到表數(shù)據(jù)塊中被索引的字段值。一較低 的聚集因子表明行被集中在表的少數(shù)塊里

22、，相反一個較高的聚集因子表明行被隨機分散到表 的數(shù)據(jù)塊中。因此，聚集因子過高意味著通過范圍掃描用 ROWID 獲取行成本會較高，因為 需要訪問表中過多的塊才能返回數(shù)據(jù)。聚集因子對成本的影響假設(shè)環(huán)境如下：一個表有 9 行數(shù)據(jù)。在 col1 上有一個非唯一索引distinct col1 值是 A 、B、C 這個表占據(jù)三個 Oracle 塊第一種情況：索引聚集因子低，如下圖：Block 1Block 2Block 3A A A B B B C C C索引字段相同的值都在同一個物理塊中，這種情況下做范圍掃描返回 col1=A 的所有的 行成本就很低，因為只需要在表中讀取一個塊就可以返回數(shù)據(jù)。第二種情況

23、：索引聚集因子高，如下圖：Block 1Block 2Block 3A B C A B C A B C 索引字段相同的值被分散存儲到表中的塊，這時要得到 col1=A 的行則要讀取三個塊。聯(lián)合單獨訪問兩個表的成本就是連接的成本，在一個連接中分為內(nèi)行集和外行集。 嵌套循環(huán)連接( nested loop join )：對于外行集中的每一行都要在內(nèi)行集尋找全部與 它匹配的行，然后連接。因此，在嵌套循環(huán)連接中外行集有多少行，內(nèi)行集就被訪 問多少次。成本計算公式如下：cost = outer access cost + (inner access cost * outer cardinality) 排序

24、合并連接( sort merge join )：如果兩個行集的連接鍵是無序的，則進(jìn)行排序。 成本計算公式如下：cost = outer access cost + inner access cost + sort costs (if sort is used) 散列連接( hash join )：內(nèi)部行集被散列到內(nèi)存中，并用連接鍵建立一個散列表， 然后探測外部行集并連接與之匹配的行。如果內(nèi)部行集非常大，則只會把一部分散 列到內(nèi)存中，這叫做一個散列分區(qū)。此時，內(nèi)存中的散列分區(qū)探測外部行集并連接 所有匹配的行，重復(fù)這個過程直到用完內(nèi)部行集的所有分區(qū)。成本計算公式如下： cost = (outer

25、access cost * num of hash partitions) + inner access cost計劃生成器由于不同的訪問路徑、連接方式和連接順序可以任意組合，以不同的方式訪問和處理數(shù) 據(jù)，但可以產(chǎn)生同樣的結(jié)果，因此一個SQL可能存在大量不同的計劃。計劃生成器的主要作用正是為查詢試驗出所有這些可能存在的計劃，并選擇一個其中成本最低的。連接順序就是不同的連接項(如，表)以一定的順序被訪問和連接在一起。例如：有一 個連接按照t1、t2、t3的順序，貝U t1是第一個被訪問的，然后是 t2，訪問t2的同時與t1做 連接并產(chǎn)生連接后的結(jié)果，最后t3被訪問，t3的數(shù)據(jù)與t1和t2產(chǎn)生的中

26、間結(jié)果做連接在建立一個查詢的計劃之前要先為每個被嵌套的子查詢和未合并的視圖建立子計劃，每 個嵌套的子查詢和未合并的視圖都是獨立的查詢塊，這些查詢塊以自底向上的順序進(jìn)行優(yōu) 化，也就是最里層的查詢塊最先優(yōu)化并產(chǎn)生子計劃，最外層的查詢塊最后優(yōu)化。計劃生成器通過試驗不同的訪問路徑、連接方式和連接順序去探測各種計劃，對于一個 查詢來說可能存在的計劃與FROM字句后面的連接項是成比例的，并以指數(shù)增長。然而實際上計劃生成器很少會試驗所有的可能存在的計劃，如果它發(fā)現(xiàn)當(dāng)前計劃的成本已經(jīng)很低 了，它將停止試驗，相反當(dāng)前計劃的成本如果很高它將繼續(xù)試驗其他計劃，因此如果計劃生 成器一開始就能夠找到一個成本較低的計劃則

27、會大量減少時間，計劃生成器通常按照連接項 有效基數(shù)由小到大的順序排列初使連接，。理解執(zhí)行計劃Oracle用來運行一個語句的步驟就叫做執(zhí)行計劃(executio n plan)，執(zhí)行計劃包含了語句所涉及的每個表的訪問路徑和連接順序。執(zhí)行計劃概述使用EXPLAIN PLAN語句可以查看優(yōu)化器所選擇的執(zhí)行計劃，下面看一個例子：1、創(chuàng)建PLAN_TABLE，用來存放執(zhí)行計劃的描述信息：connect hr/ your_password$ORACLE_HOME/RDBMS/ADMIN/UTLXPLAN.SQLTable created.當(dāng)然你也可以改變 PLAN_TABLE 的名字。Oracle公司建議

28、你在做完數(shù)據(jù)庫版本升級之后刪除PLAN_TABLE然后再重建，因為字段可能會有所變化，這可能會導(dǎo)致腳本失效或TKPROF失效。當(dāng)然你也可以改變 PLAN_TABLE 的名字。2、運行執(zhí)行計劃:EXPLAIN PLAN FORSELECT e.employee_id, j.job_title, e.salary, d.department_name FROM employees e, jobs j, departments d WHERE e.employee_id < 103AND e.job_id = j.job_idAND e.department_id = d.department

29、_id;用下面的語句可以指定 PLAN_TABLE 的名字：EXPLAIN PLANINTO my_plan_tableFORYOUR_SQL;3、顯示執(zhí)行計劃信息：這里可以用以下兩個腳本UTLXPLS.SQL -顯示計劃表信息，以串行的方式處理。UTLXPLP.SQL -顯示計劃表信息，以并行的方式處理。$ORACLE_HOME/rdbms/utlxplp.sql;|0 | SELECT STATEMENT| |3 | 189 | 10(10)| 1 |NESTED LOOPS| |3 | 189 | 10(10)| 2 |NESTED LOOPS| |3 | 141 | 7(15)|* 3

30、 |TABLE ACCESS FULL| EMPLOYEES| 3 | 60 |4 (25)| 4 |TABLE ACCESS BY INDEX ROWID| JOBS| 19 | 513 |2 (50)|* 5 |INDEX UNIQUE SCAN| JOB_ID_PK |1 | 6 |TABLE ACCESS BY INDEX ROWID | DEPARTMENTS| 27 | 432 |2 (50)|* 7 |INDEX UNIQUE SCAN| DEPT_ID_PK| 1 | | Id | Operation| Name| Rows | Bytes | Cost (%CPU)|Pred

31、icate Information (identified by operation id):3 - filter("E"."EMPLOYEE_ID"<103)5 - access("E"."JOB_ID"="J"."JOB_ID")7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")SQL Scratchpad 的圖形界面顯示：12S)SE

32、LECT STATEMENTRowe:_ K&rtes' 0.13S 4) =- = cost?NESTED LOOPSRows19 -KBYlesO.QSS u H <HR.EMPLOYEES9Rows: <KBvte 0.1857)Cost: 10NESTED LOOPS0-RKBytes； 0 1:27KByte EiOdSj Dost: 2HRJOBSW Cost 2HR.DEPARTMENTSTABLE ACCESS (8Y INDEX ROWL.9Row制TABLE ACCESS (FULL) TABLE ACCESS (BY INDEX BUWI.0O

33、QSt： 1HR ,DEPTJD_PKINDEX (UNIQUE SCANOost: 1HR.JOB_ID_PKINDEX (UNIQUE SCAN該圖中每個圖標(biāo)左上角的數(shù)字就是執(zhí)行的順序號，利用圖形工具很容易看出執(zhí)行計劃的 執(zhí)行順序。但是很多情況下我們沒用配置圖形工具的環(huán)境，而且圖形工具消耗資源，也不 太穩(wěn)定，很多人也不太喜歡用圖形工具。如果利用上面運行腳本的方法在sqlplus中獲得執(zhí)行計劃，對于新手來說不容易看出執(zhí)行的順序，通常我們用下面的方法來獲得執(zhí)行計劃：1、conn /as sysdba;2、$ORACLE_HOME/sqlplus/admin/plustrce.sql3、gran

34、t plustrace to public4、$ORACLE_HOME/rdbms/admin/utlxplan.sql5、create public synonym plan_table for plan_table;6、grant all on plan_table to public ;以上步驟只需配置一次即可。7、conn username/password;8、 set autotrace trace only （如果想看至 U結(jié)果集：set autotrace on, 關(guān)閉：set autotrace off）9、set timing on （如果想同時看到語句執(zhí)行的時間）10、運

35、行你的SQL 關(guān)于如何看執(zhí)行計劃的順序，請看下面的例子:set autotrace trace only select en ame,d namefrom emp, deptwhere emp.dept no=dept.dept noand dept.d name in ('ACCOUNTING','RESEARCH','SALES','OPERATIONS');15 rows selected.Execution PlanSELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=8 Bytes

36、=248)0 HASH JOIN (Cost=3 Card=8 Bytes=248)TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=3 Bytes=36)TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=16 Bytes=304)用這種方法產(chǎn)生的執(zhí)行計劃會有兩列數(shù)字，第一列是statement_id，第二列是child_id。我們可以從 statement_id=0 處開始讀，當(dāng)該句有子句則先執(zhí)行子句，該句就為其子句的父句； 如果該句有多個子句，則子句的執(zhí)行順序是從上到下；子句執(zhí)行完執(zhí)行父句

37、。在這個例子中，stateme nt_id=O有一個子句 stateme nt_id=1，因此在執(zhí)行 stateme nt_id=O 之前要執(zhí)行 statement_id=1，但 statement_id=1有兩個子句分別為statement_id=2和3,因此這兩句要最先執(zhí)行。此執(zhí)行計劃的執(zhí)行順序為：2 >3 >1 >0后面的 cost 說明該 SQL 是基于成本優(yōu)化的,如果沒有 cost 則是基于規(guī)則的。通過這個例子可以知道學(xué)會看執(zhí)行計劃也不是什么難事,關(guān)鍵是要理解執(zhí)行計劃,學(xué)會 如何優(yōu)化執(zhí)行計劃,下面將繼續(xù)討論。理解 CBO 訪問路徑訪問路徑就是從數(shù)據(jù)庫中檢索數(shù)據(jù)的方式

38、。通常來說,檢索一個表中少量的數(shù)據(jù)行應(yīng)該 使用索引訪問,但是檢索大量數(shù)據(jù)時全表掃描可能優(yōu)于索引。全表掃描( Full Table Scans)全表掃描將讀取 HWM 之下的所有數(shù)據(jù)塊,訪問表中的所有行,每一行都要經(jīng) WHERE 子句判斷是否滿足檢索條件。當(dāng)Oracle 執(zhí)行全表掃描時會按順序讀取每個塊且只讀一次,因此如果能夠一次讀取多個數(shù)據(jù)塊, 可以提高掃描效率, 初始化參數(shù) DB_FILE_MULTIBLOCK_READ_COUNT 用來設(shè)置在一次 I/O 中可以讀取數(shù)據(jù)塊的最大數(shù)優(yōu)化器何時會使用全表掃描在以下情況中優(yōu)化器會使用全表掃描：1、無可用索引 如下面例子：SELECT last_n

39、ame, first_nameFROM employeesWHERE UPPER(last_name)=TOMIast_ name字段有索引，但在查詢中使用了函數(shù)，因此該查詢不會使用索引。如果想讓這 個查 詢走 索引 ，則 需要 建立 函數(shù) 索引 create index ind_upper_lastname on last_name (upper(Iast_name) 。特別要注意的是隱式轉(zhuǎn)換，比如 coIx 字段是 varchar2 型但存放數(shù)字： where colx=123456，這時會發(fā)生隱式轉(zhuǎn)換TO_NUMBER(colx)，此時colx上的索引也會失效。2、大量數(shù)據(jù)如果優(yōu)化器認(rèn)為

40、查詢將會訪問表中絕大多數(shù)的數(shù)據(jù)塊，此時就算索引是可用的也會使用 全表掃描。3、小表如果一個表 HWM 之下的數(shù)據(jù)塊比 DB_FILE_MULTIBLOCK_READ_COUNT 要少，只 需要一次 I/O 就能掃完，則使用全表掃描要比使用索引的成本低，此時會使用全表掃描。如果有這樣小表訪問頻率又高，通常把它固定在內(nèi)存中為好alter table table_namestorage(buffer_pool keep)。4、并行如果在表一級設(shè)置了較高的并行度，如 alter table table_name parallel(degree 10)，通常會 使 CBO 錯誤的選擇全表掃描。通常不建議

41、在表級的設(shè)置并行。并行查詢通?？梢蕴岣呷頀呙璧男阅?，建議在語句級用 HINTS 來實現(xiàn)并行，如 /*+full(table_name) parallel(table_name degree)*/ 。5、全表掃描 hints 如果想強制優(yōu)化器使用全表掃描可以用提示 FULL 。I/O 是針對數(shù)據(jù)塊的而不是行Oracle 的 I/O 是針對數(shù)據(jù)塊的，因此被訪問的數(shù)據(jù)塊所占的百分比將影響 CBO 是否選 擇全表掃描。通常一個數(shù)據(jù)塊中存儲著多條記錄，被請求的記錄要么聚集在少數(shù)幾個塊中， 要么分散在大量的數(shù)據(jù)塊中。HWM(High Water Mark)HWM 是全表掃描范圍的標(biāo)記，每個全表掃描都要讀

42、到 HWM 位置。當(dāng)表 analyze 之后 可以在 DBA_TABLES.BLOCKS 查到 HWM ，當(dāng)表被 drop、 truncate 或者 move 之后， HWM 將會被重置。需要注意的是，當(dāng)一個表被大量刪除記錄之后，HWM 下面的大量數(shù)據(jù)塊是空的，此時若對此表進(jìn)行全表掃描， Oracle 仍然會讀到 HWM 位置，會對全表掃描的性能產(chǎn)生 極壞的影響。Rowid 掃描Rowid 就是一個記錄在數(shù)據(jù)塊中的位置，由于指定了記錄在數(shù)據(jù)庫中的精確位置，因此 rowid 是檢索單條記錄的最快方式。如果通過 rowid 來訪問表， Oracle 首先需要獲得被檢索記錄的rowid， Oracl

43、e 可以在WHERE 子句中得到 rowid ，但更多的是通過索引掃描來獲得，然后 Oracle 基于 rowid 來定位被檢索的每條記錄。優(yōu)化器何時使用 Rowid并不是每個索引掃描都伴隨著rowid的訪問，如果索引中包含了被訪問的所有字段，則不再需要通過rowid來訪問表。注意:Rowid是Oracle表示數(shù)據(jù)存儲的內(nèi)部方法，它可能會由于版本的改變而改變。不推薦通過在WHERE中指定rowid來訪問數(shù)據(jù)，因為行遷移和行鏈接會導(dǎo)致rowid變化，exp/imp也會使rowid變化。索引掃描索引不僅包含被索引字段的值，還包含表中行的位置標(biāo)識rowid，如果語句只檢索索引字段，Oracle直接從

44、索引中讀取該值而不去訪問表，如果語句通過索引檢索其他字段值，則 Oracle通過rowid訪問表中記錄。索引掃描類型：索引唯一掃描(Index Unique Scans) 索引范圍掃描(Index Range Scans 索引降序范圍掃描(In dex Range Scans Desce ndi ng) 索引跳躍掃描(Index Skip Scans) 全索引掃描(Full Scans)快速全索引掃描(Fast Full Index Scans)索引連接(In dex Joi ns) 位圖連接(Bitmap Joi ns)1、索引唯一掃描這種掃描通常發(fā)生在對一個主鍵字段或含有唯一約束的字段指定

45、相等條件時，只有單行 記錄被訪問。2、索引范圍掃描索引范圍掃描是檢索數(shù)據(jù)的常用方式，返回的數(shù)據(jù)返照索引字段升序排列，字段值相同 的則按照rowid升序排列。如果在語句中指定了order by字句，而且排序字段是索引字段時Oracle將忽略order by子句。例如：SQL> select * from t;COLXCOLY11141 0 SQL> create index ind_t on t(coly);SQL> set autotrace on SQL> select * from t where coly>0;COLX COLY111213Execution

46、 Plan0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T'2 1INDEX (RANGE SCAN) OF 'IND_T' (NON-UNIQUE)沒有使用 order by 結(jié)果集已經(jīng)是按 coly 升序排列的。SQL> set autotrace traceonlySQL> select * from t where coly>0 order by coly;Execution PlanSELECT STATEMENT Optimizer=

47、CHOOSE0 TABLE ACCESS (BY INDEX ROWID) OF 'T'可以看到執(zhí)行計劃中無INDEX ( RANGE SCAN ) OF 'IND_T' (NON-UNIQUE)SORT 步驟，說明 Oracle 忽略了 order by 子句3、索引降序范圍掃描如果在 order by 中指定了索引是降序排列的，或者使用了index_desc 提示， Oracle 可能會使用索引降序范圍掃描。例如：SQL> select /*+index_desc(t ind_t)*/colx,coly from t where coly<3;C

48、OLX COLYExecution Plan0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=4 Bytes=104)1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=4 Bytes=104)2 1 INDEX (RANGE SCAN DESCENDING ) OF 'IND_T' (NON-UNIQUE) (Cost=2 Card=1) 4、索引跳躍式掃描跳躍式掃描發(fā)生在復(fù)合索引中，它在邏輯上將索引分離為較小的子索引，當(dāng)復(fù)合索引的in dex_ss某一個字

49、段不在查詢中指定時，它將被跳過，從而提高索引掃描的效率?？梢允褂?提示強制使用跳躍掃描。舉個例子：SQL> select* from employees;SEXEMPLOYEE。ADDRESS16F98ABCF100ABCF102ABCF104ABCM101ABCM103ABCM105ABCSQL> create in dex in d_sex_empid on employees(sex,employee_id); 索引結(jié)構(gòu)如下圖所示：Lev&l 1Lev&l 2SQL>set autotrace trace onlySQL>select/*+in d

50、ex_ss(employees in d_sex_empid)*/* from employees where employee_id=101;Executi on Pla n0SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=11)(Cost=3(Cost=21 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' Card=1Bytes=11)2 1INDEX (SKIP SCAN) OF 'IND_SEX_EMPID' (NON-UNIQUE)Card=1)

51、5、全索引掃描如果要使用全索引掃描必須滿足兩個條件，一是查詢涉及的字段都包含在索引中，二是 至少一個索引字段具有非空屬性。由于索引鍵的數(shù)據(jù)是有序的，因此全索引掃描可以用消除 排序操作。全索引掃描只需要一次 I/O。select emp no,en ame from big_emp order by emp no,en ame;Executi on Pla n0 SELECT STATEMENTOptimizer=CHOOSE (Cost=3 Card=1 Bytes=11)10 INDEX (FULL SCAN) OF ?BE_IX (Cost=2 Card=1)6、快速全索引掃描 快速全索引

52、掃描只訪問索引本身，而不去訪問表，因此只有查詢涉及的字段都包含在索 引中時才會使用快速全索引掃描。如果想使用快速全索引掃描查詢所涉及的字段必須全部包 含在索引中，而且索引中至少有一個字段具有非空屬性。滿足條件后可以使用 index_ffs 提 示來強制使用快速全索引掃描，快速全索引掃描只適用于CBO?？焖偃饕龗呙璨⒉荒芟判虿僮?，因為索引鍵中的數(shù)據(jù)沒有被排序。不同于全索引 掃描，快速全索引掃描是通過多塊讀取的方式來讀取整個索引的，并可以設(shè)置并行方式。7、索引連接 只有查詢涉及的所有字段都包含在索引中，才會使用索引連接，此時只通過訪問索引就 能獲得所有需要的數(shù)據(jù)，而不用訪問表。索引連接只適用

53、于CBO ，且不能消除排序操作?？梢酝ㄟ^ index_join 提示來強制使用索引連接。8、位圖連接 位圖連接使用一個位圖作為鍵，然后通過映射函數(shù)將比特位轉(zhuǎn)換為rowid 。只有 Oracle9i企業(yè)版才支持位圖索引和位圖索引連接。Sample Table ScansSample table scan是隨機檢索表中的數(shù)據(jù)，當(dāng) FROM 后面有 SAMPLE或SAMPLEBLOCK 子句時，會執(zhí)行 Sample table scan。如： SELECT * FROM employees SAMPLE BLOCK (1);CBO 如何選擇訪問路徑CBO 首先檢查 WHERE 子句中的條件以及 FR

54、OM 子句，確定有哪些訪問路徑是可用的。 然后 CBO 使用這個訪問路徑產(chǎn)生一組可能的執(zhí)行計劃，再通過索引、表的統(tǒng)計信息評估每 個計劃的成本，最后優(yōu)化器選擇成本最低的一個。例 1 ：SELECT *FROM employeesWHERE last_name = 'JACKSON'如果 last_name 具有唯一約束或者主鍵約束，優(yōu)化器了解到只有一行數(shù)據(jù)被返回，這種 情況下查詢具有很強的選擇性，優(yōu)化很可能走唯一索引掃描。例 2 ：還是 上 面 的語句 ， 如 果 last_name 不具 有唯 一 約束或主 鍵 約束，優(yōu)化 器使用USER_TAB_COLUMNS.NUM_DIS

55、TINCT 和 USER_TABLES.NUM_ROWS 的統(tǒng)計信息來評 估查詢的選擇性，估算 last_name 為 jackson 的記錄占了 employees 表的比例。例 3 ：SELECT *FROM employeesWHERE employee_id < 7500;評估這個查詢的選擇性時優(yōu)化器使用 WHERE 子句中的邊界值 7500 和 employee_id 字 段的 USER_TAB_COLUMNS.HIGH_VALUE、 USER_TAB_COLUMNS.LOW_VALUE，優(yōu)化器假定在最小值和最大值之間 employee_id 是平均分布的，優(yōu)化器確定值小于 7

56、500 的百分 比，然后把這個值作為這個查詢的選擇性。例 4 ： SELECT *FROM employeesWHERE employee_id < :e1; 優(yōu)化器并不知道 e1 的值，綁定變量的值每次運行都可能不同，因此優(yōu)化器不能使用前 面的方法來評估含有綁定變量的查詢的選擇性，在這種情況下優(yōu)化器會使用內(nèi)部缺省值試探 著估算一個選擇性。例 5 ： SELECT *FROM employeesWHERE employee_id BETWEEN :low_e AND :high_e; 優(yōu)化器會將這句改寫為： employee_id >= :low_e employee_id <

57、;= :high_e 然后優(yōu)化器仍然是用內(nèi)部缺省值來試探著為其評估一個選擇性。例 6 ： SELECT *FROM employeesWHERE employee_id BETWEEN 7500 AND 7800; 優(yōu)化器會改寫為： employee_id >= 7500 employee_id <= 7800優(yōu)化器為每個條件獨立的評估選擇性（S1和S2）,然后用下列公式計算BETWEEN 的選擇性： S=ABS（ S1+S2-1）理解連接CBO 如何運行連接語句為一個連接語句選擇一個執(zhí)行計劃,優(yōu)化器必須做出下列相關(guān)決策：1、訪問路徑 優(yōu)化器必須給連接語句中的每個表選擇一個可用來檢索數(shù)據(jù)的路徑。2、連接方法Oracle 必須為每對行源執(zhí)行連接操作，連接的