Stop queries

Here you are shown how to find long running queries

and to kill them stopping them from using up database resources and

therefore allowing the database to respond quicker.

schema:scott

It is surprisingly easy to produce long running queries. They can be produced mainly through incorrectly written queries such as ones missing a JOIN function when it is needed. Luckily though Oracle, Postgres, MySQL and SQLServer have commands that allow the user to find and terminate these processes.

SELECT username, sid, serial#, TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI:SS')"CURRENT",
       TO_CHAR(logon_time,'YYYY-MM-DD HH24:MI:SS')"LOGON",
       (sysdate - logon_time)*24*60"MINS"
  FROM V$SESSION
  WHERE (sysdate - logon_time)*24*60 > 1
    AND username is not NULL

SHOW PROCESSLIST

USE master
SELECT spid, nt_username, 
       datediff(s, logon_time, GETDATE())
  FROM sysprocesses

SELECT datid, datname, usename, procpid,
       backend_start
  FROM pg_stat_activity

To kill a session in Oracle use: ALTER SYSTEM KILL SESSION 'sid,serial'

To terminate a session in MySQL use: KILL 'Id'

To kill a process in sqlserver use: EXEC ('kill Spid')

To kill a session in Postgres use: # kill -INT 'procpid'

SQL HACKS

Hack 10 Converting subqueries into joins

Hack 11 Converting aggregate subqueries into joins

Hack 14 Generate Combinations

Hack 16 Search for a String across columns

Hack 24 Multiply Across a Result Set

Hack 25 Keep a running total

Hack 25.5 Splitting and combining columns

Hack 26 Include the rows your JOIN forgot

Hack 30 Calculate the maximum/minimum of two fields

Hack 33 Get values and subtotals in one shot

Hack 50 Combine tables containing different data

Hack 51/52 Display rows as columns

Hack 55 Import Someone Else's Data

Hack 62 Issue Queries Without Using a Table

Hack 63 Generate rows without tables

Hack 70 Combine your queries

Hack 72 Extract a subset of the results

Hack 78 Break it down by Range

Hack 88 Test two values from a subquery

Hack 98 Find and stop long running queries

Hack 100 Run SQL from a Web Page