Informix session VS 'oninit' CPU usage

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.