View thread info?

Hi Experts, We use an informix database as database layer for an ERP application. We encounter high CPU usage of the informix server while the ERP application is hardly used (low number of active users). The high CPU load always starts at the same timepoint in the morning and ends always at the same timepoint in the evening, suggesting a scheduled action. However, the ERP application support team cannot find anything from within the application. Therefor I would like to find more information on the database level. We have configured a system with 2 CPU vp's. When looking at the active threads I see the CPU vp's to be constantly busy: $ onstat -g act IBM Informix Dynamic Server Version 10.00.FC6 -- On-Line -- Up 11 days 05:43:52 -- 1033008 Kbytes Running threads: tid tcb rstcb prty status vp-class name 8 700000040e44538 0 2 running 1cpu sm_poll 9 700000040e731d8 0 2 running 9soc soctcppoll 71276 7000000474fa028 700000043135820 2 running 3cpu sqlexec The 1cpu vp is switching between sm_poll and sqlexec. The 3cpu is always doing sqlexec and is causing the major part of the system load. When running onstat -g rea I see no queueing of threads. Is there any way in which I can find out what the cpu vp is doing for each thread? e.g. Can I revert a thread id to a session to find out what SQL is being ran by which user? I will try to stop the ERP application to see if the CPU usage lowers. If not, could it be something Informix internal? Does anyone have more suggestions on how to approach and investigate such issues? Thanks, Regards, Bart.

Hi Bart, I am not an expert. I have started to learn IDS and i am working on it for my bread. We can trace the session id of a particular thread to know what it was doing. onstat -g act would give you the list of active threads.To investigate a thread just use the thread address in the following command. onstat -u | grep thread_addressthis will display the session id of the user in its ouput, which you can use it for further investigation. onstat -g sql session_id (hope will help you). Further, onstat -g ses session_id would give you more information such as pid. Revealing that server become busy at a certain time frame, it may be due to some scheduled batch jobs running at that time. Hope for the experts to comment on this issue. Thanks, Shanoof Basheer India

Hi, It may be helpful to turn on dynamic explain. You can do that even under an ERP, you don't have to recompile with "set explain on", you can turn it on dynamically for a session. I don't have the exact syntax. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of SHANOOF BASHEER Sent: Monday, July 14, 2008 8:41 AM To: ids@iiug.org Subject: Re: View thread info? [12684] Hi Bart, I am not an expert. I have started to learn IDS and i am working on it for my bread. We can trace the session id of a particular thread to know what it was doing. onstat -g act would give you the list of active threads.To investigate a thread just use the thread address in the following command. onstat -u | grep thread_addressthis will display the session id of the user in its ouput, which you can use it for further investigation. onstat -g sql session_id (hope will help you). Further, onstat -g ses session_id would give you more information such as pid. Revealing that server become busy at a certain time frame, it may be due to some scheduled batch jobs running at that time. Hope for the experts to comment on this issue. Thanks, Shanoof Basheer India ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ============================================================ The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reproduction, dissemination or distribution of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Tellabs ============================================================

The table sysrstcb maps session id (sid) to the threads working for that session (tid). You can then also look in systhreads for more thread detail from the systhreads view or the underlying systcplst table. onstat -g rea shows only threads ready to run that are waiting for a VP torun them, not acctively running threads. Art On Mon, Jul 14, 2008 at 8:35 AM, BART GROOT <bart.groot@corusgroup.com> wrote: > Hi Experts, > > We use an informix database as database layer for an ERP application. We > encounter high CPU usage of the informix server while the ERP application > is > hardly used (low number of active users). The high CPU load always starts > at > the same timepoint in the morning and ends always at the same timepoint in > the > evening, suggesting a scheduled action. However, the ERP application > support > team cannot find anything from within the application. Therefor I would > like > to find more information on the database level. > > We have configured a system with 2 CPU vp's. When looking at the active > threads I see the CPU vp's to be constantly busy: > > $ onstat -g act> > IBM Informix Dynamic Server Version 10.00.FC6 -- On-Line -- Up 11 days > 05:43:52 -- 1033008 Kbytes> > Running threads: > tid tcb rstcb prty status vp-class name > 8 700000040e44538 0 2 running 1cpu sm_poll > 9 700000040e731d8 0 2 running 9soc soctcppoll > 71276 7000000474fa028 700000043135820 2 running 3cpu sqlexec > > The 1cpu vp is switching between sm_poll and sqlexec. The 3cpu is always > doing > sqlexec and is causing the major part of the system load. > > When running onstat -g rea I see no queueing of threads. > > Is there any way in which I can find out what the cpu vp is doing for each > thread? e.g. Can I revert a thread id to a session to find out what SQL is > being ran by which user? > > I will try to stop the ERP application to see if the CPU usage lowers. If > not, > could it be something Informix internal? > > Does anyone have more suggestions on how to approach and investigate such > issues? > > Thanks, > > Regards, > Bart. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Hi All, Thanks for your replies. I managed to identify the process that caused the problem. 1. Use onstat -g act to look at active threads 2. Perform onstat -u | grep <rstcb value of active thread in step 1> -> get session id. 3. Perform onstat -g ses <session number from step 2.) It turned out that the particular session continously fired off an expensive SQL. Thanks for your help. Regards, Bart.

Great. Thats what i meant in my previous post. Thanks, Shanoof