当前位置:网站首页>Too many sessions, too many sessions to clean up

Too many sessions, too many sessions to clean up

2020-11-10 15:38:15 Green tea is a little sweet

1.1 The phenomenon

There is a set of 11.2.0.4 RAC 2 node , The database exists 5000 Number of conversations , among active Ongoing session 500 individual , The rest are inactive conversations .

A lot of inactive Talk too much to Oracle What kind of impact does the database have ?

     [ Active indicates that there is a lot of concurrency in the database , Under normal circumstances, the business load can only be disassembled , Routine can't optimize routines 】

1) Memory consumption , Each session corresponds to a database pga process You need to allocate a separate area of memory , And the more cursors the session keeps , The more memory is consumed ;

2) cpu resource consumption , Each session corresponds to an operating system Oracle User process , Although a lot of them belong to inactive But for the operating system, it's still a process running ;

 

1.2 Related parameters

SESSIONS
Default value    Derived: (1.5 * PROCESSES) + 22
Range of values     1 to 216 (which is 1 to 65536)
SESSIONS specifies the maximum number of sessions that can be created in the system. Because every login requires a session, this parameter effectively determines
the maximum number of concurrent users in the system. You should always set this parameter explicitly to a value equivalent to your estimate of the maximum number of concurrent users,
plus the number of background processes, plus approximately 10% for recursive sessions. PROCESSES Default value 100 Range of values 6 to operating system dependent PROCESSES specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks,
job queue processes, and parallel execution processes. # vi
/etc/security/limits.conf oracle soft nproc 32 oracle hard nproc 64 #ulimit -a

 

1.3 methodology

 state 
1. Active conversations are basically impossible to handle ,Oracle Database split [ Extreme cases such as : Abnormal situations such as conversation squeeze , After handling database exceptions , Active conversation tends to be stable 】;
2. Inactive sessions can be cleaned up regularly 

 Reference documents 
https://www.anbob.com/archives/5837.html


 Inactive conversation methodology :
A:dead session
Dead connection detection(DCD) yes Oracle Network function , It is mainly used for the client to turn off its system power or the client computer crashes unexpectedly and cannot shut down normally Oracle Database connection environment .
 If the client computer crashes suddenly due to properly disconnected session , Then the resources locked by these sessions will continue to be locked , This leads to environmental performance problems . Because this can happen in any environment , therefore Oracle Web services provide DCD The function of , This can be detected early , This allows you to quickly recover locked resources .
 To solve this situation and detect dead connections ,Oracle In a network conversation (12c Medium tcp) Layer introduces a new concept . The server process will SQL * Net Probe Packets are sent to the client , To check in sqlnet.expire_time【 Company : minute 】 Whether the connection is still available for each fixed time interval specified by the .
If communication by probing packets fails , It will return an error , Causes the server process to exit . SQLNET.EXPIRE_TIME
= 10 B:idel session 1.WAS Such as middleware or program software , There is an automatic disconnection setting when the session does not work for a certain period of time ; 2.MAX_IDLE_TIME Specifies the maximum number of minutes a session can be idle . After that, the session will automatically terminate .12.2 New parameters introduced . This parameter is the whole db or CDB level , Can not be alter session. Units of minutes , exceed idle You'll get it after the time limit ORA-03113 error . 3. To write plsql Script , Do it regularly [ As detailed below ] C:idle blocker session 1. At present, the routine is basically DBA Human participation in processing ; 2. If one idle session Blocking other conversations , stay 19c 20c You can use parameters in max_idle_blocker_time Automatic termination blocker, When a session holds the resources needed by another session , This session is considered a blocking session . 1) Such as This session holds a lock required by another session . 2) The session is a parallel operation , And its user group ,PDB Or the database has reached its maximum parallel server limit or queued parallel operations . 3) The session PDB Or the database instance is about to reach its SESSIONS or PROCESSES Limit . This parameter is similar to MAX_IDLE_TIME The difference in parameters is ,MAX_IDLE_TIME For all conversations ( Blocking and non-blocking ), and MAX_IDLE_BLOCKING_TIME Only for blocked sessions . therefore , In order to make MAX_IDLE_BLOCKING_TIME It works , The limit must be less than MAX_IDLE_TIME Limit .

1.4 Simulate manual processing

 Operating system concurrency 20 Log in , Use 1000 Concurrent test host hang
#!/bin/bash i=1 while (($i<=20)) do sqlplus -S scott/tiger & let "i++" echo $1 done
Test environment process 400 When the upper limit is reached, an error is reported
ERROR: ORA
-00020: maximum number of processes (400) exceeded Thu Oct 22 02:24:15 2020 ORA-00020: maximum number of processes (400) exceeded ORA-20 errors will not be written to the alert log for the next minute. Please look at trace files to see all the ORA-20 errors. Process m000 submission failed with error = 20 Process m000 submission failed with error = 20 Process m000 submission failed with error = 20 Process m000 submission failed with error = 20 Thu Oct 22 02:24:18 2020
DB What view records the upper limit of historical login status ???
SQL> SELECT * FROM GV$RESOURCE_LIMIT WHERE RESOURCE_NAME='sessions' or RESOURCE_NAME='processes';
Manual method output KILL sentence SELECT STATUS,COUNT(
*) FROM GV$SESSION GROUP BY STATUS; select username,round(LAST_CALL_ET/3600) as "HH24",count(*) from gv$session where status='INACTIVE' group by username,round(LAST_CALL_ET/3600) order by 2,3,1; select 'ALTER SYSTEM KILL session '''||sid||','||serial#||''' IMMEDIATE;' AS "KILL_SQL" from v$session where status='INACTIVE' AND LAST_CALL_ET>600 AND USERNAME='CC' union select 'ALTER SYSTEM KILL session '''||sid||','||serial#||''' IMMEDIATE;' AS "KILL_SQL" from v$session where status='INACTIVE' AND LAST_CALL_ET>36000 AND USERNAME='CB';

plsql perform kill inactive session SET SERVEROUTPUT ON DECLARE P_SID NUMBER; P_SERIAL NUMBER; P_STATUS VARCHAR2(
32); KILL_SQLTEXT VARCHAR2(4000); CURSOR CUR_APP_ID IS SELECT SID, SERIAL# FROM V$SESSION WHERE STATUS = 'INACTIVE' AND USERNAME IN('SCOTT') AND STATUS = 'INACTIVE' AND LAST_CALL_ET >6 ; BEGIN OPEN CUR_APP_ID; LOOP FETCH CUR_APP_ID INTO P_SID,P_SERIAL; EXIT WHEN CUR_APP_ID%NOTFOUND; SELECT STATUS INTO P_STATUS FROM V$SESSION WHERE SID = P_SID AND SERIAL# = P_SERIAL AND USERNAME IN('SCOTT') AND STATUS = 'INACTIVE' AND LAST_CALL_ET >6 ; IF P_STATUS = 'INACTIVE' THEN KILL_SQLTEXT := 'ALTER SYSTEM KILL SESSION '||CHR(39)||P_SID || ',' || P_SERIAL ||CHR(39)||' IMMEDIATE'; EXECUTE IMMEDIATE KILL_SQLTEXT; DBMS_OUTPUT.PUT_LINE(KILL_SQLTEXT); END IF; END LOOP; CLOSE CUR_APP_ID; END; /

 

1.5 Configure scheduled tasks

! about RAC 2 node , Each node needs to be configured with this script , It is suggested that the time should be staggered by a certain time , for example 30 minute .  Through actual execution, it is found that kill 1300 inactive session Call time 3 About minutes .
[oracle@test script]$ chmod +x kill_inactive_session.sh [oracle@test script]$ cat kill_inactive_session.sh #!/bin/bash export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1/ export ORACLE_SID=tt export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin sqlplus / as sysdba <<EOF spool /home/oracle/script/kill_session.log select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as "date" from dual; SET SERVEROUTPUT ON DECLARE P_SID NUMBER; P_SERIAL NUMBER; P_STATUS VARCHAR2(32); KILL_SQLTEXT VARCHAR2(4000); CURSOR CUR_APP_ID IS SELECT SID, SERIAL# FROM V\$SESSION WHERE STATUS = 'INACTIVE' AND USERNAME IN('SCOTT') AND STATUS = 'INACTIVE' AND LAST_CALL_ET >6 ; BEGIN OPEN CUR_APP_ID; LOOP FETCH CUR_APP_ID INTO P_SID,P_SERIAL; EXIT WHEN CUR_APP_ID%NOTFOUND; SELECT STATUS INTO P_STATUS FROM V\$SESSION WHERE SID = P_SID AND SERIAL# = P_SERIAL AND USERNAME IN('SCOTT') AND STATUS = 'INACTIVE' AND LAST_CALL_ET >6 ; IF P_STATUS = 'INACTIVE' THEN KILL_SQLTEXT := 'ALTER SYSTEM KILL SESSION '||CHR(39)||P_SID || ',' || P_SERIAL ||CHR(39)||' IMMEDIATE'; EXECUTE IMMEDIATE KILL_SQLTEXT; DBMS_OUTPUT.PUT_LINE(KILL_SQLTEXT); END IF; END LOOP; CLOSE CUR_APP_ID; END; / exit; EOF [oracle@test script]$ crontab -l 01 07,21 * * * sh /home/oracle/script/kill_inactive_session.sh & # tail -200f //var/log/cron Oct 22 07:00:01 test crond[4834]: (oracle) CMD (sh /home/oracle/script/kill_inactive_session.sh &)

 

版权声明
本文为[Green tea is a little sweet]所创,转载请带上原文链接,感谢