Oracle程序員面試分類模擬19

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

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

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

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

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

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

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

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

2.

如何查詢表的歷史統(tǒng)計信息正確答案：從Oracle10g開始，當收集表的統(tǒng)計信息的時候，舊的統(tǒng)計數據被保留，如果因為新的統(tǒng)計信息而出現性能問題，舊的統(tǒng)計信息就可以被恢復。歷史統(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)計信息的時間，但是不能查詢到行數，所以需要結合基表來查詢，查詢的SQL語句如下：

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

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

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

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

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

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

3.

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

開啟方式為：

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

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

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

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

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

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

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

4.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

5.

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

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

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

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

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

(1)數據文件I/O相關的等待事件

dbfilesequentialread、dbfilescatteredread、dbfileparallelread、directpathread、directpathwrite。

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

controlfileparallelwrite、controlfileseauentialread、controlfilesinglewrite。

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

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

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

dbfileparallelwrite、dbfilesinglewrite、writecompletewaits、freebufferwaits。

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

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

6.

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

(1)ROWID

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

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

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

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

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

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

3)外部ROWID：是外來表(如通過網關訪問的DB2表)中的標識符。它們不是標準的Oracle數據庫ROWID。

有一種數據類型稱為通用ROWID或UROWID，支持各種ROWID。

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

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

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

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

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

(2)ROWNUM

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

ROWNUM主要應用于Top-N查詢中。

7.

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

1)數據庫的實例是否運行，最近是否有自動重啟現象。

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

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

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

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

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

7)數據庫JOB是否有運行錯誤。

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

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

10)是否有非常耗費資源的SQL曾經運行過，系統(tǒng)是否有VERSIONCOUNT過高的SQL。

11)DG、OGG是否運行正常，歸檔日