Node-oracledb: How to handle invalid connections in the pool (e.g. ORA-02396: exceeded maximum idle time)?

[kevinhaeni]

After reading the #connpoolpinging section of the documentation, I was under the impression that node-oracledb is checking the validity of a connection obtained by connectionPool.getConnection(), before it is returned to the application.

We are using oracle client 12.1, so according to the docs, an internal pinging should be performed and invalid connections should be dropped.

Unfortunately, we are often getting a "ORA-02396: exceeded maximum idle time" exception when using .getConnection().execute(...).

While this behaviour can be solved by setting IDLE_TIME to unlimited on the database, I'm worried about other things that could cause connections to be invalid, like network outages.

• Why is the internal pinging not detecting connection idle timeouts? (note that we are using 2.1.1 at the moment. Was this ping check implemented in a more recent version?)
• How does one properly check the connection and handle errors in my application that uses connectionPool.getConnection()?

[cjbj]

My general best practice is:

• If using a user profile, set IDLE_TIME to unlimited
• keep poolMin the same as poolMax
• use Oracle Client 12.2, which does the lightweight ping for network drop outs. Note some errors like ORA-02396 won't be detected by this - effectively your DBA is trying to send you a message.
• have appropriate error handling after execute(), since network or DB failures can occur at anytime. Also you may want to trap application specific PL/SQL errors and handle them differently.

The ping behavior hasn't changed since your version. The doc for your version is https://github.com/oracle/node-oracledb/blob/v2.1.1/doc/api.md

I think we could make node-oracledb nicer for 'mis-configured' systems (e.g. with IDLE_TIME enabled) at the expense of performance by re-enabling poolPingInterval for 12.2 clients, since the heavyweight ping will detect errors like ORA-02396. Debatably I would not have it enabled by default. Comments welcome.

Finally, node-oracledb currently doesn't have TG support, which would be another layer of niceness for high availability.

[kevinhaeni]

Thank you for your advice, cjbj.

For clarification: with disabling IDLE_TIME, you mean it is best practice to set it to unlimited, so the connections do not time out, right?

And do you know if I'd also gain the advantages of the improved health checks when using the oracle client 12.2 with an oracle server 12.1?

[cjbj]

@kevinhaeni yes, set IDLE_TIME to unlimited. [I'll edit the list item in my earlier comment and clarify it]. The always-enabled, lightweight ping will work with 12.2 client libraries against any DB version.

If you speak to Oracle's Real World Performance group their recommendation (this link is in the node-oracledb doc Connection Pooling section) is to keep pools at fixed sizes. The common problems they see are database servers being slammed and becoming unstable when pools grow in conjunction with users starting to do more work. So it doesn't make general sense to expire sessions with IDLE_TIME. The sessions should be running and available to do work.

This is a general recommendation. There could be special cases for particular business cases.

What sort of load are you planning for?

[kevinhaeni]

@cjbj Thank you for your help!

I would say we are excepting a pretty consistent load of maybe 10-20 concurrent users in average. Users typically execute smaller queries (in terms of bandwidth / duration) but they may be numerous...a typical webservice call will consist of several small database queries.

[cjbj]

@kevinhaeni Sounds like a good system. Don't forget to bump UV_THREADPOOL_SIZE if you increase your connection pool size.

What feature(s) do you need from node-oracledb?

[kevinhaeni]

Thank you, @cjbj . To be honest, I didn't come across any feature that I would be missing so far. Great library!

[cjbj]

Thanks @kevinhaeni

[cjbj]

@kevinhaeni thanks for the comments.