Informix

 View Only
Expand all | Collapse all

Long Running Query thru shell Script

  • 1.  Long Running Query thru shell Script

    Posted Fri May 28, 2021 08:16 AM
    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


  • 2.  RE: Long Running Query thru shell Script

    IBM Champion
    Posted Fri May 28, 2021 11:05 AM
    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
    ------------------------------



  • 3.  RE: Long Running Query thru shell Script

    Posted Mon May 31, 2021 02:29 AM
    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
    ------------------------------



  • 4.  RE: Long Running Query thru shell Script

    Posted Tue January 31, 2023 04:58 AM
    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
    ------------------------------



  • 5.  RE: Long Running Query thru shell Script

    IBM Champion
    Posted Tue January 31, 2023 05:11 AM
    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
    ------------------------------



  • 6.  RE: Long Running Query thru shell Script

    Posted Tue January 31, 2023 05:18 AM
    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
    ------------------------------



  • 7.  RE: Long Running Query thru shell Script

    IBM Champion
    Posted Tue January 31, 2023 05:24 AM
    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
    ------------------------------



  • 8.  RE: Long Running Query thru shell Script

    Posted Tue January 31, 2023 05:35 AM
    Hi Art,

    Then what would be the best method to find the  long running SQLs via SHELL script ?

    Thanks !

    Best Regards,
    indika

    ------------------------------
    Indika Jinadasa
    ------------------------------



  • 9.  RE: Long Running Query thru shell Script

    IBM Champion
    Posted Tue January 31, 2023 08:01 AM
    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
    ------------------------------



  • 10.  RE: Long Running Query thru shell Script

    Posted Fri February 03, 2023 03:25 AM
    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
    ------------------------------