Monitoring long running SQLs

When we moved to new box after upgrading the h/w - we had been seeing some of the queries taking more than normal time to finish up. Sometimes, those were ended with a blocking status.

When the ops team noticed high jdbc usage and informed me, that was too late. The customers were suffering much earlier than we get noticed. The first incident happened around 5 pm, jdbc usage shown up high on "cacti" and "Nagios" a bit later, and I got call around 5:30 pm (my time 3:30 am) , by that time the system was almost bogged down.

Anyways, to get an early notification, I write a shell script and put it in cron job which will run the script in every 5 minutes. This will check long running queries (running for more than three minutes) and mail to ops team so that we can take precaution and prevent from any possible system failure.

Here is the SQL script I used in my shell script -

set linesize 200
set pagesize 200
col "SQL Text" format a40

SELECT a.username "User", a.sid "SID", a.serial# "Serial"
, b.sql_text "SQL Text", a.last_call_et "Elapsed Time"
FROM v$session a, v$sql b
WHERE a.sql_id = b.sql_id
AND a.status='ACTIVE'
AND a.LAST_CALL_ET >= 180
AND a.USERNAME IN ('PROTS','STEE');


***