Greetings,
I am facing an issue whereby I am hitting the threshold of number of processes on the database.
A C# service does a database lookup to get a bunch of records (3000) to process.
This process which occurs outside the database involves building a long string that it then inserted back into the database. This inserts happen one at a time, with parallel threading controlled by this system (worker threads).
Some background: the string is a base64 representation of a PDF image.
The process is multi threaded to get through the load and performance is acceptable (could always be better).
The PROBLEM:
The sessions are established, do the insert, remain inactive for a short while and then drop off.
However, there are a relatively large number of processes that hang around until such time we hit the database limit of 1100.
Sometimes the processes drop off although I cant determine at what regularity and why some hang around indefinitely.
I am not that familiar with the driver used (see below). There might be some configuration that is or is not being set correctly - I would greatly appreciate any advice.
Database info:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production
TNS for IBM/AIX RISC System/6000: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
Some info from a trace file regarding the connection:
*** 2019-10-11T10:10:46.821294+02:00
*** SESSION ID:(154.54349) 2019-10-11T10:10:46.821392+02:00
*** CLIENT ID:() 2019-10-11T10:10:46.821403+02:00
*** SERVICE NAME:(SYS$USERS) 2019-10-11T10:10:46.821415+02:00
*** MODULE NAME:(w3wp.exe) 2019-10-11T10:10:46.821427+02:00
*** ACTION NAME:() 2019-10-11T10:10:46.821439+02:00
*** CLIENT DRIVER:(ODPM.NET : 12.2.0.1.0) 2019-10-11T10:10:46.821448+02:00:
This is an example of the OS process that hangs around indefinitely - there are 100's of them:
oracle 50069858 1 0 16:00:08 - 0:00 oracletrurprd (LOCAL=NO)
kind regards.