MAX_IDLE_BLOCKER_TIME参数解析——自动断开阻塞会话

文档课题：MAX_IDLE_BLOCKER_TIME参数解析.
测试环境：oracle 19.12 64位 + rhel 8.4 64位
1、理论
MAX_IDLE_BLOCKER_TIME参数指定阻塞会话可空闲的时间(单位：分)，一旦超过该时间限制，阻塞session将自动断开.
以下相关属性：
登录后复制
阻塞会话：
当一个会话持有其它会话所需要的资源时，此会话会被认为是阻塞会话，比如以下场景：
a、阻塞会话持有另一个会话所需要的锁.
b、阻塞会话为一个并行操作，并且它的使用者组、PDB、或数据库已达到其最大并行服务器限制或已排队的并行操作.
c、阻塞会话所在的PDB或数据库实例达到其SESSIONS或PROCESSES限制.
另外，MAX_IDLE_BLOCKER_TIME不同于MAX_IDLE_TIME，后者适用于所有会话，此前博客有MAX_IDLE_TIME参数的相关介绍，此处不再赘述，前者仅适用于阻塞会话.因此为使MAX_IDLE_BLOCKING_TIME有效，其限制必须小于MAX_IDLE_TIME限制.
注意：
a、该参数对并行查询进程以及sys用户的会话没有影响.
b、该参数从oracle 19c开始引进.
c、该参数缺点：阻塞会话自动kill后，DBA不清楚阻塞会话执行过的SQL信息，不方便追溯.
2、测试
2.1、修改max_idle_blocker_time
[oracle@dbserver ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Wed Nov 16 09:26:02 2022
Version 19.12.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.12.0.0.0
SQL> conn sys/oracle_4U@orclpdb as sysdba
Connected.
SQL> col issys_modifiable for a20
SQL> col name for a25
SQL> col value for a15
SQL> r
1* select name,value,issys_modifiable from v$parameter where name=’max_idle_blocker_time’

NAME VALUE ISSYS_MODIFIABLE
————————- ————— ——————–
max_idle_blocker_time 0 IMMEDIATE
SQL> ALTER SYSTEM SET max_idle_blocker_time=2 SCOPE=BOTH PDB=’ORCLPDB’;

System altered.
SQL> select name,value,issys_modifiable from v$parameter where name=’max_idle_blocker_time’;

NAME VALUE ISSYS_MODIFIABLE
————————- ————— ——————–
max_idle_blocker_time 2 IMMEDIATE
2.2、测试sys用户
会话1操作：
SQL> conn sys/oracle_4U@orclpdb as sysdba
Connected.

SQL> select sid from v$mystat where rownum=1;

SID
———-
402

SQL> select sid,serial# from v$session where sid=402;

SID SERIAL#
———- ———-
402 29176

SQL> create table test (id number(5),
2 name varchar2(20));

Table created.
SQL> insert into test values (1,’jack’);

1 row created.

SQL> insert into test values (2,’marry’);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test where id=1 for update;

ID NAME
———- ————————-
1 jack
会话2操作：
[oracle@dbserver ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Wed Nov 16 10:13:37 2022
Version 19.12.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.12.0.0.0

SQL> conn sys/oracle_4U@orclpdb as sysdba
Connected.
SQL> select sid from v$mystat where rownum=1;

SID
———-
147

SQL> select sid,serial# from v$session where sid=147;

SID SERIAL#
———- ———-
147 35160

SQL> host date
Wed Nov 16 10:14:57 CST 2022

SQL> update test set name=’mike’ where id=1;

SQL> host date
Wed Nov 16 10:18:25 CST 2022
说明：会话2会一直卡在update，会话1不会自动断开连接.
2.3、测试普通用户
会话1操作：
SQL> conn ora1/ora1@orclpdb
Connected.
SQL> select * from test for update;

ID NAME SALARY
———- ————————- ———-
1 cherry 20000
2 jack 25000
3 miss 30000
4 23000
会话2操作：
SQL> conn ora1/ora1@orclpdb;
Connected.
SQL> update test set salary=25000 where id=1;

1 row updated.
说明：会话2在发出更新test表的语句后，不会立即更新，而是需要等待2分钟.
以下是trace日志信息：
2022-11-16T10:28:50.279559+08:00
ORCLPDB(3):Process termination requested for pid 5637 [source = rdbms], [info = 2] [request issued by pid: 4188, uid: 1001]
2022-11-16T10:28:50.331587+08:00
(3):KILL SESSION for sid=(402, 11472):
(3): Reason = max_idle_blocker_time parameter
(3): Mode = KILL HARD SAFE -/-/NO_REPLAY
(3): Requestor = PMON (orapid = 2, ospid = 4188, inst = 1)
(3): Owner = Process: USER (orapid = 63, ospid = 5637)
(3): Result = ORA-0

会话1执行以下语句，正如预想的结果，已自动断开连接.
SQL> select sid from v$mystat where rownum=1;
select sid from v$mystat where rownum=1
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 5637
Session ID: 402 Serial number: 11472
———————————–
MAX_IDLE_BLOCKER_TIME