oracle開發(fā)人員面試題.doc

一、基礎(chǔ)概念1. 列舉幾種表連接方式Answer：等連接（內(nèi)連接）、非等連接、自連接、外連接（左、右、全）Or hash join/merge join/nest loop(cluster join)/index join ？ORACLE 8i，9i 表連接方法。 一般的相等連接： select * from a, b where a.id = b.id; 這個就屬于內(nèi)連接。 對于外連接：Oracle中可以使用“(+) ”來表示，9i可以使用LEFT/RIGHT/FULL OUTER JOIN LEFT OUTER JOIN：左外關(guān)聯(lián)SELECT e.last_name, e.department_id, d.department_nameFROM employees eLEFT OUTER JOIN departments dON (e.department_id = d.department_id);等價于SELECT e.last_name, e.department_id, d.department_nameFROM employees e, departments dWHERE e.department_id=d.department_id(+)結(jié)果為：所有員工及對應(yīng)部門的記錄，包括沒有對應(yīng)部門編號department_id的員工記錄。 RIGHT OUTER JOIN：右外關(guān)聯(lián)SELECT e.last_name, e.department_id, d.department_nameFROM employees eRIGHT OUTER JOIN departments dON (e.department_id = d.department_id);等價于SELECT e.last_name, e.department_id, d.department_nameFROM employees e, departments dWHERE e.department_id(+)=d.department_id結(jié)果為：所有員工及對應(yīng)部門的記錄，包括沒有任何員工的部門記錄。 FULL OUTER JOIN：全外關(guān)聯(lián)SELECT e.last_name, e.department_id, d.department_nameFROM employees eFULL OUTER JOIN departments dON (e.department_id = d.department_id);結(jié)果為：所有員工及對應(yīng)部門的記錄，包括沒有對應(yīng)部門編號department_id的員工記錄和沒有任何員工的部門記錄。ORACLE8i是不直接支持完全外連接的語法，也就是說不能在左右兩個表上同時加上(+)，下面是在ORACLE8i可以參考的完全外連接語法select t1.id,t2.id from table1 t1,table t2 where t1.id=t2.id(+)unionselect t1.id,t2.id from table1 t1,table t2 where t1.id(+)=t2.id連接類型定義 圖示 例子內(nèi)連接 只連接匹配的行 select A.c1,B.c2 from A join B on A.c3 = B.c3;左外連接 包含左邊表的全部行（不管右邊的表中是否存在與它們匹配的行）以及右邊表中全部匹配的行 select A.c1,B.c2 from A left join B on A.c3 = B.c3;右外連接 包含右邊表的全部行（不管左邊的表中是否存在與它們匹配的行）以及左邊表中全部匹配的行 select A.c1,B.c2 from A right join B on A.c3 = B.c3;全外連接 包含左、右兩個表的全部行，不管在另一邊的表中是否存在與它們匹配的行 select A.c1,B.c2 from A full join B on A.c3 = B.c3;（theta）連接 使用等值以外的條件來匹配左、右兩個表中的行 select A.c1,B.c2 from A join B on A.c3 != B.c3;交叉連接 生成笛卡爾積它不使用任何匹配或者選取條件，而是直接將一個數(shù)據(jù)源中的每個行與另一個數(shù)據(jù)源的每個行一一匹配 select A.c1,B.c2 from A,B;2. 不借助第三方工具，怎樣查看sql的執(zhí)行計劃I) 使用Explain Plan,查詢PLAN_TABLE;EXPLAIN PLANSET STATEMENT_ID=QUERY1FORSELECT *FROM aWHERE aa=1;SELECT operation, options, object_name, object_type, ID, parent_idFROM plan_tableWHERE STATEMENT_ID = QUERY1ORDER BY ID;II)SQLPLUS中的SET TRACE 即可看到Execution Plan StatisticsSET 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í)行路徑來運行查詢。CBO根據(jù)表統(tǒng)計找到最低成本的訪問數(shù)據(jù)的方法確定執(zhí)行計劃。使用CBO需要注意：I) 需要經(jīng)常對表進行ANALYZE命令進行分析統(tǒng)計;II) 需要穩(wěn)定執(zhí)行計劃;III)需要使用提示(Hint);使用RULE需要注意：I) 選擇最有效率的表名順序II) 優(yōu)化SQL的寫法;在optimizer_mode=choose時,如果表有統(tǒng)計信息（分區(qū)表外）,優(yōu)化器將選擇CBO,否則選RBO。RBO遵循簡單的分級方法學(xué),使用15種級別要點，當(dāng)接收到查詢，優(yōu)化器將評估使用到的要點數(shù)目,然后選擇最佳級別（最少的數(shù)量）的執(zhí)行路徑來運行查詢。CBO嘗試找到最低成本的訪問數(shù)據(jù)的方法,為了最大的吞吐量或最快的初始響應(yīng)時間,計算使用不同的執(zhí)行計劃的成本，并選擇成本最低的一個,關(guān)于表的數(shù)據(jù)內(nèi)容的統(tǒng)計被用于確定執(zhí)行計劃。4. 如何定位重要(消耗資源多)的SQL使用CPU多的用戶sessionSELECT a.SID, spid, status, SUBSTR (gram, 1, 40) prog, a.terminal,a.SQL_TEXT, osuser, VALUE / 60 / 100 VALUEFROM v$session a, v$process b, v$sesstat cWHERE c.statistic# = 12 AND c.SID = a.SID AND a.paddr = b.addrORDER BY VALUE DESC;select sql_text from v$sqlwhere disk_reads 1000 or (executions 0 and buffer_gets/executions 30000);5. 如何跟蹤某個session的SQL利用TRACE 跟蹤ALTER SESSION SET SQLTRACE ON;COLUMN SQL format a200;SELECT machine, sql_text SQLFROM v$sqltext a, v$session bWHERE address = sql_addressAND machine = &AORDER BY hash_value, piece;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調(diào)整最關(guān)注的是什么檢查系統(tǒng)的I/O問題sard能檢查整個系統(tǒng)的iostat（IO statistics）查看該SQL的response time(db block gets/consistent gets/physical reads/sorts (disk)7. 說說你對索引的認識（索引的結(jié)構(gòu)、對dml影響、對查詢影響、為什么提高查詢性能）索引有B-TREE、BIT、CLUSTER等類型。ORACLE使用了一個復(fù)雜的自平衡B-tree結(jié)構(gòu);通常來說，在表上建立恰當(dāng)?shù)乃饕?，查詢時會改進查詢性能。但在進行插入、刪除、修改時，同時會進行索引的修改，在性能上有一定的影響。有索引且查詢條件能使用索引時，數(shù)據(jù)庫會先度取索引，根據(jù)索引內(nèi)容和查詢條件，查詢出ROWID，再根據(jù)ROWID取出需要的數(shù)據(jù)。由于索引內(nèi)容通常比全表內(nèi)容要少很多，因此通過先讀索引，能減少I/O，提高查詢性能。b-tree index/bitmap index/function index/patitional index(local/global)索引通常能提高select/update/delete的性能,會降低insert的速度,8. 使用索引查詢一定能提高查詢的性能嗎？為什么通常,通過索引查詢數(shù)據(jù)比全表掃描要快.但是我們也必須注意到它的代價.索引需要空間來存儲,也需要定期維護, 每當(dāng)有記錄在表中增減或索引列被修改時,索引本身也會被修改. 這意味著每條記錄的INSERT,DELETE,UPDATE將為此多付出4,5 次的磁盤I/O. 因為索引需要額外的存儲空間和處理,那些不必要的索引反而會使查詢反應(yīng)時間變慢.使用索引查詢不一定能提高查詢性能,索引范圍查詢(INDEX RANGE SCAN)適用于兩種情況:基于一個范圍的檢索,一般查詢返回結(jié)果集小于表中記錄數(shù)的30%宜采用;基于非唯一性索引的檢索索引就是為了提高查詢性能而存在的,如果在查詢中索引沒有提高性能,只能說是用錯了索引,或者講是場合不同9. 綁定變量是什么？綁定變量有什么優(yōu)缺點？綁定變量是指在SQL語句中使用變量，改變變量的值來改變SQL語句的執(zhí)行結(jié)果。優(yōu)點：使用綁定變量，可以減少SQL語句的解析，能減少數(shù)據(jù)庫引擎消耗在SQL語句解析上的資源。提高了編程效率和可靠性。減少訪問數(shù)據(jù)庫的次數(shù), 就能實際上減少ORACLE的工作量。缺點：經(jīng)常需要使用動態(tài)SQL的寫法，由于參數(shù)的不同，可能SQL的執(zhí)行效率不同；綁定變量是相對文本變量來講的,所謂文本變量是指在SQL直接書寫查詢條件，這樣的SQL在不同條件下需要反復(fù)解析,綁定變量是指使用變量來代替直接書寫條件，查詢bind value在運行時傳遞，然后綁定執(zhí)行。優(yōu)點是減少硬解析,降低CPU的爭用,節(jié)省shared_pool缺點是不能使用histogram,sql優(yōu)化比較困難10. 如何穩(wěn)定(固定)執(zhí)行計劃可以在SQL語句中指定執(zhí)行計劃。使用HINTS;query_rewrite_enabled = truestar_transformation_enabled = trueoptimizer_features_enable = 9.2.0創(chuàng)建并使用stored outline11. 和排序相關(guān)的內(nèi)存在8i和9i分別怎樣調(diào)整，臨時表空間的作用是什么SORT_AREA_SIZE 在進行排序操作時，如果排序的內(nèi)容太多，內(nèi)存里不能全部放下，則需要進行外部排序，此時需要利用臨時表空間來存放排序的中間結(jié)果。8i中sort_area_size/sort_area_retained_size決定了排序所需要的內(nèi)存， 如果排序操作不能在sort_area_size中完成,就會用到temp表空間9i中如果workarea_size_policy=auto時,排序在pga內(nèi)進行,通常pga_aggregate_target的1/20可以用來進行disk sort;如果workarea_size_policy=manual時,排序需要的內(nèi)存由sort_area_size決定， 在執(zhí)行order by/group by/distinct/union/create index/index rebuild/minus等操作時,如果在pga或sort_area_size中不能完成,排序?qū)⒃谂R時表空間進行（disk sort）,臨時表空間主要作用就是完成系統(tǒng)中的disk sort.12. 存在表T(a,b,c,d),要根據(jù)字段c排序后取第2130條記錄顯示，請給出sqlSELECT *FROM (SELECT ROWNUM AS row_num, tmp_tab.*FROM (SELECT a, b, c, dFROM TORDER BY c) tmp_tabWHERE ROWNUM = 20ORDER BY row_num;create table t(a number(,b number(,c number(,d number();/beginfor i in 1 . 300 loopinsert 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 30minusselect * from (select * from test order by c desc) y where rownum alter system archive log current;數(shù)據(jù)庫只有運行在ARCHIVELOG模式下，并且能夠進行自動歸檔，才可以進行聯(lián)機備份。有了聯(lián)機備份才有可能進行完全恢復(fù)。3 如果一個表在2004-08-04 10:30:00 被drop，在有完善的歸檔和備份的情況下，如何恢復(fù)9i 新增的FLASH BACK 應(yīng)該可以;Logminer應(yīng)該可以找出DML。有完善的歸檔和備份，先歸檔當(dāng)前數(shù)據(jù)，然后可以先恢復(fù)到刪除的時間點之前，把DROP 的表導(dǎo)出來，然后再恢復(fù)到最后歸檔時間；手工拷貝回所有備份的數(shù)據(jù)文件Sqlstartup mount;sqlalter database recover automatic until time 2004-08-04:10:30:00;sqlalter database open resetlogs;4 rman是什么，有何特點RMAN(Recovery Manager)是DBA的一個重要工具，用于備份、還原和恢復(fù)oracle數(shù)據(jù)庫, RMAN 可以用來備份和恢復(fù)數(shù)據(jù)庫文件、歸檔日志、控制文件、系統(tǒng)參數(shù)文件,也可以用來執(zhí)行完全或不完全的數(shù)據(jù)庫恢復(fù)。RMAN有三種不同的用戶接口：COMMAND LINE方式、GUI 方式（集成在OEM 中的備份管理器）、API 方式（用于集成到第三方的備份軟件中）。具有如下特點：1）功能類似物理備份，但比物理備份強大N倍；2）可以壓縮空塊；3）可以在塊水平上實現(xiàn)增量；4）可以把備份的輸出打包成備份集，也可以按固定大小分割備份集；5）備份與恢復(fù)的過程可以自動管理；6）可以使用腳本（存在Recovery catalog 中）7）可以做壞塊監(jiān)測5 standby的特點備用數(shù)據(jù)庫（standby database）：ORACLE推出的一種高可用性(HIGH AVAILABLE)數(shù)據(jù)庫方案，在主節(jié)點與備用節(jié)點間通過日志同步來保證數(shù)據(jù)的同步，備用節(jié)點作為主節(jié)點的備份，可以實現(xiàn)快速切換與災(zāi)難性恢復(fù),從920開始，還開始支持物理與邏輯備用服務(wù)器。9i中的三種數(shù)據(jù)保護模式分別是：1)、MAXIMIZE PROTECTION ：最大數(shù)據(jù)保護與無數(shù)據(jù)分歧，LGWR將同時傳送到備用節(jié)點，在主節(jié)點事務(wù)確認之前，備用節(jié)點也必須完全收到日志數(shù)據(jù)。如果網(wǎng)絡(luò)不好，引起LGWR不能傳送數(shù)據(jù)，將引起嚴重的性能問題，導(dǎo)致主節(jié)點DOWN機。2)、MAXIMIZE AVAILABILITY ：無數(shù)據(jù)丟失模式，允許數(shù)據(jù)分歧，允許異步傳送。正常情況下運行在最大保護模式，在主節(jié)點與備用節(jié)點的網(wǎng)絡(luò)斷開或連接不正常時，自動切換到最大性能模式，主節(jié)點的操作還是可以繼續(xù)的。在網(wǎng)絡(luò)不好的情況下有較大的性能影響。3)、MAXIMIZE PERFORMANCE：這種模式應(yīng)當(dāng)可以說是從8i繼承過來的備用服務(wù)器模式，異步傳送，無數(shù)據(jù)同步檢查，可能丟失數(shù)據(jù)，但是能獲得主節(jié)點的最大性能。9i在配置DATA GUARD的時候默認就是MAXIMIZE PERFORMANCE6 對于一個要求恢復(fù)時間比較短的系統(tǒng)(數(shù)據(jù)庫50G,每天歸檔5G)，你如何設(shè)計備份策略數(shù)據(jù)庫比較大邏輯備份沒什么必要，每天歸檔5G，每周三/周六自動歸檔10G，每月RMAN歸檔全庫。應(yīng)該有standby。rman/每月一號 level 0 每周末/周三 level 1 其它每天level 2四：系統(tǒng)管理類1. 對于一個存在系統(tǒng)性能的系統(tǒng)，說出你的診斷處理思路 做statspack收集系統(tǒng)相關(guān)信息 了解系統(tǒng)大致情況/確定是否存在參數(shù)設(shè)置不合適的地方/查看top 5 event/查看top sql等 查v$system_event/v$session_event/v$session_wait 從v$system_event開始,確定需要什么資源(db file sequential read)等，深入研究v$session_event,確定等待事件涉及的會話，從v$session_wait確定詳細的資源爭用情況(p1-p3的值:file_id/block_id/blocks等) 通過v$sql/v$sqltext/v$sqlarea表確定disk_reads、(buffer_gets/executions)值較大的SQL2. 列舉幾種診斷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就不錯。3. 對statspack有何認識認識不深。僅限了解。StapSpack是Oracle公司提供的一個收集數(shù)據(jù)庫運行性能指標的軟件包?？梢宰鰯?shù)據(jù)庫健康檢查報告。StapSpack是Oracle公司提供的一個收集數(shù)據(jù)庫運行性能指標的軟件包，該軟件包從8i起，在9i、10g都有顯著的增強該軟件包的輔助表（存儲相關(guān)參數(shù)與收集的性能指標的表）由最初的25個增長到43個收集級別參數(shù)由原來的3個（0、5、10）增加到5個（0、5、6、7、10）通過分析收集的性能指標，數(shù)據(jù)庫管理員可以詳細地了解數(shù)據(jù)庫目前的運行情況，對數(shù)據(jù)庫實例、等待事件、SQL等進行優(yōu)化調(diào)整利用statspack收集的snapshot,可以統(tǒng)計制作數(shù)據(jù)庫的各種性能指標的統(tǒng)計趨勢圖表。4. 如果系統(tǒng)現(xiàn)在需要在一個很大的表上創(chuàng)建一個索引，你會考慮那些因素，如何做以盡量減小對應(yīng)用的影響可以先表分析一下，然后測試創(chuàng)建索引前后對應(yīng)用的性能影響；需要考慮的是該索引列不經(jīng)常更新，不是有很多重復(fù)值的情況時, 在大表中使用索引特別有效. 創(chuàng)建的索引可以跟數(shù)據(jù)表分不同表空間存儲。在系統(tǒng)比較空閑時nologging選項（如果有dataguard則不可以使用nologging）大的sort_ared_size或pga_aggregate_target較大5. 對raid10 和raid5有何認識RAID 10(或稱RAID 10)與RAID 01不同，它是用硬盤驅(qū)動器先組成RAID 1陣列，然后在RAID 1陣列之間再組成RAID 0陣列。RAID 10模式同RAID 0+1模式一樣具有良好的數(shù)據(jù)傳輸性能，但卻比RAID 0+1具有更高的可靠性。RAID 10陣列的實際容量為Mn/2，磁盤利用率為50。RAID 10也需要至少4個硬盤驅(qū)動器構(gòu)成，因而價格昂貴。RAID 10的可靠性同RAID 1一樣，但由于RAID 10硬盤驅(qū)動器之間有數(shù)據(jù)分割，因而數(shù)據(jù)傳輸性能優(yōu)良。RAID 5與RAID 3很相似，不同之處在于RAID 5的奇偶校驗信息也同數(shù)據(jù)一樣被分割保存到所有的硬盤驅(qū)動器，而不是寫入一個指定的硬盤驅(qū)動器，從而消除了單個奇偶校驗硬盤驅(qū)動器的瓶頸問題。RAID 5磁盤陣列的性能比RAID 3有所提高，但仍然需要至少3塊硬盤驅(qū)動器。其實際容量為M(n-1)，磁盤利用率為(n-1)/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)驗明顯不足，自然覺得有些困難?；贠RACLE的研究應(yīng)該是個寬廣的領(lǐng)域，所以我覺得還是有意思的。4. 為何要選擇做DBA呢?我對數(shù)據(jù)庫的備份/恢復(fù)和性能調(diào)優(yōu)經(jīng)驗明顯不足，主要是缺乏環(huán)境和交流。因此，算不上什么DBA。不過因此我更需要這樣的機會。不過就整個ORACLE 來說，一直從事與它相關(guān)的工作，感情還是頗深的。放棄可惜。而且就技術(shù)本身而言我覺得自己還是有學(xué)習(xí)和創(chuàng)新的能力，它的諸如數(shù)據(jù)倉庫，數(shù)據(jù)挖掘之類的領(lǐng)域也很廣。 六：Databases Questions & Answers1. What are two methods of retrieving SQL?2. What cursor type do you use to retrieve multiple recordsets?3. What action do you have to perform before retrieving data from the next result set of a stored procedure?Move the cursor down one row from its current position. A ResultSet cursor is initially positioned before the first row. Before you can get to the first row, you would need to Move the cursor down by one row ( For ex: in java the first call to next makes the first row the current row; the second call makes the second row the current row, and so on).4. What is the basic form of a SQL statement to read data out of a table?SELECT * FROM table_name;5. What structure can you have the database make to speed up table reads?The question is not correct. “What structure can you have the database make to speed up table reads?” It is not clear what exactly the term “structure” means in this case. Follow the rules of DB tuning we have to:1) properly use indexes ( different types of indexes)2) properly locate different DB objects across different tablespaces, files and so on.3) Create a special space (tablespace) to locate some of the data with special datatypes( for example CLOB, LOB and )6. What is a “join”?Joins merge the data of two related tables into a single result set, presenting a denormalized view of the data.7. What is a “constraint”?A constraint allows you to apply simple referential integrity checks to a table. There are 5 primary types of constraints that are currently supported by SQL Server:PRIMARY/UNIQUE enforces uniqueness of a particular table column.DEFAULT specifies a default value for a column in case an insert operation does not provide one.FOREIGN KEY validates that every value in a column exists in a column of another table.CHECK checks that every value stored in a column is in some specified listNOT NULL is a constraint which does not allow values in the specific column to be null. And also it is the only constraint which is not a table level constraint.8. What is a “primary key”?Primary Key is a type of a constraint enforcing uniqueness and data integrity for each row of a table. All columns participating in a primary key constraint must possess the NOT NULL property.9. What is a “functional dependency”? How does it relate to database table design?What functional dependence in the context of a database means is that: Assume that a table exists in the database called TABLE with a composite primary key (A, B) and other non-key attributes (C, D, E). Functional dependency in general, would mean that any non-key attribute C D or E being dependent on the primary key (A and B) in our table here.Partial functional dependency, on the other hand, is another corollary of the above, which states that all non-key attributes C D or E if dependent on the subset of the primary key (A and B) and not on it as a whole.Example :-Fully Functional Dependent : C D E A BPartial Functional dependency : C A, D E BHope that helps!10. What is a “trigger”?A trigger is a database object directly associated with a particular table. It fires whenever a specific statement/type of statement is issued against that table. The types of statements are insert, update, delete and query statements. Basically, trigger is a set of SQL statements that execute in response to a data modification/retrieval event on a table.Other than table triggers there are also schema and database triggers. These can be made to fire when new objects are created, when a user logs in, when the database shutdown etc. Table level triggers can be classified into row and statement level triggers and those can be further broken down into before and after triggers. Before triggers can modify data.11. What is “index covering” of a query?A nonclustered index that includes (or covers) all columns used in a query is called a covering index. When SQL server can use a nonclustered index to resolve the query, it will prefer to scan the index rather than the table, which typically takes fewer data pages. If your query uses only columns included in the index, then SQL server may scan this index to produce the desired output.12. What is a SQL view?View is a precompli