sysstat_sample.sql

Based on Tanel Poders sample.sql and ses.sql I was interrested to get some informations about the System at all.
There are various discussions wether or not it is useful to have a look on the system when hunting a problem within a session. Nevertheless I decided I need such a tool. On the one hand to generate a 'good' baseline, on the other to have a quick, zentralised view. It's also of some use to compare the system-statistics to the values of one dedicated sessions to see if this session is the major reason for any changes or submit only a small amount, but might suffer from others in the system.

So here is the code:

select name,
  min(value)               "MIN",
  max(value)               "MAX",
  max(value) - min(value)  "overall_delta",
  max(delta)               "max_single_delta",
  sum(decode(delta,0,0,1)) "#changes"   -- count distinct 
from
  (with c as
     (select &1 counter from dual)
  select     rn,
 name,
 value,
     decode(rn,1,0,value-prev) delta  
from    ( select /*+ ordered use_nl(t) */
         r.rn,
 NAME,
 Value,
         LAG(VALUE) OVER (Partition BY NAME order by r.rn) prev
      from
        (select /*+ no_unnest */
 rownum rn,
 1 sample_dummy
          from dual
            connect by level <= (select counter from c)        ) r,        v$sysstat t      order by name, r.rn     )  ) syss
where   value     > 0
  and delta > 0
group by name
order by name
/

This piece of code samples through v$sysstat and generates for every entry (where the value > 0, to reduce the lines) a line with the minimal value, the maximal value, the delta between them (this could also be done by DBMS_LOCK.SLEEP) but also the greatest single step between 2 sequent samples.
There might also be other statistical functions of some interrest, but I have too little knowledge on statistics and how to use them (comments/suggestions welcome!).

The script can also be found here, where I will keep the most recent version all the time.