Stop queries

From SQLZOO
Revision as of 12:12, 8 August 2012 by Connor (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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


Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense