大量STATSPACK数据统计分析

最新推荐文章于 2021-04-11 22:45:57 发布

congnen9588

最新推荐文章于 2021-04-11 22:45:57 发布

阅读量278

点赞数

/*
对于大量数据库的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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/756652/viewspace-450246/