12c,19c自动kill长时间未活动会话特性

Oracle 12c 19c Automatic terminal/kill session feature

 

转载自ANBOB ACE大神https://www.anbob.com/archives/5837.html

数据库会话同样会占用数据库资源，如客户端异常断开在客户端成为一个dead session会永远存在，如果客户端没有断开也没有活动就是一个idle session， 如果这个idle session做了一些修改未提交，然后下班或去吃饭、上WC、开会等，这时就会堵塞其他人对相同的数据做修改，这类会话可以叫做idle blocker session. 在自治数据库的时代这些session 都可以被释放或者kill / teminal 终结掉，下面对不同的session如何被释放

A， 对于dead session

Dead connection detection（DCD）是Oracle网络功能，主要用于客户端关闭其系统电源或客户端计算机意外崩溃而又无法正常关闭Oracle数据库连接的环境。

如果客户端计算机因正确断开会话而突然崩溃，则这些会话锁定的资源将继续被锁定，从而导致环境性能问题。由于这种情况可以在任何环境中发生，因此Oracle网络服务提供了DCD的功能，可以在早期检测到这种情况，从而可以快速恢复锁定的资源。

为了解决这种情况并检测死连接，Oracle在网络会话（12c中的tcp）层引入了一个新概念。服务器进程将SQL * Net Probe数据包发送到客户端，以检查在sqlnet.expire_time参数指定的每个固定时间间隔内连接是否仍然可用。如果通过探测数据包的通信失败，则会返回错误，导致服务器进程退出。

如何启用DCD

要在您的环境中启用DCD。在sqlnet.ora文件中设置SQLNET.EXPIRE_TIME参数，然后重新启动侦听器或重新装入侦听器。

其中n是时间间隔（以分钟为单位），在此间隔之后发送DCD的探测数据包。

1. For pre-9.2.0.4 oracle clients you can actually see the null packet of 10 bytes been received every x minutes after this time has elapsed.

2. Oracle Clients version > 9.2.0.4 the DCD packet is sent to the client from the respective database server. The null packets are actually written to the socket , but they are read only when the client becomes active.

3. versions 12.1 and newer The new method of DCD uses the TCP KEEPALIVE socket option.

There are 3 parameters associated with an operating system’s TCP keepalive (These are Linux parameters, but other operating system have similarly named parameters)

TCP_KEEPALIVE_TIME (the amount of time until the first keepalive packet is sent)
TCP_KEEPCNT(the number of probes to send)
TCP_KEEPINTVL (the interval between keepalive packets)

The sqlnet.ora parameter SQLNET.EXPIRE_TIME now sets the socket option TCP_KEEPALIVE.

If sqlnet.expire_time=1 then TCP_KEEPALIVE will be set to 60 seconds.  The other parameters KEEPINTVL and KEEPCNT get set to 6 and 10 respectively (which is very reasonable).  This means that, at minimum, DCD can only be set to 2 minutes.

B. 对于 idel session

MAX_IDLE_TIME指定会话可以空闲的最大分钟数。 此后会话将自动终止。12.2 引入的新参数。这参数是整个db或CDB级，不可以alter session.  单位分钟,超过idle上限时间后会收到 ORA-03113错误。

SQL> @i

USERNAME             INST_NAME            HOST_NAME                  I# SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR            PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS                  PDB1-anbob19c        oel7db1                     1 390   34170    19.0.0.0.0 20200611 3874       33    3783            000000006AC81028 000000006B9069A8

SQL> alter system set max_idle_time=1;
SQL> @pd max_id
Show all parameters and session values from x$ksppi/x$ksppcv...

       NUM N_HEX NAME                                                     VALUE                          DESCRIPTION
---------- ----- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
       480   1E0 max_idle_time                                            1                              maximum session idle time in minutes
       481   1E1 max_idle_blocker_time                                    0                              maximum idle time for a blocking session in minutes


SQL> set time on
19:46:51 SQL>
19:46:51 SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4298
Session ID: 449 Serial number: 3375

19:49:37 SQL>

C.  对于idle blocker session

如果一个idle session阻塞了其他会话，在19c 20c中可以使用参数max_idle_blocker_time 自动终止blocker, 当会话持有其他会话所需的资源时，该会话被视为阻塞会话. 如

1. 该会话持有另一个会话所需的锁。
2。 该会话是并行操作，并且其使用者组，PDB或数据库已达到其最大并行服务器限制或已排队的并行操作。
3.会话的PDB或数据库实例即将达到其SESSIONS或PROCESSES限制。
此参数与MAX_IDLE_TIME参数的不同之处在于，MAX_IDLE_TIME适用于所有会话（阻塞和非阻塞），而MAX_IDLE_BLOCKING_TIME仅适用于阻塞会话。  因此，为了使MAX_IDLE_BLOCKING_TIME有效，其限制必须小于MAX_IDLE_TIME限制。

SQL> @pd max_id
Show all parameters and session values from x$ksppi/x$ksppcv...

       NUM N_HEX NAME                                                     VALUE                          DESCRIPTION
---------- ----- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
       480   1E0 max_idle_time                                            0                              maximum session idle time in minutes
       481   1E1 max_idle_blocker_time                                    1                              maximum idle time for a blocking session in minutes

-- connect new session1
USERNAME             INST_NAME            HOST_NAME                  I# SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR            PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
ANBOB                PDB1-anbob19c        oel7db1                     1 456   25738    19.0.0.0.0 20200611 5466       52    5464            000000006AD259F8 000000006B91FFC8


SQL> set sqlp session1
session1set sqlp session1>
session1>
session1>create table t1(id int);
Table created.

session1>insert into t1 values(1);
1 row created.

session1>commit;
Commit complete.

session1>update t1 set id=10 where id=1;
1 row updated.

session1>

-- connect new session2
USERNAME             INST_NAME            HOST_NAME                  I# SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR            PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
ANBOB                PDB1-anbob19c        oel7db1                     1 465   44769    19.0.0.0.0 20200611 5605       54    5603            000000006AD3C120 000000006B922A88


SQL> set sqlp session2>
session2>delete t where id=1;

0 rows deleted.

session2>delete t1 where id=1;
-- hang 

-- session 3
SQL> select * from dba_blockers;

HOLDING_SESSION     CON_ID
--------------- ----------
            456          3

SQL> select * from dba_waiters;

WAITING_SESSION WAITING_CON_ID HOLDING_SESSION HOLDING_CON_ID LOCK_TYPE                  MODE_HELD                                MODE_REQUESTED                             LOCK_ID1   LOCK_ID2
--------------- -------------- --------------- -------------- -------------------------- ---------------------------------------- ---------------------------------------- ---------- ----------
            465              3             456              3 Transaction                Exclusive                                Exclusive                                    589824        893


-- One minute later

# session 2 feedback delete complated

session2>delete t1 where id=1;
1 row deleted.

# session 1
session1>
session1>select * from t1;
select * from t1
       *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 5466
Session ID: 456 Serial number: 25738

当然可以使用resource manager 精准指定组或用户群配置idle session或idle blocker session.

— over