statspack queries

Statspack is very nice, the report you get with spreport usable but this can be made more valuable when you can follow the history of the database. A query like this one:

select snap_id, db_name, instance_name, snap_time,item,current_value, 
            current_value - prev_value delta, 
            round((snap_time - prev_time) * 24 * 60 * 60) secs, 
            round( (current_value - prev_value) / 
                        ((snap_time - prev_time) * 24 * 60 * 60)) per_sec
from
(
select s.snap_id, s.snap_time, i.db_name, i.instance_name, s.startup_time, 
            d.db_block_gets current_value,  'db_block_gets' item,
            -- if prev startup_time = current_startup_time then 
            --       get prev snap_time 
            -- else use start_time as prev_time
            decode (s.startup_time - lag(s.startup_time,1,s.startup_time) 
                                                         over (partition by s.dbid, s.instance_number order by s.snap_id),0,
                            lag(s.snap_time,1,s.startup_time) 
                            over (partition by s.dbid, s.instance_number order by s.snap_id),
                            s.startup_time) prev_time,
            -- if prev startup_time == current_startup_time then 
            --          get prev value 
            -- else just 0 as prev value
            decode (s.startup_time - lag(s.startup_time,1,s.startup_time) 
                                                         over (partition by s.dbid, s.instance_number order by s.snap_id),0,
                            lag(d.db_block_gets,1,0) 
                            over (partition by d.dbid, d.instance_number order by d.snap_id),
                            0) prev_value
    FROM  perfstat.stats$database_instance      i
    ,             perfstat.stats$snapshot                       s
    ,             perfstat.stats$buffer_pool_statistics d 
    WHERE  i.dbid = s.dbid
    and          i.dbid = d.dbid
    and          i.instance_number = s.instance_number
    and          i.instance_number = d.instance_number
    and          i.startup_time          = s.startup_time
    and          s.snap_id = d.snap_id
order by s.dbid, s.instance_number, s.snap_id
)
/

can do the trick for db_block_gets. I always knew there had to be some usefull use for the analytical options ;-)
There is a whole lot more in the perfstat schema. Go and take a look.