優(yōu)化策略
2024-07-21 02:39:14
供稿：網(wǎng)友
性能調(diào)試的一般問題
1. IntrodUCtion 介紹
本文檔包括了最一般的調(diào)優(yōu)策略。關于各部分的更專門的信息可以通過提供的鏈接得到。
2. Shared Pool and Library Cache Performance Tuning 共享池和Library Cache的調(diào)優(yōu)
Oracle將SQL語句、存儲包、對象信息和很多其他的項目保存在SGA中一個叫共享池（shared pool）的地方。這個可共享的區(qū)域由一個成熟的高速緩存和堆治理器治理。它有3個基本的問題要克服：
1.) 內(nèi)存分配的單元不是個常量。從池中分配的內(nèi)存單元可能是從幾個字節(jié)到幾千個字節(jié)。
2.) 在用戶完成工作時，不是所有的內(nèi)存都能夠釋放出來，因為共享池的目標是使信息最大程度的共享。
3.) 沒有一個象其他常規(guī)的高速緩存的文件做后備的存儲那樣磁盤空間供整頁的導出。
只有可重新創(chuàng)建的信息可以從Cache中丟棄，在他被再次需要的時候再重新創(chuàng)建。
共享池調(diào)優(yōu)的技巧有：
-刷（ Flush）共享池可以使小塊的內(nèi)存合并為大塊的內(nèi)存。當共享池的碎片過多時，這能夠暫時恢復性能。刷共享池可以使用語句：alter system flush shared_pool;
注重執(zhí)行這個語句將會造成性能的暫時尖峰，因為對象都要重新加載。所以應當在數(shù)據(jù)庫的負載不是很大的情況下進行。
- 確保聯(lián)機事務處理（ OLTP）應用使用綁定變量 （bind variables）. 這一點對于決策支持系統(tǒng)（DSS）并不重要。
- 確保library cache 的命中率 > 95%
- 增大共享池并不總能解決命中率過多的問題。
3. Buffer Cache Performance Tuning 數(shù)據(jù)庫緩存調(diào)優(yōu)
數(shù)據(jù)庫緩存保持了從磁盤上讀去的數(shù)據(jù)塊的備份。
因為緩存通常受到內(nèi)存約束的限制，不是磁盤上所有的數(shù)據(jù)都可以放到緩存里。當緩存滿了的時候，后來的緩存不中使得Oracle將已經(jīng)在緩存中的數(shù)據(jù)寫到磁盤上。后續(xù)的對寫到磁盤上的數(shù)據(jù)的訪問還會造成緩存不中。
數(shù)據(jù)庫緩存調(diào)優(yōu)的技巧如下：
避免以下的問題
- '緩存的最近最少使用(LRN)鏈'（'cache buffers LRU chain' ）的加鎖競爭
- '平均寫隊列'（"Average Write Queue" ）長度過大
- 過多時間花在等待‘寫完畢等待上’（"write complete waits" ）
- 過多時間花在等待‘緩沖釋放等待’上 （"free buffer waits" ）
4. Latch Contention 加鎖（插銷）競爭
插銷加鎖是SGA中保護共享數(shù)據(jù)結構的低層的串行化機制。插銷latch是一類可以非常快的獲得和釋放的鎖。插銷鎖的實現(xiàn)是依靠于操作系統(tǒng)的，尤其在關于一個進程是否會等待一個鎖，和等多久方面。
有如下的鎖（插銷）需要調(diào)優(yōu):
- Redo Copy/Allocation Latch 重寫日志的復制/分配插銷
- Shared Pool Latch 共享池的插銷
- Library Cache Latch Library Cache插銷
5. Redo Log Buffer Performance Tuning 重寫日志緩沖的調(diào)優(yōu)
LGWR 將重寫日志緩沖中的重寫項寫到重寫日志文件中。
一旦LGWR將這些項復制到重寫日志文件中，用戶進程就可以重寫這些項。統(tǒng)計項目"redo log space requests"反映了用戶進程等待重寫日志緩沖中空間的時間的數(shù)字。
設置重寫日志大小的一些提示:
- "redo log space requests"的值應該接近0。
- 設定合適的重寫日志的大小，建議每15-30分鐘進行一次重寫日志的切換。
6. Query Performance Tuning 查詢效率的調(diào)優(yōu)
假如查詢運行得很慢，請考慮這些方面:
- 你希望這個查詢運行的有多快以及有理由這樣要求嗎？
- 優(yōu)化模式OPTIMIZER_MODE 設為何值？
- 查詢涉及的索引都是有效的嗎？
- 在數(shù)據(jù)庫中有沒有其他的長時間運行的查詢（大查詢）
對于CBO(基于成本的優(yōu)化)In case of CBO:
- 表和索引上有統(tǒng)計信息嗎？
- 統(tǒng)計信息是被計算出來的還是被估計出來的？
對于查詢的性能調(diào)整由兩個主要的調(diào)試工具：
- TKPROF
- AUTOTRACE
7. Rollback Segment Performance Tuning 回滾段的調(diào)優(yōu)
Oracle數(shù)據(jù)庫提供了任何數(shù)據(jù)庫對象上的SELECT, INSERT, UPDATE, 和DELETE 操作的讀一致性。
回滾段用于保存由那些要回滾的動作或系統(tǒng)需要產(chǎn)生一個和前面某一時間讀一致的影像所產(chǎn)生的可取消事務。
設置回滾段大小的技巧如下：
- 建議最少每4個事務一個回滾段
- 建議為長時間運行的大查詢提供一個大回滾段。
- v$rollstat中的wrap數(shù)接近0。否則增大擴展大小（extent size）。
SELECT b.name, a.wraps FROM V$ROLLSTAT a, V$ROLLNAME b;
8. Temporary Tablespace Performance Tuning 臨時表空間的調(diào)優(yōu)
從RDBMS release 7.3開始，Oracle引入了臨時表空間的概念。這個表空間用于保存臨時對象，如排序段。排序段采用它所在的表空間的缺省存儲參數(shù)（DEFAULT STORAGE (NEXT) 子句）作為自己的存儲參數(shù)。
臨時表空間的調(diào)優(yōu)的技巧如下:
- 假如即使在穩(wěn)定的狀態(tài)下也存在很多的排序擴展鎖（Sort Extent Pool latch）的競爭，你應該通過修改臨時表空間的DEFAULT STORAGE 子句的NEXT值來增大擴展塊的大小。
- 假如存在很多的排序擴展鎖（Sort Extent Pool latch）的競爭并且這種等待是由于過多的并發(fā)的排序造成的，你應該增大SORT_AREA_SIZE參數(shù)的大小，以使更多的排序能保存在內(nèi)存中。
- 建議讓擴展塊的大小和SORT_AREA_SIZE參數(shù)相同。
以此例說明為什么。 假設你的 extent size = 500K 而 sort_area_size = 1Mg.
現(xiàn)在假如有個到磁盤的排序，每次都要做2個500K的擴展，這會導致性能的降低。
9. Utlbstat/Utlestat Performance Tuning Utlbstat/Utlestat調(diào)優(yōu)
Bstat/Estat 是一堆存放在你的$ORACLE_HOME/rdbms/admin目錄下的SQL腳本，他們對于捕捉系統(tǒng)范圍的數(shù)據(jù)庫性能統(tǒng)計的快照非常有用。UTLESTAT 創(chuàng)建這些視圖的第二個快照，并將兩個快照之間的差異報告到文件 'report.txt'中。
Bstat.sql 在你的sys用戶下創(chuàng)建一系列的表和視圖，其中包括開始時數(shù)據(jù)庫性能統(tǒng)計的快照。
Estat.sql在你的sys用戶下創(chuàng)建一系列的表，其中包括結束時數(shù)據(jù)庫性能統(tǒng)計的快照和一個叫 'report.txt'的文件。
一些技巧:
- 確保你已經(jīng)將TIMED_STATSTICS設為TRUE (這僅僅給數(shù)據(jù)庫操作增加了一點非常小的開銷)。
- 確保數(shù)據(jù)庫在運行utlbstat前，已經(jīng)啟動并且運行了一段時間。
- 從svrmgrl 中而不是sql*plus中運行utbstat.sql和utlestat.sql。
- 確保在腳本utlbstat/estat運行時，數(shù)據(jù)庫不被Down掉，否則產(chǎn)生的統(tǒng)計就不正確。
- 至少運行utlbstat/estat 1-3個小時，才好調(diào)試你的數(shù)據(jù)庫。
10. Checkpoint Performance Tuning 檢查點性能調(diào)優(yōu)
檢查點（ Checkpoint）是一個數(shù)據(jù)庫事件，用來同步內(nèi)存和磁盤上的數(shù)據(jù)文件中的數(shù)據(jù)塊。檢查點的目的有兩個：
(1) 建立數(shù)據(jù)的一致性
(2) 是數(shù)據(jù)庫恢復更快。
調(diào)優(yōu)檢查點進程有如下的技巧：
- CKPT進程能夠明顯的提高效率來降低用戶等待一個檢查點操作完畢所需的時間。
- 假如LOG_CHECKPOINT_INTERVAL的值比重寫日志（ redo log）的大小大，那么 checkpoint只在ORACLE進行日志從一個組到另一組切換的時候才發(fā)生。這正是我們希望的。這個行為在 Oracle 8i中有了變化。
-當把LOG_CHECKPOINTS_TO_ALERT設為TRUE時，將把checkpoint啟動和停止的時間記錄在alert log日志里。這對于你確定checkpoint是否正以最佳的頻率發(fā)生很有幫助。
- 理想的情況是，checkpoint在僅在日志切換時發(fā)生。
11. Import Performance Tuning 數(shù)據(jù)載入性能調(diào)優(yōu)
沒有什么固定的信息是關于如何加速那些慢得讓人難以忍受的import的。顯然import要花費它完成所需要的時間，但是作一些事情可以縮短他們所花的時間。
