[NiFi][Oracle][DBConnectionPool] NiFi opens sessions but does not close them, leaving them inactive

[#1]

Hello,

I am trying to solve a strange behavior of NiFi but can't really figure it out.

My flow consists of a GenerateTableFetch (which gets executed once per day), a ExecuteSQLRecord (which gets executed once it receives the files from GenerateTableFetch) and finally an PutS3Bucket.
Now, both the GenerateTableFetch and the ExecuteSQLRecord are using the same Controller Service, a DBConnectionPool which connects to an Oracle Database.
In the previous days I have noticed that I get plenty of sessions from NiFi (which is ok) but these sessions never get killed/terminated and they remain inactive. I am using the following configurations within my DBConnectionPool Service:
Max Wait Time: 500 millis

Max Total Connections: 51

Minimum Idle Connections: 0

Max Idle Connections: 5

Max Connection Lifetime: -1

Time Between Eviction Runs: -1

Minimum Evictable Idle Time: 30 mins

Soft Minimum Evictable Idle Time: -1

Unfortunately I was not able to find any information in the documentation (or maybe I did not fully understand it) therefor here is my question:
How can I make sure that all the sessions are create are getting closed once my flow has ended? I mean get killed and not set to inactive.

Thank you 🙂

[#2]

Hi
Am also facing same issue,Have you got any solutions here

[#3 · Accepted solution]

hi @Satya1,

Yes I did. Eventually i was able to identify what caused the issue. I am not 100% sure that this is the best fix for the problem, but in order to solve my issue I have modified the following two properties within my DBCPConnectionPool Controller Service:
Max Total Connections: I have reduced the value because I saw that I did not require so many connections.
previously: 51
now: 10

Time Between Eviction Runs: - I have modified the value from a negative value to a positive value.
previously: -1 (meaning no idle connection evictor thread will be run)
now: 1440000 millis (meaning that my sessions will get evicted after aprox. 25 mins)

Let me know if this helped you as well 🙂

[#4]

No,have confgured the Time Between Eviction Runs: positive value- (20 mins) but still seeing the inactive sessions in the database.
Hi @cotopaul 
Are those inactive session terminated after your flow is completed or after 25 mins 

[#5]

hi @Satya1,

The inactive sessions are getting terminated after 25 mins after the extraction is complete. For example, if you extraction takes 25 minutes to load the data from your database and into the flowfile (no matter the file type), you will keep on seeing those inactive sessions for another 25 more minutes, meaning that they will be completely gone after 50 mins, since you started your flow.

If you want, you can reduce the time to a lower value, as 25 is a bit big for most cases. You can modify it into 5 minutes and test it for yourself. I did it and my flow took 2 minutes, the sessions got evicted after 5 and in 7 minutes all the sessions were gone.