Oracle程序員面試分類模擬15

Oracle程序員面試分類模擬15簡答題1.

SQL如何優(yōu)化?SQL優(yōu)化關注點有哪些?正確答案：隨著數(shù)據(jù)庫中數(shù)據(jù)量的增長，系統(tǒng)的響應速度就成為目前系統(tǒng)需要解決的最主要的問題之一。系統(tǒng)優(yōu)化中一個很重（江南博哥）要的方面就是SQL語句的優(yōu)化。對于大量數(shù)據(jù)，劣質SQL語句和優(yōu)質SQL語句之間的速度差別可以達到上千倍。對于一個系統(tǒng)不是簡單地能實現(xiàn)其功能就可以了，而是要寫出高質量的SQL語句，提高系統(tǒng)的可用性。

在多數(shù)情況下，Oracle使用索引來更快地遍歷表，優(yōu)化器主要根據(jù)定義的索引來提高性能。如果在SQL語句的WHERE子句中寫的SQL條件不合理，那么就會造成優(yōu)化器舍去索引而使用全表掃描，一般這種SQL語句的性能都是非常差的。在編寫SQL語句時，應清楚優(yōu)化器根據(jù)何種原則來使用索引，這有助于寫出高性能的SQL語句。

SQL的優(yōu)化主要涉及如下幾個方面的內容：

1)索引問題。是否可以使用組合索引；限制條件、連接條件的列是否有索引；能否使用到索引避免全表掃描。一般情況下，盡量使用索引，因為索引在很多情況下可以提高查詢效率。排序字段有正確的索引，驅動表的限制條件有索引，被驅動表的連接條件有索引。

2)相關的統(tǒng)計信息缺失或者不準確。在確保統(tǒng)計信息正確后，再結合統(tǒng)計信息查看執(zhí)行計劃是否正確以及是否最優(yōu)化。

3)直方圖使用錯誤。

4)SQL本身的效率問題，例如使用綁定變量，批量DML采用BULK等，這個就考驗寫SQL的基本功了。

5)數(shù)據(jù)量大小。如果就是幾百條數(shù)據(jù)，那么就沒有所謂效率之分，一般情況下怎么寫效率都不低。如果數(shù)據(jù)量很大，那么就得考慮是否要分頁或排序。

6)綁定變量：大多數(shù)情況綁定變量能提高查詢效率，但也有降低效率的情況。

7)批量和并行的考慮。

8)業(yè)務需求需要正確理解，實現(xiàn)業(yè)務的邏輯需要正確，減少一些重復計算。有可能是設計的不合理、業(yè)務需求的不合理，而問題SQL并非根本原因。

9)查詢特別頻繁的結果是否可以緩存，比如Oracle的/*+result_cache*/。

10)分析表的連接方式。若是NL連接，則根據(jù)業(yè)務或表的數(shù)據(jù)質量情況，分析能否減少驅動表的結果集。

11)是否可以固定執(zhí)行計劃。

12)大表是否存在高水位。

13)在創(chuàng)建表的時候，應盡量建立主鍵，盡量根據(jù)實際需要調整數(shù)據(jù)表的PCTFREE和PCTUSED參數(shù)。

2.

SQL優(yōu)化在寫法上有哪些常用的方法?正確答案：一般在書寫SQL時需要注意哪些問題，如何書寫可以提高查詢的效率呢?可以從以下幾個方面去考慮：

1)減少對數(shù)據(jù)庫的訪問次數(shù)。當執(zhí)行每條SQL語句時，Oracle在內部執(zhí)行了許多工作：解析SQL語句，估算索引的利用率，綁定變量，讀數(shù)據(jù)塊等。由此可見，減少訪問數(shù)據(jù)庫的次數(shù)，就能實際上減少Oracle的工作量。充分利用表索引，避免進行全表掃描；充分利用共享緩存機制，提高SQL工作效率；充分利用結構化編程方式，提高查詢的復用能力。常用的方法為把對數(shù)據(jù)庫的操作寫成存儲過程，然后應用程序通過調用存儲過程，而不是直接使用SQL。

2)減少對大表的掃描次數(shù)?？梢岳肳ITH對SQL中多次掃描的表來進行修改。采用各種手段來避免全表掃描。

3)SELECT子句中避免使用“*’，應該寫出需要查詢的字段。當想在SELECT子句中列出所有的列時，可以使用“*”來返回所有的列，但這是一個非常低效的方法。實際上，Oracle在解析的過程中，會將“*”依次轉換成所有的列名，這個工作是通過查詢數(shù)據(jù)字典完成的，這意味著將耗費更多的時間。不需要的字段盡量少查，多查的字段可能有行遷移或行鏈接(timesten還有行外存儲問題)。少查LOB類型的字段可以減少I/O。

4)盡量使用表的別名(ALIAS)。當在SQL語句中連接多個表時，請使用表的別名，并把別名前綴于每個列上。此時就可以減少解析的時間并減少那些由列歧義引起的語法錯誤。

5)對于數(shù)據(jù)量較少又有主鍵索引的情況，可以考慮將關聯(lián)子查詢或外連接的SQL修改為標量子查詢。

6)避免隱式類型轉換(ImplicitTypeConversion)。如果進行比較的兩個值的數(shù)據(jù)類型不同，那么Oracle必須將其中一個值進行類型轉換使其能夠比較。這就是所謂的隱式類型轉換。通常當開發(fā)人員將數(shù)字存儲在字符列時會導致這種問題的產(chǎn)生。Oracle在運行時會在索引字符列使用TO_NUMBER函數(shù)強制轉化字符類型為數(shù)值類型。由于添加函數(shù)到索引列所以導致索引不被使用。實際上，Oracle也只能這么做，類型轉換是一個應用程序設計因素。由于轉換是在每行都進行的，這會導致性能問題。一般情況下，當比較不同數(shù)據(jù)類型的數(shù)據(jù)時，Oracle自動地從復雜向簡單的數(shù)據(jù)類型轉換。所以，字符類型的字段值應該加上引號。例如，假設USER_NO是一個字符類型的索引列，則：

這個語句在執(zhí)行的時候被Oracle在內部自動的轉換為：

因為內部發(fā)生的類型轉換，這個索引將不會被使用，所以正確的寫法應該是：

7)避免使用耗費資源的操作，包括DISTINCT、UNION、MINUS、INTERSECT、ORDERBY、GROUPBY等。能用DISTINCT的就不用GROUPBY。能用UNIONALL就不要用UNION。

8)用TRUNCATE替代DELETE。若要刪除表中所有的數(shù)據(jù)，則可以用TRUNCATE替代DELETE。

9)根據(jù)查詢條件建立合適的索引，利用索引可以避免大表全表掃描(FULLTABLESCAN)。

10)合理使用臨時表。

11)避免寫過于復雜的SQL，不一定非要一個SQL解決問題。將一個大的SQL改寫為多個小的SQL來實現(xiàn)功能。條件允許的情況下可以使用批處理來完成。

12)在不影響業(yè)務的前提下盡量減小事務的粒度。

13)當使用基于規(guī)則的優(yōu)化器(RBO)時，在多表連接查詢的時候，記錄數(shù)少的表應該放在右邊。

14)避免使用復雜的集合函數(shù)，像NOTIN等。通常，要避免在索引列上使用NOT，NOT會產(chǎn)生和在索引列上使用函數(shù)相同的影響。當Oracle遇到NOT操作符時，它就會停止使用索引轉而執(zhí)行全表掃描。很多時候用EXISTS和NOTEXISTS代替IN和NOTIN語句是一個好的選擇。需要注意的是，在Oracle11g之前，若NOTIN的列沒有指定非空的話(注意：是主表和子表的列未同時有NOTNULL約束，或都未加ISNOTNULL限制)，則NOTIN選擇的是filter操作(如果指定了非空，那么會選擇ANTI的反連接)，但是從Oracle11g開始有新的ANTINA(NULLAWARE)優(yōu)化，可以對子查詢進行UNNEST，NOTIN和NOTEXISTS都選擇的是ANTI的反連接，所以效率是一樣的。在一般情況下，ANTI的反連接算法比filter更高效。對于未UNNEST的子查詢，若選擇了filter操作，則至少有兩個子節(jié)點，執(zhí)行計劃還有個特點就是Predicate謂詞部分有“:B1”這種類似綁定變量的內容，內部操作走類似NestedLoops操作。如果在Oracle11g之前，遇到NOTIN無法UNNEST，那么可以將NOTIN部分的匹配條件均設為NOTNULL約束。若不添加NOTNULL約束，則需要兩個條件均增加ISNOTNULL條件。當然也可以將NOTIN修改為NOTEXISTS。

15)盡量避免使用UNION關鍵詞，可以根據(jù)情況修改為UNIONALL。

16)在Oracle數(shù)據(jù)庫里，IN和OR是等價的，優(yōu)化器在處理帶IN的目標SQL時會將其轉換為帶OR的等價SQL。例如，“DEPTNOIN(10,20)”和“DEPTNO=10ORDEPTNO=20”是等價的。

17)選擇合適的謂詞進行過濾。

18)避免使用前置通配符(%)。在WHERE子句中，如果索引列所對應的值的第一個字符由通配符(WILDCARD)開始，索引將不被采用。在很多情況下可能無法避免這種情況，但是一定要心中有底，通配符如此使用會降低查詢速度。然而當通配符出現(xiàn)在字符串其他位置時，優(yōu)化器就能利用索引。若前置通配符實在無法取消，則可以從幾個方面去考慮。①去重和去空。應該把表中的重復記錄或者為空的記錄全部去掉，這樣可以大大減少結果集，因而提升性能，這里也體現(xiàn)了大表變小表的思想。②考慮建立文本索引。③做相關的轉換。

19)應盡量避免在WHERE子句中對字段進行函數(shù)、算術運算或其他表達式等操作，因為這樣可能會使索引失效，查詢時要盡可能將操作移至等號右邊。見如下例子：

在以上SQL中，即使NAME字段建有唯一索引，該SQL語句也無法利用索引進行檢索數(shù)據(jù)，而是走全表掃描的方式。一些常見的改寫見表。

需要注意的是，如果SELECT需要檢索的字段只包含索引列且WHERE查詢中的索引列含有非空約束的時候，以上規(guī)則并不適用。例如，SQL語句“SELECTCREATEDFROMT1WHERETRUNC(CREATED)=TRUNC(SYSDATE);”，若CREATED列上有非空約束或在WHERE子句中加上“CREATEDISNOTNULL”，則該SQL語句仍然會進行索引，如下：

20)合理使用分析函數(shù)。

21)應盡量避免在WHERE子句中使用不等操作符(!=或＜＞)，否則引擎將放棄使用索引而進行全表掃描。

22)避免不必要和無意義的排序。

23)盡可能減少關聯(lián)表的數(shù)量，關聯(lián)表盡量不要超過3張。

24)在建立復合索引時，盡量把最常用、重復率低的字段放在最前面。在查詢的時候，WHERE條件盡量要包含索引的第一列即前導列。

25)應盡量避免在WHERE子句中對字段進行ISNULL值判斷，否則將導致引擎放棄使用索引而進行全表掃描?？梢酝ㄟ^加偽列創(chuàng)建偽聯(lián)合索引來使得ISNULL使用索引。例如語句：“SELECTIDFROMTWHERENUMISNULL;”可以在NUM上設置默認值0，確保表中NUM列沒有NULL值，然后這樣查詢：“SELECTIDFROMTWHERENUM=0;”。

26)IN要慎用，因為IN會使系統(tǒng)無法使用索引，而只能直接搜索表中的數(shù)據(jù)。如：

對于連續(xù)的數(shù)值，能用BETWEEN就不要用IN了：

27)必要時使用Hint強制查詢優(yōu)化器使用某個索引，如在WHERE子句中使用參數(shù)，也會導致全表掃描。因為SQL只有在運行時才會解析局部變量，但優(yōu)化程序不能將訪問計劃的選擇推遲到運行時；它必須在編譯時進行選擇。然而，如果在編譯時建立訪問計劃，變量的值還是未知的，因而無法作為索引選擇的輸入項。

28)在條件允許的情況下，只訪問索引，從而可以避免索引回表讀(TABLEACCESSBYINDEXROWID，通過索引再去讀表中的內容)。當索引中包括處理查詢所需要的所有數(shù)據(jù)時，可以執(zhí)行只掃描索引操作，而不用做索引回表讀操作。因為索引回表讀開銷很大，能避免則避免。避免的方法就是：①根據(jù)業(yè)務需求只留下索引字段；②建立聯(lián)合索引。這里的第二點需要注意平衡，如果聯(lián)合索引的聯(lián)合列太多，必然導致索引過大，雖然消減了回表動作，但是索引塊變多，在索引中的查詢可能就要遍歷更多的BLOCK了，所以需要全面考慮，聯(lián)合索引列不宜過多，一般來說超過3個字段組成的聯(lián)合索引都是不合適的，需要權衡利弊。

29)選擇合適的索引。Oracle在進行一次查詢時，一般對一個表只會使用一個索引。例如，某表有索引1(POLICYNO)和索引2(CLASSCODE)，如果查詢條件為POLICYNO='XX'ANDCLASSCODE='XX'，則系統(tǒng)有可能會使用索引2，相較于使用索引1，查詢效率明顯降低。

30)優(yōu)先且盡可能使用分區(qū)索引。

31)在刪除(DELETE)、插入(INSERT)、更新(UPDATE)頻繁的表中，建議不要使用位圖索引。

32)對于分區(qū)表，應該減少需要掃描的分區(qū)，避免全分區(qū)掃描。對于單分區(qū)掃描，在分區(qū)表后加上PARTITION(分區(qū)名)；對于多分區(qū)掃描，使用分區(qū)關鍵字來限制需要掃描的范圍，從而可以避免全分區(qū)掃描。

33)使用分批處理、DBMS_PARALLEL_EXECUTE進行處理。

34)刪除重復記錄盡量采用ROWID的方法，如下：

35)SQL中慎用自定義函數(shù)。如果自定義函數(shù)的內容，只是針對函數(shù)輸入?yún)?shù)的運算，而沒有訪問表這樣的代碼，那么這樣的自定義函數(shù)在SQL中直接使用是高效的；否則，如果函數(shù)中含有對表的訪問的語句，那么在SQL中調用該函數(shù)很可能會造成很大的性能影響，需要謹慎!在這種情況下，往往會將函數(shù)中訪問表的代碼取出和調用它的SQL整合成新的SQL。

36)使用DECODE函數(shù)可以避免重復掃描相同記錄或重復連接相同的表，這對于大表非常有效，如下：

若使用DECODE函數(shù)則對SCOTT.EMP表只訪問一次，如下：

類似的，DECODE函數(shù)也可以運用于GROUPBY和ORDERBY子句中。

37)在計算表的行數(shù)時，若表上有主鍵，則盡量使用COUNT(*)或COUNT(1)。

38)用WHERE子句替換HAVING子句。避免使用HAVING子句，因為HAVING只會在檢索出所有記錄之后才對結果集進行過濾。這個處理需要排序、總計等操作。如果能通過WHERE子句限制記錄的數(shù)目，那么就能提高SQL的性能。如下：

39)減少對表的查詢，尤其是要避免在同一個SQL中多次訪問同一張大表?？梢钥紤]如下的改寫方法：

①先根據(jù)條件提取數(shù)據(jù)到臨時表中，然后再做連接，即利用WITH進行改寫。

②有的相似的語句可以用MAX+DECODE函數(shù)來處理。

③在含有子查詢的SQL語句中，要特別注意減少對表的查詢，例如形如“UPDATEAAATSETTA=(...)T.B=(....)

WHERE....;”該更新的SQL語句中小括號中的大表都是一樣的，且查詢非常相似，這個時候可以修改為：“UPDATEAAATSET(T.A,T.B)=(....)

WHERE....;”。

40)SQL語句統(tǒng)一使用大寫。因為Oracle總是先解析SQL語句，把小寫的字母轉換成大寫的再執(zhí)行。

41)對于一些固定性的查詢結果可以使用結果集緩存(ResultCache)，對于一些常用的小表可以使用保留池(KeepPool)。

42)如果在一條SQL語句中同時取最大值和最小值，那么需要注意寫法上的差異：

43)在PL/SQL中，在定義變量類型時盡量使用%TYPE和%ROWTYPE，這樣可以減少代碼的修改，增加程序的可維護性。

以上講解的每點優(yōu)化內容希望讀者可以通過實驗來加深理解。

3.

COUNT(1)比COUNT(*)在執(zhí)行效率上要快嗎?正確答案：錯。COUNT(1)和COUNT(*)在執(zhí)行效率上是一樣的。COUNT()函數(shù)是Oracle中的聚合函數(shù)，用于統(tǒng)計結果集的行數(shù)。其語法形式如下：

可以把COUNT的使用情況分為以下3類：

1)COUNT(1)、COUNT(*)、COUNT(常量)、COUNT(主鍵)、COUNT(ROWID)、COUNT(非空列)。

2)COUNT(允許為空列)。

3)COUNT(DISTINCT列名)。

下面分別從查詢結果和效率方面做個比較：

(1)結果區(qū)別

1)COUNT(1)、COUNT(*)、COUNT(ROWID)、COUNT(常量)、COUNT(主鍵)、COUNT(非空列)這幾種方式統(tǒng)計的行數(shù)是表中所有存在的行的總數(shù)，包括值為NULL的行和非空行。所以，這幾種方式的執(zhí)行結果相同。這里的常量可以為數(shù)字或字符串，例如，COUNT(2)、COUNT(333)、COUNT('x')、COUNT('xiaomaimiao')。需要注意的是：這里的COUNT(1)中的“1”并不表示表中的第一列，它其實是一個表達式，可以換成任意數(shù)字或字符或表達式。

2)COUNT(允許為空列)這種方式統(tǒng)計的行數(shù)不會包括字段值為NULL的行。

3)COUNT(DISTINCT列名)得到的結果是除去值為NULL和重復數(shù)據(jù)后的結果。

4)“SELECTCOUNT("),COUNT(NULL)FROMT_COUNT_LHR;”返回0行。

(2)效率、索引

1)如果存在主鍵或非空列上的索引，那么COUNT(1)、COUNT(*)、COUNT(ROWID)、COUNT(常量)、COUNT(主鍵)、COUNT(非空列)會首先選擇主鍵上的索引快速全掃描(INDEXFASTFULLSCAN)。若主鍵不存在則會選擇非空列上的索引。若非空列上沒有索引則肯定走全表掃描(TABLEACCESSFULL)。其中，COUNT(ROWID)在走索引的時候比其他幾種方式要慢。通過10053事件可以看到這幾種方式除了COUNT(ROWID)之外，其他最終都會轉換成COUNT(*)的方式來執(zhí)行。

2)對于COUNT(COL1)來說，只要列字段上有索引則會選擇索引快速全掃描(INDEXFASTFULLSCAN)。而對于“SELECTCOL1”來說，除非列上有NOTNULL約束，否則執(zhí)行計劃會選擇全表掃描。

3)對于COUNT(列)來說，隨著列的偏移位置越大，COUNT(列)的速度越來越慢。在設計表時，把經(jīng)常訪問的列盡量設計在表的前幾列。

4)COUNT(DISTINCT列名)若列上有索引，且有非空約束或在WHERE子句中使用ISNOTNULL，則會選擇索引快速全掃描。其余情況選擇全表掃描。

4.

模糊查詢可以使用索引嗎?正確答案：分為以下幾種情況：

(1)若SELECT子句只檢索索引字段那么模糊查詢可以使用索引，例如，“SELECTIDFROMTBWHEREIDLIKE'%123%';”可以使用索引。

(2)若SELECT子句不只檢索索引字段還檢索其他非索引字段那么分為以下幾種情況：

1)模糊查詢形如“WHERECOL_NAMELIKE'ABC%';”可以用到索引。

2)模糊查詢形如“WHERECOL_NAMELIKE'%ABC';”不能使用索引，但是可以通過REVERSE函數(shù)來創(chuàng)建函數(shù)索引才能使用到索引。

3)模糊查詢形如“WHERECOL_NAMELIKE'%ABC%';”不能使用索引，但是，如果所查詢的字符串有一定的規(guī)律的話，那么還是可以使用到索引的，分以下幾種情況：

①如果字符串ABC始終從原字符串的某個固定位置出現(xiàn)，那么可以創(chuàng)建SUBSTR函數(shù)索引進行優(yōu)化。

②如果字符串ABC始終從原字符串結尾的某個固定位置出現(xiàn)，那么可以創(chuàng)建函數(shù)組合索引進行優(yōu)化。

③如果字符串ABC在原字符串中位置不固定，那么可以通過改寫SQL進行優(yōu)化。

4)建全文索引后使用CONTAINS也可以用到域索引。

5.

OracleHint中的DRIVINGSITE的作用是什么?正確答案：在日常工作中經(jīng)常會用到分布式數(shù)據(jù)庫查詢，即通過DBLINK同時查詢本地表和遠程表。分布式查詢一般有兩種處理方式：一種是將遠程表數(shù)據(jù)取回本地，然后和本地表關聯(lián)查詢，獲取最終結果；另一種是將本地表數(shù)據(jù)傳到遠程和遠程表關聯(lián)查詢后，再將關聯(lián)結果取回。前一種處理方式可理解為只有一次網(wǎng)絡傳輸操作，比后一種少，也就作為了數(shù)據(jù)庫的默認處理方式。DRIVING_SITE提示能夠指定執(zhí)行計劃在遠程還是本地做，使用DRIVING_SITE，特別是本地小結果集，遠程大結果集，最終結果集較小時，那么最好是執(zhí)行計劃在遠程操作，這樣遠程執(zhí)行完畢，將結果集傳輸?shù)奖镜?，避免了大結果集的網(wǎng)絡傳輸，從而達到整體優(yōu)化的效果。使用DRIVING_SITE可以減少總體的網(wǎng)絡傳輸數(shù)據(jù)量。

當DRIVING_SITE驅動的對象嵌套在視圖中時，可通過DRIVING_SITE(V.T)方式來指定，其中，V表示視圖別名或名稱，T表示視圖里表的別名或名稱。

需要注意的是，對于DML和DDL語句，DRIVING_SITE提示是失效的，會自動被Oracle忽略掉，此時將以目標表所在庫為主計劃驅動，相當于DRIVING_SITE(目標表庫)，此時可以通過視圖轉換來達到優(yōu)化的目的。在DML和DDL中如果是對本地表做DML，主計劃總是在本地執(zhí)行，會將遠程數(shù)據(jù)拉到本地，相當于DRIVING_SITE(本地表)。如果是對遠程表做DML，那么主計劃總是在遠程執(zhí)行，會將本地數(shù)據(jù)送到遠程，相當于自動DRIVING_SITE(遠程表)。

6.

什么是RBO和CBO?正確答案：Oracle數(shù)據(jù)庫中優(yōu)化器(Optimizer)是SQL分析和執(zhí)行的優(yōu)化工具，是Oracle數(shù)據(jù)庫中內置的一個核心模塊。優(yōu)化器的目的就是為了得到目標SQL的執(zhí)行計劃。Oracle數(shù)據(jù)庫里的優(yōu)化器又分為RBO(rule-BasedOptimizer，基于規(guī)則的優(yōu)化器)和CBO(Cost-BasedOptimizer，基于成本的優(yōu)化器)這兩種類型。從Oracle10g開始，Oracle數(shù)據(jù)庫默認都是基于CBO的優(yōu)化方式。

(1)RBO

RBO的執(zhí)行機制非常簡單，就是在優(yōu)化器里面嵌入若干種規(guī)則，如果執(zhí)行的SQL語句符合某個規(guī)則(Rank，共有1～15共15個等級)，那么Oracle會按照規(guī)則(Rank)制定出相應的執(zhí)行計劃。由于RBO只是簡單地去匹配Rank，所以它的執(zhí)行計劃在很多時候并不是最佳的。例如，某個表的其中一列數(shù)據(jù)分布非常不均勻，其中90%的數(shù)據(jù)內容是一樣的，并且在這個字段上有索引。如果在目標SQL語句的謂詞里有這個字段，那么RBO就會選擇走索引。而這是一種非常慢的執(zhí)行路徑，因為Oracle要先訪問索引塊，在索引上找到相應的鍵值，然后按照鍵值上的ROWID再去訪問表中的相應數(shù)據(jù)。其實，在這種情況下，選擇全表掃描是最優(yōu)的，但是RBO不會這么選擇。RBO的缺點主要有：

1)執(zhí)行計劃出了問題，很難對其做調整。

2)執(zhí)行計劃會受目標SQL的寫法、表在WHERE條件中出現(xiàn)的先后順序等因素的影響。

3)Oracle很多新特性不被RBO支持。

4)制定出差的執(zhí)行計劃的概率比較大。

5)忽略了SQL中表本身的統(tǒng)計信息情況。

有的時候即使修改了優(yōu)化器模式或者使用了RULEHint，Oracle依然不會使用RBO(而是強制使用CBO)，這些情況包括：

1)當RULE和DRIVING_SITE聯(lián)合使用時，RULE會失效。

2)目標SQL中涉及的對象有IOT(IndexOrganizedTable)。

3)目標SQL中涉及的對象有分區(qū)表。

4)使用了并行查詢或者并行DML。

5)使用了星型連接。

6)使用了哈希連接。

7)使用了索引快速全掃描。

8)使用了函數(shù)索引。

(2)CBO

從Oracle7開始就引入了CBO。CBO是基于成本的優(yōu)化器，它根據(jù)可用的訪問路徑、對象的統(tǒng)計信息、嵌入的Hint來選擇一個成本最低的執(zhí)行計劃。優(yōu)化器在使用CBO時，主要參照的是表、列及索引的統(tǒng)計信息。DBA可以通過設置初始化參數(shù)OPTIMIZER_MODE來決定到底使用哪個優(yōu)化器，也可以用ALTERSESSION來改變當前SESSION中OPTIMIZER_MODE的值。除此之外在SQL中嵌入Hint可以指定具體某個SQL使用哪個優(yōu)化器。OPTIMIZER_MODE選項有：ALL_ROWS、FIRST_ROWS_n(這里的n只能為1、10、100、1000)、FIRST_ROWS、CHOOSE和RULE，默認為ALL_ROWS。CBO包含的組件主要有：查詢轉換器(QueryTransformer)、評估器(Estimator)和計劃生成器(PlanGenerator)，如圖所示。

CBO組件

查詢轉換器的作用是改變查詢語句的形式以產(chǎn)生較好地執(zhí)行計劃，主要的轉換技術有：視圖合并(ViewMerging)、謂詞推進(PredicatePushing)、非嵌套子查詢(SubqueryUnnesting)和物化視圖的查詢重寫(QueryRewritewithMaterializedViews)等。評估器通過計算選擇性(Selectivity)、基數(shù)(Cardinality)、成本(Cost)這3個值來評估執(zhí)行計劃的總體成本。計劃生成器的作用就是生成大量的執(zhí)行計劃，然后選擇其中總體成本最低的一個。

CBO的缺點主要有：

1)CBO會默認目標SQL語句WHERE條件中出現(xiàn)的各個列之間是獨立的，沒有關聯(lián)關系，而在實際的應用中，目標SQL的各列之間往往是有關聯(lián)關系的，在這種情況下，估算結果可能就會和實際結果有較大的偏差，導致CBO選錯執(zhí)行計劃。目前可以用來緩解上述問題所帶來負面影響的方法是使用動態(tài)采樣或者多列統(tǒng)計信息，但動態(tài)采樣的準確性取決于采樣數(shù)據(jù)的質量和采樣數(shù)據(jù)的數(shù)量，而多列統(tǒng)計信息并不適用于多表之間有關聯(lián)關系的情形，所以這兩種解決方法都不能算是完美的解決方案。

2)CBO會假設所有的目標SQL都是單獨執(zhí)行、并且是互不干擾的，但實際上，在執(zhí)行目標SQL時所需要訪問的索引葉子塊、數(shù)據(jù)塊等可能已經(jīng)被緩存在BufferCache中，所以這次執(zhí)行時也只需要去BufferCache中讀相關的緩存塊就可以了。所以，如果此時CBO還是按照目標SQL是單獨執(zhí)行，不考慮緩存的方式去計算相關成本值的話，就可能會高估走相關索引的成本，進而可能會導致選錯執(zhí)行計劃。

3)CBO對直方圖統(tǒng)計信息有諸多限制。表現(xiàn)為兩點：①在Oracle12c之前，F(xiàn)requency類型的直方圖所對應的Bucket的數(shù)量不能超過254，這樣如果目標列的distinct值的數(shù)量超過254，Oracle就會使用HeightBalanced類型的直方圖。對于HeightBalanced類型的直方圖而言，因為Oracle不會記錄所有的NonpopularValue的值，所以在此情況下CBO選錯執(zhí)行計劃的概率會比對應的直方圖統(tǒng)計信息是Frequency類型的情形要高。②在Oracle數(shù)據(jù)庫里，如果針對文本型的字段收集直方圖統(tǒng)計信息，那么Oracle只會將該文本值的頭32字節(jié)給取出來存儲在數(shù)據(jù)字典里，對于那些超過32字節(jié)的文本型字段，只要對應記錄的文本值的頭32字節(jié)相同，Oracle在收集直方圖統(tǒng)計信息的時候就會認為這些記錄該字段的文本值是相同的，即使實際上它們并不相同。這種先天性的缺陷會直接影響CBO對相關文本型字段的可選擇率及返回結果集的Cardinality的估算，進而就可能導致CBO選錯執(zhí)行計劃。

4)CBO在解析多表關聯(lián)的目標SQL時，可能會漏選正確的執(zhí)行計劃。在Oracle11gR2中，CBO在解析多表關聯(lián)的目標SQL時，所考慮的各個表連接順序的總和會受隱含參數(shù)“_OPTIMIZER_MAX_PERMUTATIONS”的限制。

雖然CBO有上述這些缺點，但是瑕不掩瑜，CBO仍然是目前Oracle中解析目標SQL的不二選擇，并且隨著Oracle數(shù)據(jù)庫版本不斷的進化，CBO也會越來越完善。

7.

什么是HighVersionCount?正確答案：一個父游標下對應的子游標個數(shù)被稱為VersionCount，每個子游標對應一個執(zhí)行計劃。對于一個特定的游標有多少個版本(VersionCount)就屬于高版本游標是沒有明確定義的。對于不同的系統(tǒng)有不同的數(shù)量界定。HighVersionCount不僅產(chǎn)生的原因是多種多樣的，而且會導致各種令人頭痛的問題，輕則導致數(shù)據(jù)庫的性能急劇下降，CPU利用率劇增，重則導致數(shù)據(jù)庫掛起，觸發(fā)ORA-04031或者其他BuG導致宕機。

在AWR報告中，VersionCount大于20就會被報告出來，如圖所示。

VersionCount大于20的情形

8.

綁定變量是什么?綁定變量有什么優(yōu)缺點?正確答案：(1)綁定變量的含義及優(yōu)缺點。通常在高并發(fā)的OLTP系統(tǒng)中，可能會出現(xiàn)這樣的現(xiàn)象，單個SQL的寫法、執(zhí)行計劃、性能都是沒問題的，但整個系統(tǒng)的性能就是很差，這表現(xiàn)在當系統(tǒng)并發(fā)的數(shù)量增加時，整個系統(tǒng)負載很高，CPU占用率接近100%。其實，這種系統(tǒng)性能隨著并發(fā)量的遞增而顯著降低的現(xiàn)象，往往是因為這些系統(tǒng)沒有使用綁定變量而產(chǎn)生了大量的硬解析所致。因為同一條SQL語句僅僅由于謂詞部分變量的不同而在執(zhí)行的時候就需要重新進行一次硬解析，造成SQL執(zhí)行計劃不能共享，這極大地耗費了系統(tǒng)時間和系統(tǒng)CPU資源。那么怎樣才能降低OLTP應用系統(tǒng)的硬解析的數(shù)量呢?答案就是使用綁定變量。高并發(fā)的OLTP系統(tǒng)若沒有使用綁定變量則會導致硬解析很大，這在AWR中的LoadProfile部分可以很容易地看出來。

使用綁定變量能夠有效降低系統(tǒng)硬解析的數(shù)量。對于同一類型的SQL語句若使用了綁定變量，則SQL文本就變得完全相同了，據(jù)此計算出來的哈希值也就完全相同，這就具備了可以重用解析樹和執(zhí)行計劃的基礎條件。這里的同一類型的SQL語句指的是除SQL文本中對應的輸入值