Description:-
In this article we are going to see setting parameter MAX_IDLE_BLOCKER_TIME on oracle 21c database.
Blocking session in Oracle?
- Blocking sessions occur when one session holds an exclusive lock on an object and doesn’t release it before another session wants to update the same data. This will block the second until the first one has done its work.
- When we see an enqueue wait event in an Oracle database, the chances are that there is some thing locking or holding up some sessions from executing their SQL statements. When a session waits on an enqueue wait event, that session is waiting for a lock that is held by a different session.
- Application completely hangs while waiting for the first session to release its lock. You will often have to identify these sessions in order to improve your application performance to avoid as many blocking sessions as possible.
Demo:-
MAX_IDLE_BLOCKER_TIME -> This parameter specifies the maximum number of minutes that a blocking session can be idle. After that point, the session is automatically terminated.
Step:- 1 Check the default value
SQL> show parameter max_idle_blocker_time
NAME TYPE VALUE
———————————— ———– ——————————
max_idle_blocker_time integer 0
Step:-2 Change the parameter MAX_IDLE_BLOCKER_TIME Value To 1 Minute
SQL> alter system set max_idle_blocker_time=1;
System altered.
SQL> show parameter max_idle_blocker_time
NAME TYPE VALUE
———————————— ———– ——————————
max_idle_blocker_time integer 1 -> 1 minute
Step:3 Testing the parameter
Session-1 Execute the update satement (Do not commit or rollback)
update test set sal=sal*2 where deptno=20;
Session-2 Execute the update satement on the same rows Now session will hangs (wait 1 minute)
delete test where deptno=20;
After 1 minute session-1 terminates automatically, because it was idle
Step:-4 session 2 Transaction is completed
SQL> delete test where deptno=20;
5 rows deleted.
SQL> commit;
Commit complete.
Connect with me on:-
Telegram App:https://t.me/oracledbwr
LinkedIn:https://www.linkedin.com/in/hariprasathdba
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page : https://www.facebook.com/dbahariprasath/?
Twitter : https://twitter.com/hariprasathdba
