【實驗】【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;
收集作業系統的計時資訊,這些資訊可被用來顯示時間等統計資訊、最佳化和 語句.
false-防止因從作業系統請求時間而引起的開銷
true-可使statspack收集統計資訊,否則收集的統計資訊大約只能起到10%的作用.可以在使用statspack之前在system更改,取樣過後把該引數動態修改成false.亦可一致開啟利大於弊.
該引數使收集的時間資訊儲存在在v$sesstat和v$sysstat等動態效能檢視中.
 
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中.在使用這個級別時需要慎重,建議在 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的功能.