Connor: Created page with "
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 res..."

2012-08-08T11:12:58Z

Created page with "<p>Here you are shown how to find long running queries</p> <p>and to kill them stopping them from using up database resources and</p> <p>therefore allowing the database to res..."

New page

<p>Here you are shown how to find long running queries</p>
<p>and to kill them stopping them from using up database resources and</p>
<p>therefore allowing the database to respond quicker.</p>
<div class='ht'>
<div class=params>schema:scott</div>
<source lang=sql class='tidy'></source>
<source lang=sql class='setup'></source>
<div>
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.
</div>
<source lang='sql' class='def e-oracle'>
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</source>
<source lang='sql' class='def e-mysql'>
SHOW PROCESSLIST</source>
<source lang='sql' class='def e-sqlserver'>
USE master
SELECT spid, nt_username,
datediff(s, logon_time, GETDATE())
FROM sysprocesses</source>
<source lang='sql' class='def e-postgres'>
SELECT datid, datname, usename, procpid,
backend_start
FROM pg_stat_activity</source>

<div class="ecomm e-oracle" style="display: none">To kill a session in Oracle use: ALTER SYSTEM KILL SESSION 'sid,serial'</div>
<div class="ecomm e-mysql" style="display: none">To terminate a session in MySQL use: KILL 'Id'</div>
<div class="ecomm e-sqlserver" style="display: none">To kill a process in sqlserver use: EXEC ('kill Spid')</div>
<div class="ecomm e-postgres" style="display: none">To kill a session in Postgres use: # kill -INT 'procpid'</div>
</div>

{{Hacks Ref}}

Stop queries - Revision history

Connor: Created page with "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 res..."

Connor: Created page with "
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 res..."