Relating OS processes to online users

Does anyone know how to take a process from the output of top and relate it to a particular oninit process, so that I can perform an onstat -g ses and get who is running what sql? We are havingperformance issues as a result of very poorly written queries. I would like to be able to identify the oninit sessionid using the most CPU via the OS pid from top. Anyone have any luck doing this? Or is there a better way that has been proven successful? We are running Informix 7.30.UC9 on Solaris 2.6. Any information would be appreciated. Thanks, Spaff Sent via Deja.com http://www.deja.com/ Before you buy.

try onstat -g ses then try onstat -g ses <session_id_from_above_output> to get details about the session.But a single user could have multiple sesisons nevertheless you could track SQLS. The other options is connect to sysmaster database and check the syssqlstats table for details analysis of the query. Link the session id in this table to the user in the syssessions table. hth savio nspaff@my-deja.com wrote: > Does anyone know how to take a process from the output of top and > relate it to a particular oninit process, so that I can perform an > onstat -g ses and get who is running what sql? We are having> performance issues as a result of very poorly written queries. I would > like to be able to identify the oninit sessionid using the most CPU via > the OS pid from top. Anyone have any luck doing this? Or is there a > better way that has been proven successful? > > We are running Informix 7.30.UC9 on Solaris 2.6. Any information would > be appreciated. > > Thanks, > > Spaff > > Sent via Deja.com http://www.deja.com/ > Before you buy.

nspaff@my-deja.com wrote: > Does anyone know how to take a process from the output of top and > relate it to a particular oninit process, so that I can perform an > onstat -g ses and get who is running what sql? We are having> performance issues as a result of very poorly written queries. I would > like to be able to identify the oninit sessionid using the most CPU via > the OS pid from top. Anyone have any luck doing this? Or is there a > better way that has been proven successful? > oninit unix processes represent Virtual Processors (VPs). As such, they cannot be tied to a session - in fact, the relationship between oninit processes and sessions is very similar to that between Physical processors and Unix processes. If top tells you that oninit processes are doing a lot of work, its usually because of session requests. You need to determine active sessions, figure out what SQL they are requesting to execute, and determine if the SQL need to be optimized. One simple way of determining poor SQL is to pick a session that appears to active more often than not and start an "onstat -g sql <sessionId> -r 1 " on it. If the "Current SQL" is not changing, its probably because its taking a long time to execute - could be a candidate for optimization. What's an active session? Do an onstat -u. Sessions with the Y flag at the beginning of the flag string are usually inactive. Skip the "standard" informix/root sessions. That leaves you with the "active" sessions. All the best. Rudy P.S. You could also buy the Informix Handbook available at http://www.informixhandbook.com/ and refer to the chapter on Application Programming for more details.