Oracle程序員面試分類模擬19

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

什么是基數(shù)反饋(CardinalityFeedback)?正確答案：基數(shù)反饋(CardinalityFeedback，CFB)是Oracle11（江南博哥）gR2出現(xiàn)的一個新特性，它的出現(xiàn)是為了幫助Oracle優(yōu)化器依據(jù)更精準(zhǔn)的基數(shù)生成更加優(yōu)秀的執(zhí)行計劃?；鶖?shù)的評估準(zhǔn)確與否，對于優(yōu)化器而言異常重要，將直接影響到后續(xù)的JOINCOST等重要的成本計算評估。若評估不當(dāng)則會造成CBO選擇不當(dāng)?shù)膱?zhí)行計劃。此技術(shù)對于僅執(zhí)行一次的SQL無效，在SQL第一次執(zhí)行時，記錄存儲實際的基數(shù)和評估的基數(shù)之間的差異，如果差異較大，在第二次執(zhí)行時，優(yōu)化器會依據(jù)實際的基數(shù)重新決策生成執(zhí)行計劃，但是需要注意的是，當(dāng)使用更準(zhǔn)確的基數(shù)重新生成執(zhí)行計劃時，生成的執(zhí)行計劃與第一次時使用的執(zhí)行計劃完全有可能是相同的。這個技術(shù)的出現(xiàn)是由于優(yōu)化器在一些情況下不能很好地去計算基數(shù)的數(shù)值，比如：統(tǒng)計信息缺失或陳舊、多渭詞、直方圖缺失等。

Oracle只針對下面情況開啟CFB：

1)沒有收集表的統(tǒng)計信息，并且動態(tài)采樣(DynamicSampling)也沒有開啟。

2)查詢條件復(fù)雜(比如條件有函數(shù))或者涉及多列，但卻沒有收集擴(kuò)展的統(tǒng)計信息(ExtendedStatistics)。

在這幾種情況下，CBO是無法估算出準(zhǔn)確的Cardinality的。針對上述情況，Oracle會監(jiān)控操作的實際行數(shù)(A-Row)，然后對比CBO估算的行數(shù)(E-Row)。如果兩個值相差很大，那么就記錄實際行數(shù)(A-Row)，做上標(biāo)記。下次執(zhí)行時再次進(jìn)行硬解析，根據(jù)實際行數(shù)來重新生成執(zhí)行計劃。如果兩個值相差不大，那么CBO就不再監(jiān)控這條SQL語句。

Oracle11gR2針對此特性，也專門在V$SQL_SHARED_CURSOR中增加了USE_FEEDBACK_STATS列來記錄SQL是否使用了基數(shù)反饋?；鶖?shù)反饋的開啟和關(guān)閉通過一個隱含參數(shù)“_OPTIMIZER_USE_FEEDBACK”來控制，該參數(shù)默認(rèn)為TRUE，表示開啟技術(shù)反饋特性。此參數(shù)除了可以在SESSION和SYSTEM級別進(jìn)行設(shè)置之外，還可以在SQL語句級使用Hint進(jìn)行開啟和關(guān)閉，如下：

需要注意的是，如果動態(tài)采樣被啟用，那么是不會使用基數(shù)反饋特性的。若使用了該特性則在執(zhí)行計劃的Note部分可以看到“cardinalityfeedbackusedforthisstatement”字樣。基數(shù)反饋在Oracle12c上得到更進(jìn)一步的擴(kuò)展稱為統(tǒng)計反饋(StatisticsFeedback)，并且成為Oracle12c自動重新優(yōu)化(AutomaticReoptimization)的一部分。但是由于CFB的評估結(jié)果數(shù)據(jù)只存在內(nèi)存中(重啟之后就會丟失)，在會話之間是不可共用的，并且由于在Oracle11g中存在過多的Bug，常見的問題就是在第二次執(zhí)行SQL時候性能下降很多。因此在Oracle11g的數(shù)據(jù)庫中往往對11.2.0.4以下的數(shù)據(jù)庫會將該特性關(guān)閉。

2.

如何查詢表的歷史統(tǒng)計信息正確答案：從Oracle10g開始，當(dāng)收集表的統(tǒng)計信息的時候，舊的統(tǒng)計數(shù)據(jù)被保留，如果因為新的統(tǒng)計信息而出現(xiàn)性能問題，舊的統(tǒng)計信息就可以被恢復(fù)。歷史統(tǒng)計信息保存在以下幾張表中：

1)WRI$_OPTSTAT_TAB_HISTORY表的統(tǒng)計信息。

2)WRI$_OPTSTAT_IND_HISTORY索引的統(tǒng)計信息。

3)WRI$_OPTSTAT_HISTHEAD_HISTORY列的統(tǒng)計信息。

4)WRI$_OPTSTAT_HISTGRM_HISTORY直方圖的信息。

從視圖DBA_TAB_STATS_HISTORY可以查詢歷史收集統(tǒng)計信息的時間，但是不能查詢到行數(shù)，所以需要結(jié)合基表來查詢，查詢的SQL語句如下：

查詢索引的歷史統(tǒng)計信息的SQL語句如下：

默認(rèn)情況下統(tǒng)計信息將被保留31天，可以使用下面的命令修改：

注意：這些統(tǒng)計信息在SYSAUX表空間中占有額外的存儲開銷，所以應(yīng)該注意并防止統(tǒng)計信息將表空間填滿。

下面的查詢返回統(tǒng)計信息已經(jīng)被刪除到的日期(所以只有在這日期之后的統(tǒng)計信息才可能被恢復(fù))。任何恢復(fù)到比這日期更早的統(tǒng)計信息的請求都會失?。骸癘RA-20006:Unabletorestorestatistics,statisticshistorynotavailable”：

查詢到可以恢復(fù)統(tǒng)計信息到某一個比較好的時間之后，可以執(zhí)行下面的命令進(jìn)行恢復(fù)：

可以通過如下的命令返回2次統(tǒng)計信息的比較結(jié)果：

3.

Oracle如何并發(fā)地收集統(tǒng)計信息?正確答案：對于大表的統(tǒng)計信息收集可以通過DEGREE參數(shù)使得掃描大表的時候進(jìn)行并行掃描，從而加快掃描速度，縮短了收集統(tǒng)計信息的時間。但是，即使加了DEGREE參數(shù)，在收集統(tǒng)計信息的時候，還是進(jìn)行一個表一個表的掃描，并沒有并發(fā)的同時掃描多個表。在Oracle11.2.0.2之后，有了一個參數(shù)，可以并發(fā)掃描表，這就是CONCURRENT參數(shù)?？梢酝ㄟ^以下SQL語句查詢數(shù)據(jù)庫是否啟用了CONCURRENT收集統(tǒng)計信息，默認(rèn)為FALSE，表示沒有開啟并發(fā)收集統(tǒng)計信息：

開啟方式為：

開啟CONCURRENT之后，收集統(tǒng)計信息就會以并發(fā)的形式進(jìn)行，會并發(fā)出多個JOB進(jìn)程。在并發(fā)收集統(tǒng)計信息時，數(shù)據(jù)庫生成的JOB數(shù)會根據(jù)具體情況來分配。在大多數(shù)情況下，DBMS_STATS程序會給每個對象分配一個JOB；但如果對象(表或者分區(qū))的大小太小，為了節(jié)省資源，Oracle會合并多個表和分區(qū)在一個JOB中執(zhí)行。為了防止同時處理多個分區(qū)表的分區(qū)時發(fā)生死鎖，所以，對于分區(qū)表的處理機(jī)制是每次只能處理一個分區(qū)表，其他的分區(qū)表需要等待，待前一個分區(qū)表處理完后再處理下一個。在Oracle11.2.0.2到11.2.0.4的版本上，CONCURRENT可取的值為TRUE(開啟并發(fā))和FALSE(關(guān)閉并發(fā))。在Oracle12c的版本上，可以設(shè)置以下的值：

可以通過以下的視圖對并發(fā)統(tǒng)計信息收集進(jìn)行監(jiān)視：

監(jiān)控并發(fā)收集統(tǒng)計信息JOB的SQL代碼如下：

對于并發(fā)收集統(tǒng)計信息需要注意如下幾點：

1)用CONCURRENT收集統(tǒng)計信息，需要收集統(tǒng)計信息的用戶具有CREATEJOB、MANAGESCHEDULER和MANAGEANYQUEUE權(quán)限。即使是該用戶具有了DBA角色，也還是需要顯式授權(quán)上述權(quán)限。否則執(zhí)行JOB的時候，可能會報錯：“ORA-27486insufficientprivileges”、“ORA-20000:Statisticscollectionfailedfor32235objectsinthedatabase”。

2)因為CONCURRENT不能控制并發(fā)度的大小，所以，如果數(shù)據(jù)庫的初始化參數(shù)JOB_QUEUE_PROCESSES設(shè)置的太高(在Oracle11.2.0.3之后，這個值的默認(rèn)值是1000，所以就可能并發(fā)出1000個JOB)，那么對數(shù)據(jù)庫的性能影響較大。所以開啟CONCURRENT的另外一個建議就是使用ResourceManager來控制資源的使用。

3)下表列出了并發(fā)和并行在收集統(tǒng)汁信息方面的一些區(qū)別。

4.

當(dāng)收集表的統(tǒng)計信息時應(yīng)該注意哪些問題?正確答案：關(guān)于收集統(tǒng)計信息需要注意以下幾點：

1)對于數(shù)據(jù)量不大的OLTP類型的系統(tǒng)，建議使用自動收集統(tǒng)計信息，并對一些特殊的大表寫JOB定時收集統(tǒng)計信息。如果是數(shù)據(jù)量很大的OLAP或者DSS系統(tǒng)，那么建議DBA自己寫JOB腳本來收集統(tǒng)計信息。

2)在導(dǎo)入大量數(shù)據(jù)后應(yīng)及時收集統(tǒng)計信息后才能進(jìn)行相關(guān)的后續(xù)業(yè)務(wù)處理(包括查詢和修改)，否則可能會由于實際數(shù)據(jù)量和統(tǒng)計信息里記錄的數(shù)據(jù)量存在巨大差異而導(dǎo)致CBO選擇錯誤的執(zhí)行計劃。

3)全局臨時表默認(rèn)不能收集統(tǒng)計信息，在生成執(zhí)行計劃時采用動態(tài)采樣比較好。

4)對于某些新上線或新遷移的系統(tǒng)，建議進(jìn)行全庫收集一次統(tǒng)計信息。

5)建議及時對包含日期型字段的表收集統(tǒng)計信息，避免出現(xiàn)謂詞越界現(xiàn)象。

6)統(tǒng)計信息收集作業(yè)采樣比例：對于Oracle11g及其以上的版本收集統(tǒng)計信息的采樣比例建議采用。DBMS_STATS.AUTO_SAMPLESIZE。如果是Oracle10g，那么建議將采樣比例的初始值設(shè)為30%，然后根據(jù)目標(biāo)SQL的實際執(zhí)行情況再做調(diào)整。

7)系統(tǒng)統(tǒng)計信息：如果系統(tǒng)的硬件環(huán)境發(fā)生了變化，那么建議要額外收集一次系統(tǒng)統(tǒng)計信息。

8)內(nèi)部對象統(tǒng)計信息：在明確診斷出系統(tǒng)已有的性能問題是因為X$表的內(nèi)部對象統(tǒng)計信息不準(zhǔn)引起的，這個時候就應(yīng)該收集X$表的內(nèi)部對象統(tǒng)計信息，其他情形就不要收集了。

9)表的大小、是否并行：若表很大，而系統(tǒng)空閑，則可以使用并行來收集統(tǒng)計信息。

10)表是否分區(qū)：若是分區(qū)表則建議收集全局的統(tǒng)計信息并且收集數(shù)據(jù)量有變更的單個分區(qū)(加GRANULARITY和參數(shù)并設(shè)置屬性INCREMENTAL)的統(tǒng)計信息。

11)是否收集索引的統(tǒng)計信息：一般情況下都應(yīng)該收集索引的統(tǒng)計信息。

12)是否收集直方圖。對直方圖統(tǒng)計信息的收集策略是對已經(jīng)存在直方圖統(tǒng)計信息的列才收集直方圖統(tǒng)計信息，而目標(biāo)列的初次直方圖統(tǒng)計信息則是由了解系統(tǒng)的DBA手工來收集直方圖。設(shè)置METHOD_OPT的值為“FORALLCOLUMNSSIZEREPEAT”。

13)是否可以并發(fā)收集統(tǒng)計信息：若系統(tǒng)有很多小表，則可以考慮并發(fā)收集統(tǒng)計信息。

14)系統(tǒng)的負(fù)載情況：在手動收集統(tǒng)計信息的時候需要注意系統(tǒng)的負(fù)載情況。

15)預(yù)估多久可以收集完成：對OLAP系統(tǒng)的大表而言，根據(jù)平時收集統(tǒng)計信息的經(jīng)驗要預(yù)估出收集統(tǒng)計信息要花費(fèi)多長的時間。

16)基于數(shù)據(jù)庫、SCHEMA或是表級別：根據(jù)情況判斷是否有必要在數(shù)據(jù)庫或SCHEMA級別來收集統(tǒng)計信息。

17)是否需要收集擴(kuò)展列的統(tǒng)計信息。如果表中的數(shù)據(jù)傾斜度較大，那么收集直方圖能最大程度地幫助優(yōu)化器計算出準(zhǔn)確的Cardinality，從而避免產(chǎn)生差的執(zhí)行計劃；再進(jìn)一步，如果存在傾斜的多個列共同構(gòu)成了Predicate里的等值連接且這些列間存在較強(qiáng)的列相關(guān)性的話，那么生成帶有直方圖的多列統(tǒng)計信息是一個上佳的選擇，能夠最大程度地幫助優(yōu)化器準(zhǔn)確預(yù)測出Cardinality。

18)是否設(shè)置NO_INVALIDATE為FALSE。該選項有TRUE、FALSE和DBMS_STATS.AUTO_INVALIDATE這3個值。如果取值為TRUE，那么表示收集統(tǒng)計信息后不進(jìn)行游標(biāo)失效動作，原有的SharedCursor保持原有狀態(tài)。如果取值為FALSE，那么表示將統(tǒng)計信息對象相關(guān)的所有Cursor全部失效。如果設(shè)置為AUTO_NVALIDATE，那么Oracle自己決定SharedCursor失效動作，當(dāng)SQL再次執(zhí)行時間距離上次收集統(tǒng)計信息的時間超過5h(隱含參數(shù)“_OPTIMIZER_INVALlDATION_PERIOD”決定)則對SQL重新做硬解析。AUTO_INVALIDATE為默認(rèn)選項。有些DBA在收集統(tǒng)計信息時，沒有使用NO_INVALIDATE=＞FALSE選項，所以，即使收集了統(tǒng)計信息，執(zhí)行計劃也不會立即改變。

19)對于OLTP類型的數(shù)據(jù)庫，需要特別關(guān)注DML比較頻繁的以及數(shù)據(jù)加載比較大的表及分區(qū)表。

20)檢查是否有臨近統(tǒng)計信息收集窗口的數(shù)據(jù)加載工作，如果有，是否能在數(shù)據(jù)庫統(tǒng)計信息的窗口時間完成，如果不能在窗口時間完成，那么應(yīng)該針對這段時間加載的數(shù)據(jù)，特別是大量的數(shù)據(jù)，在相關(guān)加載腳本完成之后，加入統(tǒng)計信息的收集。

21)如果加載數(shù)據(jù)量比較大，并且是分區(qū)表，每個分區(qū)的業(yè)務(wù)數(shù)據(jù)呈現(xiàn)的是均勻的，在Oracle11g可以考慮采用DBMS_STATS.COPY_TABLE_STATS先把統(tǒng)計信息做個快速的設(shè)置，然后，再收集該分區(qū)的統(tǒng)計信息。

其實，上述幾點是沒有一個普適性的標(biāo)準(zhǔn)答案的，因為不同的系統(tǒng)其數(shù)據(jù)量、數(shù)據(jù)分布情況都不盡相同，甚至可能會有很大的區(qū)別，所以適合于某套系統(tǒng)的統(tǒng)計信息收集策略并不一定能適用于另外一套系統(tǒng)。收集統(tǒng)計信息總的原則就是量體裁衣，即要找到適合自己系統(tǒng)的統(tǒng)計信息收集策略，用盡量小的代價收集到能穩(wěn)定跑出正確執(zhí)行計劃的統(tǒng)計信息即可，也就是說收集到的統(tǒng)計信息不一定要特別準(zhǔn)，只要具備代表性，能穩(wěn)定跑出正確的執(zhí)行計劃就可以了。

5.

等待事件的分類?常見等待事件?正確答案：Oracle的等待事件主要可以分為兩類：空閑(Idle)等待事件和非空閑(Non-Idle)等待事件。

1)空閑等待事件是指Oracle正等待某種工作，在診斷和優(yōu)化數(shù)據(jù)庫的時候，不用過多注意這部分事件。

2)非空閑等待事件專門針對Oracle的活動，是指數(shù)據(jù)庫任務(wù)或應(yīng)用運(yùn)行過程中發(fā)生的等待，這些等待事件是在調(diào)整數(shù)據(jù)庫的時候需要關(guān)注與研究的。

通過如下的SQL語句可以查詢等待事件的類型：

一些常見的、重要的等待事件如下：

(1)數(shù)據(jù)文件I/O相關(guān)的等待事件

dbfilesequentialread、dbfilescatteredread、dbfileparallelread、directpathread、directpathwrite。

(2)控制文件I/O相關(guān)的等待事件

controlfileparallelwrite、controlfileseauentialread、controlfilesinglewrite。

(3)Redo日志文件I/O相關(guān)的等待事件

logfileparallelwrite、logfilesync、logfilesequentialread、logfilesinglewrite、switchlogfilecommand、logfileswitchcompletion、logfileswitch(clearinglogfile)、logfileswitch(checkpointincomplete)、logswitch/archive、logfileswitch(archivingneeded)。

(4)高速緩存區(qū)I/O相關(guān)的等待事件

dbfileparallelwrite、dbfilesinglewrite、writecompletewaits、freebufferwaits。

下表列出一些常見等待事件用以拋磚引玉，實際的數(shù)據(jù)庫管理中需要掌握和了解的等待事件非常多，也比較復(fù)雜，只需要記住一些常見的面試知識點，其他的等待事件需要在工作中慢慢積累。

除了上表中列舉出來的等待事件還有很多其他常見的等待事件，這里就不再列舉了，讀者可以關(guān)注作者的微信公眾號或博客，里面會有所有等待事件的詳細(xì)介紹。

6.

ROWID和ROWNUM有什么區(qū)別?正確答案：Oracle有兩個著名的偽列ROWID和ROWNUM，下面分別來介紹它們。

(1)ROWID

ROWID是一個偽列，既然是偽列，那么這個列就不是用戶定義，而是系統(tǒng)自己給加上的。對每個表都有一個ROWID的偽列，但是表中并不物理存儲ROWID列的值。不過可以像使用其他列那樣使用它，但是不能刪除該列，也不能對該列的值進(jìn)行修改、插入。

ROWID對訪問一個表中的給定的行提供了最快的訪問方法，通過ROWID可以直接定位到相應(yīng)的數(shù)據(jù)塊上，然后將其讀到內(nèi)存。當(dāng)創(chuàng)建一個索引時，該索引不但存儲索引列的值，而且也存儲索引值所對應(yīng)的行的ROWID，這樣通過索引就可以快速找到相應(yīng)行的ROWID，通過該ROWID，就可以迅速將數(shù)據(jù)查詢出來。這也就是在使用索引查詢時，速度比較快的原因。

一般來說，當(dāng)表中的行確定后，ROWID就不會發(fā)生變化，一旦一行數(shù)據(jù)插入數(shù)據(jù)庫，ROWID在該行的生命周期內(nèi)是唯一的，即使該行產(chǎn)生行遷移，行的ROWID也不會改變，UPDATE不會改變ROWID，INSERT更不會。從ROWID定義可知，只有當(dāng)數(shù)據(jù)行的物理位置改變時才會導(dǎo)致ROWID改變，所以，只需要關(guān)心那些會導(dǎo)致數(shù)據(jù)物理位置變化的操作即可。

ROWID可以分為以下幾種類型：

1)物理ROWID：存儲堆組織表、表簇、表分區(qū)、和索引分區(qū)中的行地址。

2)邏輯ROWID：存儲索引組織表中的行地址。

3)外部ROWID：是外來表(如通過網(wǎng)關(guān)訪問的DB2表)中的標(biāo)識符。它們不是標(biāo)準(zhǔn)的Oracle數(shù)據(jù)庫ROWID。

有一種數(shù)據(jù)類型稱為通用ROWID或UROWID，支持各種ROWID。

當(dāng)如下情況發(fā)生時，ROWID將發(fā)生改變，即當(dāng)數(shù)據(jù)遷移到其他塊的時候，ROWID就會改變：

1)對一個表做表空間的移動或重建后。

2)對一個表進(jìn)行了exp/imp或expdp/impdp后。

3)MOVE、FLASHBACKTABLE、修改分區(qū)鍵值到另一個分區(qū)、分區(qū)表的分區(qū)數(shù)據(jù)轉(zhuǎn)移到其他分區(qū)、SHRINKTABLE等。

通過DBMS_ROWID可以獲取文件號、塊號等信息，如下：

(2)ROWNUM

ROWNUM是一個偽列，不是真正的列，在表中并不真實存在，它是Oracle數(shù)據(jù)庫從數(shù)據(jù)文件或緩沖區(qū)中讀取數(shù)據(jù)的順序。切勿理解成記錄的行號(很多人一直這樣認(rèn)為的)，例如想查詢第二行記錄按下面的方法是查詢不到的：

ROWNUM主要應(yīng)用于Top-N查詢中。

7.

Oracle健康檢查有哪些方面?正確答案：要想對數(shù)據(jù)庫進(jìn)行全面檢查，內(nèi)容比較多，下面列舉部分檢查項目：

1)數(shù)據(jù)庫的實例是否運(yùn)行，最近是否有自動重啟現(xiàn)象。

2)ASM實例是否正常運(yùn)行，剩余ASM磁盤空間有多大。

3)數(shù)據(jù)庫的參數(shù)是否正常，數(shù)據(jù)庫的參數(shù)近期是否被修改過。

4)數(shù)據(jù)庫的表空間大小，是否有表空間快滿了，表空間增長是否過快(系統(tǒng)表空間是否增長過快)。

5)是否有業(yè)務(wù)表創(chuàng)建在了SYSTEM表空間上。審計表是否在SYSTEM表空間上。

6)RMAN備份是否過期，備份是否可用，是否有控制文件的備份。

7)數(shù)據(jù)庫JOB是否有運(yùn)行錯誤。

8)數(shù)據(jù)庫的告警日志是否有異常告警，例如ORA-4030、ORA-4031、ORA-60、ORA-600、ORA-01555等。

9)數(shù)據(jù)庫歸檔空間、閃回恢復(fù)區(qū)是否足夠。

10)是否有非常耗費(fèi)資源的SQL曾經(jīng)運(yùn)行過，系統(tǒng)是否有VERSIONCOUNT過高的SQL。

11)DG、OGG是否運(yùn)行正常，歸檔日