【效能最佳化】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，否則說明日誌緩衝區開設太