clickbox

Informix: find session and query consuming high memory

This tutorial will help you to identify the high memory utilization session in Informix.

Informix-memory-usedby-sesssion

Sometimes we faced a slowness on Informix instance due to high memory utilization and need to trace the session detail for the same.

Informix onstat monitoring command helps us to identify the high memory utilization sessions in Informix.

A simple approach to run onstat -g ses command on Informix instance to get the information for memory utilization.

From above command’s output we will get used memory field in 8th column. Which we can used to trace the session id. Using that session id we can get the sql running from that session.

The output looks as follows: Where session id, total memory and used memory highlighted.

 

Informix@server1:[/home/informix]->> onstat -g ses

IBM Informix Dynamic Server Version 11.50.UC4     — On-Line — Up 72 days 05:30:29 — 3438592 Kbytes

session                                      #RSAM    total      used       dynamic
id       user     tty      pid      hostname threads  memory     memory     explain
1737675  informix –        0        –        0        12288      8496       off
1737670  informix –        0        –        0        12288      8496       off
1737661  sachin      –        1787     server 1        98304      72704      off
1735654  sachin      –        20147    server 1        102400     91648      off
1734346  sachin      –        27718    server 1        217088     148656     off
1734332  sachin      –        27099    server 1        229376     183720     off
1686268  sachin      –        14636    server 1        237568     200760     off
1686267  sachin      –        14635    server 1        237568     169928     off

 

For shorting in descending order by used memory, I generally used the short command with onstat command to get information faster as describe below.

>onstat -g ses|sort -rnk8

Above onstat -g ses command used to short the output in reverse order (r) based on numeric value(n) for column8 (k).

Now output will look like: Highest memory utilized session will be on top.

 

Informix@server1:[/home/informix]->> onstat -g ses |sort -rnk8

IBM Informix Dynamic Server Version 11.50.UC4     — On-Line — Up 72 days 05:30:29 — 3438592 Kbytes

session                                      #RSAM    total      used       dynamic
id       user     tty      pid      hostname threads  memory     memory     explain

1686268  sachin      –        14636    server 1        237568     200760     off
1686267  sachin      –        14635    server 1        237568     169928     off

1734332  sachin      –        27099    server 1        229376     183720     off

1734346  sachin      –        27718    server 1        217088     148656     off

1735654  sachin      –        20147    server 1        102400     91648      off

1737661  sachin      –        1787     server 1        98304      72704      off

1737675  informix –        0        –        0        12288      8496       off
1737670  informix –        0        –        0        12288      8496       off

 

From the above output check the session id which is using more memory and place it in onstat -g ses <sesson id> to identify the sql running from.

Eg:   onstat -g ses 1686268

Output will look like as below:

 

Informix@server1:[/home/informix]->> onstat -g ses

IBM Informix Dynamic Server Version 11.50.UC4     — On-Line — Up 72 days 05:48:08 — 3438592 Kbytes

session           effective                            #RSAM    total      used       dynamic
id       user     user      tty      pid      hostname threads  memory     memory     explain
1686268  sachin      –         –        14636    server 1        237568     161072     off

tid      name     rstcb    flags    curstk   status
1754793  sqlexec  685c0db8 Y–P—  3984     cond wait  sm_read   –

Memory pools    count 2
name         class addr     totalsize freesize #allocfrag #freefrag
1686268      V     6084b028 229376    69992    263        16
1686268*O0   V     699d9028 8192      6504     1          2

name           free       used           name           free       used
overhead       0          3376           scb            0          96
opentable      0          4856           filetable      0          1408
ru             0          464            blobio         0          5080
log            0          16512          temprec        0          8288
blob           0          264            keys           0          1640
ralloc         0          87352          gentcb         0          1240
ostcb          0          2488           sort           0          56
sqscb          0          16848          sql            0          40
rdahead        0          208            hashfiletab    0          280
osenv          0          2088           sqtcb          0          7648
fragman        0          840

sqscb info
scb      sqscb    optofc   pdqpriority sqlstats optcompind  directives
68d9a098 67f84018 0        0           0        0           1

Sess       SQL            Current            Iso Lock       SQL  ISAM F.E.
Id         Stmt type      Database           Lvl Mode       ERR  ERR  Vers  Explain
1686268    –              abc                CR  Wait       0    0    9.35  Off

Last parsed SQL statement :
INSERT INTO table_name VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? )

 

In above output insert query is running on table table_name.

Now we are able to tell which session and query creating problem or root cause of slowness.

 

 

 

Please follow and like us:
20

Written by 

Sachin Kumar is an IT professional, having a good experience of Linux, Windows, Oracle, Informix, Asterisk and VoIP. Email: clickboxsachinkumar@gmail.com

Enjoy this blog? Please spread the word :)