Is max_lifetime_session supported in thin mode or not?

[Jaza]

I'm using python-oracledb in thin mode with AsyncConnectionPool .

In the doc API: ConnectionPool Objects it doesn't specify whether or not max_lifetime_session is supported in thin mode.

In the doc Connecting to Oracle Database it says that max_lifetime_session is only supported in Thick mode.

In the doc API: AsyncConnectionPool Objects it says that AsyncConnectionPool is only supported in thin mode, and it lists max_lifetime_session as a supported attribute.

From looking at the thin pool source code it appears that it has getter and setter methods for max_lifetime_session (I assume just to satisfy the interface that's expected of a "pool impl"), but it's not actually using self._max_lifetime_session anywhere except in those getter and setter methods.

So, am I right in concluding that max_lifetime_session has no effect if a value is set for it in thin mode?

I've received a report, for one of my Python apps using AsyncConnectionPool , that sessions from the pool appear to be hanging on to PGA memory in Oracle for longer than they should. I'm looking into what else I can potentially tweak in python-oracledb, that might mitigate such an issue.

For my AsyncConnectionPool , I'm already setting getmode to SPOOL_ATTRVAL_TIMEDWAIT, wait_timeout to 10000, min to 1, max to 20, increment to 1, and timeout to 60 (and for connection objects, I'm also setting call_timeout to 30000).

Is there anything else I can set, be it max_lifetime_session or otherwise, to ensure that connections in the pool get regularly closed, and their resources cleaned up?

[anthony-tuininga]

I can confirm that max_lifetime_session is not supported in thin mode currently. That property, however, is one that simply ensures that a connection should be discarded after the interval takes place. If I recall correctly, however, this check is only done when a connection is returned to the pool or when a connection is being returned from the pool.

The timeout value is supported in thin mode and that one will ensure that any idle connections that have been idle for the given period of time will be discarded from the pool. This sounds like what you are after. The min value will remain permanently, so if you were hoping to ensure that connections never remain alive for more than a certain period of time then max_lifetime_session is needed. Let me know if that is the case as I can implement it relatively quickly if needed.

[Jaza]

Thanks @anthony-tuininga for confirming that, as I thought, it appears that I'm already doing everything I can on the python-oracledb side, given the currently available config options.

If you're offering to implement this, I won't say no, it would be great if you could add it to your backlog. I've submitted an enhancement request max_lifetime_session support in thin mode to the issue queue so you can keep track of it.

[cjbj]

Is the connection hanging itself, or is it just remaining open after being released back to the pool and holding onto DB resources (ie. for efficiency)? Is your pool a fixed size? Are there frequent acquire/releases to the pool or are there periods when the app isn't doing any DB work?

If the user has a special case which uses a lot of PGA and wants to clear that up, can they call ConnectionPool.drop(connection) explicitly?

[Jaza]

@cjbj I'm pretty sure the connection isn't hanging, as I've got either a 30-second or (for a handful of exceptional queries) a 120-second call_timeout in place. So it's just a case of holding onto DB resources. Pool isn't fixed-size, it's a min size 1 / max size 20 pool (for the environment in question, which is one of our bigger ones - max size 4 for most of our environments).

It's a web app, generally doing quick calls (most but not all are under 1 second), acquiring a connection at the start of each HTTP request and releasing it at the end - so yes, quite frequent acquire / releases. Yes, there are periods when the app is doing little or no DB work, we have different environments for different clients / regions, and the app only receives significant traffic during business hours for a given environment.

I wasn't aware of ConnectionPool.drop(connection) . We could potentially call that after running specific resource-intensive queries, but we'd prefer not to. We're calling a variety of different queries / procedures, and we're using the same shared Python code for the pool / connection handling for all the calls, the idea is to keep that handling code quite generic. It would be much easier if python-oracledb were to guarantee to drop a given connection on our behalf, after e.g. max 1 hour of that connection existing in the pool, which it sounds like is what we'd have with max_lifetime_session support.

[cjbj]

max_lifetime_session would be a tool to use.

Do you think the PGA issue is 'real' and impacting the DB, or is the user just seeing a stat and wanting a 0 size?

If DB resources are an issue, you might have a case for using DRCP.

[Jaza]

@cjbj yes, I believe the PGA issue is "real". I'm not sure how much it's impacting the DB, it's just one memory stat in a big DB with a lot going on. But python-oracledb is only calling the DB for short-lived operations, its connections shouldn't be hanging around for a long time and/or causing resources to get hogged for a long time.

We have also been considering DRCP for some time, however, using it wouldn't be a trivial achievement in our environment, and we're not convinced that it would result in a significant benefit in our case. But it's an option that's still on the table for us.

[cjbj]

We're happy to have a call if you want to discuss DRCP. I think I'm in your timezone