Every seasoned DBA has his bag of tips and tricks. I have such a bag which I will open and share with you all one code/script/tip/trick at a time. I am calling this new series‘LifeSaver of the Week’ because these scripts actually save life (most of the time) during crunch time. There have been occasions when I had a high priority ticket breathing down my neck and pulling the right trick helped identify the problem instantly.
The LifeSaver series will include tips relevant to Oracle or PeopleSoft or UNIX.
Here is the first one to kick-off the series.
”LifeSaver of the Week – 1”
A few years back, I was trying to build a script to identify long running SQL’s. The script was supposed to check for any SQL running for more than 5 mins and notify me if it found any such SQL. I was trying different methods to determine the optimal approach to build this script. I came across the column LAST_CALL_ET present in V$SESSION. The data in this column shows in seconds the time since last call. Below is the SQL I used in the script.
SELECT A.SQL_TEXT, B.PROGRAM, B.CLIENT_INFO
FROM V$SQLTEXT A, V$SESSION B
WHERE A.ADDRESS = B.SQL_ADDRESS
AND B.STATUS = ‘ACTIVE’
AND B.LAST_CALL_ET > 300
AND B.USERNAME IS NOT NULL
ORDER BY A.PIECE