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.
Here is the first one to kick-off the series.
”LifeSaver of the Week – 1”
LAST_CALL_ET:
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
Read more about LifeSaver.
0 comments:
Post a Comment