Oracle資料庫效能最佳化技術(轉載)

oracle資料庫是當前應用最廣泛的大型資料庫之一，而其性最佳化直接關係到系統的執行效率。本文以資料庫效能最佳化的基本原則為出發點，闡述了在資料庫設計階段如何避免競爭和如何最佳化資料訪問，在資料庫執行階段如何從作業系統和資料庫例項級別上調整記憶體和i/o來達到資料庫效能最佳化的各種技術。

1、引言

隨著網路應用和電子商務的不斷髮展，各個站點的訪問量越來越大，資料庫規模也隨之不斷的擴大，資料庫系統的效能問題就越來越突出，因此，如何對資料庫進行調優至關重要：如何使用有限的計算機系統資源為更多的使用者服務？如何保證使用者的響應速度和服務質量？這些問題都屬於伺服器效能最佳化的範疇。

作為全球第一大資料庫廠商，oracle資料庫在國內外獲得了諸多成功應用，據統計，全球93%的上市.com公司、65家“財富全球100強”企業不約而同地採用oracle資料庫來開展電子商務。我國很多企業、政府單位及電子商務網站也採用了oracle作為資料庫伺服器。oracle資料庫伺服器是高度可最佳化的軟體產品，經常性的調整可以最佳化應用系統的效能，防止出現系統瓶頸。

資料庫效能最佳化的基本原則就是：透過儘可能少的磁碟訪問獲得所需要的資料。要評價資料庫的效能，需要在資料庫調節前後比較其評價指標：響應時間和吞吐量之間的權衡、資料庫的可用性、資料庫的命中率以及記憶體的使用效率，以此來衡量調節措施的效果和指導調整的方向。

對oracle資料庫進行效能調整時，應當按照一定的順序進行，因為系統在前面步驟中進行的調整可以避免後面的一些不必要調整或者代價很大的調整。一般來說可以從兩個階段入手：
設計階段：對其邏輯結構和物理結構進行最佳化設計，使之在滿足需求條件的情況下,系統效能達到最佳，系統開銷達到最小；
資料庫執行階段：採取作業系統級、資料庫級的一些最佳化措施來使系統效能最佳；

[@more@]2、在系統設計開發階段調整資料庫

為了充分利用oracle資料庫的功能特性，在設計資訊系統時，資料庫設計人員需要根據業務情況（如訪問量或客戶端數量）和現有資源狀況（如資料庫伺服器的配置）考慮系統結構和資料庫的邏輯結構的設計：

2.1調整應用程式結構設計。
即應用程式採用的是傳統的c/s兩層體系結構，還是b/w/d三層體系結構。不同的應用程式體系結構要求的資料庫資源是不同的。

2.2恰當使用分割槽、索引及存檔功能。
如果某種業務的資料量增長非常快，可以考慮存放該業務的資料庫表是否使用oracle資料庫的分割槽功能；對於經常訪問的資料庫表是否需要建立索引；對於經常訪問但是當業務流程完成後不再變動的資料可採用放入歷史檔案的方法來實現應用系統中訪問儘可能少的資料量。

2.3恰當編寫訪問資料的sql語句。
良好的sql語句可以被資料庫重複使用而減少分析時間；恰當的使用索引可使訪問的資料塊大大減少從而減少響應時間。應用程式的執行最終將歸結為資料庫中的sql語句執行，因此sql語句的執行效率決定了oracle資料庫的效能。oracle公司推薦使用oracle語句最佳化器（oracle optimizer）和行鎖管理器（row-level manager）來調整最佳化sql語句。

2.4調整硬碟i/o
這一步是在資訊系統開發之前完成的。資料庫管理員可以將組成同一個表空間的資料檔案放在不同的硬碟上，做到硬碟之間i/o負載均衡。在磁碟比較富裕的情況下還應該遵循以下原則：
將表和索引分開；
創造使用者表空間，與系統表空間（system）分開磁碟；
建立表和索引時指定不同的表空間；
建立回滾段專用的表空間，防止空間競爭影響事務的完成；
建立臨時表空間用於排序操作，儘可能的防止資料庫碎片存在於多個表空間中。

2.5確定資料塊大小和儲存引數。
由於資料庫的塊大小在資料庫建立以後就不能在修改（除非重建資料庫），因此為了減少資料連結和行遷移，又提高磁碟空間的利用率，在設計資料庫時要確定合適的資料塊大小和儲存引數。通常我們是根據樣例資料確定資料塊大小，而根據業務現狀和未來發展趨勢確定儲存引數。

3、在資料庫執行階段調整資料庫

資料庫執行階段調整資料庫包括兩個方面：作業系統級的調整；資料庫級的調整。

3.1 作業系統級的調整
實施作業系統級調整的主要目的是減少記憶體交換，減少分頁，使sga(system globle area)可留駐記憶體。

3.1.1減少記憶體交換
記憶體交換（swapping）可能會造成很大的記憶體開銷，應將它最小化。執行在solaris unix作業系統上的oracle資料庫，可利用vmstat或sar命令來檢查交換，檢視到系統級記憶體和硬碟i/o的使用情況，調整unix資料緩衝池的大小、每個程式所能使用的記憶體大小等引數。
vmstat命令
它報告solaris上的程式、虛擬記憶體、磁碟、分頁和cpu的活動情況。下面命令將顯示系統每5秒鐘做的事情的概要：
% vmstat 5

sar命令
監控交換、分頁、磁碟和cpu活動。下面命令用於每10秒顯示10次分頁活動的概要：
% sar -p 10 10

若系統記憶體交換較多，且需要節省記憶體，可採用以下措施：
1） 避免執行不必要的系統daemon程式或應用程式程式；
2） 在不明顯影響資料塊命中率的前提下減少資料庫緩衝區的數量，以釋放一些記憶體；
3） 減少unix檔案緩衝區的數量（特別是在使用裸裝置時）。

3.1.2控制分頁
少量的記憶體分頁不會太顯著地影響系統的效能，因為應用程式不必全部放在記憶體中。但是分頁過多將會造成系統效能下降。為了檢測過多的分頁，可在快速響應或空閒期間執行測量，並與響應遲緩時的測量進行比較。可透過以下辦法來解決：
使用vmstat或sar -p監控分頁；
安裝更多的記憶體；
將一些工作移到另一系統中；
配置系統核心使用更少的記憶體；
保持sga在單個共享記憶體段中。

3.1.3使sga(system globle area)留駐記憶體
sga是對資料庫資料進行快速訪問的一個系統全域性區，若sga本身需要頻繁地進行釋放、分配，則不可能達到快速訪問資料的目的，因此，要求sga駐留記憶體。這時，我們可以重新配置unix核心，調整一些作業系統引數以達到增加共享記憶體的目的。

3.2 資料庫級的調整
每一個oracle例項都是由一組oracle後臺程式和sga的一個記憶體區組成的。這組後臺程式會自動的讀寫資料庫的資料檔案，因此，資料庫效能可以被這些因素所影響：sga各部分的分配是否合理，使用效率是否正常；i/o和鎖競爭是否較多。

3.2.1 sga的分配及使用效率
分配給每個例項的記憶體，即sga的使用效率如何，會大大影響資料庫系統的效能。sga由下列部分組成：共享池、資料塊緩衝區、重做日誌緩衝區、大池組成。
共享池（shared pool）
共享池存放庫快取（儲存共享sql和pl/sql區）和資料字典快取（資料庫物件資訊）以及會話期間資訊（對於mts）。由於這些資訊是應用程式需要經常訪問的，因此這些資訊需要保持高的命中率。可以透過以下語句來確認共享池資料的命中率：
庫快取：
select gethitratio from v$librarycache 應大於90%
select sum(reloads)/sum(pins) from v$librarycache 應小於1%
資料字典快取：
select sum(getmisses)/sum(gets) from v$rowcache 應小於15%
由於程式設計人員的水平參差不齊，可能存在大的匿名塊，這會導致sql不能重用，因此需要找出大的匿名塊以轉換為儲存過程達到重用：
select * from v$sqlarea where command_type=47 and length(sql_text)>500
而對於一些應用系統非常頻繁使用的sql物件如儲存過程、函式、包等，可以透過釘在記憶體中的方式來防止由於共享池太小被移出：
exec dbms_shared_pool.keep(物件名)
資料塊緩衝區（db block buffer）：
資料塊緩衝區存放使用者所經常訪問的資料檔案的資料塊內容以及使用者修改的資料內容。資料庫把資料檔案裡的內容讀到記憶體中，下次需要時直接從記憶體中讀取，從而減少了磁碟的i/o和響應時間。當然，一般只在比較小的資料表（如常用程式碼表）才快取到記憶體中。
由於資料快緩衝區中不可能存放所有的資料，因此可使用lru演算法來確定移出哪些資料塊，但又儘量保證有較高的資料命中率。
檢視資料塊命中率的sql語句為：
select 1-(phy.value/(cur.value+con.value)) from v$sysstat cur,v$sysstat con,v$sysstat phy
where cur.name="db block gets" and con.name="consistent gets"
and phy.name="physical gets"
如果這個命中率小於0.85，就要考慮為資料塊緩衝區分配更多的記憶體了。
重做日誌緩衝區（log buffer）：
重做日誌緩衝區存放從使用者記憶體區複製來的每個dml或ddl語句的重做條目。如果這個緩衝區分配太小會導致沒有足夠的空間來放重做條目而等待。

3.2.2 i/o和資源競爭
由於有眾多的程式要寫資料檔案，因此需要透過i/o調整來解決i/o瓶頸問題。如果在設計階段有效地考慮了表空間的合理分配，就能有效地在一定程度上減少i/o競爭。在資料庫執行時，由於資料的動態增長，原來分配給表或索引的空間已經用完，oracle會自動分配空間給這些資料庫物件。而這個動態分配會對系統效能有所影響，所以要求：
避免動態空間管理
表空間的本地化管理，以減少與資料字典表空間的磁碟競爭。
在系統設計和試執行階段資料量相對較小，效率低下的sql可能並不會影響系統響應時間，但當系統資料量增長到一定程度時，需要在系統執行時監控並找出是哪些sql不能有效使用索引或缺少索引，並進行相應調整：建立索引；修改sql寫法。

另外，在oracle中，需要採用一些機制來保證資料庫物件在使用期間的穩定性和資料的一致性，如使用鎖存器(latch)、鎖(lock)等。因此爭用和這些機制相關的資源會影響資料庫的效能。為了減少這種資源競爭，可以透過調整資料庫的相關初始化引數（如db_block_lru_latches、dml_locks）來減少資源的爭用，最佳化資料庫效能。

4、一些常用的效能最佳化手段和工具
oracle資料庫系統提供了一些工具和指令碼來獲取資料庫的效能指標和最佳化的方法。如使用utlbstat.sql和utlestat.sql指令碼獲取一段時間內資料庫的記憶體、磁碟i/o等的情況；使用動態效能檢視和資料字典檢視來獲取命中率和系統等待事件等資訊。當然，也可以使用oracle enterprise manager圖形化工具來監控。

5、結束語
oracle資料庫的效能調整相當重要，但難度也較大。資料庫管理員需要綜合運用上面介紹的規律，在資料庫建立時，就能根據應用的需要合理設計分配表空間以及儲存引數、記憶體使用初始化引數，對以後的資料庫效能有很大的益處。只有認真分析oracle執行過程當中出現的各種效能問題，才能保證oracle資料庫高效可靠地執行。還需要指出的是：資料庫的效能調整是一個系統工程，涉及的方面很多，不能僅僅根據一個時間點的情況就斷定資料庫執行效能的好與壞。如何有效地進行調整，資料庫管理員需要經過反反覆覆的過程。這些都需要在大量的實踐工作中不斷地積累經驗，從而更好地進行資料庫的調優。