search

oracle / dotnet-db-samples

.NET code samples for Oracle database developers #OracleDotNet
http://otn.oracle.com/dotnet
MIT License
413 stars 191 forks source link

Pooling changes between 21.10.0 and 21.14.0 #375

Closed rauhs closed 5 months ago

rauhs commented 5 months ago

Hi,

our company ran into a connection pool issues with 21.10.0. In particular our customers reported a big increase in connections (we have 100+ clients per Oracle database) and our customers had problems with the session limit and RAM usage. Since we did increased parallelism in our software release we thought it was our fault. We saw up to 40 new connections for something where we only allowed 2(!) parallel SQL queries at a time. We ruled out any connection leaks or connection or connection reclaims. We also did play with various connection pool parameters. None of which improved the big increase in connections.

One of the first checks was to go through the release notes of the ODP nuget (past as well as newer versions) so see if anything changed, but we saw nothing that would warrant any changes/fixes.

We eventually upgraded to 21.14.0 which massively reduced the connections.

My actual question: Which item in the release notes changed the behavior (or fixed?) of the connection pool?

A full text search on "Connection" or "poo" in the release notes only shows:

Bug 35335650 - PERFORMANCE ISSUE WITH CONNECTION OPEN/CLOSE WITH LOAD
Bug 35606793 - PERFORMANCE ISSUES WHEN USING "USER ID=/" IN THE CONNECTION STRING
Bug 35778668 - HANG OBSERVED WHEN CALLING ORACLECONNECTION.STATE WHILE SQLS ARE EXECUTED USING THE SAME CONNECTION
Bug 35999664 - NULLREFERENCEEXCEPTION WHEN CREATING LOT OF CONNECTIONS IN PARALLEL WITH DIFFERENT PROXY USERS

But none of these seem to indicate a bug fix with unnecessary connections being opened by the driver.

I'm just curious at this point. Thanks for any pointers.

alexkeh commented 5 months ago

It could be related to bug 36223397, which resolved two cursor leaks and could have been keeping more connections open than necessary.

It's very hard to identify which bug fix resolved your issue. A year passed between when 21.10 and 21.14 were released. The identified bugs fixed in the NuGet READMEs only cover known customer reported issues. They do not cover bugs Oracle finds in its own testing.

rauhs commented 5 months ago

It's very hard to identify which bug fix resolved your issue

Yes I understand that my post was probably not enough information. Sorry.

The identified bugs fixed in the NuGet READMEs only cover known customer reported issues. They do not cover bugs Oracle finds in its own testing.

Should this be improved on Oracle's side? Having all (even minor) changes would probably still have some value. It would have prompted us to try the upgrade much earlier.

Anyways, thanks for the answer. Feel free to close this issue.

alexkeh commented 5 months ago

The identified bugs fixed in the NuGet READMEs only cover known customer reported issues. They do not cover bugs Oracle finds in its own testing.

Should this be improved on Oracle's side? Having all (even minor) changes would probably still have some value. It would have prompted us to try the upgrade much earlier.

Anyways, thanks for the answer. Feel free to close this issue.

We considered this, but we didn't t think the effort was worth the value to customers.

Oracle's .NET testing team finds bugs during the ODP.NET development process. The vast majority are issues that never made it into a public software release. These are bugs that appear as new features are developed or regressions. They are fixed before releasing the next ODP.NET version.

It's possible these bugs appeared in a past release. Identifying whether that occurred does require some effort. It's not a big effort for a handful of bugs. If we need to do it for every bug fixed, then the effort becomes considerable.

We then weigh whether the time spent on identifying all these bugs for customers is worth the effort. It's generally not as customers prefer we develop more new features, fix more bugs, or improve quality through creating and running more tests.

The other challenge is that most of these internally found bug numbers our customers know nothing about. We identify the bugs with a number and a title in the NuGet README. The number is only useful if Oracle has told a customer to look for a bug number in the release notes so that they know which release the fix is in. The title can be of limited use as you've experienced unless the bug is a straightforward cause and effect issue.