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”

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

Posted by Nitin Pai
Comments (0)
July 2nd, 2007

Comments (0)