Javier 
Monitoring for blocking sessions in Oracle Autonomous Database helps keep things running smoothly. A blocking session happens when one user’s activity locks resources that others need, causing delays that can slow down applications or impact users. By setting up alerts, you’ll know right away when a block is happening, allowing you to fix it quickly and avoid performance issues.
In this blog I’m going to show you how to create an alert which sends an email when there is a blocking session!
Once you are in your main page of your Autonomous Database, you need to find the “View all database metrics” to find the metric “BlockingSessions“
We will create the alert directly, so go to Alarm Definitions to create a new one.
Click on create alarm.
Now we can give a name to identify the alarm. In this case I will call it Blocking Sessions.
We need to select the metric we are interested in. In this case we need to find the “BlockingSessions” which is under the namespace “oci_autonomous_database“. It is important to define the statistic, in this case we selected “Max“
Now we need to define the rule to trigger the alarm. We will select the option “greater than or equal to” value “1“. In case there is any blocking session it will trigger.
You can chose different notifications. I already had set an email notification with my email.
We need to make sure the alarm is enabled, and then we can just click on save alarm.
Now we can generate a block to generate an alert. You can use the following code for testing:
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;
If we check again the alarm, we can see it reaches 1, so the alert will be triggered in 1 minute.
If we go to performance hub also we can see the blocking session (in case we want to kill it)
After one minute, I have received the alert with a lot of information!
Creating alerts over Autonomous Database is very easy! I hope you found it interesting!