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.
