It’s crucial for any DBA to get to know about long running queries that can impact the performance of his/her database environment.
Badly coded reports along with developers’ bad habit of using “select *” to navigate data on big tables enforce the DBA to have a kind of an early warning tool to get him aware of such performance threats before it exaggerates.
If you’re running Oracle on Linux, then this script is made for you.
The use of this script is so easy, the script will run against all instances on the server reporting any active sessions for more than 60 minutes (the default), you can adjust that time by changing the following parameter under THRESHOLDS section to the number of minutes you want:
EXEC_TIME_IN_MINUTES=60 # Report Sessions running longer than N minutes (default is 60 min).
Also, you can control the number of a long active session if reached, then the email will be triggered. by using the following parameter:
LONG_RUN_SESS_COUNT=0 # The count of long-running sessions, the report will not appear unless reached (0 reports all long running sessions).
Last, you must provide your good email address in order to get a detailed report of long-running queries/active sessions by email:
MAIL_LIST=”youremail@yourcompany.com”
This script can be scheduled in the crontab to run every 5/10 minutes.
In the upcoming post, I’ll share a script that automatically kills long active sessions connected by specific programs like SQL Developer or Toad. Let’s make the life of our beloved developers easy as they always make our life easy 🙂
Download link:
http://dba-tips.blogspot.ae/2018/04/report-long-running-queries-long-active.html