大量STATSPACK資料統計分析
/*
對於大量資料庫的statspack分析我們可以把統計資料統一拿到分析資料庫來分析。
以下資料庫各個命中率情況摘自sprepins.sql中部分指令碼，其他內容我們也可以從該指令碼獲得
關於sql的消耗情況我們可以從以下兩個檢視查詢：
SELECT * FROM STATS$SQL_SUMMARY where sql_id='xxxxxx' --單個sql資訊
SELECT * FROM STATS$SQL_STATISTICS where sql_id='xxxxxx' --彙總資訊
我們也可以根據dba_source檢視statspack包體的內容獲得資料庫各個命中率的演算法直接查詢stats$檢視：
詳細資訊見package body中的如下部分，以下給出了statspack的檢視查詢資料庫各個命中率的方法，awr報告都有相對應的檢視：
根據2個snap_id求出差值，然後在計算資料庫各個命中率：
1.'Redo NoWait %:' = round(100 x (1- (redo log space requests/redo entries),2) FROM STATS$SYSSTAT
/* an example for redo nowait ratio
select sum(decode(a.name,'redo entries',b.value-a.value,0))/sum(b.value-a.value) from --decode(a.name,'redo entries',b.value-a.value),b.value-a.value from
(select name,value from stats$sysstat where name in('redo log space requests','redo entries') and snap_id=1) a,
(select name,value from stats$sysstat where name in('redo log space requests','redo entries') and snap_id=2) b
where a.name=b.name
*/
2.'Library Hit %:' = SUM(PINS-RELOADS) / SUM(PINS) FROM STATS$LIBRARYCACHE
3.'Buffer Nowait %:' = 1 - stats$waitstat.wait_count / ('session logical reads') FROM STATS$WAITSTAT AND STATS$SYSSTAT
4.'Buffer Hit %:' = 1-('physical reads' - 'physical reads direct' - 'physical reads direct (lob)')/'session logical reads'
FROM STATS$SYSSTAT
5.'Latch Hit %:' = (1 - (Sum(misses) / Sum(gets))) * 100 FROM STATS$LATCH
6.'Execute to Parse %:' = round(100*(1-'parse count (total)'/'execute count'),2) FROM STATS$SYSSTAT
7,'In-memory Sort %:', = 'sorts (memory)' / ('(sorts (memory)' + 'sorts (disk))') FROM STATS$SYSSTAT
8.'Soft Parse %:' = ('parse count (total)' - 'parse count (hard)')/'parse count (total)' FROM STATS$SYSSTAT
9.'Parse CPU to Parse Elapsd %:' = ('parse time cpu') / ('parse time elapsed') FROM STATS$SYSSTAT
10.'Non-Parse CPU %:' = ('parse time cpu') / ('CPU used by this session') FROM STATS$SYSSTAT
*/
以下是通過呼叫STATSPACK.STAT_CHANGE包來得出資料庫各個命中率：
variable dbid number;
variable inst_num number;
variable bid number;
variable eid number;
variable btim varchar2 (20);
variable etim varchar2 (20);
variable ela number;
variable instart varchar2 (18);
variable bbgt number;
variable ebgt number;
variable bdrt number;
variable edrt number;
variable bet number;
variable eet number;
variable bsmt number;
variable esmt number;
variable bvc number;
variable evc number;
variable bpc number;
variable epc number;
variable spctim number;
variable pct_sp_oss_cpu_diff number;
variable para varchar2(9);
variable lhtr number;
variable bfwt number;
variable tran number;
variable chng number;
variable ucal number;
variable urol number;
variable ucom number;
variable rsiz number;
variable phyr number;
variable phyrd number;
variable phyrdl number;
variable phyrc number;
variable phyw number;
variable prse number;
variable hprs number;
variable recr number;
variable gets number;
variable slr number;
variable rlsr number;
variable rent number;
variable srtm number;
variable srtd number;
variable srtr number;
variable strn number;
variable call number;
variable lhr number;
variable bsp varchar2(512);
variable esp varchar2(512);
variable bbc varchar2(512);
variable ebc varchar2(512);
variable blb varchar2(512);
variable elb varchar2(512);
variable bs varchar2(512);
variable twt number;
variable logc number;
variable prscpu number;
variable prsela number;
variable tcpu number;
variable exe number;
variable bspm number;
variable espm number;
variable bfrm number;
variable efrm number;
variable blog number;
variable elog number;
variable bocur number;
variable eocur number;
variable bpgaalloc number;
variable epgaalloc number;
variable bsgaalloc number;
variable esgaalloc number;
variable bnprocs number;
variable enprocs number;
variable timstat varchar2(20);
variable statlvl varchar2(40);
-- OS Stat
variable bncpu number;
variable encpu number;
variable bpmem number;
variable epmem number;
variable blod number;
variable elod number;
variable itic number;
variable btic number;
variable iotic number;
variable rwtic number;
variable utic number;
variable stic number;
variable vmib number;
variable vmob number;
variable oscpuw number;
-- OS Stat derived
variable ttic number;
variable ttics number;
variable cpubrat number;
variable cpuirat number;
-- Time Model
variable dbtim number;
variable dbcpu number;
variable bgela number;
variable bgcpu number;
variable prstela number;
variable sqleela number;
variable conmela number;
variable bncpu number;
-- RAC variables
variable dmsd number;
variable dmfc number;
variable dmsi number;
variable pmrv number;
variable pmpt number;
variable npmrv number;
variable npmpt number;
variable dbfr number;
variable dpms number;
variable dnpms number;
variable glsg number;
variable glag number;
variable glgt number;
variable gccrrv number;
variable gccrrt number;
variable gccrfl number;
variable gccurv number;
variable gccurt number;
variable gccufl number;
variable gccrsv number;
variable gccrbt number;
variable gccrft number;
variable gccrst number;
variable gccusv number;
variable gccupt number;
variable gccuft number;
variable gccust number;
variable msgsq number;
variable msgsqt number;
variable msgsqk number;
variable msgsqtk number;
variable msgrq number;
variable msgrqt number;
begin
select dbid into :dbid from v$database;
select instance_number into :inst_num from v$instance;
:bid := &begin_snap;
:eid := &end_snap;
:para := 'YES';
STATSPACK.STAT_CHANGES
( :bid, :eid
, :dbid, :inst_num
, :para -- End of IN arguments
, :lhtr, :bfwt
, :tran, :chng
, :ucal, :urol
, :rsiz
, :phyr, :phyrd
, :phyrdl, :phyrc
, :phyw, :ucom
, :prse, :hprs
, :recr, :gets
, :slr
, :rlsr, :rent
, :srtm, :srtd
, :srtr, :strn
, :lhr
, :bbc, :ebc
, :bsp, :esp
, :blb
, :bs, :twt
, :logc, :prscpu
, :tcpu, :exe
, :prsela
, :bspm, :espm
, :bfrm, :efrm
, :blog, :elog
, :bocur, :eocur
, :bpgaalloc, :epgaalloc
, :bsgaalloc, :esgaalloc
, :bnprocs, :enprocs
, :timstat, :statlvl
, :bncpu, :encpu -- OS Stat
, :bpmem, :epmem
, :blod, :elod
, :itic, :btic
, :iotic, :rwtic
, :utic, :stic
, :vmib, :vmob
, :oscpuw
, :dbtim, :dbcpu -- Time Model
, :bgela, :bgcpu
, :prstela,:sqleela
, :conmela
, :dmsd, :dmfc -- begin RAC
, :dmsi
, :pmrv, :pmpt
, :npmrv, :npmpt
, :dbfr
, :dpms, :dnpms
, :glsg, :glag
, :glgt
, :gccrrv, :gccrrt, :gccrfl
, :gccurv, :gccurt, :gccufl
, :gccrsv
, :gccrbt, :gccrft
, :gccrst, :gccusv
, :gccupt, :gccuft
, :gccust
, :msgsq, :msgsqt
, :msgsqk, :msgsqtk
, :msgrq, :msgrqt -- end RAC
);
:call := :ucal + :recr;
-- total ticks (cs)
:ttic := :btic + :itic;
-- total ticks (s)
:ttics := :ttic/100;
-- Busy to total CPU ratio
:cpubrat := :btic / :ttic;
:cpuirat := :itic / :ttic;
end;
/
--查詢示例：
select 'Buffer Nowait %:',round(100*(1-:bfwt/:gets),2) from dual union
select 'Redo NoWait %:',decode(:rent,0,to_number(null), round(100*(1-:rlsr/:rent),2)) from dual union
select 'Buffer Hit %:',round(100*(1 - :phyrc/:gets),2) from dual union
select 'In-memory Sort %:',decode((:srtm+:srtd),0,to_number(null),round(100*:srtm/(:srtd+:srtm),2)) from dual union
select 'Library Hit %:',round(100*:lhtr,2) from dual union
select 'Soft Parse %:',round(100*(1-:hprs/:prse),2) from dual union
select 'Execute to Parse %:',round(100*(1-:prse/:exe),2) from dual union
select 'Latch Hit %:' ,round(100*(1-:lhr),2) from dual union
select 'Parse CPU to Parse Elapsd %:',decode(:prsela, 0, to_number(null),round(100*:prscpu/:prsela,2)) from dual union
select '% Non-Parse CPU:',decode(:tcpu, 0, to_number(null), round(100*(1-(:prscpu/:tcpu)),2)) from dual;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-450246/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 自動刪除過期的statspack統計資料
- statspack分析
- STATSPACK資料清除（二）
- STATSPACK資料清除（一）
- STATSPACK資料清除（三）
- 資料分析 | 用 SeaTable 表格快速統計分析訂單資料
- Oracle statspack綜合分析Oracle
- statspack report分析
- statspack 報告分析
- 資料統計分析 — 泊松分佈
- R——助力大資料統計與分析大資料
- 空間統計之點資料分析
- web統計資料蒐集及分析原理Web
- 對資料庫的統計和分析。資料庫
- statspack report分析 (zt)
- SciTech-Mathmatics-Probability+Statistics:Quantifing Uncertainty_統計資料分析: 多後設資料 + 多元統計分析AI
- Oracle統計資訊以及statspack怎麼使用Oracle
- Oracle 插入大量資料Oracle
- 使用Python分析大量資料應該學些什麼？Python
- MySQL大量資料插入各種方法效能分析與比較MySql
- Statspack報告分析—第一部分：資料庫資訊資料庫
- statspack報告分析摘錄
- Statspack分析報告說明
- 一篇statspack分析例子
- Statspack分析報告詳解
- Statspack之八-刪除歷史資料
- 資料統計分析的 16 個基礎概念
- Spark2 探索性資料統計分析Spark
- 網站資料統計分析相關入門網站
- Statspack分析報告詳解 (zt)
- Statspack分析報告詳解(1)
- Statspack分析報告詳解(2)
- Statspack分析報告詳解(3)
- Statspack分析報告詳解(4)
- Statspack分析報告詳解(轉)
- [譯] 使用 Pandas 對 Kaggle 資料集進行統計資料分析
- PHP匯入大量CSV資料PHP
- vage的delete大量資料方法delete