Monitoring long-running queries is part of the early warning mechanism for the performance
issues on the database before it gets exaggerated
If you're running Oracle on Linux, then this script is for you.
Download link:
https://www.dropbox.com/s/yh6k3r7q3p876h0/report_long_runing_queries.sh?dl=0
The script is easy to use, it will run against all instances on the server reporting any active sessions/query for more than 60 minutes (the default), you can adjust this duration by changing the following parameter under THRESHOLDS section to whatever 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 report generation if the number of long active sessions have reached a specific count. By adjusting the following parameter:
LONG_RUN_SESS_COUNT=0 # The count of long-running sessions, report will not appear unless reached (0 report all long running sessions).
Lastly, you must provide your email address in order to receive a detailed Email report of long-running queries/active sessions, by editing below template in navy color:
MAIL_LIST="youremail@yourcompany.com"
You can schedule the script in the crontab to run every 5/10 minutes.
In the upcoming post, I'll share another script to automatically kill 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 :-)
Monitor Long Running Queries | Long Active Sessions Script (GitHub version):
issues on the database before it gets exaggerated
If you're running Oracle on Linux, then this script is for you.
Download link:
https://www.dropbox.com/s/yh6k3r7q3p876h0/report_long_runing_queries.sh?dl=0
The script is easy to use, it will run against all instances on the server reporting any active sessions/query for more than 60 minutes (the default), you can adjust this duration by changing the following parameter under THRESHOLDS section to whatever 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 report generation if the number of long active sessions have reached a specific count. By adjusting the following parameter:
LONG_RUN_SESS_COUNT=0 # The count of long-running sessions, report will not appear unless reached (0 report all long running sessions).
Lastly, you must provide your email address in order to receive a detailed Email report of long-running queries/active sessions, by editing below template in navy color:
MAIL_LIST="youremail@yourcompany.com"
You can schedule the script in the crontab to run every 5/10 minutes.
In the upcoming post, I'll share another script to automatically kill 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 :-)
Monitor Long Running Queries | Long Active Sessions Script (GitHub version):
No comments:
Post a Comment