20. Database Tier - Locks

Locks are used to grant explicit access when update of data is performed. Normally this is not an issue until there are too many sessions that are blocked by locks and they have been waiting for long time. This check will measure the number of blocked sessions and their max wait seconds.

Multiple Instances: No

Interval: Every 5 minutes (288 times per day)

Status Expression
Critical If (Blocked Sessions > 15) and (Max Wait Seconds > 1600 seconds)

Impact Slow response from database. User experience a not responding system.
Suggestion Analyze sessions running in the environment. Analyze sizing of Oracle Db.
Warning If (Blocked Sessions > 5) and (Max Wait Seconds > 900 seconds)

Impact Slow response from database. User experience a not responding system.
Suggestion Analyze sessions running in the environment. Analyze sizing of Oracle Db.
OK If not status Warning or Critical.
Unknown Missing application response.

Metric & Service Command line example
Blocked Sessions
 java -jar application-monitoring-metrics-cli.jar db -host=localhost -instance=<instancename> -user=ifsmonitoring -pass=<password> -appowner=<value> -request=BLOCKED_SESSIONS -wa=5 -ca=15
Max Wait Seconds
 java -jar application-monitoring-metrics-cli.jar db -host=localhost -instance=<instancename> -user=ifsmonitoring -pass=<password> -appowner=<value> -request=BLOCKED_SESSIONS_MAXWAITSECS -wa=900 -ca=1600