Re: Informix session VS 'oninit' CPU usage

(Sorry - I am reposting this as it is supposed to the reply for the currently last entry of the thread /R.) Thanx everybody for the feedback. Unfortunately I cannot use the SET EXPLAIN as Ben suggested since the executed statements are encapsulated inside an application that is executed by the users. However, I will try to mix what you suggested with other suggestions for the same kind of problem that I have found surfing around, the most interesting ones being the following: **** http://tinyurl.com/zarvm "First do onstat -u to get session ids then onstat -g ses <sessionid> to get threads. Finally do onstat -g ath to list threads and and look at the ones which have a status of running." **** http://tinyurl.com/k36no "You can see active threads with 'onstat -g act -r 2'. This will show you active threads every 2 seconds. If you will see same thread repeatedly, then you can use value of 'rstcb' column of previous output to catch session: onstat -u | grep value_of_rstcbThen you can get sql causing high CPU utilization with onstat -g ses session_id" **** http://tinyurl.com/jstww "The closest you'll get to it is with "onstat -g glo" which gives cpu by oninits (vps). I received the following statement a while ago which you can play with - DATABASE sysmaster; SELECT sid, SUM ( upf_isread ), SUM (upf_iswrite ), SUM ( upf_isrwrite ),SUM ( upf_bufreads ), SUM ( upf_bufwrites ), SUM ( upf_seqscans ), SUM ( nreads ), SUM (nwrites )FROM sysrstcb a WHERE sid > 0 GROUP BY sid ORDER BY ???? ; " **** http://tinyurl.com/fxe5a "Wait statistics tells you the time in microseconds that threads spent in several different run states: running, ready to run, waiting for buffers, etc. To get the time for state running, you can use the sql that roef...@ig.com.br just sent (I modified it a little): database sysmaster ;select username,hostname, syssessions.sid, sysopendb.odb_dbname[1,10], sum(cumtime)/1000000 cpu_time from syssessions, sysopendb, sysseswts where and syssessions.sid= sysopendb.odb_sessionid and sysopendb.odb_iscurrent = 'Y' and syssessions.sid=sysseswts.sid and reason='running' group by syssessions.sid,username,hostname,connected,sysopendb.odb_dbname -- having sum(cumtime)/1000000 > 1 order by 5 DESC; " Thanx again! R.