Long Running Query thru shell Script

Hello All, I need to write a shell script to find long running Query in Informix 11.70 . (We will upgrade with 14.10 in August 2021). As it includes three main steps 1. Search onstat -g act -g -x (x= 1, 2, 3, 4, ....)2. Check tcb column with Non ZERO value and run onstat -u | grep <Non ZERO value>o 3. Now get session id and run onstat -g ses <ID>But we can get multiple rows while running onstat -g act -r 1 ......So can someone please guide me how can I write script to find the exact session id which is causing the Performance. Thanks Amit ------------------------------ AMIT PATEL ------------------------------ #Informix

Hi Amit, I strongly guess you're not going to be happy with this approach. An easier one would be capturing a series of 'onstat -g ses 0' outputs while your problem is going on, then searching and comparig them for sessions that at more active than others, or for SQL that frequently shows up in active sessions. What qualifies as 'active' is up for debate: for sure any 'running' threads, but also 'ready' ones, esp. in cpu-bound situations, or 'IO Wait' or 'bufwait' ones in disk-i/o-bound situations. In more recent versions you could use 'onstat -g ses active' or a number of other, more specific filters for the same. BR, Andreas ------------------------------ Andreas Legner ------------------------------

I have written the following script to capture queries running more than a minute. echo "execute function task('set sql tracing on',1000,2,'low','global')"|dbaccess sysadmin >logs/slow_queries.out-${dt} while true; do echo "unload to logs/slow.unl select dbinfo('UTC_TO_DATETIME',sq.sql_finishtime),s.hostname,s.username,s.sid,sq.sql_statement,sq.sql_totaltime from syssqltrace sq ,syssessions s where s.sid=sq.sql_sid and sq.sql_runtime>60" |dbaccess sysmaster cat logs/slow.unl >>logs/slow_queries.out-${dt} sleep 1 done ------------------------------ Khurram Shahzad ------------------------------

Hi All, But I didn't get the expected result of the above SQL e.g echo " select dbinfo('UTC_TO_DATETIME',sq.sql_finishtime),sq.sql_totaltime,sq.sql_runtime from syssqltrace sq ,syssessions s where s.sid=sq.sql_sid --and sq.sql_runtime > 7" |dbaccess sysmaster 2023-01-31 15:23:39 5.9175e-05 5.9175e-05 2023-01-31 15:23:39 9.6405e-05 9.6405e-05 2023-01-31 15:23:39 7.0005e-05 7.0005e-05 2023-01-31 15:23:39 7.9875e-05 7.9875e-05 2023-01-31 15:23:39 0.000235935 0.000235935 2023-01-31 15:23:39 6.708e-05 6.708e-05 2023-01-31 15:23:39 8.0385e-05 8.0385e-05 2023-01-31 15:23:39 8.571e-05 8.571e-05 Mobis-SDS-1@mobis$echo " select dbinfo('UTC_TO_DATETIME',sq.sql_finishtime),sq.sql_totaltime,sq.sql_runtime from syssqltrace sq ,syssessions s where s.sid=sq.sql_sid and sq.sql_runtime > 5" |dbaccess sysmaster Database selected. (expression) sql_totaltime sql_runtime No rows found. Database closed. What would be the reason of this ? Pl. help me ! Thanks ! Best Regards, Indika ------------------------------ Indika Jinadasa ------------------------------

Indika: You have to have SQLTRACE enabled. Either the ONCONFIG parameter SQLTRACE has to be set, or you have to execute the API function that enables it dynamically: EXECUTE FUNCTION task("set sql tracing on",<number of trace buffers>, <buffer size in KB>,<trace level (low, medium, high)>, <who to trace (user or global)> ); EXECUTE FUNCTION task("set sql tracing on","1500","4","medium","global");See the Administrators Reference manual or online documentation (search for "SQL TRACE") for details. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hi Art, Thanks for the quick response. I have already enabled it. e.g echo "execute function task('set sql tracing on',1000,2,'low','global')"|dbaccess sysadmin Best Regards, Indika ------------------------------ Indika Jinadasa ------------------------------

Indika: That would happen if none of the sessions in the sqltrace buffers are still live sessions because you have joined syssqltrace to syssessions. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hi Art, Then what would be the best method to find the long running SQLs via SHELL script ? Thanks ! Best Regards, indika ------------------------------ Indika Jinadasa ------------------------------

Indika: Just ignore the active sessions and only query the SQLTRACE data alone: echo " select dbinfo('UTC_TO_DATETIME',sq.sql_finishtime) clock_time, sq.sql_totaltime, sq.sql_runtime from syssqltrace sq where sq.sql_runtime > 5 order by sq.sql_totaltime desc" |dbaccess sysmaster Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hi Indika, Have you checked the output of onstat -g ntt It will tell you SID and when the session was started and when it was last read/write. Regards, Gaurav ------------------------------ Gaurav Kumar ------------------------------

In case your intention is to determine long running queries in active sessions, so currently ongoing queries: as you might know (and not applicable here, but still interesting): since 14.10.xC6 "onstat -g ses <...>" (and "onstat -g sql <...>") would show 'Clock time elapsed' with any sessions currently executing any SQL. This time is the duration so far of the current SQL. What's available since 11.70.xC4, also in "onstat -g ses <...>", is an execution counter. That's the number in brackets in the "Current SQL statement ( # ) :" or "Current SQL statement ( # ) in procedure <db>:<udr>" line; it counts the executions of SQL statements and is shown only if the session is currently executing a statement, i.e. if onstat -g ses output contains that "Current SQL" block. This can be used comparing two such outputs for the same session: if both outputs have this block showing the same execution counter value (and same SQL statement), this means that this instance of SQL was the currently executed SQL at both times, so its execution must have started before first output and hasn't completed yet at second point in time. Conversely, if the same SQL statement is shown in two such outputs with differing execution counter, you could tell it's not this statement taking so long, but (this or any other SQL) statement being executed 'so frequently'. Now "long running" can mean very different things (and not necessarily a problem), besides "being a session's current SQL statement over a certain period of time": a super-frequent query could be considered long if taking a second or two (or even much shorter) some ad-hoc query for a certain purpose taking longer than expected a query for a monthly report having to go through vast amounts of data, thus taking its time And it can result from, or be accompanied by, a variety of things: consuming a lot of cpu -> you'd see the executing thread in 'running' state a lot (i.e. it would occur in onstat -g act) spending time with disk i/o -> you'd frequently see it in "IO Wait" state (or bufwait if IO is performed by read-ahead thread) frequent or long waits on client -> you'd see it in "cond wait netnorm" (for tcp connections) or "cond wait sm_read" (with ipcshm) other waits (on buffers, locks, other conditions, mutexes, cpu, ...) ... and of course all of these typically being immediate rather than root causes. So after identifying such long runners, you'd always have to have a closer look at thread states etc. over their duration (many rather than only two outputs will be useful.) Since such investigation typically involves wading through large "onstat -g ses 0" outputs, there's an enhancement, since 12.10.xC13: "onstat -g ses <state>" and "onstat -g sql <state>" (s. onstat usage) This allows selection of sessions (their thread(s)) in a given state (or collection of states), implicitly meaning "currently doing work", i.e. having a current SQL. -> best shot at "what is my system doing in this moment, SQL wise?" HTH, Andreas ------------------------------ Andreas Legner ------------------------------

Hi Gaurav, Thank you very much! Yes. That is another method to find the long running queries. Best Regards, Indika ------------------------------ Indika Jinadasa ------------------------------

Hi Art, Thank you very much! But I suppose " where sq.sql_runtime > 5 order by sq.sql_totaltime desc " is not working as we expected? What would be the reason for this? Best Regards, Indika ------------------------------ Indika Jinadasa ------------------------------