Database Reference
In-Depth Information
23 while (rset.next())
24 System.out.println("This is session " + rset.getString(1) + " on " +
rset.getString(2));
25 rset.close();
26 System.out.println("\nPlease query V$SESSION and hit return to continue when done
.\n");
27 System.in.read(buffer, 0, 80); // Pause until user hits enter
28 // with connection pooling, execute this code before returning session to connect
ion pool
29 app_instrumentation[OracleConnection.END_TO_END_CLIENTID_INDEX]="";
30 app_instrumentation[OracleConnection.END_TO_END_MODULE_INDEX]="";
31 app_instrumentation[OracleConnection.END_TO_END_ACTION_INDEX]="";
32 ((OracleConnection)conn).setEndToEndMetrics(app_instrumentation,(short)0);
The connection is established by creating an instance of the class
java.util.Properties
,
setting user name and password, and then passing the class instance to the driver manager
in
DriverManager.getConnection
. This makes it possible to use the undocumented property
v$session.program
(line 6) to set the program and module names in
V$SESSION
for JDBC Thin
connections. The default program and module name for such connections is JDBC Thin Client,
which is not very telling. JDBC OCI ignores this property.
If you cannot afford to thoroughly instrument a JDBC Thin application, I recommend that
you at least set this property prior to establishing a connection to the DBMS. Thus, the DBA will
be able to identify which database session your Java program uses, but he will not be able to
use the setting with
DBMS_MONITOR
, since it only honors the documented programming inter-
faces PL/SQL, OCI, and Java. The module name will not only appear in
V$SESSION.MODULE
, but
also in Statspack and in AWR reports. Of course, the latter benefit applies to any program that
sets
V$SESSION.MODULE
, irrespective of whether it is set with
DBMS_APPLICATION_INFO.SET_
MODULE
, OCI, or JDBC. The module should also be set with JDBC OCI, but it requires a few more
lines of code. The default program and module for JDBC OCI are java@
client_host_name
on
UNIX and java.exe on Windows, where
client_host_name
is the system from which the JDBC
OCI program connected.
On line 16, the variable
app_instrumentation
is declared as an array of strings. The constant
OracleConnection.END_TO_END_STATE_INDEX_MAX
is used to specify the array size. Client identifier,
module, and action are set in lines 17 to 19. The call to
((OracleConnection)conn).setEndToEndMetrics
in line 20 makes the new settings available to
the end-to-end metrics API. The cast is mandatory to use the extension from Oracle Corpora-
tion. In lines 20 to 25 a
SELECT
statement is executed. This statement causes parse, execute, and
fetch database calls. The new settings will be sent along with the first database call, which results
from the
SELECT
statement. Thus, while the program waits for the user to press Return in line 27, the
new settings for module, action, and client identifier will be seen in
V$SESSION
. In lines 29 to 32
module, action, and client identifier are set to empty strings. This is done to show the integra-
tion with
DBMS_MONITOR
and TRCSESS.
The source code depot also includes a Java program which is instrumented with PL/SQL
and is compatible with Oracle9
i
as well as Oracle10
g
. Use of an Oracle10
g
JDBC driver to connect
to an Oracle9
i
instance is supported. However, end-to-end metrics code has no effect when
executed against an Oracle9
i
instance. Thus, at least to set module and action, PL/SQL must be
used by Java applications running against Oracle9
i
. The Java class
OracleConnection
includes
the method
public
void
setClientIdentifier(java.lang.String
clientId)
to set the client
