Who are the most active users?

We are trying to identify which users and subsequently which processes are using the most resources at any given time. Which SMI tables can give me that information? I think we're looking for the read/write info from an onstat -u. I tried looking at the SMI tables but quickly got in over myhead time wise.

Merrill To get the most cpu consuming session do the following: 1. Add WSTATS 1 to onconfig 2. run the following sql from sysmaster select s.sid,username,hostname[1,12],tty[1,14],pid,sum(cumtime)/1000000cpu_time from sysseswts w, syssessions s where reason="running" and s.sid = w.sid group by s.sid,username,hostname,tty,pid order by cpu_time desc Uri Merrill, Do.... wrote: >We are trying to identify which users and subsequently which processes are >using the most resources at any given time. Which SMI tables can give me >that information? I think we're looking for the read/write info from an >onstat -u. I tried looking at the SMI tables but quickly got in over my>head time wise. > > > > > >

I think WSTATS and QSTATS are undocumented. Can anyone give a detailed description of these two parameters, alongwith performance implication of setting it. I want to investigate some rogue queries on our production box. Thanks. Ravi. ----- Original Message ----- From: "Uri Haham " <uri@comsoft.co.il> To: <ids@iiug.org> Sent: Wednesday, February 26, 2003 11:10 Subject: Re: Who are the most active users? [512] > Merrill > > To get the most cpu consuming session do the following: > 1. Add WSTATS 1 to onconfig > 2. run the following sql from sysmaster > select s.sid,username,hostname[1,12],tty[1,14],pid,sum(cumtime)/1000000> cpu_time > from sysseswts w, syssessions s > where reason="running" > and s.sid = w.sid > group by s.sid,username,hostname,tty,pid > order by cpu_time desc > > Uri > > > Merrill, Do.... wrote: > > >We are trying to identify which users and subsequently which processes are > >using the most resources at any given time. Which SMI tables can give me > >that information? I think we're looking for the read/write info from an > >onstat -u. I tried looking at the SMI tables but quickly got in over my> >head time wise. > > > > > > > > > > > > > > > >

You probably right it look like it does not work on NT Uri Merrill, Douglas D. wrote: >The instance has been restarted twice since I made the change and I still >get zero rows returned. Does this not functio on NT? > >-----Original Message----- >From: Uri Haham [mailto:uri@comsoft.co.il] >Sent: Thursday, February 27, 2003 1:26 AM >To: Merrill, Douglas D. >Subject: Re: Who are the most active users? [512] > > >Sorry I forgot to mansion it, but you have to restart the Informix >server after adding WSTATS to onconfig >You do not have to add QSTAT for this query > >Uri > >Merrill, Douglas D. wrote: > > > >>I followed your suggestion but the query returns zero rows. Do I also need >>QSTAT? By the way, I am running IDS 7.31.TC2 on a 4cpu Proliant NT4.0 box >>with 4gb of ram. Make a difference? >> >>-----Original Message----- >>From: Uri Haham [mailto:uri@comsoft.co.il] >>Sent: Wednesday, February 26, 2003 10:11 AM >>To: ids@iiug.org >>Subject: Re: Who are the most active users? [512] >> >> >>Merrill >> >>To get the most cpu consuming session do the following: >>1. Add WSTATS 1 to onconfig >>2. run the following sql from sysmaster >>select s.sid,username,hostname[1,12],tty[1,14],pid,sum(cumtime)/1000000>>cpu_time >> >> >>from sysseswts w, syssessions s > > >>where reason="running" >>and s.sid = w.sid >>group by s.sid,username,hostname,tty,pid >>order by cpu_time desc >> >>Uri >> >> >>Merrill, Do.... wrote: >> >> >> >> >> >>>We are trying to identify which users and subsequently which processes are >>>using the most resources at any given time. Which SMI tables can give me >>>that information? I think we're looking for the read/write info from an >>>onstat -u. I tried looking at the SMI tables but quickly got in over my>>>head time wise. >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >> >> >> >> >> >> >> > > > -- Uri Haham Professional Services Manager ComSoft Technologies Ltd. P.O.B 2016 Herzliya 46120 ISRAEL Main switchboard: +972-9-9598999 Main fax no: +972-9-9598980 Extension: +972-9-9598627 E-mail uri@comsoft.co.il URL www.comsoft.co.il

> Hi Uri, > > Your script is very usefull for us, but want to know 2 things. > It would be great if you could share some more light. > > 1) I believe that the cpu time is the cumulative time, is it possible > to look at the CPU time at any given time not the cumulative one. Yes you are right, if the SQL doesn't do group by then you get the cpu usage at the fraction the SQL run, But this is not interesting. If you run the query once, and then run it again after 1, 2 or more second and then subtract the result set of the first time from the second time it can be more interesting. If you decide to do this little script or SQL I will be glad to get it. > 2) Why you are dividing with 1000000. The measurement are in 1/1000000 of second, I wont to get the result in second. > 3) What exactly WSTATS does. WSTATS is originally been add to the server for the DB-Cokpit product. It tell the server to collect more data. I'm not sure what more beside sysseswts table. > > Thanks in advance. > Sushil.. Uri > >> ----- Original Message ----- >> From: "Uri Haham " <uri@comsoft.co.il> >> To: <ids@iiug.org> >> Sent: Wednesday, February 26, 2003 11:10 >> Subject: Re: Who are the most active users? [512] >> >> >> > Merrill >> > >> > To get the most cpu consuming session do the following: >> > 1. Add WSTATS 1 to onconfig >> > 2. run the following sql from sysmaster >> > select >> s.sid,username,hostname[1,12],tty[1,14],pid,sum(cumtime)/1000000 >> > cpu_time >> > from sysseswts w, syssessions s >> > where reason="running" >> > and s.sid = w.sid >> > group by s.sid,username,hostname,tty,pid >> > order by cpu_time desc >> > >> > Uri >> > >> > >> > Merrill, Do.... wrote: >> > >> > >We are trying to identify which users and subsequently which >> processes are >> > >using the most resources at any given time. Which SMI tables can >> give me >> > >that information? I think we're looking for the read/write info >> from an >> > >onstat -u. I tried looking at the SMI tables but quickly got in>> over my >> > >head time wise. >> > > >> > > >> > > >> > > >> > > >> > > >> > >> > >> > >> > >> > > > _________________________________________________________________ > Tired of spam? Get advanced junk mail protection with MSN 8. > http://join.msn.com/?page=features/junkmail > > > > -- Uri Haham Professional Services Manager ComSoft Technologies Ltd. P.O.B 2016 Herzliya 46120 ISRAEL Main switchboard: +972-9-9598999 Main fax no: +972-9-9598980 Extension: +972-9-9598627 E-mail uri@comsoft.co.il URL www.comsoft.co.il

Uri, Thanks for your response, good idea to get the result by substraction. I can complete the remaining part. Sushil... >From: "Uri Haham " <uri@comsoft.co.il> >To: ids@iiug.org >Subject: Re: Who are the most active users? [532] Date: Thu, 27 Feb 2003 >10:59:02 -0500 (EST) >Received: from mc9-f17.bay6.hotmail.com ([65.54.166.24]) by >mc9-s5.bay6.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Thu, 27 Feb >2003 08:17:56 -0800 >Received: from ace.iiug.org ([216.177.38.212]) by mc9-f17.bay6.hotmail.com >with Microsoft SMTPSVC(5.0.2195.5600); Thu, 27 Feb 2003 08:17:05 -0800 >Received: (from nobody@localhost)by ace.iiug.org (8.9.3/8.9.3) id >KAA28899;Thu, 27 Feb 2003 10:59:02 -0500 (EST) >X-Message-Info: dHZMQeBBv44lPE7o4B5bAg== >Message-Id: <200302271559.KAA28899@ace.iiug.org> >Apparently-To: forum.subscriber@iiug.org >Sender: forum.subscriber@iiug.org >Precedence: bulk >Return-Path: nobody@ace.iiug.org >X-OriginalArrivalTime: 27 Feb 2003 16:17:05.0727 (UTC) >FILETIME=[AB5F04F0:01C2DE7B] > > > Hi Uri, > > > > Your script is very usefull for us, but want to know 2 things. > > It would be great if you could share some more light. > > > > 1) I believe that the cpu time is the cumulative time, is it possible > > to look at the CPU time at any given time not the cumulative one. > >Yes you are right, if the SQL doesn't do group by then you get the cpu >usage at the fraction the SQL run, >But this is not interesting. >If you run the query once, and then run it again after 1, 2 or more >second and then subtract the result set of the first time from the >second time it can be more interesting. >If you decide to do this little script or SQL I will be glad to get it. > > > 2) Why you are dividing with 1000000. > >The measurement are in 1/1000000 of second, I wont to get the result in >second. > > > 3) What exactly WSTATS does. > >WSTATS is originally been add to the server for the DB-Cokpit product. >It tell the server to collect more data. >I'm not sure what more beside sysseswts table. > > > > > Thanks in advance. > > Sushil.. > >Uri > > > > >> ----- Original Message ----- > >> From: "Uri Haham " <uri@comsoft.co.il> > >> To: <ids@iiug.org> > >> Sent: Wednesday, February 26, 2003 11:10 > >> Subject: Re: Who are the most active users? [512] > >> > >> > >> > Merrill > >> > > >> > To get the most cpu consuming session do the following: > >> > 1. Add WSTATS 1 to onconfig > >> > 2. run the following sql from sysmaster > >> > select > >> s.sid,username,hostname[1,12],tty[1,14],pid,sum(cumtime)/1000000 > >> > cpu_time > >> > from sysseswts w, syssessions s > >> > where reason="running" > >> > and s.sid = w.sid > >> > group by s.sid,username,hostname,tty,pid > >> > order by cpu_time desc > >> > > >> > Uri > >> > > >> > > >> > Merrill, Do.... wrote: > >> > > >> > >We are trying to identify which users and subsequently which > >> processes are > >> > >using the most resources at any given time. Which SMI tables can > >> give me > >> > >that information? I think we're looking for the read/write info > >> from an > >> > >onstat -u. I tried looking at the SMI tables but quickly got in> >> over my > >> > >head time wise. > >> > > > >> > > > >> > > > >> > > > >> > > > >> > > > >> > > >> > > >> > > >> > > >> > > > > > > _________________________________________________________________ > > Tired of spam? Get advanced junk mail protection with MSN 8. > > http://join.msn.com/?page=features/junkmail > > > > > > > > > >-- >Uri Haham >Professional Services Manager >ComSoft Technologies Ltd. >P.O.B 2016 >Herzliya 46120 ISRAEL >Main switchboard: +972-9-9598999 >Main fax no: +972-9-9598980 >Extension: +972-9-9598627 >E-mail uri@comsoft.co.il >URL www.comsoft.co.il > > > > > _________________________________________________________________ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus