How to isolate High CPU usage for Informix

Technote (FAQ)

Question

How to isolate High CPU usage for Informix?

Looking at the OS statistics, sometimes it can be observed that ‘oninit’ process(es) corresponding to the configured CPU-class virtual processors, use an high percentage of the server CPU
.
Such high CPU usage events are short peaks and not the normal condition.

Here are a few ways to associate to each Informix running session the amount of CPU that is being used by it.

Answer

• You can 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.
• 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_rstcb
• Then you can get sql causing high CPU utilization with “onstat -g ses session_id” You can use this sql to create a list of sessions sorted on the highest amount of cpu and including the sql the session is currently running ( note that the current sql might not be the one that used so much cpu ):

SELECT s.sid, s.username, s.uid, s.pid, s.hostname, t.tid,t.name, t.statedesc, t.statedetail, q.sqs_statement, t.cpu_time
FROM syssessions s, systcblst t, sysrstcb r, syssqlstat q
WHERE t.tid = r.tid AND s.sid = r.sid AND s.sid = q.sqs_sessionid
ORDER BY t.cpu_time desc;