oracle Statspack從Oracle被引入馬上成為DBA和Oracle專家用來診斷數據庫性能的強有力工具通過Statspack我們可以很容易的確定Oracle數據庫的瓶頸所有記錄數據庫性能狀態也可以使遠程技術人員迅速了解的的數據庫運行狀況所以了解和使用Statspack對於DBA來說至關重要
在Statspack發布之前我們經常用來診斷數據庫的工具是兩個腳本utlbstatsql和utlestatsqlBSTAT/ESTAT是一個非常簡單的性能診斷工具UTLBSTAT獲得開始時很多V$視圖的快照UTLESTST通過先前的快照和當前視圖生成一個報表實際上此報表相當於statspack的兩個采樣點
Statspack通過連續的采樣能夠給我們提供很重要的趨勢分析數據這是一個巨大的進步
所以能夠使用Statspack的環境就盡量不使用BSTAT/ESTAT方式來診斷數據庫問題
(Oracle以前的版本使用Statspack需要使用statscbpssql腳本建立一個V$buffer_pool_statistics視圖可以到oracle網站上下載)
原理
運行oracle自帶腳本生成一系列的統計表
生成快照采樣(運行statspacksnap可生成快照一般通過自動任務生成快照)
根據快照生成報告
一准備
檢查部分參數值
job_queue_process為了能夠建立自動任務執行數據收集此參數必須大於
alter system set job_queue_processess=;
timed_statistics設置為true使收集的時間信息存儲在V$sessstats和V$sysstats等動態性能視圖中但會消耗資源可以在使用Satspack之前設為true采樣過後把該參數動態修改為false
alter system set timed_statistics=true;
腳本
使用此功能需要運行oracle自帶腳本在數據庫中生成一系列的表和視圖用於收集各種信息
腳本位於%oracle_home%\rdbms\admin目錄下oracle下是一組以stat開頭的文件以後的版本是一組以sp開頭的文件
二安裝
以sysdba身份登錄
i可以用internal用戶登錄sqlplus internal
i及以後版本可以用sys用戶以sysdba身份登錄sqlplus / as sysdba
(最好轉到腳本所有目錄%oracle_home%\rdbms\admin便於執行腳本)
創建表空間用於保存采樣數據
create tablespace perfstat datafile e:\hs\dat\perstatora
size m
extent management local;
Statspack的報表數據還是相當占空間的特別是在多次連續采樣的情況下所以不能太小最小M否則創建對象會失敗
運行腳本安裝statspack
Oracle%oracle_home%\rdbms\admin\statscresql
以後%oracle_home%\rdbms\admin\spcreatesql
腳本會創建用戶perfstat需要指定此用戶密碼
輸入 perfstat_password 的值: perfstat
需要輸入用戶perfstat使用的表空間指定新建的表空間即可
輸入 default_tablespace 的值: perfstat
需要指定用戶perfstat使用的臨時表空間
輸入 temporary_tablespace 的值: tmp
安裝成功可以看到如下信息
Creating Package STATSPACK
程序包已創建
沒有錯誤
Creating Package Body STATSPACK
程序包體已創建
沒有錯誤
NOTE:
SPCPKG complete Please check spcpkglis for any errors
查看錯誤信息
NT下
host find ORA *LIS
host find err *LIS
Unix下
grep ORA *lis
grep err *lis
如果出現錯誤可以運行腳本刪除相關內容@%oracle_home%\rdbms\admin\spdropsql
(注意也要在sysdba下運行腳本刪除相關對象)
然後再重新運行腳本安裝
這個安裝過程創建了一系列的表用於存入采樣數據
查看新建表
select dttable_name from dba_tables dt where dtowner=PERFSTAT
conn perfstat/perfstat
select table_name from user_tables;
三測試statspack
用perfstat用戶連接
(如果你剛執行完@spcreate則oracle默認將當前用戶切換為perfstat )
運行statspacksnap可以產生系統快照運行兩次產生兩次快照
SQL> execute statspacksnap;
PL/SQL 過程已成功完成
SQL> execute statspacksnap;
PL/SQL 過程已成功完成
然後執行腳本%oracle_home%\rdbms\admin\spreportsql就可以生成基於兩個時間點的報告
需要輸入起始快照ID和結束快照ID以及報告文件名
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
輸入 begin_snap 的值:
Begin Snapshot Id specified:
輸入 end_snap 的值:
End Snapshot Id specified:
輸入 report_name 的值: e:\sreporttxt
如果一切正常說明安裝成功
查看產生的快照
select tsnap_idto_char(tsnap_timeyyyymmdd hh:mi:ss) as S_Timetsnapshot_exec_time_s from STATS$SNAPSHOT t;
四規劃自動任務
安裝之後我們就可以設置定時任務定時采樣收集數據
使用系統腳本
使用腳本spautosql來定義自動任務
spautosql在關鍵內容
begin
select instance_number into :instno from v$instance;
dbms_jobsubmit(:jobno statspacksnap; trunc(sysdate+/HH) trunc(SYSDATE+/HH) TRUE :instno);
commit;
end;
執行spquto就建立了一個每小時執行一次的數據收集計劃
這個Job任務定義了收集數據(執行statspacksnap)的時間間隔為小時
一天小時分鐘>
/HH 每小時一次
/MI 每半小時一次
/MI 每分鐘一次
/MI 每分鐘一次
關於采樣間隔通常建議小時如有特殊需要可以設置更短如半小時但不推薦更短因為statpack的執行本身需要消息資源太短的采樣對系統的性能會產生較大的影響(甚至會使statspack的執行出現在采樣數據中)
使用自定義語句
)可以用以下語句創建一個job我們設定其每個小時自動收集一次采樣
Variable job number ;
begin
dbms_jobsubmit(:job statspacksnap; trunc( sysdate + / hh ) trunc(sysdate+/hh) );
commit ;
end ;
/
查看當前job
set linesize
col schema_user for a
col what for a
col interval for a
col next_d for a
SQL> select jobschema_userto_char(next_dateyyyymmdd hhmiss) as next_Dintervalwhat from user_jobs;
JOB SCHEMA_USER NEXT_D INTERVAL WHAT
PERFSTAT trunc(sysdate+ statspacksnap;
/hh)
)再創建一個job讓它在第二天凌晨自動停止采樣job的執行
查出job編號執行以下語句
Variable job number ;
begin
dbms_jobsubmit(:job dbms_jobbroken(true); trunc( sysdate + ) null );
commit ;
end ;
/
查看任務
set linesize
col schema_user for a
col what for a
col interval for a
col next_d for a
select job next_date next_sec what from user_jobs;
select jobschema_userto_char(next_dateyyyymmdd hhmiss) as next_Dintervalwhat from user_jobs;
查看產生的快照
select tsnap_idto_char(tsnap_timeyyyymmdd hh:mi:ss) as S_Timetsnapshot_exec_time_s from STATS$SNAPSHOT t;
五生成報告
同測試一樣
用perfstat用戶連接
執行腳本%oracle_home%\rdbms\admin\spreportsql輸入起始快照ID和結束快照ID以及報告文件名生成statspack報告
一次statspack報告不能跨越一次停機但是之前或之後的連續區間收集的信息依然有效你可以選擇之前或之後的采樣生成report
如果中間停過機會收到以下錯誤信息
第 行出現錯誤:
ORA: The instance was shutdown between snapshots and
ORA: 在 line
六後續處理
移除定時任務
當你完成一個采樣報告你應該及時移除這個job任務遺漏一個無人照顧的job是非常危險的……
查看任務並移除任務
select job next_date next_sec what from user_jobs;
execute dbms_jobremove(ID)
刪除歷史數據
)刪除采樣數據
只要刪除stat$snapshot數據表中的相應數據其他表中的數據會相應的級連刪除
select max(snap_id) from stats$snapshot;
delete from stats$snapshot where snap_id<=;
刪除過程中你可以看到所有相關的表都被鎖定了
select aobject_id aoracle_username bobject_name
from v$locked_object adba_objects b
where aobject_id=bobject_id
)truncate統計信息表使用腳本sptruncsql刪除所有的采樣數據但保留statspack的庫結構
如果有大量數據直接delete采樣數據是非常緩慢的使用腳本sptruncsql可以快速的刪除所有統計信息
查看腳本可以看出是用truncate table截掉所有相關的表中的內容
truncate table STATS$FILESTATXS;
truncate table STATS$TEMPSTATXS;
delete from STATS$DATABASE_INSTANCE;
)刪除statspack所有數據和各種對象使用腳本spdropsql全部刪除不留痕跡!但有表空間在~~
七其它重要腳本
spuexppar數據導出用參數文件主要內容如下
file=spuexpdmp log=spuexplog compress=y grants=y indexes=y rows=y constraints=y owner=PERFSTAT consistent=y
我們可以使用以下命令導出
exp perfstat/perfstat parfile=spuexppar
例
E:\oracle\product\\db_\RDBMS\ADMIN>exp perfstat/perfstat parfile=spuexppar
刪除數據腳本spdtabsql刪除表及同義詞spdusrsql刪除用戶
oracle新增腳本
)用於升級statspack對象的腳本需要sysdba權限升級前請先備份存在的schema數據
spupsql用於升級版本的模式至版本
spupsql如果從statspack升級需要運行這個腳本
spupsql從statspack升級需要運行這個腳本然後運行spupsql
)sqrepsqlsql 用於根據給定的SQL Hash值生成SQL報告
九調整statspack的收集門限
statspack有兩種類型的收集選項
level級別控制收集數據的類型
threshold門限設置收集的數據閥值
都保存在表stats$statspack_parameter中
級別(level)有三種快照級別默認值為
查看當前level級別
select snap_level from stats$statspack_parameter;
)level=一般性能統計包括等待事件系統事件系統統計回滾段統計行緩存SGA會話鎖緩沖池統計等等
)level=在level的基礎上增加SQL語句的收集SQL語句收集結果記錄在ststs$sql_summary表中
)level=增加子鎖統計包括level的所有內容還會將附加子鎖的入stats$latc_children表中使用這個級別需要慎重建議在oracle support的指導下進行
可以通過statspack包修改缺省的級別設置如
SQL>execute statspacksnap(i_snap_level=>i_modify_parameter=>true);
通過這條語句
以後的收集級別都將是級
如果你只是想修改本次收集級別可以忽略i_modify_parameter參數如下
SQL>execute statspacksnap(i_snap_level=>); (？？？是麼？)
快照門限只應用於stats$sql_summary表中獲取的Sql語句
因為每一個快照都會收集很數據及sql語句所以stats$sql_summary很快就會成為statspack中最大的表
查看當前各種門限
select executions_thdisk_reads_thparse_calls_thbuffer_gets_th from stats$statspack_parameter;
各種門限
)executions_thsql語句執行的數量(默認值=)
)disk_reads_thsql語句執行的磁盤讀入數量(默認值=)
)parse_calls_thsql語句執行的解析調用數量(默認值=)
)buffer_gets_thsql語句執行的緩沖區獲取數量(默認值=)
任何一個門限值超過以上參數就會產生一條記錄
通過調用statspackmodify_statspack_parameter函數我們可以改變門限的默認值
SQL>execute statspackmodify_statspack_parameter(i_buffer_get_th=>i_disk_reads_th=>;
十Statspack 報告分析
Statspack 報告分為如下部分
數據庫總體信息
含實例版本是否RACCPU物理內存oracle內存設置等等
每秒每事務的資源消耗情況
實例的各組件的命中率
共享池總體情況(Shared Pool Statistics)
等待時間最長的前個等待事件(Top Timed Events)
含前等待事件兩次采樣間cpu占用內存分配等信息Oracle各版本等待事件並不完全相同數量依版本升高而增加關於各項等待事情的說明三思之前的學習動態性能表系列文章中有過介紹有心的朋友可以去搜搜看
DB 所有等待事件(Wait Events)Total wait time>= 的事件
後台等待事件(Background Wait Events)Total wait time>= 的事件
柱狀顯示的等待事件(Wait Event Histogram)顯示各等待事件不同響應時間的比例
根據CPU開銷進行排序的SQL(SQL ordered by CPU)
根據執行時間進行排序的SQL(SQL ordered by Elapsed)
根據BufferGets進行排序的SQL(SQL ordered by Gets)
根據物理讀進行排序的SQL(SQL ordered by Reads)
根據執行次數排序的SQL(SQL ordered by Executions)
根據解析調用次數排序的SQL(SQL ordered by Parse Calls)
實例記錄的各項活動的統計數據(Instance Activity Stats)
表空間的IO統計(Tablespace IO Stats)
數據文件的IO統計(File IO Stats)
數據文件讀柱狀圖形式統計(File Read Histogram Stats)
Buffer 池統計數據(Buffer Pool Statistics)含實例恢復的統計數據buffer池大小設置建議等等
PGA 統計數據(PGA Aggr Target Stats)含PGA緩存命中率柱狀圖形式的統計以及PGA設置建議等等
進程的內存占用情況(Process Memory Summary Stats)含占用內存較多的進程等
undo 段摘要
undo 段統計
鎖存器的當前情況
鎖存器睡眠等待統計
鎖存器失敗情況
數據字典cache性能統計(Dictionary Cache Stats)
庫緩存的活動情況(Library Cache Activity)
Rule 集(Rule Sets)
共享池設置建議(Shared Pool Advisory)
SGA 摘要(SGA Memory Summary)
SGA 統計信息(SQL Memory Statistics)
系統參數(initora Parameters)
From:http://tw.wingwit.com/Article/program/Oracle/201311/18097.html
