By Vijay Ganapathy, Datavail
Blocking sessions can significantly impact performance and user experience in Oracle Autonomous Database if they go undetected. Left unresolved, they can cause application slowdowns, timeouts, or even outages.
Let’s walk through how to proactively monitor blocking sessions using built-in Autonomous Database metrics and OCI Alarms, and how to automatically trigger email alerts when blocking persists beyond a defined threshold. In this example, we’ll set the threshold for at least one minute.
By automating blocking session detection, data engineers and Site Reliability Engineering teams can reduce downtime, improve performance visibility, and ensure consistent application availability.
This process:
- Uses the native BlockingSessions metric,
- Monitors all users, including admin users,
- Requires no custom scripts or database jobs,
- And is fully managed through the OCI Console.
Step-by-Step Configuration
Log into cloud.oracle.com in the corresponding respective tenancy and click on the Autonomous database to get to the main page of the database.
Once you are in your main page of your Autonomous Database, you need to find the “View all database metrics” option to find the metric “BlockingSessions“.
Click on Alarm Definitions to create a new one.
Click on create alarm.
Provide a name to identify the alarm. For example, “Blocking Sessions” is used in this blog.
Select the metric “BlockingSessions” under the metric namespace “oci_autonomous_database“. Select the statistic as “Max“ with the interval as required.
Define the rule to trigger the alarm. Select the option “greater than or equal to” value “1“ to identify the blocking session of one minute or more.
Multiple notification options are available, and we are using the email notification option, which is already set up separately, as an example.
Make sure the “Enable this alarm” box is checked for the fulfilment and click “Save alarm.”
Do a Mock test to generate a block and the alert.
CREATE TABLE lock_test (
id NUMBER PRIMARY KEY,
description VARCHAR2(50)
);
INSERT INTO lock_test (id, description) VALUES (1, ‘Row 1’);
INSERT INTO lock_test (id, description) VALUES (2, ‘Row 2’);
COMMIT;
— Session 1: Lock the row with SELECT FOR UPDATE
SELECT * FROM lock_test WHERE id = 1 FOR UPDATE;
— Session 2: Attempt to lock the same row, which will block
SELECT * FROM lock_test WHERE id = 1 FOR UPDATE;
Once the alarm data reaches one minute, the alert will be triggered.
The Performance Hub can be used to view the blocking session and kill it if required from the OCI Console.
Here’s an example alert with details:
Setting up an alarm for blocking sessions in OCI Autonomous Database is a straightforward process that enables proactive monitoring and swift resolution of potential performance issues. By leveraging the available metrics, defining appropriate rules, and configuring notifications, you can ensure that blocking sessions are promptly identified and addressed. Utilizing tools like the Performance Hub further streamlines the management and troubleshooting of these incidents, helping you maintain optimal database performance and reliability.
Explore more data platform resources at Datavail.com.