In researching something for a colleague, I stumbled on this 2 year old
thread.
Here Mark discovered that conn.isValid (to force a roundtrip) is doing
close-cursor-all under the covers.
I'm now thinking: would this not conflict with JDBC statement caching if it
were enabled? With statement-caching, JDBC caches open cursors (i.e. a
close-cursor call from the application code is silently ignored), but
underneath in the database the cursors would get closed by that isValid
call?
Another thing on the todo-list for testing.
On Wed, Dec 19, 2018 at 8:10 PM Mark Williams <markwilliams596@xxxxxxxxx>
wrote:
All,
In reply to a semi-recent message from Jonathan (aka @JLOracle) about
setting connection attributes via setClientInfo
<https://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html#setClientInfo-java.lang.String-java.lang.String->
in JDBC I said something to the effect of: Yes, all modern Oracle data
providers have a facility such as this.
Not being a "Java Person™", I wanted to dig into the JDBC implementation a
bit more - partly out of selfish reasons and partly out of just plain
curiosity.
From the documentation on setClientInfo
<https://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html#setClientInfo-java.lang.String-java.lang.String->
I discovered this little bit of information which is key:
"For efficiency the driver may defer setting the value in the database
until the next time a statement is executed or prepared."
This fits with the general idea that a database round-trip is necessary
(i.e. this is a piggy-back operation) in order to "push" new values into
the database. As seen in the (g)v$session view. What I was mostly curious
about is what would happen when putting the connection back into the pool
via a close call? Such as with this sequence:
con.setClientInfo("OCSID.CLIENTID", null);
con.setClientInfo("OCSID.MODULE", null);
con.setClientInfo("OCSID.ACTION", null);
con.close();
To my disappointment, this did not set the values to null as desired and
the previous values were still alive and well in (g)v$session. Well, yuck.
Now queries against (g)v$session may report misleading information because
CLIENT_IDENTIFIER, MODULE, or ACTION may unintentionally have values
*after* the connection is closed. For comparison, the equivalent of the
above in the Oracle Data Provider for .NET (aka ODP .NET) *does* set the
values to null. Driver implementation details matter I suppose.
So, I wanted to find an efficient way to force a database round-trip in
order to set the values to null. The following sequence certainly works,
but it feels a bit too heavy for my liking:
con.setClientInfo("OCSID.CLIENTID", null);
stmt.execute("begin dbms_application_info.set_module(module_name => null,
action_name => null); end;");
con.close();
There are other variations on this sequence but they are just that:
variations that involve superfluous statements.
What I did discover is that calling the isValid
<https://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html#isValid-int->
method on the connection forces a round-trip *and* the values are
correctly set after doing so:
con.setClientInfo("OCSID.CLIENTID", null);
con.setClientInfo("OCSID.MODULE", null);
con.setClientInfo("OCSID.ACTION", null);
con.isValid(0);
con.close();
Hurray! A win for experimentation and something that looks lightweight
(the TNS packet length is 21 bytes) and doesn't require any superfluous
(PL/)SQL statements to achieve the goal.
Now, what does isValid do to verify the connection? From the documentation
(with emphasis added):
"The driver shall submit a query on the connection *or use some other
mechanism that positively verifies the connection is still valid when this
method is called*."
In the case of the Oracle JDBC implementation the bolded part is what
appears to be implemented. What I see is that the driver sends a TNS data
packet with the "Cursor close all" Call ID. Indeed, in my testing if there
are any cursors that are eligible to be closed, invoking con.isValid(0)
does close them. Here's what I used to verify:
select a.sid,
a.value,
b.name
from v$sesstat a,
v$statname b
where a.sid = <correct SID here>
and b.name = 'opened cursors current'
and a.statistic# = b.statistic#;
I'm interested in any thoughts (especially unexpected or negative side
effects) about using isValid to force the round-trip, which, again, is only
being done to reset the connection attributes (CLIENTID, MODULE, and
ACTION.) It seems kind of funny that sending a "Cursor close all" message
to the database is the chosen way to verify a connection is valid, but,
hey, what do I know? Maybe it makes perfect sense.
Please excuse any extraneous information, lack of brevity, or grammatical
errors - I admit I've not thoroughly reviewed as I was eating at the time.
Cheers,
Mark
