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:
max(value) - min(value) "overall_delta",
sum(decode(delta,0,0,1)) "#changes" -- count distinct
(with c as
(select &1 counter from dual)
from ( select /*+ ordered use_nl(t) */
LAG(VALUE) OVER (Partition BY NAME order by r.rn) prev
(select /*+ no_unnest */
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.