So the last time I actually talked about something, versus just taking note of something. I was talking about what was available from a Memory profiler standpoint for sessions. But what about just seeing what each statement in an SQL is taking up from a baseline perspective? Well the good news is that Informix already has something that answers that question, and that tool is onstat -g stm.

So what do you see when you run onstat -g stm?

As you can see onstat -g stm has some very useful stuff, as well as some stuff that people like me (i.e. Support) care about.

The first thing you will notice is that this onstat breaks out the SQL statements per session.  That's right you could run onstat -g stm  as well to get the info for a specific session. 

Now looking at the columns you will see sdblock (useful for tech support guys like me in certain situations), heapsz  and statement.

The Statement is self explanatory, and the heap size is essentially the size of memory in bytes of you heap, which essentially breaks down to the bulk of the memory your SQL is taking up. 

Note there are a few gotchas. The biggest one is that it doesn't really drill down. So if you are running SPL, it tells you the size of the SPL, not each query inside it. 

All and all though a very nice command for tracking your sessions sql. 

Next time we will talk about looking at DB resources your SQL takes up using SQLTRACE, and syssqltrace.