阿里巴巴的Oracle筆試題
DBA筆試考題
2007年06月27日 星期三 17:59
一：SQL tuning 類
1 列舉幾種表連接方式
等連接、非等連接、自連接、外連接（左、右、全）
2 不借助第三方工具，怎樣查看sql的執(zhí)行計(jì)劃
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的區(qū)別
IF 初始化參數(shù) OPTIMIZER_MODE = CHOOSE THEN –(8I DEFAULT)
IF 做過表分析
THEN 優(yōu)化器 Optimizer=CBO(COST); /*高效*/
ELSE
優(yōu)化器 Optimizer=RBO(RULE); /*高效*/
END IF;
END IF;
區(qū)別：
RBO根據(jù)規(guī)則選擇最佳執(zhí)行路徑來運(yùn)行查詢。
CBO根據(jù)表統(tǒng)計(jì)找到最低成本的訪問數(shù)據(jù)的方法確定執(zhí)行計(jì)劃。
使用CBO需要注意：
I) 需要經(jīng)常對表進(jìn)行ANALYZE命令進(jìn)行分析統(tǒng)計(jì);
II) 需要穩(wěn)定執(zhí)行計(jì)劃;
III)需要使用提示(Hint);
使用RULE需要注意：
I) 選擇最有效率的表名順序
II) 優(yōu)化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 如何跟蹤某個(gè)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調(diào)整最關(guān)注的是什么
檢查系統(tǒng)的I/O問題
sar－d能檢查整個(gè)系統(tǒng)的iostat（IO statistics）
7 說說你對索引的認(rèn)識（索引的結(jié)構(gòu)、對dml影響、對查詢影響、為什么提高查詢性能）
索引有B-TREE、BIT、CLUSTER等類型。ORACLE使用了一個(gè)復(fù)雜的自平衡B-tree結(jié)構(gòu);
通常來說，在表上建立恰當(dāng)?shù)乃饕樵儠r(shí)會(huì)改進(jìn)查詢性能。但在進(jìn)行插入、刪除、修改時(shí)，同時(shí)會(huì)進(jìn)行索引的修改，在性能上有一定的影響。
有索引且查詢條件能使用索引時(shí)，數(shù)據(jù)庫會(huì)先度取索引，根據(jù)索引內(nèi)容和查詢條件，查詢出ROWID，再根據(jù)ROWID取出需要的數(shù)據(jù)。由于索引內(nèi)容通常比全表內(nèi)容要少很多，因此通過先讀索引，能減少I/O，提高查詢性能。
8 使用索引查詢一定能提高查詢的性能嗎？為什么
通常,通過索引查詢數(shù)據(jù)比全表掃描要快.但是我們也必須注意到它的代價(jià).
索引需要空間來存儲(chǔ),也需要定期維護(hù), 每當(dāng)有記錄在表中增減或索引列被修改時(shí),
索引本身也會(huì)被修改. 這意味著每條記錄的INSERT,DELETE,UPDATE將為此多付出4,5 次的磁盤I/O. 因?yàn)樗饕枰~外的存儲(chǔ)空間和處理,那些不必要的索引反而會(huì)使查詢反應(yīng)時(shí)間變慢.
使用索引查詢不一定能提高查詢性能,索引范圍查詢(INDEX RANGE SCAN)適用于兩種情況:
基于一個(gè)范圍的檢索,一般查詢返回結(jié)果集小于表中記錄數(shù)的30%宜采用;
基于非唯一性索引的檢索
9 綁定變量是什么？綁定變量有什么優(yōu)缺點(diǎn)？
綁定變量是指在SQL語句中使用變量，改變變量的值來改變SQL語句的執(zhí)行結(jié)果。
優(yōu)點(diǎn)：使用綁定變量，可以減少SQL語句的解析，能減少數(shù)據(jù)庫引擎消耗在SQL語句解析上的資源。提高了編程效率和可靠性。減少訪問數(shù)據(jù)庫的次數(shù), 就能實(shí)際上減少ORACLE的工作量。
缺點(diǎn)：經(jīng)常需要使用動(dòng)態(tài)SQL的寫法，由于參數(shù)的不同，可能SQL的執(zhí)行效率不同；
10 如何穩(wěn)定(固定)執(zhí)行計(jì)劃
可以在SQL語句中指定執(zhí)行計(jì)劃。使用HINTS;
11 和排序相關(guān)的內(nèi)存在8i和9i分別怎樣調(diào)整，臨時(shí)表空間的作用是什么
SORT_AREA_SIZE 在進(jìn)行排序操作時(shí)，如果排序的內(nèi)容太多，內(nèi)存里不能全部放下，則需要進(jìn)行外部排序，
此時(shí)需要利用臨時(shí)表空間來存放排序的中間結(jié)果。
12 存在表T(a,b,c,d),要根據(jù)字段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;
二：數(shù)據(jù)庫基本概念類
1 Pctused and pctfree 表示什么含義有什么作用
pctused與pctfree控制數(shù)據(jù)塊是否出現(xiàn)在freelist中,
pctfree控制數(shù)據(jù)塊中保留用于update的空間,當(dāng)數(shù)據(jù)塊中的free space小于pctfree設(shè)置的空間時(shí),
該數(shù)據(jù)塊從freelist中去掉,當(dāng)塊由于dml操作free space大于pct_used設(shè)置的空間時(shí),該數(shù)據(jù)庫塊將
被添加在freelist鏈表中。
2 簡單描述tablespace / segment / extent / block之間的關(guān)系
tablespace : 一個(gè)數(shù)據(jù)庫劃分為一個(gè)或多個(gè)邏輯單位，該邏輯單位成為表空間;每一個(gè)表空間可能包含一個(gè)或多個(gè) Segment;
Segments : Segment指在tablespace中為特定邏輯存儲(chǔ)結(jié)構(gòu)分配的空間。每一個(gè)段是由一個(gè)或多個(gè)extent組成。包括數(shù)據(jù)段、索引段、回滾段和臨時(shí)段。
Extents : 一個(gè) extent 由一系列連續(xù)的 Oracle blocks組成.ORACLE為通過extent 來給segment分配空間。
Data Blocks ：Oracle 數(shù)據(jù)庫最小的I/O存儲(chǔ)單位，一個(gè)data block對應(yīng)一個(gè)或多個(gè)分配給data file的操作系統(tǒng)塊。
3 描述tablespace和datafile之間的關(guān)系
◎ 一個(gè)表空間可包含一個(gè)或多個(gè)數(shù)據(jù)文件。
◎ 表空間利用增加或擴(kuò)展數(shù)據(jù)文件擴(kuò)大表空間，表空間的大小為組成該表空間的數(shù)據(jù)文件大小的和。
◎ 一個(gè)datafile只能屬于一個(gè)表空間;
4 本地管理表空間和字典管理表空間的特點(diǎn)，ASSM有什么特點(diǎn)
本地管理表空間：（9i默認(rèn)）
空閑塊列表存儲(chǔ)在表空間的數(shù)據(jù)文件頭。
特點(diǎn)：減少數(shù)據(jù)字典表的競爭，當(dāng)分配和收縮空間時(shí)會(huì)產(chǎn)生回滾，不需要合并
字典管理的表空間：（8i默認(rèn)）
空閑塊列表存儲(chǔ)在數(shù)據(jù)庫中的字典表里.
特點(diǎn)：片由數(shù)據(jù)字典管理，可能造成字典表的爭用。存儲(chǔ)在表空間的每一個(gè)段都會(huì)有不同的存儲(chǔ)字句，需要合并相鄰的塊;
5 回滾段的作用是什么
回滾段用于保存數(shù)據(jù)修改前的映象，這些信息用于生成讀一致性數(shù)據(jù)庫信息、在數(shù)據(jù)庫恢復(fù)和Rollback時(shí)使用。一個(gè)事務(wù)只能使用一個(gè)回滾段。
6 日志的作用是什么
日志文件（Log File）記錄所有對數(shù)據(jù)庫數(shù)據(jù)的修改，主要是保護(hù)數(shù)據(jù)庫以防止故障,以及恢復(fù)數(shù)據(jù)時(shí)使用。其特點(diǎn)如下：
a)每一個(gè)數(shù)據(jù)庫至少包含兩個(gè)日志文件組。每個(gè)日志文件組至少包含兩個(gè)日志文件成員。
b)日志文件組以循環(huán)方式進(jìn)行寫操作。
c)每一個(gè)日志文件成員對應(yīng)一個(gè)物理文件。
7 SGA主要有那些部分，主要作用是什么
系統(tǒng)全局區(qū)（SGA）:是ORACLE為實(shí)例分配的一組共享緩沖存儲(chǔ)區(qū)，用于存放數(shù)據(jù)庫數(shù)據(jù)和控制信息，以實(shí)現(xiàn)對數(shù)據(jù)庫數(shù)據(jù)的管理和操作。
SGA主要包括:
a)共享池(shared pool) ：用來存儲(chǔ)最近執(zhí)行的SQL語句和最近使用的數(shù)據(jù)字典的數(shù)據(jù)。
b)數(shù)據(jù)緩沖區(qū) (database buffer cache)：用來存儲(chǔ)最近從數(shù)據(jù)文件中讀寫過的數(shù)據(jù)。
c)重作日志緩沖區(qū)（redo log buffer）：用來記錄服務(wù)或后臺(tái)進(jìn)程對數(shù)據(jù)庫的操作。
另外在SGA中還有兩個(gè)可選的內(nèi)存結(jié)構(gòu)：
d)Java pool: 用來存儲(chǔ)Java代碼。
e)Large pool: 用來存儲(chǔ)不與SQL直接相關(guān)的大型內(nèi)存結(jié)構(gòu)。備份、恢復(fù)使用。
8 Oracle系統(tǒng)進(jìn)程主要有哪些，作用是什么
數(shù)據(jù)寫進(jìn)程(DBWR)：負(fù)責(zé)將更改的數(shù)據(jù)從數(shù)據(jù)庫緩沖區(qū)高速緩存寫入數(shù)據(jù)文件
日志寫進(jìn)程(LGWR)：將重做日志緩沖區(qū)中的更改寫入在線重做日志文件
系統(tǒng)監(jiān)控 (SMON): 檢查數(shù)據(jù)庫的一致性如有必要還會(huì)在數(shù)據(jù)庫打開時(shí)啟動(dòng)數(shù)據(jù)庫的恢復(fù)
進(jìn)程監(jiān)控 (PMON): 負(fù)責(zé)在一個(gè)Oracle 進(jìn)程失敗時(shí)清理資源
檢查點(diǎn)進(jìn)程(CKPT)：負(fù)責(zé)在每當(dāng)緩沖區(qū)高速緩存中的更改永久地記錄在數(shù)據(jù)庫中時(shí),更新控制文件和數(shù)據(jù)文件中的數(shù)據(jù)庫狀態(tài)信息。
歸檔進(jìn)程 (ARCH)：在每次日志切換時(shí)把已滿的日志組進(jìn)行備份或歸檔
恢復(fù)進(jìn)程 (RECO): 保證分布式事務(wù)的一致性,在分布式事務(wù)中,要么同時(shí)commit,要么同時(shí)rollback;
作業(yè)調(diào)度器(CJQ ): 負(fù)責(zé)將調(diào)度與執(zhí)行系統(tǒng)中已定義好的job,完成一些預(yù)定義的工作.
三：備份恢復(fù)類
1 備份如何分類
物理備份
物理備份是最主要的備份方式。用于保證數(shù)據(jù)庫在最小的數(shù)據(jù)庫丟失或沒有數(shù)據(jù)丟失的情況下得到恢復(fù)。
冷物理
冷物理備份提供了最簡單和最直接的方法保護(hù)數(shù)據(jù)庫因物理損壞丟失。建議在以下幾種情況中使用。
對一個(gè)已經(jīng)存在大最數(shù)據(jù)量的數(shù)據(jù)庫，在晚間數(shù)據(jù)庫可以關(guān)閉，此時(shí)應(yīng)用冷物理備份。
對需對數(shù)據(jù)庫服務(wù)器進(jìn)行升級，（如更換硬盤），此時(shí)需要備份數(shù)據(jù)庫信息，并在新的硬盤中恢復(fù)這些數(shù)據(jù)信息，建議采用冷物理備份。
熱物理
主要是指備份過程在數(shù)據(jù)庫打開并且用戶可以使用的情況下進(jìn)行。需要執(zhí)行熱物理備份的情況有：
由于數(shù)據(jù)庫性質(zhì)要求不間斷工作，因而此時(shí)只能采用熱物理備份。
由于備份的要求的時(shí)間過長，而數(shù)據(jù)庫只能短時(shí)間關(guān)閉時(shí)。
邏輯備份 (EXP/IMP)
邏輯備份用于實(shí)現(xiàn)數(shù)據(jù)庫對象的恢復(fù)。但不是基于時(shí)間點(diǎn)可完全恢復(fù)的備份策略。只能作為聯(lián)機(jī)備份和脫機(jī)備份的一種補(bǔ)充。
完全邏輯備份
完全邏輯備份是將整個(gè)數(shù)據(jù)庫導(dǎo)出到一個(gè)數(shù)據(jù)庫的格式文件中，該文件可以在不同的數(shù)據(jù)庫版本、操作系統(tǒng)和硬件平臺(tái)之間進(jìn)行移植。
指定表的邏輯備份
通過備份工具，可以將指定的數(shù)據(jù)庫表備份出來，這可以避免完全邏輯備份所帶來的時(shí)間和財(cái)力上的浪費(fèi)。
2 歸檔是什么含義
歸檔是歸檔當(dāng)前的聯(lián)機(jī)redo日志文件。
SVRMGR> alter system archive log current;
數(shù)據(jù)庫只有運(yùn)行在ARCHIVELOG模式下，并且能夠進(jìn)行自動(dòng)歸檔，才可以進(jìn)行聯(lián)機(jī)備份。有了聯(lián)機(jī)備份才有可能進(jìn)行完全恢復(fù)。
3 如果一個(gè)表在2004-08-04 10:30:00 被drop，在有完善的歸檔和備份的情況下，如何恢復(fù)
I) 9i 新增的FLASH BACK 應(yīng)該可以;
II) Logminer應(yīng)該可以找出DML。
III)有完善的歸檔和備份，先歸檔當(dāng)前數(shù)據(jù)，然后可以先恢復(fù)到刪除的時(shí)間點(diǎn)之前，把DROP 的表導(dǎo)出來，然后再恢復(fù)到最后歸檔時(shí)間；
不過，我沒試過，都是理論的說!!
4 rman是什么，有何特點(diǎn)
RMAN可以用來備份和還原數(shù)據(jù)庫文件、歸檔日志和控制文件。它也可以用來執(zhí)行完全或不完全的數(shù)據(jù)庫恢復(fù)。
RMAN啟動(dòng)數(shù)據(jù)庫上的Oracle服務(wù)器進(jìn)程來進(jìn)行備份或還原。備份、還原、恢復(fù)是由這些進(jìn)程驅(qū)動(dòng)的。
注意：RMAN不能用于備份初始化參數(shù)文件和口令文件。
具有如下特點(diǎn)：
功能類似物理備份，但比物理備份強(qiáng)大N倍；
可以壓縮空塊；
可以在塊水平上實(shí)現(xiàn)增量；
可以把備份的輸出打包成備份集，也可以按固定大小分割備份集；
備份與恢復(fù)的過程可以自動(dòng)管理；
可以使用腳本（存在Recovery catalog 中）
可以做壞塊監(jiān)測
5 standby的特點(diǎn)
備用數(shù)據(jù)庫（standby database）：ORACLE推出的一種高可用性(HIGH AVAILABLE)數(shù)據(jù)庫方案，
在主節(jié)點(diǎn)與備用節(jié)點(diǎn)間通過日志同步來保證數(shù)據(jù)的同步，備用節(jié)點(diǎn)作為主節(jié)點(diǎn)的備份
可以實(shí)現(xiàn)快速切換與災(zāi)難性恢復(fù),從920開始，還開始支持物理與邏輯備用服務(wù)器。
9i中的三種數(shù)據(jù)保護(hù)模式分別是：
MAXIMIZE PROTECTION ：最大數(shù)據(jù)保護(hù)與無數(shù)據(jù)分歧，LGWR將同時(shí)傳送到備用節(jié)點(diǎn)，
在主節(jié)點(diǎn)事務(wù)確認(rèn)之前，備用節(jié)點(diǎn)也必須完全收到日志數(shù)據(jù)。如果網(wǎng)絡(luò)不好，引起LGWR不能傳送數(shù)據(jù)，將引起嚴(yán)重的性能問題，導(dǎo)致主節(jié)點(diǎn)DOWN機(jī)。
MAXIMIZE AVAILABILITY ：無數(shù)據(jù)丟失模式，允許數(shù)據(jù)分歧，允許異步傳送。
正常情況下運(yùn)行在最大保護(hù)模式，在主節(jié)點(diǎn)與備用節(jié)點(diǎn)的網(wǎng)絡(luò)斷開或連接不正常時(shí)，自動(dòng)切換到最大性能模式，
主節(jié)點(diǎn)的操作還是可以繼續(xù)的。在網(wǎng)絡(luò)不好的情況下有較大的性能影響。
MAXIMIZE PERFORMANCE：這種模式應(yīng)當(dāng)可以說是從8i繼承過來的備用服務(wù)器模式，異步傳送，
無數(shù)據(jù)同步檢查，可能丟失數(shù)據(jù)，但是能獲得主節(jié)點(diǎn)的最大性能。9i在配置DATA GUARD的時(shí)候默認(rèn)就是MAXIMIZE PERFORMANCE
沒實(shí)踐過，只是把答案列出來。
6 對于一個(gè)要求恢復(fù)時(shí)間比較短的系統(tǒng)(數(shù)據(jù)庫50G,每天歸檔5G)，你如何設(shè)計(jì)備份策略
數(shù)據(jù)庫比較大邏輯備份沒什么必要，每天歸檔5G，每周三/周六自動(dòng)歸檔10G，每月RMAN歸檔全庫。
應(yīng)該有standby。
我對備份和恢復(fù)缺少實(shí)踐機(jī)會(huì)，JUST PAPER。
四：系統(tǒng)管理類
1 對于一個(gè)存在系統(tǒng)性能的系統(tǒng)，說出你的診斷處理思路
I) 做statspack收集系統(tǒng)相關(guān)信息
了解系統(tǒng)大致情況/確定是否存在參數(shù)設(shè)置不合適的地方/查看top 5 event/查看top sql等
II) 查v$system_event/v$session_event/v$session_wait
從v$system_event開始,確定需要什么資源(db file sequential read)等
深入研究v$session_event,確定等待事件涉及的會(huì)話
III)從v$session_wait確定詳細(xì)的資源爭用情況
通過v$sql/v$sqltext/v$sqlarea表確定disk_reads、(buffer_gets/executions)值較大的SQL
2 列舉幾種診斷IO、CPU、性能狀況的方法
top
uptime
vmstat
iostat
statspack
sql_trace/tkprof
查v$system_event/v$session_event/v$session_wait
查v$sqlarea(disk_reads或buffer_gets/executions較大的SQL)
或者第三方的監(jiān)視工具，TOAD就不錯(cuò)。
3 對statspack有何認(rèn)識
認(rèn)識不深。僅限了解。StapSpack是Oracle公司提供的一個(gè)收集數(shù)據(jù)庫運(yùn)行性能指標(biāo)的軟件包。
可以做數(shù)據(jù)庫健康檢查報(bào)告。
4 如果系統(tǒng)現(xiàn)在需要在一個(gè)很大的表上創(chuàng)建一個(gè)索引，你會(huì)考慮那些因素，如何做以盡量減小對應(yīng)用的影響
可以先表分析一下，然后測試創(chuàng)建索引前后對應(yīng)用的性能影響；
需要考慮的是該索引列不經(jīng)常更新，不是有很多重復(fù)值的情況時(shí), 在大表中使用索引特別有效.
創(chuàng)建的索引可以跟數(shù)據(jù)表分不同表空間存儲(chǔ)。
5 對raid10 和raid5有何認(rèn)識
幾乎不懂。沒配過磁盤陣列
五：綜合隨意類
1 你最擅長的是oracle哪部分?
pl/sql及sql優(yōu)化
2 喜歡oracle嗎？喜歡上論壇嗎？或者偏好oracle的哪一部分？
喜歡。PL/SQL比較得心應(yīng)手。
3 隨意說說你覺得oracle最有意思的部分或者最困難的部分
我對數(shù)據(jù)庫的備份/恢復(fù)和性能調(diào)優(yōu)經(jīng)驗(yàn)明顯不足，自然覺得有些困難。
基于ORACLE的研究應(yīng)該是個(gè)寬廣的領(lǐng)域，所以我覺得還是有意思的。
4 為何要選擇做DBA呢?
我對數(shù)據(jù)庫的備份/恢復(fù)和性能調(diào)優(yōu)經(jīng)驗(yàn)明顯不足，主要是缺乏環(huán)境和交流。
因此，算不上什么DBA。不過因此我更需要這樣的機(jī)會(huì)。
不過就整個(gè)ORACLE 來說，一直從事與它相關(guān)的工作，感情還是頗深的。放棄可惜。而且就技術(shù)本身而言我覺得自己還是有學(xué)習(xí)和創(chuàng)新的能力，它的諸如數(shù)據(jù)倉庫，數(shù)據(jù)挖掘之類的領(lǐng)域也很廣。
【阿里巴巴的Oracle筆試題】相關(guān)文章：
oracle筆試題及答案08-16
oracle 技術(shù)筆試題07-31
oracle Management Trainee筆試題07-31
oracle financials developer筆試題07-31
Oracle的sql語句模擬試題及答案08-27
Oracle Management Trainee英文筆試題07-31
oracle(甲骨文)大量筆試題07-19
阿里巴巴筆試題08-10