SQL elapsed time over a small specific time window

This is a great quick script to identify those statements taking the most time over a small time window. It’s a good “911” type script. This will print those statements that have a total elapsed execution time of the percentage of time passed on the command line.

If you name the content of the file below sqldiff.py, you can call it as follows:

#this will print only those statements that run for at least 2.5 seconds during a five second window
sqldiff.py 5 50

The script content is below.

#!/usr/local/bin/python

import cx_Oracle, sys, time

con = cx_Oracle.connect("username/password@dbhost:dbport/dbservice")

cursor = con.cursor()
first = dict()
second = dict()
cursor.execute("""
select sql_id,sum(executions),sum(elapsed_time)
  from gv$sql where (lower(sql_text) like 'select%'
                  or lower(sql_text) like 'delete%'
                  or lower(sql_text) like 'update%'
                  or lower(sql_text) like 'merge%'
                  or lower(sql_text) like 'insert%')
  group by sql_id
""")
for r in cursor:
  first[r[0]] = str(r[1]) + "~" + str(r[2])
time.sleep(int(sys.argv[2]))
cursor.execute("select sql_id,sum(executions),sum(elapsed_time) from gv$sql group by sql_id")
for r in cursor:
  second[r[0]] = str(r[1]) + "~" + str(r[2])

for i in first:
  if i in second:
    tmp = first[i].split("~")
    tmp2 = second[i].split("~")
    if int(tmp2[1]) - int(tmp[1]) > int(int(sys.argv[2]) * 1000000 * (float(sys.argv[3]) / 100)):
      cursor.execute("select sql_fulltext from gv$sql where sql_id = :1 and rownum = 1",[i])
      for r in cursor:
        sql = r[0]
      print "SQL_ID".ljust(30,"."),i
      print "SQL_TEXT".ljust(30,"."),sql
      print "EXECUTIONS".ljust(30,"."),int(tmp2[0]) - int(tmp[0])
      avg = (int(tmp2[1]) - int(tmp[1]))/1000/(int(tmp2[0]) - int(tmp[0])) if int(tmp2[0]) - int(tmp[0]) > 0 else (int(tmp2[1]) - int(tmp[1]))/1000
      print "MILLIS_PER_EXEC".ljust(30,"."),avg
      print "-".ljust(80,"-")