【STATSPACK】Statspack安裝、測試與使用

Statspack腳本存放目錄:
$ORACLE_HOME/RDBMS/ADMIN
 
1.需要設置的參數:
1).job_queue_processes
SQL> alter system set job_queue_processes = 6;
SQL> alter system set job_queue_processes = 6 scope=both;
為了能夠建立自動任務,執行數據收集,該參數需要大于0.
你可以在初試化參數文件中修改該參數(使該參數在重起后以然有效).
該參數可以在系統級動態修改(重起后失效).

2).timed_statistics
SQL> alter system set timed_statistics = true;
收集操作系統的計時信息,這些信息可被用來顯示時間等統計信息、優化數據庫和 SQL 語句.
false-防止因從操作系統請求時間而引起的開銷
true-可使statspack收集統計信息,否則收集的統計信息大約只能起到10%的作用.可以在使用statspack之前在system更改,采樣過后把該參數動態修改成false.亦可一致打開利大于弊.
該參數使收集的時間信息存儲在在V$SESSTATS和V$SYSSTATS等動態性能視圖中.
 
2.安裝statspack
1).以internal身份或具有SYSDBA權限的用戶登陸
cd $ORACLE_HOME/RDBMS/ADMIN
sqlplus / as sysdba
2).檢查數據文件路徑及磁盤空間,以決定創建數據文件的位置,建立一個100M以上的表空間.
SQL> select file_name from dba_data_files;
SQL> create tablespace perfstat datafile 'd:\oracle\oradata\eygle\perfstat.dbf' size 500M extent management local;
3).執行創建腳本,創建過程中會提示輸入default_tablespace和temporary_tablespace的內容,若有錯誤可以查看相應生成的.lis文件.
SQL> @spcreate
若需要重建,可以運行spdrop.sql腳本來刪除這些對象.然后重新運行spcreate.sql
SQL> @spdrop.sql
SQL> @spcreate

3.測試Statspack的可用性
運行statspack.snap可以產生系統快照,運行兩次,然后執行spreport.sql就可以生成一個基于兩個時間點的報告.
如果一切正常,說明安裝成功.
SQL>execute statspack.snap
SQL>execute statspack.snap
SQL>@spreport.sql
 
4.設置定時任務
SQL> @spauto
腳本中有關時間間隔的設置內容,可以修改spauto.sql其內容來更改執行間隔,默認間隔為一小時.
dbms_job.submit(:jobno, 'statspack.snap;',trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
這個job任務定義了收集數據的時間間隔:
一天有24個小時,1440分鐘,那么:
1/24 HH每小時一次
1/48 MI每半小時一次
1/144 MI每十分鐘一次
1/288 MI每五分鐘一次

5.移除定時任務
SQL> select job,log_user,priv_user,last_date,next_date,interval from user_jobs;
SQL> execute dbms_job.remove('28')

6.生成分析報告
SQL> @spreport

7.刪除歷史數據
第一種方法:刪除stats$snapshot數據表中的相應數據,其他表中的數據會相應的級連刪除:
SQL> select max(snap_id) from stats$snapshot;
SQL> delete from stats$snapshot where snap_id <= 166;
第二種方法:使用自帶的腳本sptrunc.sql
SQL> @sptrunc

8.使用spuexp.par文件exp保存相應數據
spuexp.par內容:
file=spuexp.dmp log=spuexp.log compress=y grants=y indexes=y rows=y constraints=y wner=PERFSTAT consistent=y
導出語句:
exp userid=perfstat/perfstat parfile=spuexp.par

9.使用sprepsql.sql根據給定的SQL Hash值生成SQL報告
SQL> @sprepsql

10.調整STATSPACK的收集門限
Statspack有兩種類型的收集選項:
級別（level）:控制收集數據的類型
門限（threshold）:設置收集的數據的閾值.
1)．級別（level）
Statspack共有三種快照級別,默認值是5
a.level 0: 一般性能統計.包括等待事件、系統事件、系統統計、回滾段統計、行緩存、SGA、會話、鎖、緩沖池統計等等.
b.level 5: 增加SQL語句.除了包括level0的所有內容,還包括SQL語句的收集,收集結果記錄在stats$sql_summary中.
c.level 10: 增加子鎖存統計.包括level5的所有內容.并且還會將附加的子鎖存存入stats$lathc_children中.在使用這個級別時需要慎重,建議在Oracle support的指導下進行.
可以通過statspack包修改缺省的級別設置
SQL> execute statspack.snap(i_snap_level=>0,i_modify_parameter=>’true’);
如果你只是想本次改變收集級別,可以忽略i_modify_parameter參數.
SQL> execute statspack.snap(i_snap_level=>0);
2).快照門限
快照門限只應用于stats$sql_summary表中獲取的SQL語句.
因為每一個快照都會收集很多數據,每一行都代表獲取快照時數據庫中的一個SQL語句,所以stats$sql_summary很快就會成為Statspack中最大的表.
門限存儲在stats$statspack_parameter表中:
executions_th這是SQL語句執行的數量(默認值是100)
disk_reads_tn這是SQL語句執行的磁盤讀入數量（默認值是1000）
parse_calls_th這是SQL語句執行的解析調用的數量（默認值是1000）
buffer_gets_th這是SQL語句執行的緩沖區獲取的數量（默認值是10000）
任何一個門限值超過以上參數就會產生一條記錄.
通過調用statspack.modify_statspack_parameter函數改變門限的默認值:
SQL>execute statspack.modify_statspack_parameter(i_buffer_gets_th=>100000,i_disk_reads_th=>100000);

11.Statspack報告重要內容
1)."Execute to Parse %" 執行分析比率
= 100 * (1 - Parses/Executions)
Parses = "parse count (total) "
Executions = "execute count"
當Parses > Executions時,就會出現比率小于0的情況.
該值<0通常說明shared pool設置或效率存在問題
造成反復解析,reparse可能較嚴重,或者可能與snapshot有關
如果該值為負值或者極低,通常說明數據庫性能存在問題

2)."Parse CPU to Parse Elapsd % "
= 100*（"parse time cpu" / "parse time elapsed"）= "Parse CPU to Parse Elapsd %"

3)."Rollback per transaction %" 平均事務回滾率
= Round("user rollbacks" / ("user commits" + "user rollbacks") ,4)* 100%
如果回滾率過高,可能說明你的數據庫經歷了太多的無效操作
過多的回滾可能還會帶來Undo Block的競爭

4).db file scattered read DB 文件分散讀（全表掃）
這種情況通常顯示與全表掃描相關的等待.當數據庫進行全表掃時,基于性能的考慮,數據會分散(scattered)讀入Buffer Cache.如果這個等待事件比較顯著,可能說明對于某些全表掃描的表,沒有創建索引或者沒有創建合適的索引,我們可能需要檢查這些數據表已確定是否進行了正確的設置.
然而這個等待事件不一定意味著性能低下,在某些條件下Oracle會主動使用全表掃描來替換索引掃描以提高性能,這和訪問的數據量有關,在CBO下Oracle會進行更為智能的選擇,在RBO下Oracle更傾向于使用索引.
因為全表掃描被置于LRU（Least Recently Used,最近最少使用）列表的冷端（cold end）,對于頻繁訪問的較小的數據表,可以選擇把他們Cache到內存中,以避免反復讀取.
當這個等待事件比較顯著時,可以結合v$session_longops動態性能視圖來進行診斷,該視圖中記錄了長時間（運行時間超過6秒的）運行的事物,可能很多是全表掃描操作（不管怎樣,這部分信息都是值得我們注意的）.

5).Enqueue
enqueue是一種?す蠶磣試吹乃?ɑ??該鎖定機制?す蠶磣試?如記錄中的數據,以避免兩個session在同一時間更新同一數據.enqueue包括一個排隊機制,即FIFO（先進先出）排隊機制.
Enqueue等待常見的有ST、HW 、TX 、TM等
(1).ST enqueue,用于空間管理和字典管理的表空間(DMT)的區間分配,在DMT中典型的是對于uet$和fet$數據字典表的爭用.對于支持LMT的版本,應該盡量使用本地管理表空間. 或者考慮手工預分配一定數量的區(Extent),減少動態擴展時發生的嚴重隊列競爭.
(2).HW enqueue指和段的高水位標記相關等待;手動分配適當區可以避免這一等待.
(3).TX是最常見的enqueue等待.TX enqueue等待通常是以下三個問題之一產生的結果.
第一個問題是唯一索引中的重復索引,你需要執行提交（commit）/回滾（rollback）操作來釋放enqueue.
第二個問題是對同一位圖索引段的多次更新.因為單個位圖段可能包含多個行地址（rowid）,所以當多個用戶試圖更新同一段時,可能一個用戶會鎖定其他用戶請求的記錄,這時等待出現.直到獲得鎖定的用戶提交或回滾,enqueue釋放.
第三個問題,也是最可能發生的問題是多個用戶同時更新同一個塊.如果沒有足夠的ITL槽,就會發生塊級鎖定.通過增大initrans和/或
maxtrans以允許使用多個ITL槽（對于頻繁并發進行DML操作的數據表,在建表之初就應該考慮為相應參數設置合理的數值,避免系統運行
以后在線的更改,在8i之前,freelists等參數不能在線更改,設計時的考慮就尤為重要）,或者增大表上的pctfree值,就可以很容易的避免這種情況.
(4).TM enqueue隊列鎖在進行DML操作前獲得,以阻止對正在操作的數據表進行任何DDL操作(在DML操作一個數據表時,其結構不能被更改).

6).log file sync  等待事件
當一個用戶提交(commits)或者回滾(rollback),session的redo信息需要寫出到redo logfile中.
用戶進程將通知LGWR執行寫出操作,LGWR完成任務以后會通知用戶進程.
這個等待事件就是指用戶進程等待LGWR的寫完成通知.
對于回滾操作,該事件記錄從用戶發出rollback命令到回滾完成的時間.
如果該等待過多,可能說明LGWR的寫出效率低下,或者系統提交過于頻繁.
針對該問題,可以關注:
log file parallel write等待事件
user commits,user rollback等統計信息可以用于觀察提交或回滾次數
解決方案:
a.提高LGWR性能
盡量使用快速磁盤,不要把redo log file存放在raid 5的磁盤上
b.使用批量提交
c.適當使用NOLOGGING/UNRECOVERABLE等選項
可以通過如下公式計算平均redo寫大??
avg.redo write size = (Redo block written/redo writes)*512 bytes
如果系統產生redo很多,而每次寫的較少,一般說明LGWR被過于頻繁的激活了.
可能導致過多的redo相關latch的競爭,而且Oracle可能無法有效的使用piggyback的功能.

Good luck.

secooler

-- The End --