[20220228]测试Max_idle_blocker_time 参数.txt

来源：这里教程网时间：2026-03-03 17:29:28 作者：

[20220228]测试Max_idle_blocker_time 参数.txt --//今天本来想测试Max_idle_blocker_time在19c的情况,发现不起作用,我使用sys用户测试,我估计可能对于sys用户这样的设置可能不 --//起作用.我又做了max_idle_time的测试发现问题依旧,我没有19c的测试环境,先在18c上测试Max_idle_blocker_time的情况. 1.环境： SYS@xxx.xxx.x.x:1521/orclcdb> @ prxx ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 18.0.0.0.0 BANNER : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production BANNER_FULL : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 BANNER_LEGACY : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. SYS@xxx.xxx.x.x:1521/orclcdb> alter system set max_idle_time=1 scope=memory; System altered. --//1分钟. 2.登录普通用户的PDB会话测试： --//session 1: TTT@xxx.xxx.x.x:1521/orcl> show parameter max_idle_ PARAMETER_NAME TYPE VALUE -------------- ------- ----- max_idle_time integer 1 TTT@xxx.xxx.x.x:1521/orcl> @ spid SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50 ---- ---------- ------- --------- ------ ------- ---------- -------------------------------------------------- 45 63449 54371 DEDICATED 15672 48 298944 alter system kill session '45,63449' immediate; --//记下sid=45. TTT@xxx.xxx.x.x:1521/orcl> host sleep 122 TTT@xxx.xxx.x.x:1521/orcl> show parameter max_idle_ ORA-03113: end-of-file on communication channel Process ID: 15071 Session ID: 280 Serial number: 37330 --//我以前的测试必须等待2*max_idle_time时间。而不是max_idle_time. --//重新登陆,我的测试环境就我一个人使用,一般情况下sid依旧等于45. --//session 1: SYS@xxx.xxx.x.x:1521/orcl> select sysdate from dual; SYSDATE ------------------- 2022-02-28 15:21:53 --//session 2: TTT@xxx.xxx.x.x:1521/orcl> select SECONDS_IN_WAIT,event from v$session where sid=45 ; SECONDS_IN_WAIT EVENT --------------- --------------------------- 97 SQL*Net message from client TTT@xxx.xxx.x.x:1521/orcl> select SECONDS_IN_WAIT,event from v$session where sid=45 ; SECONDS_IN_WAIT EVENT --------------- ---------------------------------------- 122 SQL*Net message from client TTT@xxx.xxx.x.x:1521/orcl> @ sid 45 sid = 45 SPID PID SID SERIAL# CLIENT_INFO PNAME TRACEFILE PROGRAM TERMINAL SQL_ID STATUS C50 ------ ------- ---------- ---------- -------------------- ------ ---------------------------------------------------------------------------- ---------------------------------------- ------------ -------------------------- ---------------- -------------------------------------------------- 15672 48 45 63449 /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_ora_15672.trc sqlplus@gxqyydg4 (TNS V1-V3) pts/2 INACTIVE alter system kill session '45,63449' immediate; --//过去122秒,进程还在.视乎总是多一点点，不知道具体算法，我估计一段时间轮询。 TTT@xxx.xxx.x.x:1521/orcl> @ sid 45 sid = 45 no rows selected --//sid=45消失. --//回到会话执行： --//session 1: TTT@xxx.xxx.x.x:1521/orcl> select sysdate from dual; ERROR: ORA-03114: not connected to ORACLE --//已经kill 。 --//如果是sys用户登录呢？ --//session 1: SYS@xxx.xxx.x.x:1521/orcl> show user USER is "SYS" --//当前时sys用户. SYS@xxx.xxx.x.x:1521/orcl> @ spid SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50 ---- ---------- ------- ------------------ ------ ------- ---------- -------------------------------------------------- 38 37824 54481 DEDICATED 17224 64 132996 alter system kill session '38,37824' immediate; --//打开另外会话： --//session 2: TTT@xxx.xxx.x.x:1521/orcl> select SECONDS_IN_WAIT,event from v$session where sid=38 ; SECONDS_IN_WAIT EVENT --------------- ---------------------------------------- 133 SQL*Net message from client --//回到原来会话： --//session 1: SYS@xxx.xxx.x.x:1521/orcl> select sysdate from dual; SYSDATE ------------------- 2022-02-28 17:22:53 --//可以发现对于sys用户该参数max_idle_time无效。 3.换一种方式测试: --//session 1: TTT@xxx.xxx.x.x:1521/orcl> @ spid SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50 --- ---------- ------- ------------------ ------ ------- ---------- -------------------------------------------------- 291 35911 59525 DEDICATED 18855 62 30182 alter system kill session '291,35911' immediate; --//session 2: $ seq 200 | xargs -IQ echo -e 'set head off verify off \nselect SECONDS_IN_WAIT,SERIAL#,event from v$session where sid=&1 ;\nhost sleep 1\n'| sqlplus -s -l ttt/oracle@xxx.xxx.x.x:1521/orcl @ ver1 291 172 SQL*Net message from client no rows selected 151 44251 SQL*Net message from client no rows selected 144 33075 SQL*Net message from client no rows selected 154 5619 SQL*Net message from client no rows selected 137 45124 SQL*Net message from client no rows selected 169 46894 SQL*Net message from client no rows selected --//可以发现并不是max_idle_time*2. --//我设置alter system set max_idle_time=3 scope=memory;重复测试仅仅记录结果。 298 265 SQL*Net message from client 298 265 SQL*Net message from client 294 29591 SQL*Net message from client 244 44065 SQL*Net message from client 259 38284 SQL*Net message from client --//不知道为什么仅仅做一个记录吧。