MAX_IDLE_BLOCKER_TIME参数解析

denglj

文档课题：MAX_IDLE_BLOCKER_TIME参数解析.

测试环境：oracle 19.1264位+ 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、  该参数从oracle19c开始引进.

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

Version19.12.0.0.0

Copyright(c) 1982, 2021, Oracle.  All rightsreserved.

Connectedto:

OracleDatabase 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version19.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 fromv$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';

Systemaltered.

SQL>select name,value,issys_modifiable from v$parameter wherename='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));

Tablecreated.

SQL>insert into test values (1,'jack');

1 rowcreated.

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

1 rowcreated.

SQL>commit;

Commitcomplete.

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

Version19.12.0.0.0

Copyright(c) 1982, 2021, Oracle.  All rightsreserved.

Connectedto:

OracleDatabase 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version19.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 Nov16 10:14:57 CST 2022

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

SQL>host date

Wed Nov16 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 rowupdated.

说明：会话2在发出更新test表的语句后，不会立即更新，而是需要等待2分钟.

以下是trace日志信息：

2022-11-16T10:28:50.279559+08:00

ORCLPDB(3):Processtermination requested for pid 5637 [source = rdbms], [info = 2] [request issuedby pid: 4188, uid: 1001]

2022-11-16T10:28:50.331587+08:00

(3):KILLSESSION 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;

selectsid from v$mystat where rownum=1

*

ERROR atline 1:

ORA-03113:end-of-file on communication channel

ProcessID: 5637

Session ID: 402 Serialnumber: 11472