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.
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
Hack 10 Converting subqueries into joins
Hack 11 Converting aggregate subqueries into joins
Hack 16 Search for a String across columns
Hack 24 Multiply Across a Result Set
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 72 Extract a subset of the results
Hack 78 Break it down by Range
Hack 88 Test two values from a subquery