大量STATSPACK資料統計分析

zhouwf0726發表於2019-05-20

/*
對於大量資料庫的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統計資料
2019-07-20
statspack分析
2008-05-29
STATSPACK資料清除（二）
2008-07-14
STATSPACK資料清除（一）
2008-07-10
STATSPACK資料清除（三）
2011-12-10
資料分析 | 用 SeaTable 表格快速統計分析訂單資料
2021-10-16
Oracle statspack綜合分析
2014-09-05
Oracle
statspack report分析
2009-03-25
statspack 報告分析
2013-03-18
資料統計分析 — 泊松分佈
2023-10-30
R——助力大資料統計與分析
2012-09-29
大資料
空間統計之點資料分析
2016-03-02
web統計資料蒐集及分析原理
2016-03-04
Web
對資料庫的統計和分析。
2007-09-02
資料庫
statspack report分析 (zt)
2010-01-19
Oracle 插入大量資料
2013-03-31
Oracle
Oracle統計資訊以及statspack怎麼使用
2011-03-26
Oracle
使用Python分析大量資料應該學些什麼？
2022-01-23
Python
MySQL大量資料插入各種方法效能分析與比較
2015-04-02
MySql
Statspack報告分析—第一部分：資料庫資訊
2016-01-24
資料庫
statspack報告分析摘錄
2008-06-24
Statspack分析報告說明
2010-05-11
一篇statspack分析例子
2007-02-04
Statspack分析報告詳解
2007-11-26
Statspack之八-刪除歷史資料
2007-12-27
資料統計分析的 16 個基礎概念
2022-09-14
Spark2 探索性資料統計分析
2016-12-02
Spark
網站資料統計分析相關入門
2017-07-22
網站
[譯] 使用 Pandas 對 Kaggle 資料集進行統計資料分析
2018-11-12
Statspack分析報告詳解 (zt)
2008-05-12
Statspack分析報告詳解(1)
2009-02-21
Statspack分析報告詳解(2)
2009-02-21
Statspack分析報告詳解(3)
2009-02-21
Statspack分析報告詳解(4)
2009-02-21
Statspack分析報告詳解(轉)
2007-08-17
PHP匯入大量CSV資料
2022-02-17
PHP
vage的delete大量資料方法
2013-01-06
delete
大量刪除資料的速度
2011-02-09

大量STATSPACK資料統計分析

相關文章