Re: Informix session VS 'oninit' CPU usage

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.

Rupan3rd wrote: > 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. If the SQL statement is taking a significant amount of time to run you may be able to capture the statement by identifying a session number (sid) from "onstat -g ses" and using "onstat -g ses <sid>" or "onstat -g sql <sid>" to print it to screen. Copy and paste into SQL Editor or similar and run the query with SET EXPLAIN ON set, replacing any placeholders (question marks) with the actual values. I hope this helps, Ben.

> Rupan3rd wrote: > > 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. > > If the SQL statement is taking a significant amount of time > to run you > may be able to capture the statement by identifying a session number > (sid) from "onstat -g ses" and using "onstat -g ses <sid>" or > "onstat -g > sql <sid>" to print it to screen. Copy and paste into SQL Editor or > similar and run the query with SET EXPLAIN ON set, replacing any > placeholders (question marks) with the actual values. > > I hope this helps, Ben. If you are on the later engines you can just turn sqlexplain via onmode if you can identify the session Cheers Paul Paul Watson Tel: +44 1414161772 Mob: +44 7818003457 GO FURTHER with DB2 GET THERE FASTER with Informix. Attend the IDUG 2006 North America Conference. Tampa, Florida, USA. 7-11 May 2006. Visit http://www.iiug.org/conf for more information.