阻塞会话：
当一个会话持有其它会话所需要的资源时，此会话会被认为是阻塞会话，比如以下场景：
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
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) | Powered by Discuz! X3.2 |