Hi Gurus, a question for you. And sorry in advance if it has been already
answered: I cannot find any previous reference to what I am looking for.

I use Informix IDS 9.40.UC6 on Sun Solaris 9.

Looking at the OS statstics, 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.

Is there any (possibly scriptable) way to associate to each Informix running
session the amount of CPU that is being used by it?

The scope is to isolate the SQL statements that cause the high CPU usage and
might need optimization.

Thanx in advance,
Rupan3rd (Italy)

On 04/04/2006 01:48 PM, Rupan3rd wrote:
> Hi Gurus, a question for you. And sorry in advance if it has been already
> answered: I cannot find any previous reference to what I am looking for.
>
> I use Informix IDS 9.40.UC6 on Sun Solaris 9.
>
> Looking at the OS statstics, 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.
>
> Is there any (possibly scriptable) way to associate to each Informix running
> session the amount of CPU that is being used by it?

Unfortunately, NO!

Check http://tinyurl.com/qtw2j

>
> The scope is to isolate the SQL statements that cause the high CPU usage and
> might need optimization.
>
> Thanx in advance,
> Rupan3rd (Italy)
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
>

Best regards,

--
Mladen Jovanovski

Phone: +389 2 244 1140
Mobile: +389 75 400 309

COSMOFON - Mobile Telecommunications Services - A.D. Skopje
__________________________________________________ _____________
This e-mail (including any attachments) is confidential and may be protected by legal privilege. If you are not the intended recipient, you should not copy it, re-transmit it, use it or disclose its contents, but should return it to the sender immediately and delete your copy from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. COSMOFON A.D. Skopje shall not be liable for the improper or incomplete transmission of the information contained in this communication nor for any delay in its receipt or damage to your system.

Rupan3rd wrote:

> The scope is to isolate the SQL statements that cause the high CPU
> usage and might need optimization.

As you can't do this directly, why don't you run your scripts with "SET
EXPLAIN ON;" set and then look in $HOMEDIR/sqlexplain.out for the
resulting query plan and estimated costs? $HOMEDIR is the home directory
of the user you connect to the database using.

Ben.

NOTE: I have just done the Informix Internals course

You could set

WSTATS 1

in the onconfig file

and run onstat -g wst and look at the run time per thread. Of course
all this getting
the current time every time a thread moves from the run queue to the
ready or wait or sleep
queues and back again can causes a performance decrease that can be
large...

David.

Thanx everybody for the feedback. Unfortunately I cannot use the SET EXPLAIN
as Ben suggested since the executed statements are encapsulated inside an
application that is executed by the users.

However, I will try to mix what you suggested with other suggestions for the
same kind of problem that I have found surfing around, the most interesting
ones being the following:
**** http://tinyurl.com/zarvm
"First 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."

**** http://tinyurl.com/k36no
"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"

**** http://tinyurl.com/jstww
"The closest you'll get to it is with "onstat -g glo" which gives cpu by
oninits (vps). I received the following statement a while ago which you can play
with -
DATABASE sysmaster;
SELECT sid, SUM ( upf_isread ), SUM (upf_iswrite ), SUM ( upf_isrwrite ),
SUM ( upf_bufreads ), SUM ( upf_bufwrites ), SUM ( upf_seqscans ), SUM ( nreads
), SUM (nwrites )FROM sysrstcb a WHERE sid > 0 GROUP BY sid ORDER BY ???? ; "

**** http://tinyurl.com/fxe5a
"Wait statistics tells you the time in microseconds that threads spent in
several different run states: running, ready to run, waiting for buffers, etc.
To get the time for state running, you can use the sql that roef...@ig.com.br
just sent (I modified it a little):
database sysmaster ;
select
username,hostname,
syssessions.sid, sysopendb.odb_dbname[1,10],
sum(cumtime)/1000000 cpu_time
from syssessions, sysopendb, sysseswts
where
and syssessions.sid= sysopendb.odb_sessionid
and sysopendb.odb_iscurrent = 'Y'
and syssessions.sid=sysseswts.sid
and reason='running'
group by
syssessions.sid,username,hostname,connected,sysope ndb.odb_dbname
-- having sum(cumtime)/1000000 > 1
order by 5 DESC; "


Thanx again!
R.

(Sorry - I am reposting this as it is supposed to the reply for the currently
last entry of the thread /R.)

Thanx everybody for the feedback. Unfortunately I cannot use the SET EXPLAIN
as Ben suggested since the executed statements are encapsulated inside an
application that is executed by the users.

However, I will try to mix what you suggested with other suggestions for the
same kind of problem that I have found surfing around, the most interesting
ones being the following:
**** http://tinyurl.com/zarvm
"First 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."

**** http://tinyurl.com/k36no
"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"

**** http://tinyurl.com/jstww
"The closest you'll get to it is with "onstat -g glo" which gives cpu by
oninits (vps). I received the following statement a while ago which you can play
with -
DATABASE sysmaster;
SELECT sid, SUM ( upf_isread ), SUM (upf_iswrite ), SUM ( upf_isrwrite ),
SUM ( upf_bufreads ), SUM ( upf_bufwrites ), SUM ( upf_seqscans ), SUM ( nreads
), SUM (nwrites )FROM sysrstcb a WHERE sid > 0 GROUP BY sid ORDER BY ???? ; "

**** http://tinyurl.com/fxe5a
"Wait statistics tells you the time in microseconds that threads spent in
several different run states: running, ready to run, waiting for buffers, etc.
To get the time for state running, you can use the sql that roef...@ig.com.br
just sent (I modified it a little):
database sysmaster ;
select
username,hostname,
syssessions.sid, sysopendb.odb_dbname[1,10],
sum(cumtime)/1000000 cpu_time
from syssessions, sysopendb, sysseswts
where
and syssessions.sid= sysopendb.odb_sessionid
and sysopendb.odb_iscurrent = 'Y'
and syssessions.sid=sysseswts.sid
and reason='running'
group by
syssessions.sid,username,hostname,connected,sysope ndb.odb_dbname
-- having sum(cumtime)/1000000 > 1
order by 5 DESC; "


Thanx again!
R.

Rupan3rd wrote:
> Thanx everybody for the feedback. Unfortunately I cannot use the SET
> EXPLAIN
> as Ben suggested since the executed statements are encapsulated inside an
> application that is executed by the users.

If the SQL statement is taking a significant amount of time to run you
may be able to capture the statement by identifying a session number
(sid) from "onstat -g ses" and using "onstat -g ses <sid>" or "onstat -g
sql <sid>" to print it to screen. Copy and paste into SQL Editor or
similar and run the query with SET EXPLAIN ON set, replacing any
placeholders (question marks) with the actual values.

I hope this helps, Ben.

> Rupan3rd wrote:
> > Thanx everybody for the feedback. Unfortunately I cannot
> use the SET
> > EXPLAIN
> > as Ben suggested since the executed statements are
> encapsulated inside an
> > application that is executed by the users.
>
> If the SQL statement is taking a significant amount of time
> to run you
> may be able to capture the statement by identifying a session number
> (sid) from "onstat -g ses" and using "onstat -g ses <sid>" or
> "onstat -g
> sql <sid>" to print it to screen. Copy and paste into SQL Editor or
> similar and run the query with SET EXPLAIN ON set, replacing any
> placeholders (question marks) with the actual values.
>
> I hope this helps, Ben.


If you are on the later engines you can just turn sqlexplain via onmode
if you can identify the session

Cheers
Paul

Paul Watson
Tel: +44 1414161772
Mob: +44 7818003457

GO FURTHER with DB2
GET THERE FASTER with Informix.
Attend the IDUG 2006 North America Conference.
Tampa, Florida, USA. 7-11 May 2006.
Visit http://www.iiug.org/conf for more information.

Paul Watson wrote:
> If you are on the later engines you can just turn sqlexplain via onmode
> if you can identify the session
>
> Cheers
> Paul

Thanks Paul, good hint.

For the record, I have discovered this can be done using
onmode -Y <session_id> 1
Results will be in the home directory of the owner (executor) of the the
application.

Cheers
R.

One more thing: you can use onperf to monitor session activity, it could
help you a little.

"Rupan3rd" <rupan.nospam.3rd@hotmail.com> wrote in message
news:n2tYf.49175$nz4.46626@tornado.fastwebnet.it.. .
> Hi Gurus, a question for you. And sorry in advance if it has been already
> answered: I cannot find any previous reference to what I am looking for.
>
> I use Informix IDS 9.40.UC6 on Sun Solaris 9.
>
> Looking at the OS statstics, 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.
>
> Is there any (possibly scriptable) way to associate to each Informix
> running
> session the amount of CPU that is being used by it?
>
> The scope is to isolate the SQL statements that cause the high CPU usage
> and
> might need optimization.
>
> Thanx in advance,
> Rupan3rd (Italy)
>