數(shù)據(jù)庫程序員面試分類真題13

數(shù)據(jù)庫程序員面試分類真題13簡答題1.

如何列舉某個用戶下所有表的注釋及列的注釋?正確答案：可以使用DBA_TAB_COMMENTS視圖來查詢表的注釋，使用DBA_COL_COMMENTS視圖來查（江南博哥）詢列的注釋。某個用戶下所有表的注釋：

SELECTD.OWNER,D.TABLE_NAME,D.COMMENTSFROMDBA_TAB_COMMENTSDWHERED.OWNER='LHR'ANDD.COMMENTSISNOTNULL;

其結果如下：

某個用戶下某個表的所有列的注釋：

SELECTD.OWNER,D.TABLE_NAME,D.COLUMN_NAME,D.COMMENTS

FROMDBA_COL_COMMENTSDWHERED.OWNER='LHR'

ANDD.TABLE_NAME='CMMND_INFO_HSTRY'ANDD.COMMENTSISNOTNULL;

其結果如下：

2.

Oracle10g和11g告警日志文件的位置在哪里?正確答案：告警日志文件有兩種類型，一種是純文本格式的，另外一種是xml文件格式的，平時查看最多的是純文本格式的告警日志。告警日志的內容包含消息和錯誤的類型，ORA-600內部錯誤，ORA-1578塊損壞錯誤，ORA-12012作業(yè)隊列錯誤，實例啟動關閉、恢復等信息，特定的DDL命令，影響表空間、數(shù)據(jù)文件及回滾段的命令，可持續(xù)的命令被掛起，LGWR不能寫入日志文件，歸檔進程啟動信息、調度進程的相關信息和動態(tài)參數(shù)的修改信息等。

無論是Oracle10g還是11g，其告警日志的位置都可以由參數(shù)BACKGROUND_DUMP_DEST來查詢，只不過在Oracle11g中位置有所變化。在Oracle10g中，告警日志一般在$ORACLE_BASE/admin/$ORACLE_SID/bdump目錄下：

在Oracle11g中，告警日志一般在$ORACLE_BASE/diag/rdbms/$DBNAME/$ORACLE_SID/traca目錄下：

從Oracle開始，參數(shù)BACKGROUND_DUMP_DEST已廢棄，告警日志的位置應該由以下SQL來查詢：

SQL＞SELECTd.VALUEFROMV$DIAG_INFOdWHEREd.NAME='DiagTrace';

VALUE

--------------------------------------------

/u01/app/oraele/diag/rdbms/lhrdb/lhrdb/trace

需要注意的是，該SQL也適用于Oracle11g的版本。

3.

若告警日志文件過大，則如何有效管理告警日志文件?正確答案：由于告警日志按時間的先后順序不斷累積存儲，久而久之，勢必造成告警日志的過大，難以維護，查找相關的信息也不方便。另外，若告警日志超大，也會嚴重影響系統(tǒng)的性能。這里提供3種辦法來管理告警日志：①使用外部表方式來管理告警日志將大大簡化維護工作量，也可以更直觀地獲取所需的信息；②視圖V$DIAG_ALERT_EXT對應的基表里存儲了告警日志的內容，可以根據(jù)該視圖將告警日志的內容存儲在歷史表中；③利用SHELL腳本定時將告警日志進行備份，防止告警日志過大而影響系統(tǒng)性能。

4.

數(shù)據(jù)庫處于RESTRICT、QUIESCE和SUSPEND狀態(tài)的區(qū)別是什么?正確答案：在Oracle中，數(shù)據(jù)庫可以有3種限制性狀態(tài)，見下表。

需要注意的是，可以使用“SELECT*FROMSESSION_ROLES;”查詢當前用戶具有的角色。

5.

數(shù)據(jù)庫的啟動經歷幾個過程?正確答案：Oracle數(shù)據(jù)庫的啟動，嚴格來說應該是實例的啟動，數(shù)據(jù)庫僅僅是在實例啟動后進行裝載。Oracle數(shù)據(jù)庫啟動的過程被劃分為幾個不同的步驟，在不同的啟動過程中，可以對其實現(xiàn)不同的操作。

啟動命令：STARTUP[FORCE][RESTRICT][PFILE=...][NOMOUNT][MOUNT][OPEN]

啟動過程：NOMOUNT---＞MOUNT---＞OPEN，詳細情況見下表。

6.

關閉數(shù)據(jù)庫有幾種方式?正確答案：數(shù)據(jù)庫的關閉也有一些參數(shù)需要注意，關閉命令為

SHUTDOWNABORT|IMMEDIATE|TRANSACTIONAL|NORMAL

其中，SHUTDOWN不帶參數(shù)將默認為NORMAL。一般情況下，關閉數(shù)據(jù)庫都使用“SHUTDOWNIMMEDIATE”命令。這幾個參數(shù)的區(qū)別見下表。SHUTDOWNNORMALTRANSACTIONALIMMEDIATEABORT是否允許新連接NNNN是否允許新事務YNNN是否等待所有來提交事務完成提交YYNN等待所有會話斷開連接YNNN強制檢查點YYYN實例恢復NNNY告警日志Shuttingdowninstance(normal)Shuttingdowninstance(transactional)Shuttingdowninstance(immediate)Shuttingdowninstance(abort)

1)NORMAL表示不準許新的連接，等待當前所有連接到數(shù)據(jù)庫系統(tǒng)的用戶都斷開連接(DISCONNECT)，強制檢查點并關閉文件，下次啟動不需要實例恢復。

2)TRANSACTIONALE表示不準許新的連接，不允許開啟新事務，數(shù)據(jù)庫等待所有事務完成后斷開當前連接到數(shù)據(jù)庫的所有用戶，強制檢查點并關閉文件，下次啟動不需要實例恢復。

3)IMMEDIATE表示不準許新的連接，不允許開啟新事務，任何未提交的事務都進行回滾。數(shù)據(jù)庫顯式回滾活動的事務并斷開所有當前連接到數(shù)據(jù)庫的用戶，強制檢查點并關閉文件，下次啟動不需要實例恢復。

4)ABORT表示不準許新的連接，不允許開啟新事務，任何未提交的事務不進行回滾。數(shù)據(jù)庫立刻中斷所有sQL的執(zhí)行，并斷開所有用戶的連接。不做強制檢查點，下次啟動需要做實例恢復。

7.

索引包括那幾個特性?正確答案：一般來說索引有3大特性，即索引高度比較低、索引存儲列值及索引本身有序，對這3大特性的應用見下表。索引特性帶來的優(yōu)勢應用的常見SQL高度比較低索引高度低有利于索引范圍掃描，這也是通過索引可以非常迅速地從海量數(shù)據(jù)中獲取少量數(shù)據(jù)的原因。索引的高度越高，訪問索引需要讀取的數(shù)據(jù)塊數(shù)越多，效率越差SELECT*FROMTWHEREID=1;索引存儲列值由于索引比表一般要小得多，所以在通過索引本身就可以查找到所需要的數(shù)據(jù)的情況下，可以將表看成是一個“瘦表”或“小表”，無須索引回表讀這個過程(索引回表讀是非常耗費性能的)，這樣訪問路徑就會大大減少。利用該特性可以優(yōu)化的SQL包括COUNT(*)、SUM(索引列)等聚合語句SELECTCOUNT(*)FROMT;SELECTSUM(OBJECT_ID)FROMT;索引本身有序能夠利用索引本身已有的排序特性來消除目標SQL的排序操作，從而優(yōu)化ORDERBY等排序語句SELECT*FROMTWHEREOBJECT_ID＞2ORDERBYOBJECT_ID;能夠用索引有序的特性在葉子塊的最左邊或最右邊找到最小和最大值，從而優(yōu)化類型MAX、MIN語句SELECTMAX(OBJECT_ID)FROMT;

另外，需要特別注意的是，索引不存儲空值。[考點]索引

8.

如何預估即將創(chuàng)建索引的大小?正確答案：如果當前表大小是1TB，那么在某一列上創(chuàng)建索引的話索引大概占用多大的空間?對于這個問題，Oracle提供了2種可以預估將要創(chuàng)建的索引大小的辦法：

1)利用系統(tǒng)包DBMS_SPACE.CREATE_INDEX_COST直接得到。利用DBMS_SPACE.CREATE_TABLE_COST可以獲得將要創(chuàng)建的表的大小。

2)利用Oracle11g新特性NOTERAISEDWHENEXPLAINPLANFORCREATEINDEX。[考點]索引

9.

如何監(jiān)控索引的使用狀況?正確答案：在開發(fā)應用程序時，可能會建立很多索引，那么這些索引的使用到底怎么樣，是否有些索引一直都沒有用到過，在這種情況下就需要對這些索引進行監(jiān)控，以便確定它們的使用情況，并為是否可以清除它們給出依據(jù)。

監(jiān)控索引有兩種方式：

(1)直接監(jiān)控索引的使用情況

1)設置所要監(jiān)控的索引：ALTERINDEXIDX_T_XXMONITORINGUSAGE;

2)查看該索引有沒有被使用：SELECT*FROMV$OBJECT_USAGE;

3)關閉監(jiān)控：ALTERINDEXIDX_T_XXNOMONITORINGUSAGE;

查詢V$OBJECT_USAGE就可以知道數(shù)據(jù)庫對索引的使用情況。通過一段時間的監(jiān)控，就可以確定哪些是無用的索引。另外，為了避免使用V$OBJECT_USAGE只能查詢到當前用戶下索引的監(jiān)控情況，可以使用如下語句查詢數(shù)據(jù)庫中所有被監(jiān)控索引的使用情況：

SELECTU.NAMEOWNER,IO.NAMEINDEX_NAME,T.NAMETABLE_NAME,DECODE(BITAND(I.FLAGS,65536),0,'NO','YES')MONITORING,

DECODE(BITAND(OU.FLAGS,1),0,'NO','YES')USED,

OU.START_MONITORINGSTART_MONITORING,

OU.END_MONITORINGEND_MONITORING

FROMSYS.USERSU,

SYS.OBJ$IO,

SYS.OBJ$

T.

SYS.IND$I,

SYS.OBJECT_USAGEOU

WHEREI.OBJ#=OU.OBJ#

ANDIO.OBJ#=OU.OBJ#

ANDT.OBJ#=I.BO#

ANDU.USER#=IO.OWNER#;

(2)通過查看歷史的執(zhí)行計劃，分析索引的使用情況

可以從視圖DBA_HIST_SQL_PLAN中獲取到數(shù)據(jù)庫中所有索引的掃描次數(shù)情況，然后根據(jù)掃描次數(shù)和開發(fā)人員溝通是否需要保留索引。[考點]索引

10.

哪些操作會導致索引失效?正確答案：當某些操作導致數(shù)據(jù)行的ROWID改變，索引就會完全失效。可以分普通表和分區(qū)表來討論哪些操作將導致索引失效。

1)普通表索引失效的情形如下：

①手動置索引無效：ALTERINDEXIND_OBJECT_IDUNUSABLE;。

②如果對表進行MOVE操作(包含移動表空間和壓縮操作)或在線重定義表后，那么該表上所有的索引狀態(tài)會變?yōu)閁NUSABLE。MOVE操作的SQL語句為ALTERTABLETTMOVE;。

③SQL*Loader加載數(shù)據(jù)。

在SQL*Loader加載過程中會維護索引，由于數(shù)據(jù)量比較大，在SQL*Loader加載過程中出現(xiàn)異常情況，也會導致Oracle來不及維護索引，導致索引處于失效狀態(tài)，影響查詢和加載。異常情況主要有在加載過程中殺掉SQL*Loader進程、重啟或表空間不足等。

2)分區(qū)表索引失效的情形如下：

①對分區(qū)表的某個含有數(shù)據(jù)的分區(qū)執(zhí)行了TRUNCATE、DROP操作可以導致該分區(qū)表的全局索引失效，而分區(qū)索引依然有效，如果操作的分區(qū)沒有數(shù)據(jù)，那么不會影響索引的狀態(tài)。需要注意的是，對分區(qū)表的ADD操作對分區(qū)索引和全局索引沒有影響。

②執(zhí)行EXCHANGE操作后，全局索引和分區(qū)索引都會被無條件地置為UNUSABLE(無論分區(qū)是否含有數(shù)據(jù))。但是，若包含INCLUDINGINDEXES子句(默認情況下為EXCLUDINGINDEXES)，則全局索引會失效，而分區(qū)索引依然有效。

③如果執(zhí)行SPLIT的目標分區(qū)含有數(shù)據(jù)，那么在執(zhí)行SPLIT操作后，全局索引和分區(qū)索引都會被置為UNUSABLE。如果執(zhí)行SPLIT的目標分區(qū)沒有數(shù)據(jù)，那么不會影響索引的狀態(tài)。

④對分區(qū)表執(zhí)行MOVE操作后，全局索引和分區(qū)索引都會被置于無效狀態(tài)。

⑤手動置其無效：ALTERINDEXIND_OBJECT_IDUNUSABLE;。

對于分區(qū)表而言，除了ADD操作之外，TRUNCATE、DROP、EXCHANGE和SPLIT操作均會導致全局索引失效，但是可以加上UPDATEGLOBALINDEXES子句讓全局索引不失效。重建分區(qū)索引的命令為ALTERINDEXIDX_RANG_LHRREBUILDPARTITIONP1;。[考點]索引

11.

聚簇因子(ClusteringFactor)是什么?正確答案：Oracle數(shù)據(jù)庫中最普通、最為常用的即為堆表，堆表的數(shù)據(jù)存儲方式為無序存儲，當對數(shù)據(jù)進行檢索的時候，非常消耗資源，這個時候就可以為表創(chuàng)建索引了。在索引中，數(shù)據(jù)是按照一定的順序排列起來的。當新建或重建索引時，索引列上的順序是有序的，而表上的順序是無序的，這樣就存在了差異，即表現(xiàn)為聚簇因子(ChasteringFactor，CF)，也稱為群集因子或集群因子等，本書統(tǒng)一稱為聚簇因子。聚簇因子值的大小對CBO判斷是否選擇相關的索引起著至關重要的作用。

在Oracle數(shù)據(jù)庫中，聚簇因子是指按照索引鍵值排序的索引行和存儲于對應表中數(shù)據(jù)行的存儲順序的相似程度，也就是說，表中數(shù)據(jù)的存儲順序和某些索引字段順序的符合程度。CF是基于表上索引列上的一個值，每一個索引都有一個CF值。

Oracle按照索引塊所存儲的ROWID來標識相鄰索引記錄在表塊中是否為相同塊。好的CF值接近于表上的塊數(shù)，而差的CF值則接近于表上的行數(shù)。CF值越小，相似度越高，CF值越大，相似度越低。如果CF的值接近塊數(shù)，那么說明表的存儲和索引存儲排序接近，也就是說表中的記錄很有序，這樣在做INDEXRANGESCAN的時候，讀取少量的數(shù)據(jù)塊就能得到想要的數(shù)據(jù)，代價比較小。如果CF值接近表記錄數(shù)，那么說明表的存儲和索引排序差異很大，在做INDEXRANGESCAN的時候，由于表記錄分散，所以會額外讀取多個塊，代價較高。

由于聚簇因子高的索引走索引范圍掃描時比相同條件下聚簇因子低的索引要耗費更多的物理I/O，所以聚簇因子高的索引走索引范圍掃描的成本會比相同條件下聚簇因子低的索引走索引范圍掃描的成本高。Oracle選擇索引范圍掃描的成本可以近似看作是和聚簇因子成正比，因此，聚簇因子值的大小實際上對CBO判斷是否走相關的索引起著至關重要的作用。其實，聚簇因子決定著索引回表讀的開銷。在Oracle數(shù)據(jù)庫中，能夠降低目標索引的聚簇因子的唯一方法就是對表中數(shù)據(jù)按照目標索引的索引鍵值排序后重新存儲。需要注意的是，這種方法可能會同時增加該表上存在的其他索引的聚簇因子的值。

可以通過如下的命令顯式地設置聚簇因子的值：

EXECDBMS_STATS.SET_INDEX_STATS(OWNNAME=＞'LHR',INDNAME=＞'IND2',CLSTFCT=＞400000000,NO_INVALIDATE=＞FALSE);

CF值可以通過查詢視圖DBA_INDEXES中的CLUSTERING_FACTOR列來獲取。下邊的SQL是查詢索引的相關信息，通過視圖DBA_INDEXES、DBA_OBJECTS和DBA_TABLES關聯(lián)得到，可以查詢當前索引的大小、行數(shù)、創(chuàng)建日期、索引高度和聚簇因子等信息。[考點]索引

12.

什么是索引的選擇性(IndexSelectivity)?正確答案：索引的選擇性(Indexselectivity，索引的選擇度或索引的選擇率)是指索引列中不同值的記錄數(shù)與表中總記錄數(shù)的比值。索引的選擇性的取值范圍是[0,1]。例如，某個表的記錄數(shù)是1000條，而該表的索引列的值只有900個不同的值(有100個是相同或者空)，所以，該列索引的選擇性為900/1000=0.9。對于索引的選擇性，值越高表示該列索引的效率也就越高。可以使用如下的SQL來計算索引的選擇性：

SELECTCOUNT(DISTINCTNAME)/COUNT(*)FROMTB_A;

這種方法的優(yōu)點是在創(chuàng)建索引前就能評估索引的選擇性。當索引被收集了最新的統(tǒng)計信息時，可以使用如下的SQL語句查詢索引的選擇性：

SELECTINDEX_NAME,DISTINCT_KEYS/NUM_ROWSSELECTIVEITYFROMDBA_INDEXES;

毋庸置疑，主鍵的選擇性為1。選擇性越接近1，那么該索引就越好。[考點]索引

13.

哪幾種情況不能使用索引?正確答案：有多種原因會導致索引不能被使用。首要的原因就是統(tǒng)計信息不準，第二原因就是索引的選擇度不高，使用索引比使用全表掃描效率更差。還有一個比較常見的原因，就是對索引列進行了函數(shù)、算術運算或其他表達式等操作，或出現(xiàn)隱式類型轉換，導致無法使用索引。還有很多其他原因會導致不能使用索引，這個問題在MOS(MOS即MyOracleSupport)“文檔1549181.1為何在查詢中索引未被使用”中有非常詳細的解釋。詳細情況見下表。分類原因解釋快速檢查表上是甭存在索引檢查那些認為應該通過索引訪問的表上是否真的創(chuàng)建了索引。那些索引可能已經被刪掉或者在創(chuàng)建的時候就失敗了。例如，一種可能的場景是，在對表做導入操作后，由于軟件或人為錯誤造成索引沒有被創(chuàng)建。通過DBA_INDEXES視圖可以檢查索引是否存在索引是否應該被使用Oracle不會僅僅因為有索引存在就一定要使用索引。如果一個查詢需要檢索出這個表里所有的記錄，那么只需要單獨訪問表的數(shù)據(jù)會更快。對所有的查詢而言，Oracle優(yōu)化器會基于統(tǒng)計信息來計算各種訪問路徑，包括索引，從而選出最優(yōu)的一條路徑索引本身的問題索引的索引列是否在WHERE條件中(PredicateList)對于單列索引而言，只有當索引列出現(xiàn)在查詢的WHERE條件中時，Oracle才能使用到索引。對于組合索引而言，如果索引的前置列沒有出現(xiàn)在WHERE條件中，而是用到了組合索引的其他索引列，那么這時候Oracle可能會選擇索引跳躍掃描(IndexSkipScan，INDEX_SS)或不會選擇索引掃描索引列是否用在連接謂詞中(JoinPredicates)如果索引列是連接謂詞的一部分，那么需要查看使用了哪種類型的連接方式?在兩張表連接中，且內表的目標列上建有索引時，只有NL連接才能有效地利用到該索引。SMJ即使相關列上建有索引，最多只能因索引的存在，避免數(shù)據(jù)排序過程。HJ由于須做HASH運算，索引的存在對數(shù)據(jù)查詢速度幾乎沒有影響連接順序(JoinOrder)是否允許使用索引查看連接順序(JoinOrder)是否允許使用相關索引。假設表A的ID列上有索引，表B的列ID上無索引，WHERE語句有A.ID=B.ID條件，并且查詢中沒有與A.ID相關的其他謂詞。在做NL連接時，表A作為外部表，先被訪問，由于連接機制原因，外部表的數(shù)據(jù)訪問方式是全表掃描，A.ID上的索引顯然是用不上索引列是否在IN或者多個OR語句中如果索引列在IN或OR子句中，那么查詢可能已經被轉化為不能使用索引的語句是否對索引列進行了函數(shù)、算術運算或其他表達式等操作應盡量避免在WHERE子句中對索引字段進行函數(shù)、算術運算或其他表達式等操作，因為這樣可能會使索引失效，查詢時要盡可能將操作移至等號右邊索引列是否出現(xiàn)了隱式類型轉換(ImplicitTypeConversion)如果進行比較的兩個值的數(shù)據(jù)類型不同，那么Oracle必須將其中一個值進行類型轉換使其能夠比較，這就是所謂的隱式類型轉換。通常當開發(fā)人員將數(shù)字存儲在字符列時會導致這種問題的產生。Oracle在運行時會在索引字符列使用TO_NUMBER函數(shù)強制轉化字符類型為數(shù)值類型。由于添加函數(shù)到索引列，導致索引不被使用。實際上，Oracle也只能這么做，類型轉換是一個應用程序設計因素。由于轉換是在每行都進行的，這會導致性能問題。一般情況下，當比較不同數(shù)據(jù)類型的數(shù)據(jù)時，Oracle自動地從復雜向簡單的數(shù)據(jù)類型轉換。所以，字符類型的字段值應該加上引號是否在語義(semantically)上無法使用索引出于對查詢整體成本的考慮，一個成本較低的執(zhí)行計劃中可能是無法使用索引的。某索引可能已經被考慮在某種連接排序及方法中，但是成本最低的那個執(zhí)行計劃中卻無法從“語義”角度使用該索引(續(xù))分類原因解釋索引本身的問題錯誤類型的索引掃描可以定義索引的排序順序為遞增或遞減。Oracle對待降序索引就好像它是基于函數(shù)的索引，因此與默認使用的升序的執(zhí)行計劃不同。通過查看執(zhí)行計劃是看不到使用升序或降序的，需要額外檢查視圖DBA_IND_COLUMNS的DESCEND列。如果系統(tǒng)中經常使用索引范圍掃描進行讀取數(shù)據(jù)(例如在WHERE子句中使用“BETWEENAND”語句或比較運算符“＞”“＜”“＞=”“＜=”等)，那么反向鍵索引將不會被使用，此時Oracle會選擇全表掃描。只有對反向鍵索引列進行“=”操作時，其反向鍵索引才會使用索引列是否可以為空除了聯(lián)合索引(即多列索引)和位圖索引外，其他索引都不存儲NULL值。只有至少有一個索引列有值，聯(lián)合索引才存儲空值。聯(lián)合索引中尾部的空值也會被存放在索引中。如果所有列的值都為空，這行將不會存儲在索引中。由于索引中缺乏NULL值，那么一些結果中可能會返回NULL值(例如，COUNT)的操作可能會被禁用索引。這是因為優(yōu)化器不能保證在單獨使用索引時可以獲得準確的信息。位圖索引允許存儲空值。因此，無論它們的結果可信與否，優(yōu)化器都會使用這些索引NLS_SORT是否設置為二進制(BINARY)如果NLS_SORT未設置為二進制，索引將不會被使用。這是因為索引是基于Key值的二進制順序來建立的(pre-sorted使用二進制值)。無論優(yōu)化器設置為何種方法，當NLS_SORT不是二進制時，將使用全表掃描是否使用的是不可見索引(InvisibleIndexes)從Oracle11gR1開始，可以創(chuàng)建不可見索引或將一個已經存在的索引標記為不可見。如果在SESSION或SYSTEM級別將參數(shù)OPTIMIZER_USE_INVISIBLE_INDEXES設置為TRUE(默認為FALSE，表示Oracle優(yōu)化器不會考慮不可見索引)，那么Oracle優(yōu)化器就會考慮不可見索引。無論該參數(shù)的值為TRUE還是FALSE，DML操作還是會維護這些不可見索引的優(yōu)化器和成本計算相關問題是否存在準確且合適的統(tǒng)計信息(Statistics)CBO依賴于準確的、最新的和完整的統(tǒng)計信息來確定一個特定查詢的最佳執(zhí)行計劃。如果使用CBO，需確保統(tǒng)計信息已經收集。如果沒有統(tǒng)計信息，CBO將使用預定義的統(tǒng)計信息，這樣很可能不會產生良好的計劃或讓應用程序使用索引。請注意，CBO會根據(jù)開銷(COST)來決定使用不同的索引。除了基本的表和索引的信息之外，如果在某些列上數(shù)據(jù)分布是不均勻的，那么還需要收集這些列的數(shù)據(jù)的分布情況，即直方圖。一般情況下，對象的數(shù)據(jù)或結構的改變會使以前的統(tǒng)計信息不準確，因此應該重新收集新的統(tǒng)計信息。例如，對表裝載了大量的數(shù)據(jù)后，需要收集新的統(tǒng)計信息。安裝新補丁集(Patchset)后，也建議重新收集統(tǒng)計信息。表訪問最佳效果是統(tǒng)計信息在相同版本的數(shù)據(jù)庫中生成的一個索引是否與其他的索引有相同的等級或者成本(Cost)對于相同開銷(COST)的索引，CBO會使用多種辦法將不同的索引區(qū)分開，如將索引名稱按字母順序排序，完全匹配的索引掃描會選擇更大的NDK(不同鍵值的個數(shù))的索引(不適用于快速全掃描)或選擇葉塊數(shù)量較少的索引索引的選擇度是否不高CBO會假定列數(shù)據(jù)不會傾斜，并均勻分布。如果數(shù)據(jù)分布不是這種情況，那么統(tǒng)計信息可能沒有反映真實情況，即使某些值的選擇度高，索引也會因為整個列的選擇度不高而不使用索引。如果是這種情況，那么應考慮采用直方圖記錄更準確的列的數(shù)據(jù)分布或者采用提示(Hint)在總體成本中，表掃描的成本是否占大部分通常來說，當使用索引的時候，需要再次檢索表本身來找到索引中不存在的字段的值(索引回表讀)，這個操作比檢索索引本身的開銷要大很多。由于優(yōu)化器是基于總體的成本來計算執(zhí)行計劃的，如果通過索引檢索表的成本很大，并且超過了某個閥值，優(yōu)化器就會考慮其他的訪問路徑。優(yōu)化器(Optimizer)使用聚簇因子(ClusteringFactor)來判斷若使用索引的話需要對表做多少次訪問，因此當索引的聚簇因子很大的時候Oracle會選擇全表掃描訪問空索引并不意味著比訪問有值的索引高效重組、TRUNCATE或DELETE操作不一定會影響SQL語句執(zhí)行的成本。需要注意的是，刪除操作并不會從對象中真正釋放空間。也就是說，DELETE操作不會重置對象的高水位。TRUNCATE操作會重黃高水位?？諌K的存在會使索引和表掃描的成本比實際應該的成本高參數(shù)設置是否正確某些參數(shù)的設置可能會影響索引的使用。比如在大多數(shù)情況下都建議使用DB_FILE_MULTIBLOCK_READ_COUNT和OPTIMIZER_INDEX_COST_ADJ的默認值。除非某些特定的操作有特定的建議，使用其他值會使索引的成本不現(xiàn)實地減少或變大，從而極大地降低查詢的性能其他問題是否存在遠程表(RemoteTable)通常遠程表不會使用索引。索引在分布式查詢中的使用依賴于被發(fā)送到遠程的查詢。CBO將評估遠程訪問的成本，并評估比較發(fā)送或者不發(fā)送索引的謂詞到遠程站點的成本。因此，CBO可以做出有關遠程表上使用索引的更加明智的決定。一種非常有效的方法就是，在遠程建立包含相關謂詞的視圖并強制使用索引，之后在本地查詢中使用這個視圖是否使用了并行執(zhí)行(PX)在并行執(zhí)行時索引的采用比在串行執(zhí)行(SerialExecution)時更加嚴格。一種快速檢測的方法就是禁用并行，然后查看該索引是否被使用。并行查詢將不會用到索引是否包含了子查詢的UPDATE語句在一些情況下，基于成本的考慮，索引沒有被使用是因為它依賴于一個子查詢返回的值。這種情況下，可以使用提示(Hint)來強制使用索引查詢是否使用了綁定變量CBO對LIKE或范圍謂詞的綁定變量不能產生準確的成本，或綁定變量窺探操作都可能會導致索引不被選擇查詢是否引用了帶有延遲約束的列如果一個表中的某一列上含有延遲約束(比如NOTNULL)并且這一列上有索引，那么不管這個約束當前是延遲狀態(tài)或者被顯式地設置為立即使用，Oracle都不會考慮使用這一列上的索引索引提示(Hint)是否不工作語法不正確、使用了RBO、Hint的對象名不正確、使用了非空索引列來計算行數(shù)等情況都可能導致索引提示不工作(續(xù))分類原因解釋其他問題索引列是否使用了前置通配符(%)在WHERE子句中，如果索引列所對應的值的第一個字符由通配符(WILDCARD)開始，索引將不被采用索引列是否使用了非等值連接符應盡量避免在WHERE子句中使用!=或＜＞操作符，否則引擎將放棄使用索引而進行全表掃描?？梢酝ㄟ^改寫為IN或UNIONALL來使用索引是否在WHERE子句中對索引列進行了ISNULL值判斷應盡量避免在WHERE子句中對字段進行ISNULL值判斷，否則將導致引擎放棄使用索引而進行全表掃描?？梢酝ㄟ^加偽列創(chuàng)建偽聯(lián)合索引來使得ISNULL使用索引查詢轉換失敗，走不了索引查詢轉換是非常復雜的過程，OracleCBO的查詢轉換有子查詢展開、視圖合并、星型轉換、連接謂詞推入、表擴展等。如果查詢轉換失敗，那么必將影響后續(xù)優(yōu)化器的一些操作，比如JPPD中JOIN謂詞無法推入視圖中，那么很可能視圖就無法選擇索引了。另外，查詢轉換有很多BUG，若觸發(fā)BUG則需要找到原因，比如設置隱含參數(shù)、fixcontrol，或者改寫SQL繞過BUG等是否使用了視圖或子查詢查詢涉及視圖或者子查詢時可能會被改寫，導致不使用索引(盡管該改寫的目標之一是擴展更多的訪問路徑)。這些改寫(Rewrite)一般來說都是合并(Merging)操作

建議讀者對每種類型的情況都做實驗，以便加深理解和印象。

14.

ISNULL如何用到索引?正確答案：ISNULL用于判斷某一列中的值是否為空。當ISNULL作為WHERE條件的時候，該列是不會用到索引的，但是可以加偽列創(chuàng)建偽聯(lián)合索引來使得ISNULL使用索引。[考點]索引

15.

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

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

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

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

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

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

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

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

c.如果字符串ABC在原字符串中位置不固定，那么可以通過改寫SQL進行優(yōu)化。改寫的方法主要是通過先使用子查詢查詢出需要的字段，然后在外層嵌套，這樣就可以使用到索引了。

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

16.

Hint是什么?正確答案：Oracle的Hint是用來提示Oracle的優(yōu)化器，用來選擇用戶期望的執(zhí)行計劃。Oracle推出了一個隱含參數(shù)“_OPTIMIZER_IGNORE_HINTS”，其取值為TRUE或FALSE，默認值是FALSE。Oracle可以通過將該隱含參數(shù)設置為TRUE，使得Oracle優(yōu)化器忽略語句中所有的Hint。顯然，Oracle提供此參數(shù)的目的是在不修改應用的前提下，忽略所有Hint，讓Oracle優(yōu)化器自己來選擇執(zhí)行路徑。[考點]Hint

17.

NOLOGGING是一種Hint嗎?正確答案：下面幾條SQL都是使用NOLOGGING時的錯誤用法：

INSERTINTOT1NOLOGGING;

INSERTINTOT1SELECT*FROMT2NOLOGGING;

INSERT/*+NOLOGGING*/INTOT1VALUES('0');

INSERT/*+NOLOGGING*/INTOT1SELECT*FROMT2;

DELETE/*+NOLOGGING*/FROMT1;

UPDATE/*+NOLOGGING*/T1SETA='1';

實際上，上述所有的SQL沒有一個能夠實現(xiàn)“不產生”日志的數(shù)據(jù)更改操作。事實上，NOLOGGING并不是Oracle的一個有效的Hint，而是一個SQL關鍵字，通常用于DDL語句中。這里NOLOGGING相當于給SELECT的表指定了一個別名為“NOLOGGING”。下面是NOLOGGING的一些正確用法：

CREATETABLET1NOLOGGINGASSELECT*FROMT2;

CREATEINDEXT1_IDXONT1(A)NOLOGGING;

ALTERINDEXT1_IDXREDUILDONLINENOLOGGING;

ALTERTABLET1NOLOGGING;

若面試官問如何強制一個SQL語句使用索引，此時就可以回答使用Hint，/*+INDEX(TABLEINDEX_NAME)*/來完成。[考點]Hint

18.

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

當DRWING_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(遠程表)。[考點]Hint

19.

Oracle數(shù)據(jù)庫中庫緩存(LibraryCache)的作用有哪些?正確答案：庫緩存是SGA中共享池(SharedPool)中的一塊內存區(qū)域，主要作用就是緩存執(zhí)行過的SQL語句和PL/SQL語句(例如存儲過程、函數(shù)、包、觸發(fā)器)及其所對應的解析樹(ParseTree)和執(zhí)行計劃等信息。當同樣的SQL語句和PL/SQL語句再次被執(zhí)行的時候就可以直接利用已經緩存在庫緩存中的那些相關對象而無須再次從頭開始解析，這樣就提高了這些SQL語句和PL/SQL語句在重復執(zhí)行時的執(zhí)行效率。庫緩存在SGA中的位置如下圖所示。

[考點]游標

20.

游標分為哪幾類?父游標和子游標的區(qū)別是什么?正確答案：游標(Cursor)是Oracle數(shù)據(jù)庫中SQL解析和執(zhí)行的載體，它可以分為共享游標(SharedCursor)和會話游標(SessionCursor)。共享游標是指緩存在庫緩存(LibraryCache)里的一種庫緩存對象，其實就是指緩存在庫緩存里的SQL語句和匿名PL/SQL塊所對應的庫緩存對象。共享游標是Oracle緩存在庫緩存中的幾十種庫緩存對象之一，它所對應的庫緩存對象句柄的Namespace屬性的值是CRSR(也就是Cursor的縮寫)。共享游標會存儲目標SQL的SQL文本、解析樹、該SQL所涉及的對象定義、該SQL所使用的綁定變量類型和長度，以及該SQL的執(zhí)行計劃等信息。共享游標可以細分為父游標(ParentCursor)和子游標(ChildCursor)，可以通過視圖V$SQLAREA來查看當前緩存在庫緩存(LibraryCache)中的父游標，而通過V$SQL來查看緩存在庫緩存中的子游標。Oracle設計這種嵌套的ParentCursor和ChildCursor并存的結構是為了能盡量減少對應的HashBucket中庫緩存對象句柄鏈表的長度。

Oracle中游標的分類如下圖所示。

父游標和子游標的對比見下表。

父游標子游標查詢視圖V$SQLAREAV$SQL存儲內容父游標存儲SQL文本(庫緩存對象句柄的屬性NAME中)；父游標的heap0中存儲著子游標的句柄地址子游標存儲解析樹(ParseTlree)及執(zhí)行計劃(ExecutionPlan，實際上存儲在庫緩存對象句柄的heap6中)，以及該SQL所使用的綁定變量的類型和長度庫緩存對象句柄的屬性NAME存儲內容存儲SQL文本空聯(lián)系1)父游標和子游標的結構是一樣的，它們都是以庫緩存對象句柄的方式緩存在厙緩存中，Namespace屬性的值均為CRSR2)由于子游標所對應的庫緩存對象句柄的NAME屬性值為空，所以，只能通過父游標才能找到相應的子游標3)任意一個經過解析的目標SQL一定會同時對應兩個共享游標，一個是父游標，另一個則是子游標[考點]游標

21.

會話游標的含義是什么?共享游標和會話游標的區(qū)別有哪些?正確答案：會話游標(SessionCursor)是當前會話(Session)解析和執(zhí)行SQL的載體，即會話游標用于在當前會話中解析和執(zhí)行SQL，會話游標是以哈希表的方式緩存在PGA中(共享游標是緩存在SGA的庫緩存里)。在目標SQL的執(zhí)行過程中，會話游標起承上啟下的作用。因為Oracle依靠會話游標來將目標SQL所涉及的數(shù)據(jù)從BufferCache的對應數(shù)據(jù)塊讀到PGA里，然后在PGA里做后續(xù)的排序、表連接等處理，最后將最終的處理結果返回給用戶，所以，會話游標是當前會話解析和執(zhí)行SQL的載體。

共享游標和會話游標的對比見下表。

共享游標會話游標緩存位置緩存在SGA中的共享池里的庫緩存中緩存在每個會話的PGA中共享共享游標在所有會話之間共享會話游標與會話是一一對應的，不同會話的會話游標之間不能共享，這是與共享游標的本質區(qū)別。生命周期共享游標無生命周期，會進行緩存會話游標是有生命周期的，每個會話游標在使用的過程中都至少會經歷一次Open、Parse、Bind、Execute、Fetch和Close中的一個或多個階段。Oracle會根據(jù)參數(shù)SESSION_CACHED_CURSORS的值來決定是否將已經用過的會話游標緩存在對應會話的PGA中聯(lián)系1)會話游標足以哈希表的方式緩存在PGA中，意味著Oracle會通過棚關的哈希運算來存儲和訪問在當前會話的PGA中的對應會話游標。這種訪問機制和共享游標是一樣的，可以簡單地認為Oracle是根據(jù)目標SQL的SQL文本的哈希值去PGA中的相應HashBucket中找匹配的會話游標。由于在緩存會話游標的哈希表的對應HashBucket中，Oracle會存儲目標SQL對應的父游標的庫緩存對象句柄地址，所以，Oracle可以通過會話游標找到對應的父游標，進而就可以找到對應子游標中目標SQL的解析樹和執(zhí)行計劃，然后Oracle就可以重用目標SQL的解析樹和執(zhí)行計劃來執(zhí)行SQL語句了2)一個會話游標只能對應一個共享游標，而一個共享游標卻可以同時對應多個會話游標[考點]游標

22.

會話游標分為哪幾類?正確答案：會話游標的詳細分類參考下表。

在上表中需要注意的是，動態(tài)游標是Oracle數(shù)據(jù)庫中最靈活的一種會話游標，它的靈活性表現(xiàn)在：①動態(tài)游標的定義方式非常靈活，它可以有多種定義方式；②動態(tài)游標可以作為存儲過程的輸入參數(shù)和函數(shù)的輸出參數(shù)。上表中的各種游標希望讀者可以通過做大量的練習題來掌握，畢竟游標是存儲過程開發(fā)過程中必不可少的內容。[考點]游標

23.

會話游標有哪些屬性?正確答案：會話游標有4個屬性，見下表。屬性類型簡介適用對象適用SQLSQL%FOUND布爾型最近的FETCH是否提取到數(shù)據(jù)，表示一條SQL語句被執(zhí)行成功后受其影響而改變的記錄數(shù)是否大于或等于1，若是則賦值為TRUE，否則為FALSE。在一條DML語句被執(zhí)行前，SQL%FOUND的值是NULL隱式游標、顯式游標INSERT、DELETE、UPDATE、SELECT...INTO...SQL%NOTFOUND布爾型最近的FETCH是否沒有提取到數(shù)據(jù)，表示一條SQL語句被執(zhí)行成功后受其影響而改變的記錄數(shù)是否為0，若是則賦值為TRUE，否則為FALSE。在一條DML語句被執(zhí)行前，SQL%NOTFOUND的值是NULL隱式游標、顯式游標SQL%ROWCOUNT數(shù)值型表示最近的一條SQL語句成功執(zhí)行后受其影響而改變的記錄的數(shù)最，后續(xù)執(zhí)行的SQL會覆蓋SQL%ROWCOUNT的值隱式游標、顯式游標SQL%ISOPEN布爾型游標是否打開，當游標打開時返回TRUE。對于隱式游標而言，SQL%ISOPEN的值永遠是FALSE顯式游標

當執(zhí)行一條DML語句后，DML語句的結果保存在這4個游標屬性中，這些屬性用于控制程序流程或者了解程序的狀態(tài)。當運行DML語句時，PL/SQL打開一個內建游標并處理結果。在這些屬性中，SQL%FOUND和SOL%NOTFOUND是布爾值，SQL%ROWCOUNT是整數(shù)值。需要注意的是，若游標屬于隱式游標，則在PL/SOL中可以直接使用上表中的屬性；若游標屬于顯式游標，則上表中的屬性里“SQL%”需要替換為自定義顯式游標的名稱。上表中的這4個屬性對于動態(tài)游標依然適用。[考點]游標

24.

硬解析、軟解析、軟軟解析的區(qū)別是什么?正確答案：在Oracle中，每條SQL語句在正式執(zhí)行之前都需要經過解析(Parse)，根據(jù)解析的過程可以分為3種類型：硬解析(HardParse)、軟解析(SoftParse)和軟軟解析(SoftSoftParse)，軟軟解析也叫快速解析(FastParse)。DDL語句是從來不會共享使用的，也就是說，DDL語句每次執(zhí)行都需要進行硬解析。但是，DML語句和SELECT語句會根據(jù)情況選擇是進行硬解析，還是進行軟解析或者進行軟軟解析。SQL的解析過程大致可以參考下圖。

Oracle在解析和執(zhí)行目標SQL時，會先去當前會話的PGA中查找是否存在匹配的緩存會話游標。當Oracle第一次解析和執(zhí)行目標SQL時(顯然是硬解析)，當前會話的PGA中肯定不存在匹配的會話游標，這時Oracle會新生成一個會話游標和一對共享游標(包含一個父游標和一個子游標)，這其中的共享游標會存儲能被所有會話共享、重用的內容(比如目標s0L的解析樹、執(zhí)行計劃等)，而會話游標則會經歷一次Open、Parse、Bind、Execute、Fetch和Close中的一個或多個階段。

對會話游標和共享游標之間的關聯(lián)關系如下總結：

1)無論是硬解析、軟解析還是軟軟解析，Oracle在解析和執(zhí)行目標SQL時，始終會先去當前會話的PGA中尋找是否存在匹配的緩存會話游標。

2)如果在當前會話的PGA中找不到匹配的緩存會話游標，那么Oracle就會去SGA的庫緩存中查找是否存在匹配的父游標。如果在庫緩存中找不到匹配的父游標，那么Oracle就會新生成一個會話游標和一對共享游標(即父游標和子游標)；如果找到了匹配的父游標，但找不到匹配的子游標，那么Oracle就會新生成一個會話游標和一個子游標(這個子游標會被掛在之前找到的匹配父游標下)。無論哪一種情況，這兩個過程對應的都是硬解析。

3)如果在當前會話的PGA中找不到匹配的緩存會話游標，但在庫緩存中找到了匹配的父游標和子游標，那么Oracle會新生成一個會話游標并重用剛剛找到的匹配父游標和子游標，這個過程對應的就是軟解析。

4)如果在當前會話的PGA中找到了匹配的緩存會話游標，那么此時Oracle就不再需要新生成一個會話游標，并且也不再需要像軟解析那樣去SGA的庫緩存中查找匹配的父游標，因為Oracle此時可以重用找到的匹配會話游標，并且可以通過這個會話游標直接訪問到該SQL對應的父游標，這個過程就是軟軟解析。

硬解析、軟解析和軟軟解析的比對參考下表。

硬解析軟解析軟軟解析簡介硬解析是指Oracle在執(zhí)行目標SQL時，在庫緩存中找不到可以重用的解析樹和執(zhí)行計劃，而不得不從頭開始解析目標SQL并生成相應的父游標和子游標的過程。硬解析實際上有兩種類型：一種是在庫緩存中找不到匹配的父游標，此時Oracle會從頭開始解析目標SQL，新生成一個父游標和一個子游標，并把它們掛在對應的HashBucket中：另外一種是找到了匹配的父游標但未找到匹配的子游標，此時Oracle也會從頭開始解析該目標SQL，新生成一個子游標，并把這個子游標掛在對應的父游標下軟解析是指Oracle在執(zhí)行目標SQL時，在庫緩存中找到了匹配的父游標和子游標，并將存儲在子游標中的解析樹和執(zhí)行計劃直接拿過來重用而無須從頭開始解析的過程軟軟解析也叫快速解析(FastParse)，是指若參數(shù)SESSION_CACHED_CURSORS的值大于0，并且該會話游標所對應的目標SQL解析和執(zhí)行的次數(shù)超過3次，則此時該會話