阿里巴巴的Oracle筆試題

阿里巴巴的Oracle筆試題

DBA筆試考題
2007年06月27日 星期三 17:59
一：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影響、對查詢影響、為什么提高查詢性能）
          索引有B-TREE、BIT、CLUSTER等類型。ORACLE使用了一個復雜的自平衡B-tree結構;
          通常來說，在表上建立恰當的索引，查詢時會改進查詢性能。但在進行插入、刪除、修改時，同時會進行索引的修改，在性能上有一定的影響。
        有索引且查詢條件能使用索引時，數據庫會先度取索引，根據索引內容和查詢條件，查詢出ROWID，再根據ROWID取出需要的數據。由于索引內容通常比全表內容要少很多，因此通過先讀索引，能減少I/O，提高查詢性能。
8 使用索引查詢一定能提高查詢的性能嗎？為什么
         通常,通過索引查詢數據比全表掃描要快.但是我們也必須注意到它的代價.
         索引需要空間來存儲,也需要定期維護, 每當有記錄在表中增減或索引列被修改時,
          索引本身也會被修改. 這意味著每條記錄的INSERT,DELETE,UPDATE將為此多付出4,5 次的磁盤I/O. 因為索引需要額外的存儲空間和處理,那些不必要的索引反而會使查詢反應時間變慢.
          使用索引查詢不一定能提高查詢性能,索引范圍查詢(INDEX RANGE SCAN)適用于兩種情況:
          基于一個范圍的檢索,一般查詢返回結果集小于表中記錄數的30%宜采用;
          基于非唯一性索引的檢索
9 綁定變量是什么？綁定變量有什么優缺點？
          綁定變量是指在SQL語句中使用變量，改變變量的值來改變SQL語句的執行結果。
          優點：使用綁定變量，可以減少SQL語句的解析，能減少數據庫引擎消耗在SQL語句解析上的資源。提高了編程效率和可靠性。減少訪問數據庫的次數, 就能實際上減少ORACLE的工作量。
          缺點：經常需要使用動態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之間的關系
          tablespace    : 一個數據庫劃分為一個或多個邏輯單位，該邏輯單位成為表空間;每一個表空間可能包含一個或多個 Segment;
          Segments      : Segment指在tablespace中為特定邏輯存儲結構分配的空間。每一個段是由一個或多個extent組成。包括數據段、索引段、回滾段和臨時段。
          Extents       : 一個 extent 由一系列連續的 Oracle blocks組成.ORACLE為通過extent 來給segment分配空間。
          Data Blocks   ：Oracle 數據庫最小的I/O存儲單位，一個data block對應一個或多個分配給data file的操作系統塊。
3 描述tablespace和datafile之間的關系
◎      一個表空間可包含一個或多個數據文件。
◎      表空間利用增加或擴展數據文件擴大表空間，表空間的大小為組成該表空間的數據文件大小的和。
◎      一個datafile只能屬于一個表空間;
4 本地管理表空間和字典管理表空間的特點，ASSM有什么特點
         本地管理表空間：（9i默認）
         空閑塊列表存儲在表空間的數據文件頭。
         特點：減少數據字典表的競爭，當分配和收縮空間時會產生回滾，不需要合并
         字典管理的表空間：（8i默認）
         空閑塊列表存儲在數據庫中的字典表里.
         特點：片由數據字典管理，可能造成字典表的爭用。存儲在表空間的每一個段都會有不同的存儲字句，需要合并相鄰的塊;
5 回滾段的作用是什么
回滾段用于保存數據修改前的映象，這些信息用于生成讀一致性數據庫信息、在數據庫恢復和Rollback時使用。一個事務只能使用一個回滾段。
6 日志的作用是什么
日志文件（Log File）記錄所有對數據庫數據的修改，主要是保護數據庫以防止故障,以及恢復數據時使用。其特點如下：
a)每一個數據庫至少包含兩個日志文件組。每個日志文件組至少包含兩個日志文件成員。
b)日志文件組以循環方式進行寫操作。
c)每一個日志文件成員對應一個物理文件。
7 SGA主要有那些部分，主要作用是什么
系統全局區（SGA）:是ORACLE為實例分配的一組共享緩沖存儲區，用于存放數據庫數據和控制信息，以實現對數據庫數據的管理和操作。
SGA主要包括:
        a)共享池(shared pool) ：用來存儲最近執行的SQL語句和最近使用的數據字典的數據。
        b)數據緩沖區 (database buffer cache)：用來存儲最近從數據文件中讀寫過的數據。
        c)重作日志緩沖區（redo log buffer）：用來記錄服務或后臺進程對數據庫的操作。
另外在SGA中還有兩個可選的內存結構：
        d)Java pool:   用來存儲Java代碼。
        e)Large pool: 用來存儲不與SQL直接相關的大型內存結構。備份、恢復使用。
8 Oracle系統進程主要有哪些，作用是什么
        數據寫進程(DBWR)：負責將更改的數據從數據庫緩沖區高速緩存寫入數據文件
        日志寫進程(LGWR)：將重做日志緩沖區中的更改寫入在線重做日志文件
        系統監控   (SMON): 檢查數據庫的一致性如有必要還會在數據庫打開時啟動數據庫的恢復
       進程監控   (PMON): 負責在一個Oracle 進程失敗時清理資源
        檢查點進程(CKPT)：負責在每當緩沖區高速緩存中的更改永久地記錄在數據庫中時,更新控制文件和數據文件中的數據庫狀態信息。
        歸檔進程   (ARCH)：在每次日志切換時把已滿的日志組進行備份或歸檔
        恢復進程   (RECO): 保證分布式事務的一致性,在分布式事務中,要么同時commit,要么同時rollback;
        作業調度器(CJQ ):   負責將調度與執行系統中已定義好的job,完成一些預定義的工作.
三：備份恢復類
1 備份如何分類
          物理備份
        物理備份是最主要的備份方式。用于保證數據庫在最小的數據庫丟失或沒有數據丟失的情況下得到恢復。
          冷物理
        冷物理備份提供了最簡單和最直接的方法保護數據庫因物理損壞丟失。建議在以下幾種情況中使用。
        對一個已經存在大最數據量的數據庫，在晚間數據庫可以關閉，此時應用冷物理備份。
        對需對數據庫服務器進行升級，（如更換硬盤），此時需要備份數據庫信息，并在新的硬盤中恢復這些數據信息，建議采用冷物理備份。
          熱物理
        主要是指備份過程在數據庫打開并且用戶可以使用的情況下進行。需要執行熱物理備份的情況有：
        由于數據庫性質要求不間斷工作，因而此時只能采用熱物理備份。
        由于備份的要求的時間過長，而數據庫只能短時間關閉時。
          邏輯備份 (EXP/IMP)
        邏輯備份用于實現數據庫對象的恢復。但不是基于時間點可完全恢復的備份策略。只能作為聯機備份和脫機備份的一種補充。
          完全邏輯備份

        完全邏輯備份是將整個數據庫導出到一個數據庫的格式文件中，該文件可以在不同的數據庫版本、操作系統和硬件平臺之間進行移植。
          指定表的邏輯備份
        通過備份工具，可以將指定的數據庫表備份出來，這可以避免完全邏輯備份所帶來的時間和財力上的浪費。
2 歸檔是什么含義
          歸檔是歸檔當前的聯機redo日志文件。
          SVRMGR> alter system archive log current;
         數據庫只有運行在ARCHIVELOG模式下，并且能夠進行自動歸檔，才可以進行聯機備份。有了聯機備份才有可能進行完全恢復。
3 如果一個表在2004-08-04 10:30:00 被drop，在有完善的歸檔和備份的情況下，如何恢復
I)   9i 新增的FLASH BACK 應該可以;
II) Logminer應該可以找出DML。
III)有完善的歸檔和備份，先歸檔當前數據，然后可以先恢復到刪除的時間點之前，把DROP 的表導出來，然后再恢復到最后歸檔時間；
不過，我沒試過，都是理論的說!!
4 rman是什么，有何特點
          RMAN可以用來備份和還原數據庫文件、歸檔日志和控制文件。它也可以用來執行完全或不完全的數據庫恢復。
          RMAN啟動數據庫上的Oracle服務器進程來進行備份或還原。備份、還原、恢復是由這些進程驅動的。
          注意：RMAN不能用于備份初始化參數文件和口令文件。
具有如下特點：
          功能類似物理備份，但比物理備份強大N倍；
          可以壓縮空塊；
          可以在塊水平上實現增量；
          可以把備份的輸出打包成備份集，也可以按固定大小分割備份集；
          備份與恢復的過程可以自動管理；
          可以使用腳本（存在Recovery catalog 中）
          可以做壞塊監測
5 standby的特點
備用數據庫（standby database）：ORACLE推出的一種高可用性(HIGH AVAILABLE)數據庫方案，
在主節點與備用節點間通過日志同步來保證數據的同步，備用節點作為主節點的備份
可以實現快速切換與災難性恢復,從920開始，還開始支持物理與邏輯備用服務器。
9i中的三種數據保護模式分別是：
          MAXIMIZE PROTECTION   ：最大數據保護與無數據分歧，LGWR將同時傳送到備用節點，
        在主節點事務確認之前，備用節點也必須完全收到日志數據。如果網絡不好，引起LGWR不能傳送數據，將引起嚴重的性能問題，導致主節點DOWN機。
         MAXIMIZE AVAILABILITY ：無數據丟失模式，允許數據分歧，允許異步傳送。
       正常情況下運行在最大保護模式，在主節點與備用節點的網絡斷開或連接不正常時，自動切換到最大性能模式，
        主節點的操作還是可以繼續的。在網絡不好的情況下有較大的性能影響。
          MAXIMIZE PERFORMANCE：這種模式應當可以說是從8i繼承過來的備用服務器模式，異步傳送，
        無數據同步檢查，可能丟失數據，但是能獲得主節點的最大性能。9i在配置DATA GUARD的時候默認就是MAXIMIZE PERFORMANCE
沒實踐過，只是把答案列出來。
6 對于一個要求恢復時間比較短的系統(數據庫50G,每天歸檔5G)，你如何設計備份策略
數據庫比較大邏輯備份沒什么必要，每天歸檔5G，每周三/周六自動歸檔10G，每月RMAN歸檔全庫。
應該有standby。
我對備份和恢復缺少實踐機會，JUST PAPER。
四：系統管理類
1 對于一個存在系統性能的系統，說出你的診斷處理思路
I)   做statspack收集系統相關信息
了解系統大致情況/確定是否存在參數設置不合適的地方/查看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,確定等待事件涉及的會話
III)從v$session_wait確定詳細的資源爭用情況

通過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公司提供的一個收集數據庫運行性能指標的軟件包。
可以做數據庫健康檢查報告。
4 如果系統現在需要在一個很大的表上創建一個索引，你會考慮那些因素，如何做以盡量減小對應用的影響
可以先表分析一下，然后測試創建索引前后對應用的性能影響；
需要考慮的是該索引列不經常更新，不是有很多重復值的情況時, 在大表中使用索引特別有效.
創建的索引可以跟數據表分不同表空間存儲。
5 對raid10 和raid5有何認識
幾乎不懂。沒配過磁盤陣列
五：綜合隨意類
1 你最擅長的是oracle哪部分?
pl/sql及sql優化
2 喜歡oracle嗎？喜歡上論壇嗎？或者偏好oracle的哪一部分？
喜歡。PL/SQL比較得心應手。
3 隨意說說你覺得oracle最有意思的部分或者最困難的部分
我對數據庫的備份/恢復和性能調優經驗明顯不足，自然覺得有些困難。
基于ORACLE的研究應該是個寬廣的領域，所以我覺得還是有意思的。
4 為何要選擇做DBA呢?
我對數據庫的備份/恢復和性能調優經驗明顯不足，主要是缺乏環境和交流。
因此，算不上什么DBA。不過因此我更需要這樣的機會。
不過就整個ORACLE 來說，一直從事與它相關的工作，感情還是頗深的。放棄可惜。而且就技術本身而言我覺得自己還是有學習和創新的能力，它的諸如數據倉庫，數據挖掘之類的領域也很廣。

【阿里巴巴的Oracle筆試題】相關文章：

阿里巴巴非技術類筆經及試題08-13

oracle筆試題及答案08-16

oracle 技術筆試題07-31

oracle Management Trainee筆試題07-31

oracle financials developer筆試題07-31

Oracle的sql語句模擬試題及答案08-27

Oracle Management Trainee英文筆試題07-31

oracle(甲骨文)大量筆試題07-19

甲骨文(oracle)技術筆試題07-13

阿里巴巴筆試題08-10