Oracle資料庫性能優化

一.資料庫性能優化

Oracle 性能管理既是一種藝術，也是一種科學，從實用角度講，它可以分 為兩種型別，主動式和被動式性能管理，主動式性能管理涉及到特定系統實施初 期的設計和開發，包括硬體選擇、性能及容量規劃，海量存盤系統的選擇， I-O 子系統配置及優化，以及如何對不同組件進行定制，以滿足 Oracle 資料庫和應 用系統的復雜要求，
被動式性能管理涉及到現有環境中不同組件的性能評估、故障排除和 Oracle 環境的優化，本文旨在探討如何進行被動式性能調優，以便為 Oracle 性能調優 提供必要的指導，從而避免僅僅通過反復嘗試的方式進行性能調優，提高 Oracle 性能管理的效率，
所以 ORACLE 資料庫性能惡化表現基本上都是用戶回應時間比較長，須要用 戶長時間的等待，獲得滿意的用戶回應時間有兩個途徑：
一是減少系統服務時間，即提高資料庫的吞吐量； 二是減少用戶等待時間，即減少用戶訪問同一資料庫資源的沖突率， 對于以上的兩個問題，通常我們采用以下幾個方面來進行改善：
? 調整服務器記憶體分配，例如，可以根據資料庫運行狀況調整資料庫系統 全域區（SGA 區）的資料緩沖區、日志緩沖區和共享池的大小；還可以 調整程式全域區（PGA 區）的大小，
? 調整硬碟 I/O 問題，達到 I/O 負載均衡，
? 調整運用程式結構設計
? 優化調整作業系統引數和使用資源管理器
?SQL 優化、診斷 latch 競爭、Rollback(undo) Segment 優化、提升 block
的效率等等

二.檢查Oracle資料庫性能

檢查 Oracle 資料庫性能情況，包含：檢查資料庫的等待事件，檢查死鎖及 處理，檢查 cpu、I/O、記憶體性能，查看是否有僵死行程，檢查行鏈接/遷移，定 期做統計分析，檢查緩沖區命中率，檢查共享池命中率，檢查排序區，檢查日志緩沖區，總共十個部分，

2.1 檢查資料庫的等待事件

select sid,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%' and event not like  'rdbms%';

如果資料庫長時間持續出現大量像 latch free，enqueue，buffer busy waits， db file sequential read，db file scattered read 等等待事件時，需要對其 進行分析，可能存在問題的陳述句，

2.2 Disk Read最高的SQL陳述句的獲取

SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS) WHERE ROWNUM<=5 desc;

2.3 查找前十條性能差的sql

SELECT * FROM (SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS, SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC) WHERE ROWNUM<10 ;

2.4 等待時間最多的 5 個系統等待事件的獲取

SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5;

2.5 檢查運行很久的SQL

COLUMN USERNAME FORMAT A12 COLUMN OPNAME FORMAT A16 COLUMN PROGRESS FORMAT A8
SELECT	USERNAME,SID,OPNAME,ROUND(SOFAR*100	/	TOTALWORK,0)		||	'%'	AS PROGRESS,TIME_REMAINING,SQL_TEXT	FROM	V$SESSION_LONGOPS	,	V$SQL		WHERE
TIME_REMAINING <> 0 AND SQL_ADDRESS=ADDRESS AND SQL_HASH_VALUE = HASH_VALUE;

2.6 檢查消耗CPU最高的行程

SET LINE 240 SET VERIFY OFF
COLUMN SID FORMAT 999 COLUMN PID FORMAT 999 COLUMN S_# FORMAT 999
COLUMN USERNAME FORMAT A9 HEADING "ORA USER"
COLUMN PROGRAM FORMAT A29 COLUMN SQL	FORMAT A60
COLUMN OSNAME FORMAT A9 HEADING "OS USER"
SELECT P.PID PID,S.SID SID,P.SPID SPID,S.USERNAME USERNAME,S.OSUSER OSNAME,P.SERIAL# S_#,P.TERMINAL,P.PROGRAM PROGRAM,P.BACKGROUND,S.STATUS,RTRIM(SUBSTR(A.SQL_TEXT, 1, 80)) SQLFROM V$PROCESS P, V$SESSION S,V$SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS(+) AND P.SPID LIKE '%&1%';

2.7 檢查碎片程度高的表

SELECT segment_name table_name,COUNT(*) extents FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name HAVING COUNT(*)=(SELECT  MAX(COUNT(*))
FROM dba_segments GROUP BY segment_name);

2.8 檢查表空間的 I/O 比例

SELECT DF.TABLESPACE_NAME NAME,DF.FILE_NAME "FILE",F.PHYRDS PYR, F.PHYBLKRD PBR,F.PHYWRTS PYW, F.PHYBLKWRT PBW FROM V$FILESTAT F, DBA_DATA_FILES DF WHERE F.FILE# = DF.FILE_ID ORDER BY DF.TABLESPACE_NAME;

2.9 檢查檔案系統的 I/O 比例

SELECT SUBSTR(A.FILE#,1,2) "#", SUBSTR(A.NAME,1,30) "NAME", 
A.STATUS,A.BYTES,B.PHYRDS,B.PHYWRTS  FROM  V$DATAFILE  A,  V$FILESTAT  B  WHERE A.FILE# =
B.FILE#;

2.10 檢查死鎖及處理

select sid,serial#,username,SCHEMANAME,osuser,MACHINE, terminal,PROGRAM,owner,object_name,object_type,o.object_id from dba_objects o,v$locked_object l,v$session  s
where o.object_id=l.object_id and s.sid=l.session_id;

oracle 級 kill 掉該 session

alter system kill session  '&sid,&serial#';

作業系統級 kill 掉 session

#>kill -9 pid

2.11 檢查資料庫cpu、I/O、記憶體性能

記錄資料庫的 cpu 使用、IO、記憶體等使用情況，使用 vmstat,iostat,sar,top
等命令進行資訊收集并檢查這些資訊，判斷資源使用情況，

CPU 使用情況：
[root@sale8 ~]# top

系統 I/O 情況：
iostat -k 1 3
系統負載情況：
#uptime

2.12 查看是否有僵死行程

有些僵尸行程有阻塞其他業務的正常運行，定期殺掉僵尸行程，

select spid  from v$process where addr not in (select paddr from  v$session);

2.13 檢查行鏈接/遷移

Sql>select table_name,num_rows,chain_cnt From dba_tables Where owner='CTAIS2' And chain_cnt<>0;

2.14 檢查緩沖區命中率

SELECT a.VALUE + b.VALUE logical_reads, c.VALUE phys_reads,
round(100*(1-c.value/(a.value+b.value)),4) hit_ratio FROM v$sysstat a,v$sysstat b,v$sysstat c
WHERE a.NAME='db block gets' AND b.NAME='consistent gets' AND c.NAME='physical reads' ;

2.15 檢查共享池命中率

select sum(pinhits)/sum(pins)*100   from v$librarycache;

2.16 檢查排序區

select name,value from v$sysstat where name like '%sort%';

如 果 disk/(memoty+row) 的 比 例 過 高 ， 則 需 要 調 整
sort_area_size(workarea_size_policy=false 或
pga_aggregate_target(workarea_size_policy=true)

2.17 檢查日志緩沖區

select name,value from v$sysstat where name in ('redo entries','redo buffer allocation retries');

三 性能調優及方法

性能調優主要有主動調優和被動調優，主動調優在前面我們已經進行了闡述， 被動調優主要有以下方法進行，

• 確定合理的性能優化目標

• 測驗并記錄當前的性能指標

• 確定當前存在的 Oracle 性能瓶頸 (Oracle 中何處存在等待，哪個 SQL陳述句與此有關)

• 確定當前的作業系統瓶頸

• 優化相關的組件 (應用、資料庫、I/O、連接 OS 及其它)

• 跟蹤并實施變化管理制度

• 測驗并記錄目前的性能指標

• 重復第 3 到第 7 步直至達到既定的優化目標 不要對并非性能瓶頸的部分進行優化，否則可能引起額外的問題，

正如任何聰明的人會告訴你的：“如果還未壞，千萬不要修”，更重要的是，一旦既定的優 化目標已經達到，就務必停止所有的優化，
獲取 Oracle 的性能指標 (測驗前及測驗后)必須在峰值處理時測驗并獲取系 統在優化前和優化后的性能指標，資料采集不應在資料庫 instance 剛剛起動后 進行， 同時， 測驗 資料 應在峰 值期 間每過 15 分 鐘進 行一次 ，初 始化引數

TIMED_STATISTICS 應該被設為 TRUE，
通過運行以下腳本開始快照：

$ORACLE_HOME/rdbms/admin/utlbstat.sql.

通過運行以下腳本結束快照：

$ORACLE_HOME/rdbms/admin/utlestat.sql.

完成 utlestat.sql 操作后，會在當前目錄中生成名為“report.txt”的檔案， 包含系統的性能資料，該報告包括每 15 分鐘捕獲的所有與 Oracle 例程相關的 引數，

3.1. 尋找問題根源

如上所述，通過查看 v s y s t e m e v e n t 事 件 開 始 系 統 事 件 的 問 題 診 斷 ， 下 一 步 是 查 看 v system_event 事件開始系統事件的問題診斷，下一 步是查看 v systeme?vent事件開始系統事件的問題診斷，下一步是查看vsession_event，找出引起或經歷等待事件的行程，最后一步是通過 v$session_wait 獲得事件的細節，同時，應該進一步通過 OS 進行深入分析，了 解核心的 CPU、記憶體和 IO 狀態引數，最后，結合兩種不同的診斷的結論，找出 系統瓶頸所在，

3.2 System_Event事件

v$system_event 可以從全域的角度查看 Oracle 系統中的所有事件，盡管它 并不包括任何行程級的資訊(當前或歷史)，但卻可以顯示上次例程彈出后總的等 待時間，這種動態性能視圖中的資料，會在下次例程起動時清零，出于這種原因， 這種視力中的資料應該在不同時段進行抽樣，

3.3Session_Event事件

v s e s s i o n e v e n t 視 圖 在 進 程 級 提 供 與 v session_event 視圖在行程級提供與 v sessione?vent視圖在進程級提供與vsystem_event 相同的資訊(即， SID 等)，這種視圖可以從“system-wide events” 級進一步鉆取，到達行程級， 以確哪個行程引起或經歷了等待事件，

3.4Session_Wait

v$session_wait 視圖在特定事件的行程級提供低層次的資訊挖掘，不同于 其它一些視圖，這種方式可以“實時”獲取行程級的等待資訊，這是真正有用的 資訊，切記，每次查看這一視圖得到的結果可能不一樣，這可能與資料庫中當前 的活動有關，

3.5 應用優化

從統計(和現實) 的角度看，80% 的 Oracle 系統性能問題可以通過 SQL 代碼 優化來解決，任何應用優化的程序，不外乎是索引優化、全表掃描、并行機制改 進和選擇正確資料組合方法的程序，這正是要達到最佳應用性能所必須考慮的因 素，沒有 SQL 的優化，就無法實作高性能的應用，良好的 SQL 陳述句可以減少 CPU 資源的消耗，提高回應速度，同時，優化后的 SQL 陳述句還可以提高應用的可擴 展性，這是除增加大量記憶體外，任何其它硬體手段也無法實作的，

四 例程調優

需要配置的主要初始化引數 以下是一些已知與例程優化關系最密切的一些核心 Oracle 初始化引數，它
們都會影響 Oracle 及 SGA 區的活動，任何對這些引數的改動，在實施到生產環 境之前，都必須進行測驗，一旦改變了生產環境的引數，就必須對相關的 Oracle 動態性能指標和作業系統的性能進行監測，尋找可能由此產生的例外現象，

1. DB_BLOCK_SIZE

該引數在資料庫建立前設定，決定了資料庫中每個資料塊的大小，只有重新 建立資料庫，才有可能改變該引數，db_block_size 的配置應遵循以下公式： DB_BLOCK_SIZE = FILESYSTEM BLOCKSIZE >= O-S PAGESIZE 這可以確保 Oracle
獲得最佳 I/O 性能，同時不會由于冗余或不必要的 I/O，給 I/O 子系統帶來壓力，

2. DB_BLOCK_BUFFERS

該引數決定了 SGA 區資料庫緩沖區中的塊數量，由于這是 Oracle 讀取和寫入的區域，它的不正確配置會引起嚴重的 I/O 性能問題，盡管緩沖區的大小與應 用性質、資料庫大小、同步用戶數等無關，它的確是 SGA 區中最大的組件，經常 可以看到緩沖區占用 75-80%SGA 區記憶體的情況，另外，這一引數設定過大，也會 引起整個系統的記憶體不足，引起作業系統過多的讀寫操作，
該引數及 SHARED_POOL_SIZE 通常是兩個最重要的 SGA 優化目標，只有當數 據庫緩沖率長時間低于 70%時，才需要增加其大小說，即使在這種情況下，也需 要進一步審查應用的性能和整個系統的吞吐性，若存在延遲性的應用設計問題， 則無論資料庫緩沖區的大小如何，緩沖和讀寫率都不會有太大改變為，在實調優 中，也曾發現由于 SQL 陳述句的問題，出現緩沖率很高，但仍存在全系統性能問題 的情況，

3. SHARED_POOL_SIZE

該引數按位元組數設定，定義了 SGA 中共享區的大小，該組件的大小嚴重依賴 于應用的型別 (即該應用是重用 SQL，還是生成動態 SQL，等等)，同時它也取決 于同步用戶的數量，以及實體是否被配置成支持多執行緒服務器(MTS)，如果該應 用采用了 MTS 配置，則共享區應該明顯增加，因為游標狀態和用戶行程資料等程 序全域區域(PGA)都被置入了共享區，
有關多數應用的 SHARED_POOL_SIZE 大小設定，可以從每 10 個同步用戶 16 MB 共享區開始，這不是一成不變的，因為應用的性質最侄訓決定該組件的大小，只 有當庫緩沖和字典緩沖使用率一直低于 90%時，才需要關注這一引數，但如果應 用并未采用變數合并和/共離圖示時，記憶體的數量并不會使緩沖使用率高于 90%，
共享區過大會導致處理時間增加，甚至 SQL 陳述句的掛起，如果應用不能有效 地重用 SQL，則無論配置多大的庫緩沖或字典緩沖都無濟于事，不能改善緩沖使 用率，
另一個值得考慮的因素是需要隨時使用的存盤 PL/SQL 代碼數量，應用的核 心包可以通過查看 DBA_SOURCE 、 USER_SOURCE 得以確認，其大小通過查詢 DBA_OBJECT_SIZE 了解，另外，為了確定存盤 PL/SQL 是否被置于記憶體，可以查 詢動態性能視圖 V$DB_OBJECT_SIZE，內時，包 DBMS_SHARED_POOL 中的程式大小 可被用于確定應用中大包的規模，

4. LOG_BUFFER

根據位元組設定，該引數定義了 SGA 緩沖區中 redo log 的大小，預設值通常 是資料庫塊大小的四倍，這對于多數環境并不是最佳的，對于中型的 Oracle 環 境，其結構應該為 512 Kb 左右，對該存盤結構而言，更大并不意味著更好，超 過 1 MB 就可能有問題，需要監控 V$SESSION_WAIT 中 log buffer space 的等待 事件，以優化該記憶體結構，需要提醒的是，在線 redo log 檔案的大小設定不當， 會引起 redo 請求的等待，

5. DB_WRITERS

該引數可以針對所有檔案系統支持，且不可使用 Direct I-O 的 Oracle 實施 設定，這并不需要與 raw partitions 一起使用，因為異步 I-O 更加，建議將該 引數設定為(2 * 獨立磁盤驅動器數量/卷)，該引數只有在 report.txt 中的 “average write queue length”持續高于 1 時，才需要設定，在 Oracle 8.0 和 更高版本中，該引數已不再被支持，而為其它兩個名為 DB_WRITER_PROCESSES 和 DBWR_IO_SLAVES 的引數取代，若需要設定 DB_WRITER_PROCESSES 值高于 8，則 DB_WRITER_PROCESSES 可被設為 1，且 DBWR_IO_SLAVES 可被設為 “n”，其中 n 的值必須設定為 (2 * 獨立磁盤驅動器數量/卷)

五 其他優化

5.1 I-O 優化

I-O 優化是系統優化中的一個關鍵步驟，還涉及到其它任務，將檔案在不同 驅動器/卷中進行分布，采用優化磁區技術、確定 I-O 子系統瓶頸、確定控制器 瓶頸并根據應用的型別選擇最佳的 RAID 級，I-O 優化應該在全面了解 Oracle 及 Oracle RDBMS 結構之后進行，應該在進行 I-O 優化前后實施 I-O 資料監控，如 平均服務時間，IOPS，平均磁盤佇列長度等，

5.2 競爭優化

多數與 Oracle 有關的競爭問題可以通過主動配置管理相關的初始化引數進 行，不恰當地配置 init.ora 中的鎖引數可能引起競爭，為了不打破其中的平衡， 所需的引數可進行配置并主動得以處理，
包括表在內的 資料庫對 象可能存在 兩個競爭 點，第一個 是所配置的 “freelists”的數量 (預設值為 1)，freelist 結構維護著表中可用于插入的 塊，對于存在大量同步插入的表，有必要配置該結構，為了以主動方式處理 freelist 競爭，必須在建立表時配置 FREELISTS，可考慮的最佳值為 (2 * CPU 數量) ，V$WAITSTAT 不可能指示存在 freelist 競爭，除非存在 freelist 組， 而這種設定只存在于 Oracle Parallel Server 中，即便如此，也無法了解哪個 表存在競爭中，主動式的 freelist 競爭 調優可以事先預防問題出現，
資源競爭的第二個來源與索引有關，即物件塊頭中配置的事務槽數量，事務 槽是塊頭中的區域，是事務處理行程采用自身識別號進行注冊，以便任何被修改 的更能夠通過特定事務槽數量在低層得以識別的地方，如果所有現存的事務槽已 經被其它事務占用，服務器器行程會從塊的 PCTFREE 中請求 23 個位元組，建立一 個新的槽，這種情況適用于存在大量同步事務的物件，對于事務槽的競爭，需要 設定 INITRANS 引數，對于塊大小為 8K 的資料庫，多數情況下，4 為最佳設定， 占用的空間僅為 92 位元組，卻可以大大減少運行時故障和性能問題，

5.3 O-S 監控

資料庫忙時，應該對作業系統進行監控，因為作業系統的性能指標會揭示數 據庫活動的性質及其對系統的影響，例如，為了了解 CPU 的利用率，可以通過 system activity reporter (sar – u interval frequency) 、 mpstat (Sun Solaris), top (多數 UNIX)、 osview (SGI Irix) 及 vmstat 等命令，Sar 和 vmstat 也可被用于確定包括記憶體使用率、I-O 引數、佇列等待、讀取/交換區活 動等資訊，在 Solaris 上，mpstat utility 也可用于獲取前面提到的 CPU 利用 率資料，Solaris 上的 Adrian 性能管理工具也很有用，可以利用其中的一到多 個工具來確定系統的性能狀況，找出可能存在的瓶頸，
Oracle 資料庫性能的管理需要遵循系統的方法論，以確保所有核心問題得 以解決，多數問題可以事先得以管理，了解與 O-S 相關的問題是成功的關鍵，勿 需置疑，系統硬體配置上的良好平衡也是至關重要的，必須承認， 80% 的系統 性能問題可以通過書寫更好的 SQL 陳述句來解決，來文試圖探究其余 20%中可能覆 蓋的內容，同時，必須遵守嚴格的規定，在調優目標達到后終止所有努力，了解 自己想到何處是重要的，更重要的是，要知道自己何時到達了目的地，