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