Dienstag, 25. November 2008

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.
Kommentar veröffentlichen