Expand all | Collapse all

Slow Running Query

  • 1.  Slow Running Query

    Posted Mon February 22, 2021 11:09 PM
    Good morning All,

      Kindly le t me know , if I run "onstat -g act -r 2" and get multiple rows with some RSTCB values , so how can we check which SQL causing to run slow query.

    Because it will be tough to check which RSTCB value is constant among  multiple rows. 

    Amit Patel


  • 2.  RE: Slow Running Query

    Posted Tue February 23, 2021 03:19 AM

    If like multiple rows , then how can be checked which thread id we need to compare ?

    Amit P


  • 3.  RE: Slow Running Query

    Posted Tue February 23, 2021 05:37 AM
    Instead of using -r2 run the onstat in a loop with a sleep and write each output to a different file. Then you can diff the files to find the long runners:

    while [ $iter -lt 10 ]; do
      onstat -g act >act.$iter.out
      sleep 2
      iter=$(( iter + 1 ))

    You could also enable SQLTRACE and poll the data from there periodically to determine long running queries. Also there are 3rd part applications that will track that for you: iWatch and SQL PowerTools

    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.