We had a situation where an issue in the application was causing a large number of threads to be spawned from Apache/Tomcat. These threads would persist in an inactive status until we hit the max processes limit and then impact new threads.
As a quick fix, we changed the user’s profile to set the IDLE_TIME at 60 minutes. We did see the sessions being snipped after the IDLE_TIME threshold was reached:
USERNAME STATUS COUNT(*) ------------------------------ -------- ---------- XYZ INACTIVE 10 PRDUSR ACTIVE 1 PRDUSR INACTIVE 44 PRDUSR SNIPED 20 SYS ACTIVE 1 SYS INACTIVE 1 ACTIVE 35
However, PMON was not actually killing the sessions resulting in us continuing to hit max processes. Oracle support suggested that this was due to “Bug 16494960 – killed session not being cleaned up (Doc ID 16494960.8)”.
Adding the keyword “immediate” resolved this as shown below:
alter system kill session '443,16740' immediate;
Since we tended to hit max processes in the early hours of the morning, I created the below script to monitor, report on and kill the sessions in an ‘INACTIVE’,’SNIPED’,’KILLED’ status:
#!/bin/ksh cd /dba_scripts export ORACLE_HOME=/oracle_home/12.1.0/ export PATH=$PATH:/oracle_home/12.1.0/bin export ORACLE_SID=proddb1 sqlplus -s / as sysdba << EOF SET NEWPAGE NONE set linesize 100 SET HEADING ON spool /dba_scripts/dean_info_before.txt select username, status, count(*) from gv\$session group by username, status order by username, status ; select username, status, last_call_et, sid, serial# from v\$session where username = 'PRDUSR' and status in ('INACTIVE','SNIPED','KILLED') and last_call_et > 6000 order by sid, serial# ; spool off SET HEADING OFF spool /dba_scripts/dean_kills.sql select 'alter system kill session '''||sid||','||serial#||''' immediate;' as script from v\$session where username='PRDUSR' and status in ('INACTIVE','SNIPED','KILLED') and last_call_et > 6000 order by sid, serial# ; spool off SET HEADING ON spool /dba_scripts/dean_killed.log set echo on set feedback on @@/dba_scripts/dean_kills.sql spool off spool /dba_scripts/dean_info_after.txt select username, status, count(*) from gv\$session group by username, status order by username, status ; spool off exit EOF echo 'Status before kills' > dean_report.txt echo '-------------------' >> dean_report.txt echo ' ' >> dean_report.txt cat /dba_scripts/dean_info_before.txt >> dean_report.txt echo ' ' >> dean_report.txt echo ' ' >> dean_report.txt echo ' ' >> dean_report.txt echo 'Sessions identified for kills' >> dean_report.txt echo '-----------------------------' >> dean_report.txt echo ' ' >> dean_report.txt cat /dba_scripts/dean_kills.sql >> dean_report.txt echo ' ' >> dean_report.txt echo ' ' >> dean_report.txt echo ' ' >> dean_report.txt echo 'Kill output' >> dean_report.txt echo '-----------' >> dean_report.txt echo ' ' >> dean_report.txt cat /dba_scripts/dean_killed.log >> dean_report.txt echo ' ' >> dean_report.txt echo ' ' >> dean_report.txt echo ' ' >> dean_report.txt echo 'Status after kills' >> dean_report.txt echo '-------------------' >> dean_report.txt echo ' ' >> dean_report.txt cat /dba_scripts/dean_info_after.txt >> dean_report.txt mail -s "Kills from proddb1" "My-email-id@mydomain.com" < /dba_scripts/dean_report.txt
This was then scheduled in cron as follows:
# ## Oct 04, 2019 - Dean Capps - Start ## This scripts kills threads to stay under the 900 processes limit # 00 00,12 * * * * cd /dba_scripts; ksh ./dean_kills.ksh > /dba_scripts/dean_kills.ksh.cron.out 2>&1 # ## Oct 04, 2019 - Dean Capps - End #
The output will be as follows:
Status before kills ------------------- USERNAME STATUS COUNT(*) ------------------------------ -------- ---------- XYZ INACTIVE 10 PRDUSR ACTIVE 1 PRDUSR INACTIVE 44 PRDUSR SNIPED 20 SYS ACTIVE 1 SYS INACTIVE 1 ACTIVE 35 7 rows selected. USERNAME STATUS LAST_CALL_ET SID SERIAL# ------------------------------ -------- ------------ ---------- ---------- PRDUSR SNIPED 6199 443 16740 PRDUSR SNIPED 6199 556 37500 PRDUSR SNIPED 6199 615 17620 PRDUSR SNIPED 6199 670 46290 Sessions identified for kills ----------------------------- alter system kill session '443,16740' immediate; alter system kill session '556,37500' immediate; alter system kill session '615,17620' immediate; alter system kill session '670,46290' immediate; Kill output ----------- System altered. System altered. System altered. System altered. Status after kills ------------------- USERNAME STATUS COUNT(*) ------------------------------ -------- ---------- XYZ INACTIVE 10 PRDUSR ACTIVE 2 PRDUSR INACTIVE 43 PRDUSR KILLED 1 PRDUSR SNIPED 16 SYS ACTIVE 1 SYS INACTIVE 1 ACTIVE 35 8 rows selected.