[20220228]測試Max_idle_blocker_time 引數.txt

[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
--//不知道為什麼僅僅做一個記錄吧。