Dienstag, 25. November 2008


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
(with c as
(select &1 counter from dual)
select rn,
decode(rn,1,0,value-prev) delta
from ( select /*+ ordered use_nl(t) */
LAG(VALUE) OVER (Partition BY NAME order by r.rn) prev
(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.


Unknown hat gesagt…

the url link about the script seems broken

Martin Berger hat gesagt…

thanks khair,
I've updated the links to tanels scripts.