DBA 經典面試題(1)

 
2:不藉助第三方工具，怎樣查看sql的執行計畫
set autotrace on
 
如果出現以下錯誤：
  SP2-0613:無法驗證PLAN_TABLE格式或實體
  SP2-0611:啟用EXPLAIN報告時出錯
執行oraclehome\ora92\rdbms\admin\utlxplan.sql
 
explain plan set statement_id = &item_id for &sql;
select * from table(dbms_xplan.display);
 
3:如何使用CBO,CBO與RULE的區別
在optimizer_mode=choose時,如果表有統計資訊(分區表外),最佳化器將選擇CBO,否則選RBO。
RBO遵循簡單的分級方法學,使用15種層級要點，當接收到查詢，最佳化器將評估使用到的要點數目,然後選擇最佳層級(最少的數量)的執行路徑來執行查詢。
　　CBO嘗試找到最低成本的訪問資料的方法,為了最大的輸送量或最快的初始回應時間,計算使用不同 的執行計畫的成本，並選擇成本最低的一個,關於表的資料內容的統計被用於確定執行計畫。
 
4:如何定位重要(消耗資源多)的SQL
select sql_text
from v$sql
where disk_reads > 1000 or (executions > 0 and buffer_gets/executions > 30000); 
 
5:如何跟蹤某個session的SQL
exec dbms_system.set_sql_trace_in_session(sid,serial#,&sql_trace);
select sid, serial# from v$session where sid = (select sid from v$mystat where rownum = 1);
exec dbms_system.set_ev(&sid,&serial#,&event_10046,&level_12,’’); 
 
6:SQL調整最關注的是什麼
   查看該SQL的response time(db block gets/consistent gets/physical reads/sorts (disk))
 
7:說說你對索引的認識(索引的結構、對dml影響、為什麼提高查詢效能)
   b-tree index/   bitmap index/    function index/  partitional index(local/global)
索引通常能提高select/update/delete的效能,會降低insert的速度,
 
8:使用索引查詢一定能提高查詢的效能嗎?為什麼
　　索引就是為了提高查詢效能而存在的,如果在查詢中索引沒有提高效能,只能說是用錯了索引,或者講是場合不同
 
9:綁定變數是什麼?綁定變數有什麼優缺點?
綁定變數是相對文本變數來講的,所謂文本變數是指在SQL直接書寫查詢條件，這樣的SQL在不同條件下需要反覆解析,綁定變數是指使用變數來代替直接書寫條件，查詢bind value在運行時傳遞，然後綁定執行。
優點是減少硬解析,降低CPU的爭用,節省shared_pool ;缺點是不能使用histogram,sql最佳化比較困難
 
10:如何穩定(固定)執行計畫
　　query_rewrite_enabled = true  
　　star_transformation_enabled = true 
　　optimizer_features_enable = 9.2.0 
 
　　建立並使用stored outline
 
11:和排序相關的記憶體在8i和9i分別怎樣調整，暫存資料表空間的作用是什麼
　　8i中sort_area_size/sort_area_retained_size決定了排序所需要的記憶體
　　如果排序操作不能在sort_area_size中完成,就會用到temp資料表空間
　　9i中如果workarea_size_policy=auto時,
　　排序在pga內進行,通常pga_aggregate_target的1/20可以用來進行disk sort;
　　如果workarea_size_policy=manual時,排序需要的記憶體由sort_area_size決定
　　在執行order by/group by/distinct/union/create index/index rebuild/minus等操作時,如果在pga或sort_area_size中不能完成,排序將在暫存資料表空間進行(disk sort),
　　暫存資料表空間主要作用就是完成系統中的disk sort.
 
12:存在表T(a,b,c,d),要根據欄位c排序後取第21—30條記錄顯示，請給出sql
　　create table t(a number(8),b number(8),c number(8),d number(8)); 
　　/ 
　　begin 
　　for i in 1 .. 300 loop 
　　insert into t values(mod(i,2),i/2,dbms_random.value(1,300),i/4); 
　　end loop; 
　　end; 
　　/ 
　　select * from (select c.*,rownum as rn from (select * from t order by c desc) c) where rn between 21 and 30; 
　　/ 
　　select * from (select * from test order by c desc) x where rownum < 30 
　　minus 
　　select * from (select * from test order by c desc) y where rownum < 20 order by 3 desc 
相 比之minus效能較差
　
 
 
 
 
 
二、資料庫基本概念類
================================================================================
1:pctused and pctfree表示什麼含義有什麼作用
　　pctused與pctfree控制資料區塊是否出現在freelist中, pctfree控制資料區塊中保留用於update的空間,當資料區塊中的free space小於pctfree設定的空間時,該資料區塊從freelist中去掉,當塊由於dml操作free space大於pct_used設定的空間時,該資料庫塊將被添加在freelist鏈表中。
 
2:簡單描述table / segment / extent / block之間的關係
　　table建立時,預設建立了一個data segment,每個data segment含有min extents指定的extents數,每個extent據據資料表空間的儲存參數分配一定數量的blocks
 
3:描述tablespace和datafile之間的關係
　　一個tablespace可以有一個或多個datafile,每個datafile只能在一個tablespace內, table中的資料,通過hash演算法分布在tablespace中的各個datafile中, tablespace是邏輯上的概念,datafile則在物理上儲存了資料庫的種種對象。
 
4:本地管理資料表空間和字典管理資料表空間的特點，ASSM有什麼特點
本地管理資料表空間(Locally Managed Tablespace簡稱LMT)
  8i以後出現的一種新的資料表空間的管理員模式，通過位元影像來管理資料表空間的空間使用。
字典管理資料表空間(Dictionary-Managed Tablespace簡稱DMT)
  8i以前包括以後都還可以使用的一種資料表空間管理員模式，通過資料字典管理資料表空間的空間使用。
 
動段空間管理(ASSM)，
　　它首次出現在Oracle920裡有了ASSM，連結清單freelist被位元影像所取代，它是一個二進位的數組，能夠迅速有效地管理儲存擴充和剩餘區塊(free block)，因此能夠改善分段儲存本質，ASSM資料表空間上建立的段還有另外一個稱呼叫Bitmap Managed Segments(BMB段)。 
 
5:復原段的作用是什麼
　　交易回復：當事務修改表中資料的時候，該資料修改前的值(即前影像)會存放在復原段中，當使用者復原事務(ROLLBACK)時，ORACLE將會利用復原段中的資料前影像來將修改的資料恢複到原來的值。
　　事務恢複：當事務正在處理的時候，常式失敗，復原段的資訊儲存在undo資料表空間中，　　ORACLE將在下次開啟資料庫時利用復原來恢複未提交的資料。
　　讀一致性：當一個會話正在修改資料時，其他的會話將看不到該會話未提交的修改。
 
當一個語句正在執行時，該語句將看不到從該語句開始執行後的未提交的修改(語句級讀一致性)
   當ORACLE執行SELECT語句時，ORACLE依照當前的系統改變號(SYSTEM CHANGE NUMBER-SCN)來保證任何前於當前SCN的未提交的改變不被該語句處理。可以想象：當一個長時間的查詢正在執行時，若其他會話改變了該查詢要查詢的某個資料區塊，ORACLE將利用復原段的資料前影像來構造一個讀一致性視圖。
 
6:日誌的作用是什麼
　　記錄資料庫事務,最大限度地保證資料的一致性與安全性
　　重做記錄檔：含對資料庫所做的更改記錄，這樣萬一出現故障可以啟用資料恢複,一個資料庫至少需要兩個重做記錄檔
　　歸檔記錄檔：是重做記錄檔的離線副本，這些副本可能對於從介質失敗中進行恢複很必要。
 
7:SGA主要有那些部分，主要作用是什麼
 
   SGA：db buffer cache/redo log buffer/shared pool/large pool/java pool 
db_cache: 
　　資料庫緩衝(Block Buffer)對於Oracle資料庫的運轉和效能起著非常關鍵的作用，　　它佔據Oracle資料庫SGA(系統共用記憶體區)的主要部分。Oracle資料庫通過使用LRU演算法，將最近訪問的資料區塊存放到緩衝中，從而最佳化對磁碟資料的訪問.
 
shared_pool:
　　共用池的大小對於Oracle效能來說都是很重要的。 共用池中儲存資料字典高速緩衝和完全解析或編譯的的PL/SQL塊和SQL語句及控制結構
 
large_pool:
　　使用MTS配置時，因為要在SGA中分配UGA來保持使用者的會話，就是用Large_pool來保持這個會話記憶體;使用RMAN做備份的時候，要使用Large_pool這個記憶體結構來做磁碟I/O緩衝器.
 
java_pool:
　　為java procedure預備的記憶體地區,如果沒有使用java proc,java_pool不是必須的。
 
8 Oracle系統進程主要有哪些，作用是什麼
　　資料寫進程(dbwr)：負責將更改的資料從資料庫緩衝區快取寫入資料檔案 
　　日誌寫進程(lgwr)：將重做日誌緩衝區中的更改寫入線上重做記錄檔 
　　系統監控(smon)：檢查資料庫的一致性如有必要還會在資料庫開啟時啟動資料庫的恢複 
　　進程監控(pmon)：負責在一個Oracle進程失敗時清理資源 
　　檢查點進程(chpt)：負責在每當緩衝區快取中的更改永久地記錄在資料庫中時,更新控制檔案和資料檔案中的資料庫狀態資訊。 
　　歸檔進程(arcn)：在每次日誌切換時把已滿的日誌組進行備份或歸檔 
　　作業調度器(cjq) :負責將調度與執行系統中已定義好的job,完成一些預定義的工作. 
　　恢複進程(reco) :保證分散式交易的一致性,在分散式交易中,要麼同時commit,要麼同時rollback;