Inactive sessions caused by data_engine probe building to Oracle limit after 9.02 upgrade

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

After upgrading to 9.02 we noticed inactive Oracle sessions building due to the data_engine probe. 

Environment

Release : 9.).2 

Component : UIM - DATA_ENGINE

Cause

In UIM 9.0.2 the data_engine was enahced for multi-threading so it reuses sessions instead of closing them. 

Oracle settings should allow for inactive sessions to expire:
Sessions= 1240
Processes=800
open_cursors= 1500
SQLNET.EXPIRE_TIME = 10

But still observed the inactive sessions because DCD will not kill any inactive sessions if they are held by a client.

There is a DAL_SCHEMA_PROFILE for XIM user of UIM with the resource IDLE_TIME set to unlimited. 
example:
select * from dba_profiles where profile='DAL_SCHEMA_PROFILE' and resource_name in ('IDLET_TIME','CONNECT_TIME);

Resolution

Recommendation is to:
1 increase the session to 1800 or approximately double the number of QoS being monitored. 
2 set the idle_time to 30 or 60 minutes
example:
alter profile DAL_SCHEMA_PROFILE limit idle_time 30 

Schedule this script to clean up the idle sessions. 
#!/usr/bin/ksh
export ORACLE_HOME=/prd/ximop01/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=ximop01
export LOG_PATH=/opt/mit/scripts/QOS/db_scripts/log
rm -f $LOG_PATH/snip_kill.sql
 
sqlplus -s /nolog << EOF
connect / as sysdba
select count(*) from v\$session where status='SNIPED';
spool $LOG_PATH/session_list.log append
set lines 200 pages 1500 long 99999999
alter session set nls_date_format='DD-MON-YYYY HH24:MI';
select sysdate from dual;
select sid,serial#,event,sql_id,last_call_et,username,status,machine,logon_time,process,substr(module,1,15) module from v\$session where status='SNIPED';
spool off;
set head off;
set feed off;
set pages 0;
spool $LOG_PATH/snip_kill.sql
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v\$session where status='SNIPED' ;
spool off;
@"$LOG_PATH/snip_kill.sql"
exit;
!
date