DESCRIPTION This alert will calculate the ratio of current number of locks (counts rows in syslocks) against the max locks available (number of locks from sp_configure / sysconfigures). ALERT DEFINITION To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Single Numeric Return. Configure the alert as needed and paste in the attached query.

Description Some customers execute database jobs using DBMS_SCHEDULER and this alert will notify you of failures in those jobs. Because DPA custom alerts work with numeric returns, this SQL statement changes the status value to one of 0 (normal), 10 (stopped) or 20 (failed) in increasing severity. The thresholds in the alert should be defined accordingly. Alert Definition To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple...

Description This alert will review the number of locks currently being used and compare to the max_locks parameter. Alert Definition To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Single Numeric Return. Configure the alert as needed and paste in the attached query.

Description This alert will utilize the V$RECOVERY_FILE_DEST view to calculate free space for each recovery area. If there are multiple recovery areas, this alert will return all that are running low on free space. Alert Definition To create this alert, click on Alerts > Manage Alerts tab and create a Custom SQL Alert with Multiple Numeric Return type. Configure the alert as needed and past in the attached query.

DESCRIPTION When modifications are made to a cache in Sybase, a spinlock (or mutex) denies all other tasks access to the cache while the changes are being made. Although spinlocks are held for extremely brief durations, they can slow performance in systems with high transaction rates. If spinlock contention is more than 10%, consider using named caches or adding cache partitions. This alert will return a list of all caches along with their spinlock contention which DPA will then compare to the...

DESCRIPTION This alert is for SQL Server instances only and will tell you when SQL statements are currently running and have been for more than a defined amount of time. The query limits the result set to any session that has been running for more than 30 seconds. If you want to catch queries that have been running for more than 5 minutes, set you Critical threshold to 300 seconds for Min and leave Max empty. Also set the execution interval for the alert to 5 minutes or less. ALERT DEFINITION...

DESCRIPTION This alert is for Oracle instances only and will tell you when SQL statements are currently running and have been for more than the defined amount of time. For example, if you want to catch any session that runs a query for more than 5 minutes, set your Min threshold to 300 seconds and leave the Max empty meaning anything higher. Also, set the execution interval to 5 minutes or less. ALERT DEFINITION To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert...

Description If your Oracle database is configured for auditing, this alert will monitor for logon failures using the DBA_AUDIT_SESSION view. Alert Definition To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Single Numeric Return. Configure the alert as needed and past in the attached query.

Description This alert will tell you when an SQL statement has executed more often in the last 2 hours than it normally does. This example uses a 150% threshold, meaning the SQL has executed 1.5 as many times as normal. The alert definition uses the last 14 days for each SQL statement as the baseline for execution counts per hour. It then compares the number of average executions in the last 2 hours with the baseline to derive the percent increase. It also uses summary data so running it more...

Description This alert will determine if any SQL had a plan change within he last 90 min (configurable in script) that resulted in a higher amount of wait time on average for the query. Plan changes are not necessarily bad, but if the wait times go up, that is a good indication the new plan is not as good as the previous. Alert Definition To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert...

DESCRIPTION This alert will list SQL Statements that have recently executed outside their normal execution time. The previous 30 days are compared when calculating the average execution time for a SQL. The results are reported back in percentage above normal executions time, e.g. if 200 is returned that means the query executed twice as long on average within the last hour than normal. Warning: This alert supports SQL Server repositories only. Available Database Instances: All database platforms...

Description This alert will run against the monitored SQL Server instance and retrieve the time each session has been blocked ("Seconds Blocked" column). It will also give some details about the blocking session in the "Message" column and this can be customized with anything else from sys.dm_exec_requests DMV. Note that DPA includes a base alert for locking, but it uses the total amount of time for all blocking that happened. This alert is different in that it uses the blocking time of each...

Description This alert will run against the monitored Oracle databases and retrieve the time a session has been waiting ("Seconds Blocked" column) on a blocking session. It will also give some details about the blocking session in the "Message" column and this can be customized with anything else from v$session. Note that DPA also has a standard blocking alert located in the Alerts under Wait Time alerts. The difference in the base alert is that it goes after total locking time, vs the seconds...

I had a customer ask if they could get alerted from DPA when any SQL statement exceeded X seconds in a 10 minute timeframe. This is the alert (Custom SQL Alert - Multiple Numeric Return type) we ended up with and here is the SQL statement used: SELECT 'DB: ' + db.name + ' - SQL: ' + convert(varchar(20), sw.izho), sum(sw.qp/100) timesecs FROM ignite.consw_#DBID# sw INNER JOIN ignite.cono_#DBID# db on db.id = sw.ixoy WHERE sw.d >= datediff(mm, -#FREQUENCY#, current_timestamp) GROUP BY db.name...

DPA has a base alert named Database Availability that will notify you if an instance is down. However, alerts can only execute at most once per minute, and if an instance goes down briefly with an immediate restart, the alert can miss the downtime. This new custom alert will let you know anytime a SQL Server instance restarts no matter how long it takes. Note: that this alert will not warn you about downtime until the instance has restarted. Because of that, this alert should be used in conjunction...

Alerting on free disk space for your SQL Server instance is not a base alert within DPA. This is because the information is not readily available from within the database instance via a query. However, using xp_cmdshell and a PowerShell command, we can add a custom alert to DPA to handle this. Note: the xp_cmdshell option is disabled by default on SQL Server instances. For this alert to work properly, xp_cmdshell must be enabled. When creating the alert in DPA, create a Custom Alert of type "Custom...

When it comes to blocking, DPA can currently alert when the cumulative blocking time goes beyond a threshold. The alert can be found under the category of Wait Time and is named Total Blocking Wait Time. However, one drawback to alerting on cumulative blocking time, is that it can potentially be noisy in a busy server. For example, lets take 2 blocking scenarios: 10 root blocker sessions each blocking 20 other sessions for 15 seconds 1 root blocker session blocking 2 other sessions for 150...

Description It's important to identify and investigate if there are any long running queries in your PostgreSQL database(s). These long running queries may interfere in overall database performance and could be the result of a stuck background process. This alert utilizes pg_stat_activity to monitor all non "autovacuum" related queries and notifies you if there are queries running longer than the defined thresholds. Alert Definition To create the alert, click on Alerts > Manage Alerts tab...