[zt] Oracle基礎面試題

一：SQL tuning 類
1：列舉幾種表連線方式答：merge join，hash join，nested loop
2：不借助第三方工具，怎樣檢視sql的執行計劃？答：sqlplus
set autotrace ...
utlxplan.sql 建立 plan_table 表
3：如何使用CBO，CBO與RULE的區別？答：在初始化引數裡面設定 optimizer_mode=choose/all_rows/first_row 等可以使用 cbo。
rbo會選擇不合適的索引，cbo需要統計資訊。
4：如何定位重要（消耗資源多）的SQL？答：根據v$sqlarea 中的邏輯讀/disk_read。以及尋找CPU使用過量的session，查出當前session的當前SQL語句，或者：監控WIN平臺Oracle的執行
5：如何跟蹤某個session的SQL？答：先找出對應的'sid,serial'，然後呼叫system_system.set_sql_trace_in_session(sid,serial,true)；參考：跟蹤某個會話
6：SQL調整最關注的是什麼？答：邏輯讀。IO量
7：說說你對索引的認識（索引的結構、對dml影響、對查詢影響、為什麼提高查詢效能）答：預設的索引是b-tree。
對insert的影響：分裂，要保證tree的平衡。
對delete的影響：刪除行的時候要標記改節點為刪除。
對update的影響：如果更新表中的索引欄位，則要相應的更新索引中的鍵值。查詢中包含索引欄位的鍵值和行的實體地址。
8：使用索引查詢一定能提高查詢的效能嗎？為什麼？答：不能。如果返回的行數目較大，使用全表掃描的效能較好。
9：繫結變數是什麼？繫結變數有什麼優缺點？答：通俗的說，繫結變數就是變數的一個佔位符，使用繫結變數可以減少只有變數值不同的語句的解析。
10：如何穩定(固定)執行計劃？答：使用stored outline。
11：和排序相關的記憶體在8i和9i分別怎樣調整，臨時表空間的作用是什麼？答：8i：使用sort_area_size，hash_area_size，每個session分配相同的值，不管有無使用。
9i：使用pga_aggregate來統一管理。臨時表空間的作用：
在sort_area_size中不能完成的部分在臨時表空間完成，臨時表空間在重建索引，建立臨時表等都要用到。
還有hash join不能完成的也在臨時表空間中做。
12：存在表T(a,b,c,d)，要根據欄位c排序後取第21-30條記錄顯示，請給出sqlselect a,b,c,d from (select a,b,c,d from T order by c) where rownum<=30
minus
select a,b,c,d from (select a,b,c,d from T order by c) where rownum <=20;
或者：
select * from (select rownum rn,a.* from (select a,b,c,d from T order by c) a )where rn between 21 and 30;

二：資料庫基本概念類
1：pctused and pctfree 表示什麼含義有什麼作用？答：表示資料塊什麼時候移入和移出freelist。
pctused：如果資料塊的使用率小於pctused的值，則該資料塊重新加入到fresslist中。
pctfree：如果資料塊的使用率高於pctfree的值，則該資料塊從freelist中移出。
2：簡單描述table / segment / extent / block之間的關係答：一個table至少是一個segment，如果分割槽表，則每個分割槽是一個segment，table可以看成是一個邏輯上的概念，segment可以看成是這個邏輯概念的物理實現；
segment由一個或多個extents組成，segment不可以跨表空間但可以跨資料檔案；
extent由多個連續的blocks組成，不可以跨資料檔案；
block由1-多個os塊組成，是oracle i/o的最小儲存單位。
3：描述tablespace和datafile之間的關係答：tablespace是邏輯上的概念，datafile是物理上的概念。
一個tablespace可以由多個datafile組成，一個datafile不能跨越多個tablespace。
4：本地管理表空間和字典管理表空間的特點，ASSM有什麼特點？答：一個使用freelist管理，一個使用點陣圖管理。
5：回滾段的作用是什麼？答：儲存資料的前像，保證資料讀取的時間點一致性。Oracle裡資料的多版本特性就是透過回滾段來實現的，正因為此，Oracle資料庫實現了讀寫不競爭的效能優勢！
6：日誌的作用是什麼？答：記錄對資料庫的操作，便於恢復。
7：SGA主要有那些部分，主要作用是什麼？答：db_cache(快取資料塊)，shared_pool(快取sql，執行計劃，資料字典資訊等)，large_pool(MTS模式、parallel 、rman等要用到)，java pool(java程式如SQLJ儲存過程執行時要用到)。
8：Oracle系統程式主要有哪些，作用是什麼？答：smon（合併空間，例項恢復），pmon（清理失敗的程式），歸檔程式（負責在日誌切換的時候歸檔日誌檔案），lgmr（日誌書寫器程式，負責寫日誌），ckpt（檢查點程式，觸發檢查點），dbwr（資料庫寫入器，負責把資料寫入導 datafile）。

三：備份恢復類
1：備份如何分類？答：邏輯備份(exp)與物理備份。或者冷備份與熱備份。
2：歸檔是什麼含義？答：把日誌檔案放到另一個地方。
3：如果一個表在2004-08-04 10:30:00 被drop，在有完善的歸檔和備份的情況下，如何恢復答：複製備份，
recover database until time 2004-08-04 10:30:00
alter database open resetlogs;
4：rman是什麼，有何特點？答：rman叫恢復管理器。
特點很多。可以線上備份，到少目標資料庫是mount狀態。1）熱備份。
2）可以儲存指令碼。
3）可以增量備份。
4）自動管理備份集。
5：standby的特點答：利用傳輸重做日誌來達到同步的目的。可以設定多個保護級別，9i後支援邏輯standbyDB。
6：對於一個要求恢復時間比較短的系統(資料庫50G，每天歸檔5G)，你如何設計備份策略答：每天一個全備份。

四：系統管理類
1：對於一個存在系統效能的系統，說出你的診斷處理思路答：做一個statspack，根據top 5，system load，top sql等來做相應的調整。
2：列舉幾種診斷IO、CPU、效能狀況的方法答：hp-unix:iostat -x 1 5；
top/vmstat/glance
3：對statspack有何認識？答：一個效能診斷工具而已，其本質就是在兩個時間點取樣兩個系統資料。(動態效能檢視)，然後根據兩個snapshot，產生一個報告。
4：如果系統現在需要在一個很大的表上建立一個索引，你會考慮那些因素，如何做以儘量減小對應用的影響答：
1）增大sort_area_size（8i）/pga_aggregate_target（9i）值。
2）如果表有分割槽（一般大表都要用到分割槽的），按分割槽逐個建索引，如果是本地索引的話。
3）系統空閒的時候建。
5：對raid10 和raid5有何認識？答：raid10是先映象後條帶，適合對寫入速度要求較高的資料庫系統，特別是online redolog檔案，raid5適合大部分的資料庫系統和資料倉儲系統，讀效能優於寫效能。