oracle Find and Stop Long-Running Queries

It is all too easy to set off a query that will run for several hours, consuming system resources and disturbing other users. Spot long-running queries and kill them off.

Long-running queries are surprisingly easy to produce. It is not always clear how long a query will take to run, and even correct queries may take a long time to execute. Incorrectly written queries, such as ones withJOIN conditions missing, may take a considerable time to solve. Oracle, SQL Server, MySQL, and PostgreSQL each support ways to detect long-running queries. They also offer ways to terminate running queries. The commands to manage queries have been implemented differently for each of these database systems.

In Oracle and SQL Server, you can monitor activity by querying a system view. This means that you can useSELECT statements to choose the columns and rows that you want to be shown.

Oracle

In Oracle, you can find a long-running database session (which could have been involved with many different transactions). The query to find long-running sessions uses the system viewV$SESSION:

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;

USERNAME  SID SERIAL# CURRENT             LOGON                MINS
----------------------------------------------------------------------------
GRUSSELL  246 52683   2006-06-16 14:38:22 2006-06-16 14:30:51     7.51
DBRW      251 49308   2006-06-16 14:38:22 2006-06-15 17:33:48  1264.56

You can subtract the logon_time fromsysdate (the current time) to get the length of time each session has been running. This floating-point value is in units of days; so you can multiply by 24 to get this in hours and multiply again by 60 to get the number of minutes.

To kill a session use:

ALTER SYSTEM KILL SESSION 'sid,serial'

To kill off user DBRW's query use:

ALTER SYSTEM KILL SESSION '251,49308'

转载http://codeidol.com/sql/sql-hack/Wider-Access/Find-and-Stop-Long-Running-Queries/
备注：type为background类型的session不可以kill，请参照上篇文章