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.<\/p>\n
If you name the content of the file below sqldiff.py, you can call it as follows:<\/p>\n
\r\n#this will print only those statements that run for at least 2.5 seconds during a five second window\r\nsqldiff.py 5 50\r\n<\/pre>\nThe script content is below.<\/p>\n
\r\n#!\/usr\/local\/bin\/python\r\n\r\nimport cx_Oracle, sys, time\r\n\r\ncon = cx_Oracle.connect(\"username\/password@dbhost:dbport\/dbservice\")\r\n\r\ncursor = con.cursor()\r\nfirst = dict()\r\nsecond = dict()\r\ncursor.execute(\"\"\"\r\nselect sql_id,sum(executions),sum(elapsed_time)\r\n from gv$sql where (lower(sql_text) like 'select%'\r\n or lower(sql_text) like 'delete%'\r\n or lower(sql_text) like 'update%'\r\n or lower(sql_text) like 'merge%'\r\n or lower(sql_text) like 'insert%')\r\n group by sql_id\r\n\"\"\")\r\nfor r in cursor:\r\n first[r[0]] = str(r[1]) + \"~\" + str(r[2])\r\ntime.sleep(int(sys.argv[2]))\r\ncursor.execute(\"select sql_id,sum(executions),sum(elapsed_time) from gv$sql group by sql_id\")\r\nfor r in cursor:\r\n second[r[0]] = str(r[1]) + \"~\" + str(r[2])\r\n\r\nfor i in first:\r\n if i in second:\r\n tmp = first[i].split(\"~\")\r\n tmp2 = second[i].split(\"~\")\r\n if int(tmp2[1]) - int(tmp[1]) > int(int(sys.argv[2]) * 1000000 * (float(sys.argv[3]) \/ 100)):\r\n cursor.execute(\"select sql_fulltext from gv$sql where sql_id = :1 and rownum = 1\",[i])\r\n for r in cursor:\r\n sql = r[0]\r\n print \"SQL_ID\".ljust(30,\".\"),i\r\n print \"SQL_TEXT\".ljust(30,\".\"),sql\r\n print \"EXECUTIONS\".ljust(30,\".\"),int(tmp2[0]) - int(tmp[0])\r\n 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\r\n print \"MILLIS_PER_EXEC\".ljust(30,\".\"),avg\r\n print \"-\".ljust(80,\"-\")\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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…<\/p>\n