Oracle查詢效能最佳化

資料庫最基本的任務是儲存、管理資料，而終端使用者唯一能看到的資料庫特性就是其效能：資料庫以何速度處理某一指定查詢的結果，並且將結果返回到使用者所用的工具和應用程式。從大多數系統的應用執行個體來看，查詢操作在各種資料庫操作中所佔據的比重最大、查閱新聞、 查看檔案、 查詢統計資訊等。因此，資料庫查詢操作的效率是影響一個應用系統回應時間的關鍵因素。隨著一個應用系統中資料的動態增長，資料量變大，資料庫查詢效率就會有所降低，應用系統的響應速度也隨之減慢，尤其對于海量資料的管理和查詢問題就更加突出，Oracle查詢最佳化就顯得尤為重要。   

目前通用的資料庫產品有很多種，其中Oracle資料庫以其支援大資料庫、多使用者的高效能交易處理, 對業界各項工業標準的支援,完整的安全和完整性控制，支援分散式資料庫利分布處理 具有可移植性、可相容性和可串連性等突出優點倍受使用者喜愛，應用較為廣泛，在互連網資料庫平台上處於領先地位、其Spatial技術能更加有效地管理地理資訊，實現海量空間資訊的儲存和管理。本文結合Oracle資料庫應用經驗，從命中率提高、多表查詢最佳化、大表查詢最佳化和SQL最佳化等四個方面闡述Oracle查詢最佳化的經驗和方法。

Oracle查詢最佳化第一方面：Oracle資料查詢命中率的提高   

“命中率（HITRATIO） 是指直接從記憶體中取得資料而不從磁碟中取得資料的比率，也就是查詢請求的資料區塊已經在記憶體中次數的百分比”。影響命中率的因素有四種：字典表活動、臨時段活動、復原段活動、表掃描， 應用DBA可以對這四種因素進行分析，找出資料庫命中率低的癥結所在。

1)字典表活動  

當一個SQL語句第一次到達Oracle核心時資料庫對SQL語句進行分析，包含在查詢中的資料字典對象被分解，產生SQL執行路徑。如果SQL語句指向一個不在SGA中的對象??表或視圖，Oracle執行SQL語句到資料典中查詢有關對象的資訊。資料區塊從資料字典表被讀取到SGA的資料緩衝中。由於每個資料字典都很小，因此，我們可緩衝這些表以提高對這些表的命中率。但是由於資料字典表的資料區塊在SGA中佔據空間，當增加全部的命中率時，它們會降低表資料區塊的可用空間， 所以若查詢所需的時間字典資訊已經在SGA緩衝中，那麼就沒有必要遞迴調用。

2)臨時段的活動   

當使用者執行一個需要排序的查詢時，Oracle設法對記憶體中排序區內的所有行進行排序，排序區的大小由資料庫的init.ora檔案的數確定。如果排序地區不夠大，資料庫就會在排序操作期間開闢臨時段。臨時段會人為地降低OLTP(online transaction processing）應用命中率,也會降低查詢進行排序的效能。如果能在記憶體中完成全部排序操作，就可以消除向臨時段寫資料的開銷。所以應將SORT_AREA_SIZE設定得足夠大，以避免對臨時段的需要。這個參數的具體調整方法是:查詢相關資料，以確定這個參數的調整。   
select * from v$sysstat where name='sorts(disk)'or name='sorts(memory);

大部分排序是在記憶體中進行的，但還有小部分發生在臨時段， 需要調整 值，查看init.ora檔案的 SORT_AREA_SIZE值，參數為：SORT_AREA_SIZE＝65536;將其調整到SORT_AREA_SIZE＝131072、這個值調整後，重啟ORACLE資料庫即可生效。

3)復原段的活動   

復原段活動分為復原活動和復原段頭活動。對復原段頭塊的訪問會降低應用的命中率， 對OLTP系統命中率的影響最大。為確認是否因為復原段影響了命中率，可以查看監控輸出報表中的“資料區塊相容性讀一重寫記錄應用” 的統計值，這些統計值是用來確定使用者從復原段中訪問資料的發生次數。

4)表掃描   

通過大掃描讀得的塊在資料區塊緩衝中不會保持很長時間， 因此表掃描會降低命中率。為了避免不必要的全表掃描，首先是根據需要建立索引，合理的索引設計要建立人對各種查詢的分析和預測上，筆者會在SQL最佳化中詳細談及；其次是將經常用到的表放在記憶體中，以降低磁碟讀寫次數。例如 Alter table your_table_name cathe。

　　Oracle查詢最佳化第二方面：多表查詢的最佳化

　　在進行多表聯集查詢時，資料庫可能會採取MERGEJOINS、NESTED LOOP、HASH JOIN。其中，不論什麼時候雜湊連接要比另兩種連接開銷要小。

我們可以使用雜湊連接代替MERGEJOINS、NESTED LOOP連接、因此，在應用中，可添加一些設定使得資料庫在有多大聯集查詢發生時使用雜湊連接。其方法是:以 oracle使用者身份登入資料庫伺服器，在initosid.ora檔案中添加:
　　HASH_JOIN_ENABLED=TRUE
　　HASJ_AREA_SIZE=26000  
修改完後,重新啟動資料庫,使這些參數值生效。

Oracle查詢最佳化第三方面：大表查詢最佳化

資料庫中有些表是增長非常快的，記錄量很大，對這種表進行訪問時，索引的好處就微乎其微了，通常採用兩種辦法來進行大表訪問的最佳化。

1)大表建立在雜湊簇中

create cluster TRADE_CLUSTER(vuserid integer) 
　　storage(initial 50M next 50M)
　　hash is vuserid
　　size 60 hashkeys 10000000;/*hashkeys指定了在雜湊表裡的所期望的行數。*/ create table 
　　trade_detail_new as select * from trade_detail cluster 
　　TRADE_CLUSTER(userid);
　　drop table trade_detail;
　　rename trade_detail_new to trade_detail;

2)建分區表  

將一個大表分開放置在幾個邏輯分區中或者是將一個大表分成了幾張小表 ，即可以單獨對這些小表進行查詢，也可以union all一起查詢。   

例如：將 一個記錄交易詳情的表拆分：
　　create trade_detail_1 as select * from trade_detail
　　where trade_time between to_date('mm-dd','01-01')and to_date('mm-dd','03-31');
　　alter table trade_detail_1 add constraint check_trade_detail_1
　　check (trade_time between to_date('mm-dd','01-01')and to_date('mm-dd','03-31'));

同樣,建立起另幾張按交易發生的季度而劃分的表。然後建立執行四個表聯合的視圖；
　　create view trade_detail as select * from trade_detail_1
　　union all select * from trade_detail_2
　　union all select * from trade_detail_3
　　union all select * from trade_detail_4;

這樣在查詢某段時間內的資料時只訪問小表就可以了，需要時也可進行聯集查詢。

Oracle查詢最佳化第四方面：SQL最佳化

應用程式的執行最終將歸結為資料庫中的SQL語句執行，SQL語句消耗了70%到90%的資料庫資源。因此SQL語句的執行效率最終決定了ORACLE資料庫的效能。許多程式員認為查詢最佳化是DBMS（資料庫管理系統）的任務，與程式員所編寫的SQL語句關係不大，這是錯誤的。一個好的查詢計劃往往可以使程式效能提高數十倍。另外，SQL語句獨立於程式設計邏輯，相對於對程式原始碼的最佳化，對SQL語句的最佳化在時間成本和風險上的代價都很低。

SQL最佳化的主要途徑是：   

a.有效索引的建立。在經常進行串連，但是沒有指定為外鍵的列上建立索引；在頻繁進行排序或分組（即進行group by 或 order by 操作）的列上建立索引；在條件運算式中經常用到的不同值較多的列上建立檢索，在不同值少的列上不要建立索引；如果待排序的列有多個，可以在這些列上建立複合索引（compound index）。  

為了降低I/O竟爭, 索引要建在與使用者資料表空間不在同一磁碟上的索引空間裡。索引分為：分區索引、完全索引、唯一索引、位元影像索引等幾種類型，在建立索引前，應該測量這個索引的選擇性，索引的選擇性是指索引列裡不同值的數目與表中記錄數的比。    

b.在有大量重複值並且經常有範圍查詢（例如 between,>,<>=,<=）的列,或是用到order by、group by的列,可考慮建立群集索引 ；  

c.要經常同時存取多列,且每列都含有重複值可考慮建立複合式索引   

d.最佳化運算式，在能使用範圍查詢時儘可能使用範圍索引， 而少用“like”,因為“LIKE”關鍵字支援的萬用字元匹配特別耗費時間。  

f.使用Oracle語句最佳化器（oracle optimizer）和行鎖管理器（row-level manager）來調整最佳化SQL語句。

 

文章來自：http://database.51cto.com/art/201004/196516_1.htm