oracle DBA面試題__oracle

ORACLE DBA面試題集
一：SQL tuning 類
1 列舉幾種表串連方式
等串連、非等串連、自串連、外串連（左、右、全）
2 不藉助第三方工具，怎樣查看sql的執行計畫
I) 使用Explain Plan,查詢PLAN_TABLE;
EXPLAIN PLAN
SET STATEMENT_ID='QUERY1'
FOR
SELECT *
FROM a
WHERE aa=1;
SELECT operation, options, object_name, object_type, ID, parent_id
FROM plan_table
WHERE STATEMENT_ID = 'QUERY1'
ORDER BY ID;

II)SQLPLUS中的SET TRACE 即可看到Execution Plan Statistics
SET AUTOTRACE ON;

3:如何使用CBO,CBO與RULE的區別
IF 初始化參數 OPTIMIZER_MODE = CHOOSE THEN --(8I DEFAULT)
IF 做過表分析
THEN 最佳化器 Optimizer=CBO(COST); /*高效*/
ELSE
最佳化器 Optimizer=RBO(RULE); /*高效*/
END IF;
END IF;

區別：
RBO根據規則選擇最佳執行路徑來執行查詢。
CBO根據表統計找到最低成本的訪問資料的方法確定執行計畫。
使用CBO需要注意：
I) 需要經常對錶進行ANALYZE命令進行分析統計;
II) 需要穩定執行計畫;
III)需要使用提示(Hint);

使用RULE需要注意：
I) 選擇最有效率的表名順序
II) 最佳化SQL的寫法;

4 如何定位重要(消耗資源多)的SQL
使用CPU多的使用者session
SELECT a.SID, spid, status, SUBSTR (a.program, 1, 40) prog, a.terminal,a.SQL_TEXT,
osuser, VALUE / 60 / 100 VALUE
FROM v$session a, v$process b, v$sesstat c
WHERE c.statistic# = 12 AND c.SID = a.SID AND a.paddr = b.addr
ORDER BY VALUE DESC;

5 如何跟蹤某個session的SQL
利用TRACE 跟蹤
ALTER SESSION SET SQLTRACE ON;
COLUMN SQL format a200;
SELECT machine, sql_text SQL
FROM v$sqltext a, v$session b
WHERE address = sql_address
AND machine = '&A'
ORDER BY hash_value, piece;
6 SQL調整最關注的是什麼
檢查系統的I/O問題
sar－d能檢查整個系統的iostat（IO statistics）
7 說說你對索引的認識（索引的結構、對dml影響、對查詢影響、為什麼提高查詢效能）
l 索引有B-TREE、BIT、CLUSTER等類型。ORACLE使用了一個複雜的自平衡B-tree結構;
l 通常來說，在表上建立恰當的索引，查詢時會改進查詢效能。但在進行插入、刪除、修改時，同時會進行索引的修改，在效能上有一定的影響。
l 有索引且查詢條件能使用索引時，資料庫會先度取索引，根據索引內容和查詢條件，查詢出ROWID，再根據ROWID取出需要的資料。由於索引內容通常比全表內容要少很多，因此通過先讀索引，能減少I/O，提高查詢效能。
8 使用索引查詢一定能提高查詢的效能嗎。為什麼
l 通常,通過索引查詢資料比全表掃描要快.但是我們也必須注意到它的代價.
n 索引需要空間來儲存,也需要定期維護, 每當有記錄在表中增減或索引列被修改時,
n 索引本身也會被修改. 這意味著每條記錄的INSERT,DELETE,UPDATE將為此多付出4,5 次的磁碟I/O. 因為索引需要額外的儲存空間和處理,那些不必要的索引反而會使查詢反應時間變慢.
l 使用索引查詢不一定能提高查詢效能,索引範圍查詢(INDEX RANGE SCAN)適用於兩種情況:
n 基於一個範圍的檢索,一般查詢返回結果集小於表中記錄數的30%宜採用;
n 基於非唯一性索引的檢索
9 綁定變數是什麼。綁定變數有什麼優缺點。
l 綁定變數是指在SQL語句中使用變數，改變變數的值來改變SQL語句的執行結果。
l 優點：使用綁定變數，可以減少SQL語句的解析，能減少資料庫引擎消耗在SQL語句解析上的資源。提高了編程效率和可靠性。減少訪問資料庫的次數, 就能實際上減少ORACLE的工作量。
l 缺點：經常需要使用動態SQL的寫法，由於參數的不同，可能SQL的執行效率不同；
10 如何穩定(固定)執行計畫
可以在SQL語句中指定執行計畫。使用HINTS;
11 和排序相關的記憶體在8i和9i分別怎樣調整，暫存資料表空間的作用是什麼
SORT_AREA_SIZE 在進行排序操作時，如果排序的內容太多，記憶體裡不能全部放下，則需要進行外部排序，
此時需要利用暫存資料表空間來存放排序的中間結果。
12 存在表T(a,b,c,d),要根據欄位c排序後取第21-30條記錄顯示，請給出sql
SELECT *
FROM (SELECT ROWNUM AS row_num, tmp_tab.*
FROM (SELECT a, b, c, d
FROM T
ORDER BY c) tmp_tab
WHERE ROWNUM <= 30)
WHERE row_num >= 20
ORDER BY row_num;
二：資料庫基本概念類
1 Pctused and pctfree 表示什麼含義有什麼作用
pctused與pctfree控制資料區塊是否出現在freelist中,
pctfree控制資料區塊中保留用於update的空間,當資料區塊中的free space小於pctfree設定的空間時,
該資料區塊從freelist中去掉,當塊由於dml操作free space大於pct_used設定的空間時,該資料庫塊將
被添加在freelist鏈表中。
2 簡單描述tablespace / segment / extent / block之間的關係
n tablespace : 一個資料庫劃分為一個或多個邏輯單位，該邏輯單位成為資料表空間;每一個資料表空間可能包含一個或多個 Segment;
n Segments : Segment指在tablespace中為特定邏輯儲存結構分配的空間。每一個段是由一個或多個extent組成。包括資料區段、索引段、復原段和臨時段。
n Extents : 一個 extent 由一系列連續的 Oracle blocks組成.ORACLE為通過extent 來給segment分配空間。
n Data Blocks ：Oracle 資料庫最小的I/O儲存單位，一個data block對應一個或多個分配給data file的作業系統塊。
3 描述tablespace和datafile之間的關係
◎ 一個資料表空間可包含一個或多個資料檔案。
◎ 資料表空間利用增加或擴充資料檔案擴大資料表空間，資料表空間的大小為組成該資料表空間的資料檔案大小的和。
◎ 一個datafile只能屬於一個資料表空間;
4 本地管理資料表空間和字典管理資料表空間的特點，ASSM有什麼特點
n 本地管理資料表空間：（9i預設）
n 空閑塊列表格儲存體在資料表空間的資料檔案頭。
n 特點：減少資料字典表的競爭，當分配和收縮空間時會產生復原，不需要合并
n 字典管理的資料表空間：（8i預設）
n 空閑塊列表格儲存體在資料庫中的字典表裡.
n 特點：片由資料字典管理，可能造成字典表的爭用。儲存在資料表空間的每一個段都會有不同的儲存字句，需要合并相鄰的塊;
5 復原段的作用是什麼
復原段用於儲存資料修改前的映象，這些資訊用於產生讀一致性資料庫資訊、在資料庫恢複和Rollback時使用。一個事務只能使用一個復原段。
6 日誌的作用是什麼
記錄檔（Log File）記錄所有對資料庫資料的修改，主要是保護資料庫以防止故障,以及恢複資料時使用。其特點如下：
a)每一個資料庫至少包含兩個記錄檔組。每個記錄檔組至少包含兩個記錄檔成員。
b)記錄檔組以迴圈方式進行寫操作。
c)每一個記錄檔成員對應一個物理檔案。
7 SGA主要有那些部分，主要作用是什麼
系統全域區（SGA）:是ORACLE為執行個體分配的一組共用緩衝儲存區，用於存放資料庫資料和控制資訊，以實現對資料庫資料的管理和操作。
SGA主要包括:
u a)共用池(shared pool) ：用來儲存最近執行的SQL語句和最近使用的資料字典的資料。
u b)資料緩衝區 (database buffer cache)：用來儲存最近從資料檔案中讀寫過的資料。
u c)重作日誌緩衝區（redo log buffer）：用來記錄服務或後台進程對資料庫的操作。
另外在SGA中還有兩個可選的記憶體結構：
u d)Java pool: 用來儲存Java代碼。
u e)Large pool: 用來儲存不與SQL直接相關的大型記憶體結構。備份、恢複使用。
8 Oracle系統進程主要有哪些，作用是什麼
u 資料寫進程(DBWR)：負責將更改的資料從資料庫緩衝區快取寫入資料檔案
u 日誌寫進程(LGWR)：將重做日誌緩衝區中的更改寫入線上重做記錄檔
u 系統監控 (SMON): 檢查資料庫的一致性如有必要還會在資料庫開啟時啟動資料庫的恢複
u 進程監控 (PMON): 負責在一個Oracle 進程失敗時清理資源
u 檢查點進程(CKPT)：負責在每當緩衝區快取中的更改永久地記錄在資料庫中時,更新控制檔案和資料檔案中的資料庫狀態資訊。
u 歸檔進程 (ARCH)：在每次日誌切換時把已滿的日誌組進行備份或歸檔
u 恢複進程 (RECO): 保證分散式交易的一致性,在分散式交易中,要麼同時commit,要麼同時rollback;
u 作業調度器(CJQ ): 負責將調度與執行系統中已定義好的job,完成一些預定義的工作.
三：備份恢複類
1 備份如何分類
n 物理備份
u 物理備份是最主要的備份方式。用於保證資料庫在最小的資料庫丟失或沒有資料丟失的情況下得到恢複。
n 冷物理
u 冷物理備份提供了最簡單和最直接的方法保護資料庫因物理損壞丟失。建議在以下幾種情況中使用。
u 對一個已經存在大最資料量的資料庫，在晚間資料庫可以關閉，此時應用冷物理備份。
u 對需對資料庫伺服器進行升級，（如更換硬碟），此時需要備份資料庫資訊，並在新的硬碟中恢複這些資料資訊，建議採用冷物理備份。
n 熱物理
u 主要是指備份過程在資料庫開啟並且使用者可以使用的情況下進行。需要執行熱物理備份的情況有：
u 由於資料庫性質要求不間斷工作，因而此時只能採用熱物理備份。
u 由於備份的要求的時間過長，而資料庫只能短時間關閉時。
n 邏輯備份 (EXP/IMP)
u 邏輯備份用於實現資料庫物件的恢複。但不是基於時間點可完全恢複的備份策略。只能作為聯機備份和離線備份的一種補充。
n 完全邏輯備份
u 完全邏輯備份是將整個資料庫匯出到一個資料庫的格式檔案中，該檔案可以在不同的資料庫版本、作業系統和硬體平台之間進行移植。
n 指定表的邏輯備份
u 通過備份工具，可以將指定的資料庫表備份出來，這可以避免完全邏輯備份所帶來的時間和財力上的浪費。
2 歸檔是什麼含義
n 歸檔是歸檔當前的聯機redo記錄檔。
n SVRMGR> alter system archive log current;
n 資料庫只有運行在ARCHIVELOG模式下，並且能夠進行自動歸檔，才可以進行聯機備份。有了聯機備份才有可能進行完全恢複。
3 如果一個表在2004-08-04 10:30:00 被drop，在有完善的歸檔和備份的情況下，如何恢複
I) 9i 新增的FLASH BACK 應該可以;
II) Logminer應該可以找出DML。
III)有完善的歸檔和備份，先歸檔當前資料，然後可以先恢複到刪除的時間點之前，把DROP 的表匯出來，然後再恢複到最後歸檔時間；

不過，我沒試過，都是理論的說!!
4 rman是什麼，有何特點

n RMAN可以用來備份與還原資料庫檔案、歸檔日誌和控制檔案。它也可以用來執行完全或不完全的資料庫恢複。
n RMAN啟動資料庫上的Oracle伺服器處理序來進行備份或還原。備份、還原、恢複是由這些進程驅動的。
n 注意：RMAN不能用於備份初始化參數檔案和口令檔案。
具有如下特點：
n 功能類似物理備份，但比物理備份強大N倍；
n 可以壓縮空塊；
n 可以在塊水平上實現增量；
n 可以把備份的輸出打包成備份組，也可以按固定大小分割備份組；
n 備份與恢複的過程可以自動管理；
n 可以使用指令碼（存在Recovery catalog 中）
n 可以做壞塊監測
5 standby的特點
備用資料庫（standby database）：ORACLE推出的一種高可用性(HIGH AVAILABLE)資料庫方案，
在主節點與備用節點間通過日誌同步來保證資料的同步，備用節點作為主節點的備份
可以實現快速切換與災難性恢複,從920開始，還開始支援物理與邏輯待命伺服器。
9i中的三種資料保護模式分別是：
n MAXIMIZE PROTECTION ：最大資料保護與無資料分歧，LGWR將同時傳送到備用節點，
u 在主節點事務確認之前，備用節點也必須完全收到日誌資料。如果網路不好，引起LGWR不能傳送資料，將引起嚴重的效能問題，導致主節點DOWN機。
n MAXIMIZE AVAILABILITY ：無資料丟失模式，允許資料分歧，允許非同步傳送。
u 正常情況下運行在最大保護模式，在主節點與備用節點的網路斷開或串連不正常時，自動切換到最大效能模式，
u 主節點的操作還是可以繼續的。在網路不好的情況下有較大的效能影響。
n MAXIMIZE PERFORMANCE：這種模式應當可以說是從8i繼承過來的待命伺服器模式，非同步傳送，
u 無資料同步檢查，可能遺失資料，但是能獲得主節點的最大效能。9i在配置DATA GUARD的時候預設就是MAXIMIZE PERFORMANCE
沒實踐過，只是把答案列出來。
6 對於一個要求恢復比較短的系統(資料庫50G,每天歸檔5G)，你如何設計備份策略
資料庫比較大邏輯備份沒什麼必要，每天歸檔5G，每周三/周六自動歸檔10G，每月RMAN歸檔全庫。
應該有standby。
我對備份和恢複缺少實踐機會，JUST PAPER。