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

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

2).timed_statistics
SQL> alter system set timed_statistics = true;
收集操作系統(tǒng)的計(jì)時(shí)信息,這些信息可被用來顯示時(shí)間等統(tǒng)計(jì)信息、優(yōu)化數(shù)據(jù)庫和 SQL 語句.
false-防止因從操作系統(tǒng)請求時(shí)間而引起的開銷
true-可使statspack收集統(tǒng)計(jì)信息,否則收集的統(tǒng)計(jì)信息大約只能起到10%的作用.可以在使用statspack之前在system更改,采樣過后把該參數(shù)動(dòng)態(tài)修改成false.亦可一致打開利大于弊.
該參數(shù)使收集的時(shí)間信息存儲在在V$SESSTATS和V$SYSSTATS等動(dòng)態(tài)性能視圖中.
 
2.安裝statspack
1).以internal身份或具有SYSDBA權(quán)限的用戶登陸
cd $ORACLE_HOME/RDBMS/ADMIN
sqlplus / as sysdba
2).檢查數(shù)據(jù)文件路徑及磁盤空間,以決定創(chuàng)建數(shù)據(jù)文件的位置,建立一個(gè)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).執(zhí)行創(chuàng)建腳本,創(chuàng)建過程中會提示輸入default_tablespace和temporary_tablespace的內(nèi)容,若有錯(cuò)誤可以查看相應(yīng)生成的.lis文件.
SQL> @spcreate
若需要重建,可以運(yùn)行spdrop.sql腳本來刪除這些對象.然后重新運(yùn)行spcreate.sql
SQL> @spdrop.sql
SQL> @spcreate

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

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

6.生成分析報(bào)告
SQL> @spreport

7.刪除歷史數(shù)據(jù)
第一種方法:刪除stats$snapshot數(shù)據(jù)表中的相應(yīng)數(shù)據(jù),其他表中的數(shù)據(jù)會相應(yīng)的級連刪除:
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保存相應(yīng)數(shù)據(jù)
spuexp.par內(nèi)容:
file=spuexp.dmp log=spuexp.log compress=y grants=y indexes=y rows=y constraints=y wner=PERFSTAT consistent=y
導(dǎo)出語句:
exp userid=perfstat/perfstat parfile=spuexp.par

9.使用sprepsql.sql根據(jù)給定的SQL Hash值生成SQL報(bào)告
SQL> @sprepsql

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

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

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

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

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

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

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

Good luck.

secooler

-- The End --