Re: nmon and Top Processes
I forgot...
include this delete at the end of the script .. or the table will grown
unnecessary.
delete from mon_users_cpu_cesar where id < (select next_seq - 50 fromtp_mon_cesar) ;
2013/3/19 Cesar Inacio Martins <cesar.inacio.martins@gmail.com>
> 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
> ;>
>
>
> 2013/3/19 <tomcaml@gmail.com>
>
>> When 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
>> Informix-list@iiug.org
>> http://www.iiug.org/mailman/listinfo/informix-list
>>
>
>