Re: Spooling all SQL Statements

In article <5kps14$jn0$2@gulfa.kuwait.net>, Rudy Fernandes <rferdy@kuwait.net> writes >In article <5ko05e$p9k@cssun.mathcs.emory.edu>, > lperson@mcny.com (Lou Person) wrote: >>We are sending sql statements to OWS from an >>application. Is there anyway to "spool" all sql >>statements which are sent to the server? Meaning, >>can we store all SQL statements, which OWS >>executed, to a table (or file) for subsequent review? >> >>Thanks, >> >>Lou Person > >You can consider using the onstat command to retrieve sql >statements which are active. Of course, you would only get >a snapshot. So you'd have to do it at intervals, which >means that you could miss some sql stmts. However, if the >idea is to identify stmts which are 'hoggers', this approach >may be feasible. Also, you may find the output of this >sampling approach easier to process, even though >some accuracy may be lost. > >'onstat -g sql session_id' will give you info. about the sql >stmts of a session. You could use the output of 'onstat -g sql' >(without session id) as the driver for 'onstat -g sql >session_id'. > >On the other hand, if you are after the 'hoggers', you could >use 'onstat -u' using the flags in that output as an indicator >of active sessions (if the first flag of a session is not 'Y', >there's a good chance that its a hogger), extract the session >ids and pipe them to onstat -g sql. Here's a possible script > >: >for SESSION in `onstat -u | \\ > # skip informix sessions > grep -v 'informix' | \\ > # skip non-active sessions (18th character 'Y') > grep -v '^.................Y' | \\ > # skip header information (19th character to be -) > grep '^..................-' | \\ > # extract session id > cut -b 26-34` >do > onstat -g sql $SESSION >> sqllog>done > >If you are want to track the session better, you could change >the 'sql' flag of onstat -g to 'ses' - you will get the prize >information of WHO is running the statement. However, the output >will be substantially bigger. > >Bye, > >----------------------- >Rudy Fernandes >GIC, Kuwait >OL 7.20UC4, 4GL 6.04UC1 >----------------------- I normally first do onstat -u and check if any processes have many more reads than any other process. This indicates if they are doing 'hogging' queries which are sequential scanning a table.. -- David Williams