TCP keepalive via expire_time in thick driver

[hvmptydvmpty]

Hi oracledb developers,

Appreciate all the work on this driver, thank you! We're having some issues with long-running commands that expire_time=5 appears to rectify for the thin mode. In thick mode it doesn't seem to have the same effect so I tried to trace it by grepping this driver and ODPI code and so far was not able to prove to myself that in thick mode setting expire_time actually translates into a connection TCP keepalive as documented here: 6.8.2 EXPIRE_TIME (and there's also a setting in sqlnet). Could you please confirm if non-zero expire_time is expected to work in thick mode as the docs say (emphasis mine):

The expire_time parameter is expected to be an integer which indicates the number of minutes between the sending of keepalive probes. If this parameter is set to a value greater than zero it enables keepalive. This value is used in both the python-oracledb Thin and Thick modes.

[anthony-tuininga]

In the thick driver you must use the sqlnet.ora value to configure it, or you have to specify all of the components separately (do not use the dsn parameter).

[hvmptydvmpty]

It's also quite difficult to determine whether TCP keepalive is set without network probes and those are frowned upon by security people. By definition it doesn't show in any traces/logs emitted by the driver with debug enabled.

[anthony-tuininga]

This demonstrates that it works:

params = oracledb.ConnectParams(host="myhost", service_name="my_service", expire_time=5)
print("dsn:", params.get_connect_string())

The expire time value is included in the connect string, so it will be passed through to OCI. It should work as expected, I believe.

[hvmptydvmpty]

Sounds good! If I understand correctly it's not directly applicable to our case because we connect via dsn reference to tnsnames and ConnectionParams doesn't accept that but we can figure it out from here. Thank you, Anthony!

[anthony-tuininga]

You can do it automatically by setting thick_mode_dsn_passthrough to False, or you can do it explicitly via ConnectParams.parse_connect_string(). I'll talk to @cjbj about the documentation.

[cjbj]

And I'll talk to @priyankanair8 :)