阿里巴巴的Oracle DBA筆試題參考答案 - SQL tuning類

SQL tuning類參考解答：
　　一：SQL tuning 類

　　1:列舉幾種表連線方式

 程式程式碼
　　hash join/merge join/nest loop(cluster join)/index join

 

　　2:不借助第三方工具，怎樣檢視sql的執行計劃

 程式程式碼
　　set autot on
　　explain plan set statement_id = &item_id for &sql;
　　select * from table(dbms_xplan.display);
　　http://download-west.oracle.com/ ... /b10752/ex_plan.htm

 

　　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/patitional 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
oracle can automatically create outlines for all SQL statements, or you can create them for specific SQL statements. In either case, the outlines derive their input from the optimizer.

oracle creates stored outlines automatically when you set the initialization parameter Create_STORED_OUTLINES to true. When activated, oracle creates outlines for all compiled SQL statements. You can create stored outlines for specific statements using the Create OUTLINE statement.

Creating Outlines：http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/outlines.htm

　　11:和排序相關的記憶體在8i和9i分別怎樣調整，臨時表空間的作用是什麼

　　Oracle 8i中sort_area_size/sort_area_retained_size決定了排序所需要的記憶體

　　如果排序操作不能在sort_area_size中完成,就會用到temp表空間

　　Oracle 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(,b number(,c number(,d number();
　　/
　　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

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/wyymaomi/archive/2008/11/23/3356955.aspx

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10113559/viewspace-616564/，如需轉載，請註明出處，否則將追究法律責任。