Saturday, April 21, 2018

Shell Script For Reporting Long Running Queries In Oracle

It's crucial for any DBA to know about long running queries that can impact the performance on the database.
Badly coded reports along with developers' bad habit of using "select *" to view the 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 gets exaggerated.

If you're running Oracle on Linux, then this script is made for you.
Download link:
https://www.dropbox.com/s/a1wn1j1squjf1qx/DBA_BUNDLE2_6Feb2016.tar?dl=0

The script is easy to use, it will run against all instances on the server reporting any active sessions for more than 60 minutes (the default), you can adjust this time 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 using 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).

Last, you must provide your email address in order to receive 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 :-)

Report Long Running Queries | Long Active Sessions Script (GitHub version):

No comments:

Post a Comment