性能測試調優策略之數據庫性能調優分析

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

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

　　它的原理是

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

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

　　3、根據快照生成報告。

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

　　AWR可以通過10g以后的oracle DBconsole去生成。

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

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

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

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

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

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

　　生成了statspack報告，我們就可以開始分析了。需要提醒的是，真正看懂這樣一份報告，并不需要知道所有指標的含義，最好能夠了解oracle內部的運行機制，理解的越深，判斷數據庫性能也就越準確。這里只能談談我的一些理解和思路，供大家思考。

　　我們來看一份報告例子。

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

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

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

　　Top 5 Timed Events一般是我每次重點關注的地方，也是我認為最主要的地方。如果這一部分顯示前五位的等待事件，并沒有占用很長時間，說明系統狀態看起來很好。那么我們可能需要多采集一些時間段的數據來分析了。

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

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

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

　　control file sequential read

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

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

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

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

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

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