基于Linux平臺Oracle 10g數據庫性能調優

陳正舉

摘 要： 筆者單位對外業務網站為宣傳、承攬主營業務的重要窗口。隨著業務范圍的逐步擴大，網站數據庫的數據量迅速增長。系統性能設計不足的問題逐步暴漏出來，如頁面加載速度慢，執行數據庫操作等待時間過長等。針對這一問題，筆者提出了對系統所采用的數據庫的優化設計，最終實現了網頁數據加載速度加快及數據庫操作時間等待減少的調優目標。本文旨在結合此項實踐分享基于Linux操作系統平臺的Oracle數據庫服務器的調優實踐，進而歸納幾個調整和優化基于Linux操作系統平臺的Oracle數據庫服務器的方法。

關鍵詞：Linux Oracle 數據庫 性能調整優化

中圖分類號：TP392 文獻標識碼：A 文章編號：1003-9082（2016）06-0001-03

引言

筆者單位的對外業務網站為宣傳、承攬主營業務的重要窗口。隨著各項業務范圍的逐步擴大，網站數據庫的數據量迅速增長。系統性能設計不足的問題逐步暴露出來，出現了頁面加載速度慢，執行數據庫操作等待時間過長等問題。系統使用Oracle 10g數據庫，筆者根據多年數據庫管理和開發經驗，以滿足業務性能需求為出發點，對業務數據庫進行了調整、優化。取得了預期的成效。

數據庫性能優化技術主要包含以下幾個方面[1]：查詢優化；優化數據庫內存配置；優化操作系統及網絡配置。筆者將從各個不同角度考慮、分析數據庫問題及解決之道。

本文將結合此項工作實踐分享構建在Linux操作系統之上的Oracle 10g數據庫的調優心得，總結調整、優化基于Linux操作系統平臺的Oracle數據庫服務器的幾種方法。

一、Linux操作系統層面優化

1.Linux常用的調優工具

Linux系統性能監控命令可用于監控Oracle數據庫性能，進而根據這些監控數據決定數據庫的調優需求。這些命令可查看進程信息、服務器CPU使用信息以及操作系統內存交換情況。概述如下：

vmstat 查看Linux的進程、虛擬內存等情況。例如：vmstat 2 5可以查看系統每隔2秒鐘共5次的系統負載采樣情況；

iostat 顯示磁盤活動。在平衡I/O負載時很有用；

free 查看內存及交換空間使用情況。如：free -m；

top 反映CPU運行情況。用來分析進程占用CPU情況；

2.CPU對于系統的影響

系統負荷高峰期CPU的使用率接近80%是比較好的情況。使用上述top命令查看CPU使用率，找到耗用服務器CPU比較多的會話。通過查看Oracle的V$SYSSTAT視圖可以得到數據庫CPU時間（CPU used by this session），操作系統的用戶CPU時間（OS User level CPU time），以及Linux系統CPU時間（OS System call CPU time）。

通過如上視圖發現耗用CPU較大的Oracle進程（包括占用CPU用戶時間比例以及持續時長），這些進程很可能反映了數據庫存在性能問題。通過這些進程的id反查到數據庫中相應的服務器進程及會話。查看活動SQL情況，進而定位SQL問題（如：重解析、鎖沖突等）或其他可能的問題（如：數據庫內存不足、連續數據塊讀取等）。

同時還要考慮在多個系統共用一個實例的情況下不同應用交叉使用CPU帶來的影響。本例業務網站曾遇到信息發布慢的問題，檢查并且分析awr報告，發現數據庫DB Time略高，且有明顯row lock等待。

進而比對alert告警日志，發現警告日志里近期關于ORA-60錯誤，每個錯誤下面對應的trace文件。可以看到2個用戶進程發生了死鎖。在里面可以看到sql_id和sql腳本，發現3ptv952k9747，c5k5cpdnvjuy9這2條sql是數據庫產生死鎖的sql，對應的腳本可以通過下面的路徑進去看見“當前的sql”，

ORA-00060： Deadlock detected. More info in file /oracle/diag/rdbms/zsdb/zsdb/trace/zsdb_ora_19387.trc.

Wed Jul 22 17：06：51 2015

通過查看trace文件，確定是上面2條sql腳本里包含如下兩條類似的sql語句，且沒有及時commit，造成死鎖。通過ALTER SYSTEM KILL SESSION結束相應會話，在應用里添加commit語句解決。

UPDATE SP_EXTRACTION_RECORD

SET EXTRACTION_ID = ：1，

EXTRACTION_APPLICATION_CODE = ：2，

EXPERT_ID = ：3，

EXTRACTION_COUNT = ：4，

AFFIRM_FLAG = ：5，

ABANDON_REASON = ：6，

ABANDON_REASON_DETAIL = ：7，

GROUP_NUMBER = ：8，

CHOOSE_MODE = ：9，

OCCUPATION_BEGIN_DATE = ：10，

OCCUPATION_END_DATE = ：11，

EXTRACTION_SPECIALITY_ID = ：12，

BUSINESS_DOCUMENT_NO = ：13，

ABANDON_TYPE = ：14

WHERE ID = ：15

3.對于基于虛擬化建立的數據庫

筆者也從事虛擬化方面的工作，鑒于虛擬機在硬件管理、資源調配、遠程運維等方面相較傳統物理機的巨大便利性，筆者嘗試了將數據庫服務器虛擬化的相關工作。為了彌補虛擬化磁盤I/O性能的下降，虛擬化數據庫服務器應選擇使用SAS盤組成的磁盤陣列。同時，通過一些措施盡量減少磁盤I/O，如：靈活增加虛擬服務器內存大小；給虛擬服務器分配更多的預留內存空間等。將數據庫服務器虛擬化帶來的另一大優勢就是可用性的增加。傳統的ORACLE數據庫高可用通過物理服務器RAC集群來實現，以避免單節點硬件故障導致數據庫不可用。在虛擬化集群中，借助虛擬軟件的HA功能和虛機漂移技術，可實現對單節點數據庫虛擬服務器的硬件保護，從而規避ORACLE RAC管理的復雜性及其他技術風險。筆者單位的一些業務系統數據庫就從物理雙節點RAC轉變為了單節點虛擬服務器，業務性能未有明顯影響，管理復雜性大幅度減少，數據庫監控指標也大幅減少，提升了可運維程度及可控程度。

4.磁盤分區優化設計

4.1磁盤硬件考慮

隨著Oracle數據庫使用規模和數據量的不斷增大，如何實時高效地處理存儲實時數據，以及如何快速響應用戶應用請求，對數據庫系統的存儲和設計提出了更高的要求[5]。

硬件I/O瓶頸的性能問題是比較容易識別的。在沒有RAID設備的情況下，最好采用分條（strip）形式，容許數據文件分布在所有可用的磁盤，將磁盤I/O操作平均到不同的磁盤上去[3]，利用多個磁盤提供并發訪問能力。這樣可非常有效地減少磁盤I/O的等待時間。

如果具備RAID設備，應盡量考慮采用RAID0+1或RAID5的方式組織磁盤。優選RAID 0+1，且根據筆者的工作經驗，8塊SAS盤的RAID0+1是實現數據庫應用比較理想的配置。且在存儲設備支持的情況下，應對多組RAID0+1磁盤組的再進行條帶化。這樣既保證了數據的安全可用有最大程度的提升了磁盤存儲的I/O能力。這種磁盤組的再條帶化功能已為大多數主流存儲產品所支持。實際就是LUN（邏輯存儲單元）的捆綁擴容功能，只不過要選擇以條帶化方式進行擴容。RAID 5對于有大量讀操作的應用程序可以獲得最大的性能，這是一種低成本的解決方案[4]，適用于性價比要求較高的中小型應用。

4.2使用IOSTAT查找大的分區請求事務

ostat -t命令可以查看磁盤、分區的I/O數，TPS指標說明了單位時間內特定磁盤或分區設備接收請求的事務量。正常情況下，磁盤正常的TPS應不大于200[2]，如果這個值高于推薦值，說明磁盤的讀寫太頻繁，應及時檢查相應設備上Oracle數據庫文件是否存在熱點。進而對使用相關數據庫文件的SQL查詢進行比較分析，找到設備被頻繁讀寫的真正原因。對SQL語句進行優化，減少數據庫I/O等待時間。

筆者在對文中業務網站系統數據庫優化過程中就曾借助iostat方法快速對問題進行了定位及解決。通過對巡檢過程中TPS超過200的分區進行檢索，發現某業務表空間的全部數據文件都位于一個分區內，且這一表空間內的大表較多。聯想到業務響應較之前變慢，初步定位問題。通過調整表空間內數據文件的分布，優化大表檢索SQL等方法成功降低了相應設備的TPS，其值從之前的251下降到57，實際上也大大加快了業務響應時間。

二、數據庫性能制約因素及調整

1.調整數據庫緩沖區高速緩存大小

Oracle數據塊在被從內存寫到磁盤前，都是保存在Oracle緩沖區高速緩存中的。Oracle 10g后，Oracle支持自動共享內存管理（ASMM），允許Oracle根據數據庫實際運行情況自動調整SGA中的各個組件大小。要啟用ASMM，必須設定sga_target參數，該參數指定了所有共享內存預期的總大小。足夠的內存分配給Oracle系統緩沖區就顯得很重要，sga_target參數可以來規劃內存給Oracle的分配[5]。通常情況下，這個值應不高于服務器物理內存的70%。Oracle將根據高速緩沖區緩存顧問、共享池和其他類似的顧問的輸出動態地調整各種SGA組件的大小。

ASMM在波動不大的工作負載中運行是比較好的，不適合在變動頻繁的工作負載中使用，輸出可能不準確。因為，ASMM基本上在持續監控各項顧問數據，并且有規律地根據顧問的建議調整內存。但是，如果工作負載變動頻繁，ASMM可能會用剛調整的內存來適應剛出現的情況，而不是為當前總體的工作負載服務，這樣就容易產生內存拉動。如果出現內存拉動，則需要禁用ASMM，因為這樣可以然系統處于更好的工作狀態。這時，一些特定參數的配置是必要的，如db_cache_size，shared_pool_size，db_keep_pool_size等。系統將會根據這些資源池配置大小進行工作。

2.充分利用Oracle自動調優工具

Oracle提供了豐富的數據庫監控視圖及手段。如，V$SESSION和V$SQLAREA用來找出正在發生的性能最差的SQL語句；ASH報告用來查看一個小時以內發生的事件；AWR報告用來查看1小時以上或幾天前的事件。如果遇到數據庫性能問題，建議采用Oracle數據庫自帶的工具ADDM來對數據庫的運行狀況進行分析[8]。ADDM的報告就是基于AWR庫生成的，默認可以保存30天的ADDM報告。當然，數據庫管理員也可根據實際需求調整這個保存期限。

本文中業務網站的數據庫的性能統計分析信息是2014年2月收集的，統計分析析過舊，導致數據庫優化器無法選擇正確的執行計劃，致使原來使用索引掃描的SQL語句使用全表掃描。

optimizer_danymic_sampling參數定義了數據庫動態自動采集統計信息的間隔。及時采集數據庫統計信息，有利于提高數據庫自動調優的效率及準確程度[5]，可使數據庫提供更準確的低成本執行計劃給用戶，提高數據庫執行效率。因此，建議開啟Oracle自動收集數據庫統計分析信息功能。也可執行如下命令手動收集一次數據庫統計信息：exec

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT

3.SQL優化

眾所周知，應用系統對數據庫的訪問無非就是借助SQL語句來實現。所以數據庫調優工作的出發點和落腳點也都將落在SQL的實際執行效果上。因此，SQL語句的性能優劣就直接影響著整個信息系統的可用性。而SQL語句的優化其實是進行查詢等價轉換，主要是從查詢語義的角度將用戶的SQL查詢語句轉換為效率更高的形式SQL語句[6]。

Oracle提供了基于成本（CBO）和基于規則（RBO）兩種優化器，用于確定查詢操作的執行計劃。實際工作中主要精力應集中在優化SQL解析和優化CBO上[4]。Oracle10g采用基于代價的優化器CBO，其作用是查看語句的代價，主要指CPU和內存消耗[8]。本文也將采用基于CBO的SQL優化。

在上述優化基礎上，筆者連續幾天對業務系統網站數據庫TOP5的SQL活動進行了觀察。對頭條的SQL語句進行分析、優化。發現此語句執行效率低下，以下是該SQL語句的優化過程。語句內容如下：

select task.guid， task.moduleid， task.jobGUID， task.runlevel

from bi_schd_host host，

bi_schd_module m，

bi_schd_jobqueue job，

bi_schd_taskqueue task

where host.openstatus = 1

and host.hostid = ：1

and （（m.hosts is null） or m.hosts = '' or （m.hosts like ：2））

and （m.loadWeight <= host.loadweight - nvl（host.currloadweight， 0））

and （job.runlevel is null or task.runlevel is null or

job.runlevel = task.runlevel）

and task.runstate = 0

and task.moduleid = m.moduleClzName

and task.jobguid = job.guid

and （task.servername is null or task.servername = '' or

task.servername = ：3）

order by task.servername desc， m.priorty desc， task.runlevel

優化前的執行計劃如圖1所示，發現全表掃描現象。SQL語句性能不佳大致有兩個原因：一是開發人員只關注查詢結果的正確性，忽視查詢語句的效率；二是開發人員只關注SQL語句本身的效率，對SQL語句的執行原理、影響SQL執行效率的主要因素不清楚[7]。

定位全表掃描對象，對其進行優化。在選擇性高的列上創建索引及進行行的重新排序。優化后的執行計劃：

優化之前，數據庫執行全表掃描，整體cost 6454。優化后，數據庫使用索引范圍掃描，整體cost 12。數據庫執行效率有明顯提升，事實上數據庫反應速度也明顯加快。網站中涉及此表的應用模塊的運行速度顯著提升，且這種改善能夠傳遞給終端用戶，達到了優化的目的。

三、結論

通過此次優化工作，業務網站的訪問速度得到了顯著提高，相關數據庫操作等待時間明顯減少。達到了預期的優化效果。可見Oracle數據庫的性能可伸縮性比較強，需要規律性地維護，以便充分發揮其性能優勢。充分利用以上命令和方法，就可以較好地調整與優化Oracle 10g數據庫，足以應對大多數業務需求。

同時，規律性地對數據庫服務器進行系統性能調整優化還可極大提高數據庫運行穩定性及效率，提前預防數據庫性能問題，降低數據庫瓶頸出現的概率，以滿足用戶對系統更高的性能需求。

參考文獻

[1]Richard，J.Niemiec.Oacle9i性能調整[M].清華大學出版社.2004：46-67.

[2]Edward Whalen，Mitchell Schroeter.Oracle性能調整與優化[M].高艷春，周兆確，唐艷軍譯.北京：北京人民郵電出版社.2002，12.

[3]劉春林.Oracle數據庫技術使用教程[M].北京：北京鑫智鴻鵬科技發展有限公司組編，2010-04：166.

[4]Oracle數據庫性能優化分析與配置[J].電腦知識與技術.2013，11，18（9）：4146.

[5]袁佰順，朱擁軍，李曉鶴，汪鴻濱，陳 薇.基于ORACLE的隴東南區域自動氣象站數據庫設計及應用[J].干旱氣象，2014，32 （ 3 ）：475-480.

[6]張輝，趙郁亮，徐江，孫偉華.基于Oracle數據庫海量數據的查詢優化研究[J].計算機技術與發展，2012，2（ 22 ）：165.

[7]Christopher Allen.Oracle PL/SQL程序設計基礎教程[M].鐘鳴，文衛東等譯.北京機械工業出版社.2001，5：131.

[8]楊文清，吳昊，胡榮，謝浩安，孫昌愛.一個工業產品發布系統查詢優化技術研究[J].中國科技論文在線，2014，11：15.http：//www.paper.edu.cn.