【性能優化】ORACLE資料庫性能優化概述

   為了保證ORACLE資料庫運作在最佳的性能狀态下，在資訊系統開發之前就應該考慮資料庫的優化政策。優化政策一般包括伺服器作業系統參數調整、ORACLE資料庫參數調整、網絡性能調整、應用程式SQL語句分析及設計等幾個方面，其中應用程式的分析與設計是在信

　　分析評價ORACLE資料庫性能主要有資料庫吞吐量、資料庫使用者響應時間兩項名額。資料庫吞吐量是指機關時間内資料庫完成的SQL語句數目；資料庫使用者響應時間是指使用者從送出SQL語句開始到獲得結果的那一段時間。資料庫使用者響應時間又可以分為系統服務時間和使用者等待

　　資料庫使用者響應時間＝系統服務時間 ＋ 使用者等待時間

　　上述公式告訴我們，獲得滿意的使用者響應時間有兩個途徑：一是減少系統服務時間，即提高資料庫的吞吐量；二是減少使用者等待時間，即減少使用者通路同一資料庫資源的沖突率。

　　資料庫性能優化包括如下幾個部分：

　　1、調整資料結構的設計。這一部分在開發資訊系統之前完成，程式員需要考慮是否使用ORACLE資料庫的分區功能，對于經常通路的資料庫表是否需要建立索引等。

　　2、調整應用程式結構設計。這一部分也是在開發資訊系統之前完成，程式員在這一步需要考慮應用程式使用什麼樣的體系結構，是使用傳統的Client/Server兩層體系結構，還是使用Browser/Web/Database的三層體系結構。不同的應用程式體系結構要求的資料庫資源是不同

　　3、調整資料庫SQL語句。應用程式的執行最終将歸結為資料庫中的SQL語句執行，是以SQL語句的執行效率最終決定了ORACLE資料庫的性能。ORACLE公司推薦使用ORACLE語句優化器（Oracle Optimizer）和行鎖管理器（row-level manager）來調整優化SQL語句。

　　4、調整伺服器記憶體配置設定。記憶體配置設定是在資訊系統運作過程中優化配置的，資料庫管理者可以根據資料庫運作狀況調整資料庫系統全局區（SGA區）的資料緩沖區、日志緩沖區和共享

池的大小；還可以調整程式全局區（PGA區）的大小。需要注意的是，SGA區不是越大越好，SGA區過大會占用作業系統使用的記憶體而引起虛拟記憶體的頁面交換，這樣反而會降低系統。

　　5、調整硬碟I/O，這一步是在資訊系統開發之前完成的。資料庫管理者可以将組成同一個表空間的資料檔案放在不同的硬碟上，做到硬碟之間I/O負載均衡。

　　6、調整作業系統參數，例如：運作在UNIX作業系統上的ORACLE資料庫，可以調整UNIX資料緩沖池的大小，每個程序所能使用的記憶體大小等參數。

　　實際上，上述資料庫優化措施之間是互相聯系的。ORACLE資料庫性能惡化表現基本上都是使用者響應時間比較長，需要使用者長時間的等待。但性能惡化的原因卻是多種多樣的，有時是多個因素共同造成了性能惡化的結果，這就需要資料庫管理者有比較全面的計算機知識，能夠敏感地察覺到影響資料庫性能的主要原因所在。另外，良好的資料庫管理工具對于優化資料庫性

　　ORACLE資料庫性能優化工具

　　常用的資料庫性能優化工具有：

　　1、ORACLE資料庫線上資料字典，ORACLE線上資料字典能夠反映出ORACLE動态運作情況，

　　2、作業系統工具，例如UNIX作業系統的vmstat，iostat等指令可以檢視到系統系統級記憶體和硬碟I/O的使用情況，這些工具對于管理者弄清出系統瓶頸出現在什麼地方有時候很有用

　　3、SQL語言跟蹤工具（SQL TRACE FACILITY），SQL語言跟蹤工具可以記錄SQL語句的執行情況，管理者可以使用虛拟表來調整執行個體，使用SQL語句跟蹤檔案調整應用程式性能。SQL語言跟蹤工具将結果輸出成一個作業系統的檔案，管理者可以使用TKPROF工具檢視這些檔案。

　　4、ORACLE Enterprise Manager（OEM），這是一個圖形的使用者管理界面，使用者可以使用

它友善地進行資料庫管理而不必記住複雜的ORACLE資料庫管理的指令。

　　5、EXPLAIN PLAN――SQL語言優化指令，使用這個指令可以幫助程式員寫出高效的SQL語

　　ORACLE資料庫的系統性能評估

　　資訊系統的類型不同，需要關注的資料庫參數也是不同的。資料庫管理者需要根據自己的

資訊系統的類型着重考慮不同的資料庫參數。

　　1、線上事務處理資訊系統（OLTP），這種類型的資訊系統一般需要有大量的Insert、Update操作，典型的系統包括民航機票發售系統、銀行儲蓄系統等。OLTP系統需要保證資料庫的并發性、可靠性和最終使用者的速度，這類系統使用的ORACLE資料庫需要主要考慮下述參數：

　　l 資料庫復原段是否足夠？

　　l 是否需要建立ORACLE資料庫索引、聚集、散列？

　　l 系統全局區（SGA）大小是否足夠？

　　l SQL語句是否高效？

　　2、資料倉庫系統（Data Warehousing），這種資訊系統的主要任務是從ORACLE的海量數

據中進行查詢，得到資料之間的某些規律。資料庫管理者需要為這種類型的ORACLE資料庫着重

　　l 是否采用B*-索引或者bitmap索引？

　　l 是否采用并行SQL查詢以提高查詢效率？

　　l 是否采用PL/SQL函數編寫存儲過程？

　　l 有必要的話，需要建立并行資料庫提高資料庫的查詢效率

　　SQL語句的調整原則

　　SQL語言是一種靈活的語言，相同的功能可以使用不同的語句來實作，但是語句的執行效

率是很不相同的。程式員可以使用EXPLAIN PLAN語句來比較各種實作方案，并選出最優的實作

方案。總得來講，程式員寫SQL語句需要滿足考慮如下規則：

　　1、盡量使用索引。試比較下面兩條SQL語句：

　　語句A：SELECT dname, deptno FROM dept WHERE deptno NOT IN

　　(SELECT deptno FROM emp);

　　語句B：SELECT dname, deptno FROM dept WHERE NOT EXISTS

　　(SELECT deptno FROM emp WHERE dept.deptno = emp.deptno);

　　這兩條查詢語句實作的結果是相同的，但是執行語句A的時候，ORACLE會對整個emp表進行掃描，沒有使用建立在emp表上的deptno索引，執行語句B的時候，由于在子查詢中使用了聯合查詢，ORACLE隻是對emp表進行的部分資料掃描，并利用了deptno列的索引，是以語句B的

　　2、選擇聯合查詢的聯合次序。考慮下面的例子：

　　SELECT stuff FROM taba a, tabb b, tabc c

　　WHERE a.acol between :alow and :ahigh

　　AND b.bcol between :blow and :bhigh

　　AND c.ccol between :clow and :chigh

　　AND a.key1 = b.key1

　　AMD a.key2 = c.key2;

　　這個SQL例子中，程式員首先需要選擇要查詢的主表，因為主表要進行整個表資料的掃描，是以主表應該資料量最小，是以例子中表A的acol列的範圍應該比表B和表C相應列的範圍小

　　3、在子查詢中慎重使用IN或者NOT IN語句，使用where (NOT) exists的效果要好的多。

　　4、慎重使用視圖的聯合查詢，尤其是比較複雜的視圖之間的聯合查詢。一般對視圖的查詢最好都分解為對資料表的直接查詢效果要好一些。

　　5、可以在參數檔案中設定SHARED_POOL_RESERVED_SIZE參數，這個參數在SGA共享池中保留一個連續的記憶體空間，連續的記憶體空間有益于存放大的SQL程式包。

　　6、ORACLE公司提供的DBMS_SHARED_POOL程式可以幫助程式員将某些經常使用的存儲過程“釘”在SQL區中而不被換出記憶體，程式員對于經常使用并且占用記憶體很多的存儲過程“釘”

　　CPU參數的調整

　　CPU是伺服器的一項重要資源，伺服器良好的工作狀态是在工作高峰時CPU的使用率在90％以上。如果空閑時間CPU使用率就在90％以上，說明伺服器缺乏CPU資源，如果工作高峰時

　　使用操作相同指令可以看到CPU的使用情況，一般UNIX作業系統的伺服器，可以使用sar u指令檢視CPU的使用率，NT作業系統的伺服器，可以使用NT的性能管理器來檢視CPU的使用

　　資料庫管理者可以通過檢視v$sysstat資料字典中“CPU used by this session”統計項

得知ORACLE資料庫使用的CPU時間，檢視“OS User level CPU time”統計項得知作業系統使用者态下的CPU時間，檢視“OS System call CPU time”統計項得知作業系統系統态下的CPU時間，作業系統總的CPU時間就是使用者态和系統态時間之和，如果ORACLE資料庫使用的CPU時間占作業系統總的CPU時間90％以上，說明伺服器CPU基本上被ORACLE資料庫使用着，這是合理，反之，說明伺服器CPU被其它程式占用過多，ORACLE資料庫無法得到更多的CPU時間。

　　資料庫管理者還可以通過檢視v$sesstat資料字典來獲得目前連接配接ORACLE資料庫各個會話占用的CPU時間，進而得知什麼會話耗用伺服器CPU比較多。

　　出現CPU資源不足的情況是很多的：SQL語句的重解析、低效率的SQL語句、鎖沖突都會引

　　1、資料庫管理者可以執行下述語句來檢視SQL語句的解析情況：

　　SELECT * FROM V$SYSSTAT

　　WHERE NAME IN

　　('parse time cpu', 'parse time elapsed', 'parse count (hard)');

　　這裡parse time cpu是系統服務時間，parse time elapsed是響應時間，使用者等待時間

　　waite time = parse time elapsed parse time cpu

　　由此可以得到使用者SQL語句平均解析等待時間＝waite time / parse count。這個平均等

待時間應該接近于0，如果平均解析等待時間過長，資料庫管理者可以通過下述語句

　　SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA

　　ORDER BY PARSE_CALLS;

　　來發現是什麼SQL語句解析效率比較低。程式員可以優化這些語句，或者增加ORACLE參數

　　2、資料庫管理者還可以通過下述語句：

　　SELECT BUFFER_GETS, EXECUTIONS, SQL_TEXT FROM V$SQLAREA;

　　檢視低效率的SQL語句，優化這些語句也有助于提高CPU的使用率。

　　3、資料庫管理者可以通過v$system_event資料字典中的“latch free”統計項檢視

ORACLE資料庫的沖突情況，如果沒有沖突的話，latch free查詢出來沒有結果。如果沖突太大

的話，資料庫管理者可以降低spin_count參數值，來消除高的CPU使用率。

　　記憶體參數的調整

　　記憶體參數的調整主要是指ORACLE資料庫的系統全局區（SGA）的調整。SGA主要由三部分構

　　1、 共享池由兩部分構成：共享SQL區和資料字典緩沖區，共享SQL區是存放使用者SQL指令

的區域，資料字典緩沖區存放資料庫運作的動态資訊。資料庫管理者通過執行下述語句：

　　select (sum(pins - reloads)) / sum(pins) "Lib Cache" from v$librarycache;

　　來檢視共享SQL區的使用率。這個使用率應該在90％以上，否則需要增加共享池的大小。

　　select (sum(gets - getmisses - usage - fixed)) / sum(gets) "Row Cache" from

　　檢視資料字典緩沖區的使用率，這個使用率也應該在90％以上，否則需要增加共享池的大

　　2、 資料緩沖區。資料庫管理者可以通過下述語句：

　　SELECT name, value FROM v$sysstat WHERE name IN ('db block gets', 'consistent

　　來檢視資料庫資料緩沖區的使用情況。查詢出來的結果可以計算出來資料緩沖區的使用命

中率＝1 - ( physical reads / (db block gets + consistent gets) )。

　　這個命中率應該在90％以上，否則需要增加資料緩沖區的大小。

　　3、 日志緩沖區。資料庫管理者可以通過執行下述語句：

　　select name,value from v$sysstat where name in ('redo entries','redo log

space requests');檢視日志緩沖區的使用情況。查詢出的結果可以計算出日志緩沖區的申請

申請失敗率＝requests/entries，申請失敗率應該接近于0，否則說明日志緩沖區開設太