Oracle程序員面試分類模擬17

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

＞，＜一般情況下會走索引嗎?正確答案：會，使用的是索引范圍掃描。

2.

如何清除SharedPool中某條SQL語句?正確答案：如果數(shù)（江南博哥）據(jù)庫版本為Oracle10.2.0.4以前，那么只能清空整個SharedPool，命令為：“ALTERSYSTEMFLUSHSHARED_POOL;”。在生產(chǎn)庫上，該句要慎用。而從Oracle10.2.0.4開始提供了一個包DBMS_SHARED_POOL，該包可以實現(xiàn)清除SharedPool中某條SQL語句的功能。若該包沒有安裝，則可以通過$ORACLE_HOME/rdbms/admin/dbmspool.sql進行安裝。

使用這種方法可以精確地將一個SQL從共享池中刪除，從而使得Oracle為這個SQL重新生成執(zhí)行計劃。這種方法只針對單個SQL語句，使得解決問題的同時不會造成任何的誤傷。

示例如下：

需要注意的是，在Oracle10.2.0.4中該功能有BUG(MOS為：751876.1)，需要通過設(shè)置事件來規(guī)避該BUG，命令為：“ALTERSESSIONSETEVENTS'5614566TRACENAMECONTEXTFOREVER';”。若不設(shè)置該事件，則DBMS_SHARED_POOL.PLYRGE將不起作用。

清除SharedPool中某條SQL語句無非是讓目標SQL語句重新進行硬解析，其實重新進行硬解析的方法還有如下兩種：

1)使用DDL操作可以讓Oracle再次執(zhí)行目標SQL時使用硬解析(新產(chǎn)生一個子游標)，但這種方法的弊端在于其影響范圍還是太廣了，因為一旦對某個表執(zhí)行了DDL操作，庫緩存中所有在SQL文本中包含了這個表的SharedCursor都會被Oracle標記為失效(INVALID)，這意味著這些SharedCursor中存儲的解析樹和執(zhí)行計劃將不再能被重用，再次執(zhí)行與這個表相關(guān)的所有SQL時就會全部使用硬解析。這是很不好的，特別是對于OLTP類型的應用系統(tǒng)而言，這可能會導致短時間內(nèi)的硬解析數(shù)量劇增，進而影響系統(tǒng)的性能。盡管如此，有時仍然會采用這種方法。這里的DDL操作有很多種，通常選擇添加注釋的COMMENT語句(“COMMENTONTABLETB_NAMEIS'xxxbylhr';”)，因為使用COMMENT添加注釋也是DDL操作，但同時對系統(tǒng)的影響相對而言比較小。

2)在重新收集統(tǒng)計信息時指定NO_INVALIDATE=＞FALSE選項。如果取值為FALSE，那么表示將統(tǒng)計信息對象相關(guān)的所有Cursor全部失效。目標SQL語句在下次執(zhí)行時就會使用硬解析。

3.

什么是物理讀和邏輯讀?正確答案：當會話所需要的數(shù)據(jù)在內(nèi)存的BufferCache中找不到，此時就要去磁盤上的數(shù)據(jù)文件中讀取，這樣就產(chǎn)生了物理讀(PhysicalReads)，即物理讀是從磁盤文件把需要的數(shù)據(jù)讀入內(nèi)存(SGA中的BufferCache)。

邏輯讀(LogicalReads)表示CPU需要的數(shù)據(jù)在內(nèi)存中被找到，數(shù)據(jù)被直接從內(nèi)存中傳入CPU執(zhí)行，即邏輯讀是從內(nèi)存中讀取。在Oracle中，邏輯讀=即時讀(又稱為當前讀，CurrentRead)+一致性讀(ConsistentRead)，在“SETAuTOTRACEON”中，邏輯讀=dbblockgets(當前讀)+consistentgets(一致性讀)。即時讀就是讀取數(shù)據(jù)塊當前的最新數(shù)據(jù)。任何時候在BufferCache中都只有一份當前數(shù)據(jù)塊。即時讀通常發(fā)生在對數(shù)據(jù)進行修改，刪除操作時。這時，后臺進程會給相關(guān)數(shù)據(jù)加上行級鎖，并且標識數(shù)據(jù)為“臟數(shù)據(jù)”。

需要注意的是，物理讀過大表現(xiàn)為磁盤I/O較高，邏輯讀過大表現(xiàn)為CPU使用率過高。

4.

什么是謂詞越界?正確答案：謂詞越界即如果對目標列指定的WHERE查詢條件不在該列的最大值和最小值之間，CBO就無法判斷出針對該列的查詢條件的可選擇率，所以只能用一個估算值來作為針對該目標列的查詢條件的可選擇率。如果這個估算的可選擇率與實際情況嚴重不符的話，那么就有可能導致CBO評估出來的Cardinality(基數(shù))出現(xiàn)嚴重偏差，進而使得CBO選錯執(zhí)行計劃。

5.

什么是直接路徑訪問?正確答案：直接路徑訪問是繞過SGA，直接把數(shù)據(jù)讀入到PGA中，這個過程數(shù)據(jù)不經(jīng)過SGA的緩沖，所以在理論上性能更快。在PGA中的數(shù)據(jù)只能由當前會話進程訪問，當其他會話需要訪問這部分數(shù)據(jù)時需要從磁盤讀取數(shù)據(jù)，會發(fā)生物理讀。若多個會話同時需要相同的數(shù)據(jù)，則會導致系統(tǒng)物理讀增大，從而影響了數(shù)據(jù)庫的性能。

保存數(shù)據(jù)的方法分為常規(guī)路徑加載和直接路徑加載：

(1)常規(guī)路徑加載

執(zhí)行INSERT語句來填充數(shù)據(jù)庫中的表。直接路徑加載會格式化Oracle數(shù)據(jù)塊，并將數(shù)據(jù)塊直接寫入數(shù)據(jù)庫文件，從而可消除大量數(shù)據(jù)庫開銷。直接加載不與其他用戶競爭數(shù)據(jù)庫資源，因此通?？梢杂媒咏疟P速度的速度加載數(shù)據(jù)。常規(guī)路徑加載使用SQL處理和數(shù)據(jù)庫COMMIT操作來保存數(shù)據(jù)。插入記錄數(shù)組后會執(zhí)行COMMIT操作。每次數(shù)據(jù)加載可能涉及多個事務處理。

(2)直接路徑加載

使用數(shù)據(jù)保存將數(shù)據(jù)塊寫入Oracle數(shù)據(jù)文件。這就是為什么直接路徑加載比常規(guī)路徑加載快很多的原因。數(shù)據(jù)保存與COMMIT的區(qū)別在于：

1)在數(shù)據(jù)保存期間，僅將整個數(shù)據(jù)庫塊寫入數(shù)據(jù)庫中。這些塊是在表的高水位標記(HWM)之后寫入的。

2)完成數(shù)據(jù)保存后，HWM會移動。

3)完成數(shù)據(jù)保存后不會釋放內(nèi)部資源。

4)完成數(shù)據(jù)保存不會結(jié)束事務處理。

5)每次執(zhí)行數(shù)據(jù)保存時不會更新索引。

就DML活動方面而言，直接路徑加載與并行直接路徑加載非常相似。常規(guī)路徑加載和直接路徑加載的區(qū)別見表。

6.

什么是基數(shù)(Cardinality)和可選擇率(Selectivity)?正確答案：基數(shù)(Cardinality)是Oracle預估的返回行數(shù)，即對目標SQL的某個具體執(zhí)行步驟的執(zhí)行結(jié)果所包含記錄數(shù)的估算值。如果是針對整個目標SQL，那么此時的Cardinality就表示該SQL最終執(zhí)行結(jié)果所包含記錄數(shù)的估算值。例如，一張表T有1000行數(shù)據(jù)，列COL1上沒有直方圖，沒有空值，并且不重復的值(DistinctValue)有500個。那么，在使用條件“WHERECOL1=＜VALUE＞”去訪問表的時候，優(yōu)化器會假設(shè)數(shù)據(jù)均勻分布，它估計出會有1000/500=2行被選出來，其中“2”就是這步操作的Cardinality。通常情況下，Crdrdinality越準確，生成的執(zhí)行計劃就會越高效。

可選擇率(Selectivity)是指施加指定謂詞條件后返回結(jié)果集的記錄數(shù)占未施加任何謂詞條件的原始結(jié)果集的記錄數(shù)的比率?？蛇x擇率的取值范圍顯然是0～1，它的值越小，就表明可選擇性越好。當可選擇率為1時的可選擇性是最差的。CBO就是用可選擇率來估算對應結(jié)果集的Cardinality的，可選擇率和Cardinality之間的關(guān)系如下：

其中，NUM_ROWS表示表的總行數(shù)。一些常見的可選擇率計算公式見隨書pdf文檔。

在Oracle數(shù)據(jù)庫中，Oracle會默認認為SQL語句的WHERE條件中出現(xiàn)的各列彼此之間是獨立的，是沒有關(guān)聯(lián)關(guān)系的。所以，如果目標SQL語句各列之間是以AND來組合的話，那么該SQL語句整個WHERE條件的組合可選擇率就等于各個列各自施加查詢條件后可選擇率的乘積。在得到了SQL語句整個WHERE條件的組合可選擇率后，Oracle會用它來估算整個SQL語句返回結(jié)果集的Cardinality，估算的方法就是用目標表的總記錄數(shù)(NUM_ROWS)乘組合可選擇率。但Oracle默認認為的各列之間是獨立的、沒有關(guān)聯(lián)關(guān)系的前提條件并不總是正確的，在實際的應用中各列之間有關(guān)聯(lián)關(guān)系的情況實際上并不罕見。在這種情況下如果還用上述計算方法來計算目標SQL語句整個WHERE條件的組合可選擇率并用它來估算返回結(jié)果集的Cardinality的話，那么估算結(jié)果可能就會與實際結(jié)果有較大的偏差，進而可能導致CBO選錯執(zhí)行計劃，所以O(shè)racle又引入了動態(tài)采樣和多列統(tǒng)計信息。

7.

執(zhí)行計劃里的access和filter有什么區(qū)別?正確答案：如下：

一般而言，access表示這個謂詞條件的值將會影響數(shù)據(jù)的訪問路徑(表還是索引)；filter表示謂詞條件的值不會影響數(shù)據(jù)的訪問路勁，只起到過濾的作用。NOTIN或MIN函數(shù)等容易產(chǎn)生filter操作。

對于filter而言如果只有一個子節(jié)點，那么就是簡單過濾操作(獨立操作)。如果有兩個或更多子節(jié)點，那么就是類似NestedLoops操作，只不過與NestedLoops差別在于，filter內(nèi)部會構(gòu)建HASH表，對于重復匹配的，不會再次進行循環(huán)查找，而是利用已有結(jié)果，提高效率。但是一旦重復匹配的較少，循環(huán)次數(shù)多，那么，filter操作將是嚴重影響性能的操作，可能會導致目標SQL幾天都執(zhí)行不完。

8.

說說COUNT(*)計算行數(shù)有哪些優(yōu)化手段?正確答案：可以通過創(chuàng)建索引、主鍵索引、常數(shù)索引、常數(shù)壓縮索引、位圖索引、位圖索引+并行、物化視圖、結(jié)果集緩存等技術(shù)來優(yōu)化，也可以根據(jù)業(yè)務規(guī)則來分析需求，是否只統(tǒng)計一條數(shù)據(jù)即可，或者根本用不到統(tǒng)計數(shù)據(jù)。其中，位圖索引可以按很高密度存儲數(shù)據(jù)，因此往往比B樹索引小很多，前提是在基數(shù)比較小(列重復度比較高)的情況下。位圖索引是保存空值的，因此可以在COUNT中利用。位圖索引不太適合OLTP類型數(shù)據(jù)庫。物化視圖是應用在數(shù)據(jù)要求不怎么及時的場景下。若表頻繁更新，則不適合緩存結(jié)果集。優(yōu)化沒有止境，對數(shù)據(jù)庫了解越多，能想到的方法就越多。

9.

給出下面語句的幾種可能的優(yōu)化思路。

正確答案：優(yōu)化思路有：①采用綁定變量；②使用靜態(tài)SQL：③采用批量提交或循環(huán)外提交；④根據(jù)功能，可以去掉PL/SQL塊，采用直接一次性插入的方式來完成，SQL為“INSERTINTOT_YH_20170705_LHRSELECTROWNUMFROMDUALCONNECTBYLEVEL＜=100000;”；⑤采用直接路徑方式，例如，“C