I use the cpu_time statistics to detect who is "eating" my CPUs....
This is the copy of the base SQLs from script I wrote .
The logic is simple... save each N seconds a "snapshot" of all sessions
statistics and show the difference between last two snapshot, sorting by
who have bigger consumption.
Maybe need adjust for you need...
-- first create this table...
create raw table mon_users_cpu_cesar (
id int
,usuario char(18)
,sesid int
,pid int
,cpu_time float
,last_run datetime year to second
,hora datetime year to second
,tx_nlocks integer { number of locks currently held}
,tx_logspuse integer { % log space currently used }
,tx_lgrecs integer { numbe of log records written }
,io_seqscans integer { number of sequential scans }
,io_waittime float { time spent waiting on disk io }
,io_nwaits integer { Number of disk IO waits }
,io_idxbufr integer { Number of index buffer reads }
,io_bufreads integer { ... buffer reads }
,io_bufwrites integer { ... buffer writes }
,io_nreads integer { number of reads (paginas) }
,io_nwrites integer { number of writes (paginas) }
,is_read integer { number of reads request }
,is_write integer { number of writes request }
,is_rwrite integer { number of rewrites request }
,is_delete integer { number of deletes request }
,is_commit integer { number of commits request }
,is_rollback integer { number of rollbacks request }
)
;
create index ix1_mon_users_cpu_cesar on mon_users_cpu_cesar (id, sesid,
usuario );
-- then, execute this commands each N seconds...
select max(nvl(id,0))+1 as next_seq
from mon_users_cpu_cesar
into temp tp_mon_cesar
with no log ;
insert into mon_users_cpu_cesar
select tp.next_seq
, s.username
, s.sid
, s.pid
, sum(t.cpu_time) as cpu_time
, max(dbinfo('utc_to_datetime', t.last_run_time)) as last_run
, current hour to second as hora
, sum(u.nlocks)
, sum(u.upf_logspuse)
, sum(u.upf_lgrecs)
, sum(u.upf_seqscans)
, sum(u.iowaittime)
, sum(u.upf_niowaits)
, sum(u.upf_idxbufreads)
, sum(u.upf_bufreads)
, sum(u.upf_bufwrites)
, sum(u.nreads)
, sum(u.nwrites)
, sum(u.upf_isread)
, sum(u.upf_iswrite)
, sum(u.upf_isrwrite)
, sum(u.upf_isdelete)
, sum(u.upf_iscommit)
, sum(u.upf_isrollback)
from sysmaster:syssessions s
, sysmaster:sysrstcb u --sysmaster:sysuserthreads u
, sysmaster:systcblst t
, tp_mon_cesar tp
where u.sid = s.sid
and u.tid = t.tid
group by 1
, 2
, 3
, 4
;
-- show the top 10 sessions with more CPU use on the last N seconds...
select first 10
m1.sesid
, m1.usuario
, (nvl(m1.cpu_time,0) - nvl(m2.cpu_time,0))::dec(15,2) as cpu_time1
-- , m1.last_run
, (m1.hora - m2.hora)::interval day to second as __tempo_exec
, (current -m1.last_run)::interval day to second as __tempo_idle
-- , m1.*
-- , m2.*
from mon_users_cpu_cesar m1
, outer (mon_users_cpu_cesar m2)
, tp_mon_cesar tp
where m1.id = tp.next_seq
and m2.id = (m1.id-1)
and m2.sesid = m1.sesid
and m2.usuario = m1.usuario
order by cpu_time1 desc
;
Post by t***@gmail.comWhen watching the top processes in nmon and what is driving oninit for
work, PID's are shown next to the %cpu used.
how can i relate those PID's back to sessions id's within the instance?
thanks,
tom
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list