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