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;