Inactive ISG Sessions Not Closing In The Database From OAFM (Doc ID 2626392.1)

Last updated on AUGUST 23, 2023

Applies to:

Symptoms

ACTUAL BEHAVIOR
Inactive Integrated SOA Gateway (ISG) sessions (from OAFM) not closed in the database.

Encountered an odd behavior where significant amount of database sessions are coming from node dedicated to ISG services (mostly OAFM services for ISG are running there).

~~~
SQL> select a.inst_id, cnt "CURRENT", to_number(value) limit, round(cnt/value*100) usage_ptc
  from (select inst_id,count(1) cnt from gv$process group by inst_id) a
  join gv$parameter b
  on a.inst_id = b.inst_id
where b.name = 'processes';

  INST_ID CURRENT LIMIT USAGE_PTC
---------- ---------- ---------- ----------
  1 3083 4000 77
  2 3160 4000 79

SQL> select *
  from (select count(*), status, machine
  from gv$session group by status, machine
  order by 1 desc
  )
where rownum <= 10 and MACHINE like 'name=<NAME>%';

  COUNT(*) STATUS MACHINE
---------- -------- ------------------------------
  5551 INACTIVE name=<NAME>.com

~~~

Where name=<NAME>.xyz is dedicated ISG node.

Mentioned sessions are mostly spawned during some significant processing, but they are not being released/killed afterwards.  Sometimes it even cause significant issues to concurrent processing due to amount of database sessions being close to limit.

EXPECTED BEHAVIOR
Connections should be closed or released after use and not sit as inactive.

The issue can be reproduced at will with the following steps:
1. Use of the ISG product.

The issue has the following business impact:
Due to this issue, too many sessions in the database exist thatleads to performance problems.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.