SQL Script

Statspack Historical File statistics Script

Updated: 02-Jun-2005
Version: 1.0

Description

Displays all File 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 sprepfile.txt

column snap_id      	heading 'SnapId' format 9990;
col tbsp       format a16         heading 'Tablespace'
col reads      format 99,999,990  heading 'Reads'
col atpr       format 990.0       heading 'Avg Read (ms)' just c;
col writes     format 99,999,990  heading 'Writes'
col waits      format 9,999,990   heading 'Total|Waits'
col atpwt      format 990.0       heading 'Avg Wait (ms)' just c;
col snapt      format a12         heading 'Snap Time'

select b.snap_id 
     ,e.snap_id  , to_char(es.snap_time,'dd Mon HH24:mi') snapt
     ,e.tsname                                             tbsp
     , sum (e.phyrds - nvl(b.phyrds,0))                     reads
     , decode( sum(e.phyrds - nvl(b.phyrds,0))
             , 0, 0
             , (sum(e.readtim - nvl(b.readtim,0)) /
                sum(e.phyrds  - nvl(b.phyrds,0)))*10)       atpr
     , sum (e.phywrts    - nvl(b.phywrts,0))                writes
     , sum (e.wait_count - nvl(b.wait_count,0))             waits
     , decode (sum(e.wait_count - nvl(b.wait_count, 0))
            , 0, 0
            , (sum(e.time       - nvl(b.time,0)) / 
               sum(e.wait_count - nvl(b.wait_count,0)))*10) atpwt
  from (select * 
        from stats$filestatxs
        where tsname NOT IN ('SYSTEM','RBS','TEMP','TOOLS','USERS') ) e
      ,(select * 
        from stats$filestatxs
        where tsname NOT IN ('SYSTEM','RBS','TEMP','TOOLS','USERS') ) b
     , stats$snapshot es
 where b.dbid(+)            = :dbid
   and e.dbid               = :dbid
   and b.dbid(+)            = e.dbid
   and b.instance_number(+) = :inst_num
   and e.instance_number    = :inst_num
   and b.instance_number(+) = e.instance_number
   and e.tsname             = b.tsname(+)
   and e.filename           = b.filename(+)
   and es.dbid = e.dbid
   and es.instance_number = e.instance_number
   and es.snap_id = e.snap_id
   and e.snap_id = b.snap_id + 1
   and e.phyrds - b.phyrds > 1000
   and to_char(es.snap_time,'DY') NOT IN ('SAT','SUN') 
 group by b.snap_id 
     ,e.snap_id  , to_char(es.snap_time,'dd Mon HH24:mi')
     ,e.tsname 
 order by b.snap_id, e.tsname
/

spool off

Return to Index of SQL Scripts

Site Map

SQL Script

Statspack Historical File statistics Script

Updated: 02-Jun-2005Version: 1.0

Description

Parameters

SQL Source

Site Map

Updated: 02-Jun-2005
Version: 1.0