dbi Blog

The increasing popularity of tools like SQL Developer and Toad, sometimes comes hand in hand with an increase of stuck sessions.
This is especially seen in cases where:

User A does a change (neither committed nor rolled back). Then, switches to another task, or even leaves for lunch.
Then, User B updates the same row and stays there, stuck for hours.

In such cases, DBAs can obviously kill the initial blocker session.
In the past, Resource Manager could be setup to fix these automatically. According the Oracle 12.1 manual, the procedure DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE() has the 2 below parameters

max_idle_time	Indicates the maximum session idle time. Default is NULL, which means unlimited.
max_idle_blocker_time	Maximum amount of time in seconds that a session can be idle while blocking another session’s acquisition of a resource

Nowadays, in 21c (checked in 21.6.0.0.220419), we can achieve the same by setting only 1 init.ora/spfile parameter.
Actually, although often marked as 21c New Feature, this already works in 19c (checked in 19.10.0.0.210119)

Let’s have a closer look at this “good to know” parameter.

What’s on by default?

SQL> show parameter idle
NAME				     TYPE	 VALUE
------------------------------------ ----------- --------------------
max_idle_blocker_time		     integer	 0
max_idle_time			     integer	 0

By default, both parameters are set to 0, meaning there is no limit set.

According Oracle 19c documentation, we can set MAX_IDLE_BLOCKER_TIME to a number of minutes that a session holding needed resources can be idle before it is automatically terminated.

So, shall we set MAX_IDLE_TIME or MAX_IDLE_BLOCKER_TIME?
By giving a value to MAX_IDLE_TIME, we limit all idle sessions
whereas by setting MAX_IDLE_BLOCKER_TIME, we only limit idle sessions blocking resources.

Setting MAX_IDLE_TIME can be be an issue in case of connection pool.
In such a case, we could continuously re-create sessions automatically terminated by this parameter.

Set MAX_IDLE_BLOCKER_TIME value to 5 minutes

alter system set max_idle_blocker_time=5;

This parameter is modifiable at PDB-level.

While connected to CDB$ROOT, we can easily doublecheck the change by selecting against the pdb_spfile$ system view:

select ps.db_uniq_name, ps.pdb_uid, p.name as pdb_name, ps.name, ps.value$
from pdb_spfile$ ps
 join v$pdbs p on ps.pdb_uid = p.con_uid
 order by 1, 2, 3;
 
 DB_UNIQ_NA    PDB_UID PDB_NAME		   NAME 		     VALUE$
---------- ---------- -------------------- ------------------------- ----------
*	   3067207640 PDB1T		   max_idle_blocker_time     5

In a 1st session, let’s now update the table (without commit nor rollback)

SQL> select * from products;

	ID NAME
---------- ----------------------------------------
	 1 bread
	 2 chocolate

SQL> show auto
autocommit OFF

SQL> set time on
11:08:42 SQL> update products set name='cheese' where id=2;

1 row updated.

Here, let’s now leave as is (without commit nor rollback)

In a 2nd session, Update the same row will hang

show auto
autocommit OFF
set time on

SQL> update products set name='bier' where id=2;

… Wait …

Optionally, in a 3rd session, show locks

SQL> set time on
11:10:02 SQL> @qinalocksess.sql

    SID Lock Type		       Lock Mode				Request Block Owner			       Table Name			 Wait s.
------- ------------------------------ ---------------------------------------- ------- ----- -------------------------------- -------------------------------- --------
    105 DML Lock (TM)		       ROW-X (SX)				      0     0 USER1			       PRODUCTS 			      75
     67 DML Lock (TM)		       ROW-X (SX)				      0     0 USER1			       PRODUCTS 			      35

2 rows selected.

.../...

11:12:33 SQL> @qinalocksess.sql

    SID Lock Type		       Lock Mode				Request Block Owner			       Table Name			 Wait s.
------- ------------------------------ ---------------------------------------- ------- ----- -------------------------------- -------------------------------- --------
    105 DML Lock (TM)		       ROW-X (SX)				      0     0 USER1			       PRODUCTS 			     230
     67 DML Lock (TM)		       ROW-X (SX)				      0     0 USER1			       PRODUCTS 			     190

2 rows selected.
11:14:35 SQL> @qinalocksess.sql

Thu Jul 28																							 page	 1
													 Sessions Blocking Other Sessions Report

    SID Lock Type		       Lock Mode				Request Block Owner			       Table Name			 Wait s.
------- ------------------------------ ---------------------------------------- ------- ----- -------------------------------- -------------------------------- --------
     67 DML Lock (TM)		       ROW-X (SX)				      0     0 USER1			       PRODUCTS 			      48

1 row selected.

Let’s now go back to session 1

After the 5 minutes, we can verify the 1st session has been automatically terminated with an ORA-03113.

11:08:52 SQL> /
update products set name='cheese' where id=2
       *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 6451
Session ID: 105 Serial number: 36338

Usually, seeing ORA-03113 is not expected as this often reveals some kind of oracle bugs (ORA-00600 or ORA-07445).
In the present case, it is nice to see such error.

The Update from Session 2 is now completed as the initial row-level lock has gone

SQL> set time on
11:09:21 SQL> update products set name='bier' where id=2;

1 row updated.

11:13:58 SQL> commit;

Commit complete.

11:16:25 SQL>

Look at the leftovers logs and tracefiles

adrci
adrci> set homepath diag/rdbms/cdb1t_site1/CDB1T
adrci> show alert -tail

The automatic session closure is written black on white in the Alert log:

2022-07-28 11:13:58.032000 +02:00
KILL SESSION for sid=(105, 36338):
  Reason = max_idle_blocker_time parameter, idle time = 5 mins, currently waiting on 'SQL*Net message from
  Mode = KILL HARD SAFE -/-/NO_REPLAY
  Requestor = PMON (orapid = 2, ospid = 2187, inst = 1)
  Owner = Process: USER (orapid = 64, ospid = 6451)
  User = oracle
  Program = [email protected] (TNS V1-V3)
  Result = ORA-0

If need be, we can get more details by looking at the trace files generated by the Diag background process:

oracle@l8-bsl:/u01/app/oracle/diag/rdbms/cdb1t_site1/CDB1T/trace/ [CDB1T(PDB1T)] ls -ltr
...
-rw-r-----. 1 oracle oinstall   37932 Jul 28 11:19 CDB1T_dia0_2241_base_1.trc

cat CDB1T_dia0_2241_base_1.trc
.....
*** 2022-07-28T11:10:28.504611+02:00 (CDB$ROOT(1))
HM: Session with ID 105 serial # 36338 (FG)
    on single instance 1 in container PDB1T is hung
    and is waiting on 'SQL*Net message from client' for 96 seconds.
    Session was previously waiting on 'SQL*Net message to client'.
    Session ID 105 is blocking 1 session
.....
*** 2022-07-28T11:11:09.462853+02:00 (CDB$ROOT(1))
HM: Session with ID 67 serial # 14207 (FG)
    on single instance 1 in container PDB1T is hung
    and is waiting on 'enq: TX - row lock contention' for 97 seconds.
    Session was previously waiting on 'PGA memory operation'.
    Final Blocker is Session ID 105 serial# 36338 on instance 1
     which is waiting on 'SQL*Net message from client' for 135 seconds, wait id 48
     p1: 'driver id'=0x54435000, p2: '#bytes'=0x1, p3: ''=0x0
.....
*** 2022-07-28T11:11:11.513636+02:00 (CDB$ROOT(1))
All Current Hang Statistics

                      current number of hangs 1
    hangs:current number of impacted sessions 2
                  current number of deadlocks 0
deadlocks:current number of impacted sessions 0
           number of locally blocked sessions 1
  local contention - locally blocked sessions 33.33%
          number of remotely blocked sessions 0
remote contention - remotely blocked sessions  0.00%
                 current number of singletons 0
      current number of local active sessions 3
        current number of local hung sessions 1

Suspected Hangs in the System
and possibly Rebuilt Hangs
                     Root       Chain Total               Hang
  Hang Hang          Inst Root  #hung #hung  Hang   Hang  Resolution
    ID Type Status   Num  Sess   Sess  Sess  Conf   Span  Action
 ----- ---- -------- ---- ----- ----- ----- ------ ------ -------------------
     2 HANG    VALID    1   105     2     2    LOW  LOCAL Terminate Process

  Inst  Sess   Ser             Proc  Wait    Wait
   Num    ID    Num      OSPID  Name Time(s) Event
  ----- ------ ----- --------- ----- ------- -----
        PDBID PDBNm
        ----- ---------------
      1     67 14207      6691    FG      99 enq: TX - row lock contention
            3 PDB1T
      1    105 36338      6451    FG     139 SQL*Net message from client
            3 PDB1T
.....
HM: current SQL: update products set name='bier' where id=2


                                                     IO
 Total  Self-         Total  Total  Outlr  Outlr  Outlr
  Hung  Rslvd  Rslvd   Wait WaitTm   Wait WaitTm   Wait
  Sess  Hangs  Hangs  Count   Secs  Count   Secs  Count Wait Event
------ ------ ------ ------ ------ ------ ------ ------ -----------
     2      0      0      1    192      1    192      0 enq: TX - row lock contention

Post Views: 4,173

A few words about the good to know MAX_IDLE_BLOCKER_TIME

What’s on by default?

Set MAX_IDLE_BLOCKER_TIME value to 5 minutes

In a 1st session, let’s now update the table (without commit nor rollback)

In a 2nd session, Update the same row will hang

Optionally, in a 3rd session, show locks

Let’s now go back to session 1

The Update from Session 2 is now completed as the initial row-level lock has gone

Look at the leftovers logs and tracefiles

Oracle Team

Leave a Reply:

Related blog articles

What’s on by default?

Set MAX_IDLE_BLOCKER_TIME value to 5 minutes

In a 1st session, let’s now update the table (without commit nor rollback)

In a 2nd session, Update the same row will hang

Optionally, in a 3rd session, show locks

Let’s now go back to session 1

The Update from Session 2 is now completed as the initial row-level lock has gone

Look at the leftovers logs and tracefiles

Oracle Team

Leave a Reply:

Related blog articles

SQL Server: The importance of the default language during a login migration

“Edit Top 200 rows” does not work between SSMS 18.12.1 & SQL Server 2022

SQL Server: Basic AG – One listener, multiple databases

CloudNativePG – Connecting external applications