大量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/，如需轉載，請註明出處，否則將追究法律責任。