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;

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

Informix Servers

Software version:

10.0, 11.1, 11.7

Operating system(s):

AIX, HP-UX, Linux, Solaris, Windows

Reference #:

1617223

Modified date:

2013-03-27

Translate my page

Machine Translation

Content navigation