性能測試調優(yōu)策略之數據庫性能調優(yōu)分析

　　和前面提到的SQL_TRACE不同，當我們遇到了數據庫性能整體下降的時(shí)候，又沒(méi)有特定的對象可以分析時(shí)，做一個(gè)Statspack報告是合適的。通過(guò)全面的檢查，我們可以分析出系統瓶頸在哪兒，如果瓶頸出在sql上面，我們就能獲取相應的sql，通過(guò)SQL_TRACE來(lái)分析。

　　oracle Statspack從Oracle8.1.6被引入，馬上成為DBA和Oracle專(zhuān)家用來(lái)診斷數據庫性能的強有力工具。通過(guò)Statspack我們可以很容易的確定Oracle數據庫的瓶頸所在，記錄數據庫性能狀態(tài)，也可以使遠程技術(shù)人員迅速了解的的數據庫運行狀況。所以，了解和使用Statspack 對于DBA來(lái)說(shuō)至關(guān)重要。

　　它的原理是

　　1、運行oracle自帶腳本，生成一系列的統計表。

　　2、生成快照，采樣(運行statspack.snap可生成快照，一般通過(guò)自動(dòng)任務(wù)生成快照)

　　3、根據快照生成報告。

　　除了statspack，oracle10g以后都提供一個(gè)AWR報告，它是oracle自動(dòng)采集的，采集周期為1小時(shí)，不需要人工干預，收集的信息和statspack非常像，很多時(shí)候選擇哪種方式都可以。

　　AWR可以通過(guò)10g以后的oracle DBconsole去生成。

　　如何安裝statspack，這里簡(jiǎn)單的介紹一下，有興趣的同事可以通過(guò)查閱資料更深入的了解和實(shí)踐。

　　首先檢查oracle系統參數，job_queue_process：為了能夠建立自動(dòng)任務(wù)，執行數據收集，此參數必須大于0;timed_statistics，設置為true，使收集的時(shí)間信息存儲在V$sessstats和V$sysstats等動(dòng)態(tài)性能視圖中。

　　如果是9i以后版本可以以oradba的身份登錄。sqlplus / as sysdba。首先創(chuàng )建表空間create tablespace perfstat datafile 'D:\**\oradata\orcl\perstat.ora'size 100m extent management local;空間視實(shí)際情況而定，如果只是學(xué)習使用，不需要那么大，實(shí)際生產(chǎn)環(huán)境下，可以設置大一些。畢竟Statspack的報表數據還是相當占空間的。然后運行腳本%oracle_home%\rdbms\admin\spcreate.sql，安裝statspack，根據提示輸入密碼，表空間，臨時(shí)表空間，安裝完成，查看lis后綴的日志文件確認是否有錯誤。

　　select dt.table_name from dba_tables dt where dt.owner='PERFSTAT'可以查看采樣數據存儲的表格。

　　下一步我們測試statspack，剛才如果create的時(shí)候，默認修改了當前的連接用戶(hù)為perfstat，運行statspack.snap可以產(chǎn)生系統快照，運行兩次，產(chǎn)生兩次快照。

　　execute statspack.snap;然后執行腳本%oracle_home%\rdbms\admin\spreport.sql就可以生成基于兩個(gè)時(shí)間點(diǎn)的報告。如果一切正常，可以在運行批處理的目錄下查看生成的報告文件。

　　生成了statspack報告，我們就可以開(kāi)始分析了。需要提醒的是，真正看懂這樣一份報告，并不需要知道所有指標的含義，最好能夠了解oracle內部的運行機制，理解的越深，判斷數據庫性能也就越準確。這里只能談?wù)勎业囊恍├斫夂退悸?，供大家思考?/p>

　　我們來(lái)看一份報告例子。

　　對于我們現在已有的系統來(lái)說(shuō)，絕大多數都是屬于OLTP系統(在線(xiàn)事務(wù)處理系統),sql執行非常密集，我們不妨關(guān)注以下2個(gè)指標，Library Hit,Buffer Hit,前面一個(gè)體現了共享池命中率，如果很多SQL不能重用，需要重復解析的話(huà)，會(huì )大大降低系統的性能。后面一個(gè)體現了sql需要的數據塊是否能夠保留在內存中，這樣執行效率要比從磁盤(pán)讀取數據要高很多。

　　我們來(lái)看報告第一部分，主要是數據庫和實(shí)例的信息，然后是采集周期里面系統的信息。這里有一個(gè)數值，DB time: 表示用戶(hù)操作花費時(shí)間，判斷一下，在收集周期里面，用戶(hù)時(shí)間占用的比率，然后結合top5來(lái)分析。Load Profile描述了數據庫資源負載的明細列表?？梢酝ㄟ^(guò)字面含義來(lái)理解它們。這里我們可以關(guān)注下，物理讀寫(xiě)，邏輯讀寫(xiě)，硬分析次數等指標。Redo size是日志的生成量，分為每秒和每事務(wù)所產(chǎn)生的，通常在很繁忙的系統中日志生成量可能達到上百k，甚至幾百k.邏輯讀一般發(fā)生在內存中，和物理讀是區別對待的。硬分析次數前面已經(jīng)提到了。

　　Instance Efficiency Indicators表示內存效率的統計信息，對于OLTP來(lái)說(shuō)，盡可能都接近100%，原因前面已經(jīng)說(shuō)過(guò)了。如果哪項數值過(guò)低，就要做相應的分析研究。

　　Top 5 Timed Events一般是我每次重點(diǎn)關(guān)注的地方，也是我認為最主要的地方。如果這一部分顯示前五位的等待事件，并沒(méi)有占用很長(cháng)時(shí)間，說(shuō)明系統狀態(tài)看起來(lái)很好。那么我們可能需要多采集一些時(shí)間段的數據來(lái)分析了。

　　那么結合我們的top 5的等待事件，我們可以來(lái)衡量不同等級的top

　　sql:1.消耗最多CPU的(邏輯IO比較多的)2.導致過(guò)多物理I/O的(物理IO比較多的)3.執行次數較頻繁的(Execution次數比較多的)4.執行時(shí)間較長(cháng)的(Elapse time比較長(cháng)的)

　　先看看我的機器上采集的結果。

　　control file sequential read

　　control file single write ：控制文件連續讀/控制文件單個(gè)寫(xiě)對單個(gè)控制文件I/O 存在問(wèn)題時(shí)，這兩個(gè)事件會(huì )出現。如果等待比較明顯，檢查單個(gè)控制文件，看存放位置是否存在I/O 瓶頸。

　　control file parallel write：當server 進(jìn)程更新所有控制文件時(shí)，這個(gè)事件可能出現。如果等待很短，可以不用考慮。如果等待時(shí)間較長(cháng)，檢查存放控制文件的物理磁盤(pán)I/O 是否存在瓶頸。

　　以XXX測試為例，我當時(shí)采集的是AWR報告中的一部分，和statspack基本一致，可以看到排名第一位的是頂級 SQL 語(yǔ)句。不得不說(shuō)oracle的分析報告非常智能，非常明細，能夠幫助我們迅速找到問(wèn)題，配合DBA來(lái)調優(yōu)。

　　前面這些內容是報告中最重要的部分，雖然不同的系統生成的報告都會(huì )不一樣，但是解決問(wèn)題的思路是一樣的，根據前面的一手信息，我們能夠了解到等待時(shí)間很長(cháng)的事件，再去其他的部分查找原因。

　　下面簡(jiǎn)單說(shuō)說(shuō)后面報告的含義，Statistic表示各種操作占用數據庫的時(shí)間比例，接下來(lái)是等待事件的明細，主要用來(lái)配合前面的top5事件來(lái)分析。等待事件(Wait Events)是Oracle中比較復雜難懂的概念。Oracle 的等待事件是衡量Oracle 運行狀況的重要依據及指標。等待事件很多這里不一一贅述。常見(jiàn)的等待事件，一般都有對應的分析手段，大家可以參考oracle的資料學(xué)習。這里我們根據XXX測試中的實(shí)例來(lái)分析?？梢钥吹脚琶诙坏氖录堑却?"日志文件同步" 事件消耗了大量數據庫時(shí)間。英文翻譯就是log file sync: 日志文件同步。當一個(gè)用戶(hù)提交或回滾數據 時(shí)，LGWR (Log Writer)將session 會(huì )話(huà)的重做由redo buffer 寫(xiě)入到重做日志中。log file sync 必須等待這一過(guò)程成功完成(Oracle 通過(guò)寫(xiě)redo log file 保證commit 成功的數據不丟失)，這個(gè)事件說(shuō)明提交可能過(guò)于頻繁。為了減少這種等待事件，可以嘗試每次提交更多的記錄，將重做日志置于較快的磁盤(pán)上。

原文轉自：http://blog.csdn.net/xuyubotest/article/details/8158500