SQL Script Statspack Historical Memory Script

Updated: 02-Jun-2005
Version: 1.0

Description

Displays all Memory statistics for many statspack snapshots in single report.

This makes for easy trend analysis of statspack reports longer time periods which can then be imported into Spreadsheet for graphing

Parameters

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

--
-- Get the current database/instance information - this will be used 
-- later in the report along with bid, eid to lookup snapshots

column inst_num  heading "Inst Num"    new_value inst_num  format 99999;
column inst_name heading "Instance"  new_value inst_name format a10;
column db_name   heading "DB Name"   new_value db_name   format a10;
column dbid      heading "DB Id"     new_value dbid      format 9999999999 just c;


variable dbid       number;
variable inst_num   number;
variable inst_name  varchar2(20);
variable db_name    varchar2(20);
variable l_instance_num_def number;  

begin 

/* change the instance_num if you have many instances in the same perfstat schema (e.g. RAC/OPS) */

  :l_instance_num_def := 1;

  select dbid, instance_number, instance_name,db_name
  INTO :dbid, :inst_num, :inst_name, :db_name 
  FROM STATS$DATABASE_INSTANCE 
  WHERE instance_number = :l_instance_num_def ;

end;
/

print dbid
print db_name

--
--  Tablespace IO summary statistics

set pages 20000
set lines 200
set trimspool on
set term off
set echo off
set feed off

spool sprepmem.txt

column snap_id      	heading 'SnapId' format 9990;
column buffhit format 99,990.99 heading "Buf Hit Ratio";
column sortr   format 99,990.99 heading "In Mem Sort Ratio";
column softp   format 99,990.99 heading "Soft Parse Ratio";
col snapt      format a12         heading 'Snap Time'


select bp.snap_id 
     ,ep.snap_id  , to_char(es.snap_time,'dd Mon HH24:mi') snapt
     ,round(100*(1-(ep.value - bp.value)/(eg.value - bg.value)),2)     buffhit
     ,decode(((esm.value-bsm.value)+(esd.value-bsd.value)),0,to_number(null),
                         round(100*(esm.value-bsm.value)/((esd.value-bsd.value)+(esm.value-bsm.value)),2))  sortr
     ,  round(100*(1-(ehp.value-bhp.value)/(etp.value-btp.value)),2)     softp
  from (select sys.snap_id, sys.value 
	   from stats$sysstat sys
          where sys.dbid        = :dbid
            and sys.instance_number = :inst_num
            and sys.name = 'physical reads') bp
       ,(select sys.snap_id, sys.value 
	   from stats$sysstat sys
          where sys.dbid        = :dbid
            and sys.instance_number = :inst_num
            and sys.name = 'physical reads') ep
       ,(select sys.snap_id, sys.value 
	   from stats$sysstat sys
          where sys.dbid        = :dbid
            and sys.instance_number = :inst_num
            and sys.name = 'session logical reads') bg
       ,(select sys.snap_id, sys.value 
	   from stats$sysstat sys
          where sys.dbid        = :dbid
            and sys.instance_number = :inst_num
            and sys.name = 'session logical reads') eg
       ,(select sys.snap_id, sys.value 
	   from stats$sysstat sys
          where sys.dbid        = :dbid
            and sys.instance_number = :inst_num
            and sys.name = 'sorts (disk)') bsd
       ,(select sys.snap_id, sys.value 
	   from stats$sysstat sys
          where sys.dbid        = :dbid
            and sys.instance_number = :inst_num
            and sys.name = 'sorts (disk)') esd
       ,(select sys.snap_id, sys.value 
	   from stats$sysstat sys
          where sys.dbid        = :dbid
            and sys.instance_number = :inst_num
            and sys.name = 'sorts (memory)') bsm
       ,(select sys.snap_id, sys.value 
	   from stats$sysstat sys
          where sys.dbid        = :dbid
            and sys.instance_number = :inst_num
            and sys.name = 'sorts (memory)') esm
       ,(select sys.snap_id, sys.value 
	   from stats$sysstat sys
          where sys.dbid        = :dbid
            and sys.instance_number = :inst_num
            and sys.name = 'parse count (hard)') bhp
       ,(select sys.snap_id, sys.value 
	   from stats$sysstat sys
          where sys.dbid        = :dbid
            and sys.instance_number = :inst_num
            and sys.name = 'parse count (hard)') ehp
       ,(select sys.snap_id, sys.value 
	   from stats$sysstat sys
          where sys.dbid        = :dbid
            and sys.instance_number = :inst_num
            and sys.name = 'parse count (total)') btp
       ,(select sys.snap_id, sys.value 
	   from stats$sysstat sys
          where sys.dbid        = :dbid
            and sys.instance_number = :inst_num
            and sys.name = 'parse count (total)') etp
     , stats$snapshot es
 where es.snap_id = ep.snap_id
   and ep.snap_id = bp.snap_id + 1
   and bg.snap_id = bp.snap_id
   and eg.snap_id = ep.snap_id
   and bsd.snap_id = bp.snap_id
   and esd.snap_id = ep.snap_id
   and bsm.snap_id = bp.snap_id
   and esm.snap_id = ep.snap_id
   and bhp.snap_id = bp.snap_id
   and ehp.snap_id = ep.snap_id
   and btp.snap_id = bp.snap_id
   and etp.snap_id = ep.snap_id
   and ep.value >= bp.value
   and (ep.value - bp.value) <= (eg.value - bg.value)
   and to_char(es.snap_time,'DY') NOT IN ('SAT','SUN')
 order by bp.snap_id;

spool off

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links