Database Reference
In-Depth Information
not receive the response at the moment the wait event SQL*Net message to client completes. Thus
the response time measured by the client is longer than that measured by the database server.
Not all code paths in the database server are thoroughly instrumented, such that further
inaccuracies aside from measurement intrusion are introduced. The deviation between the
elapsed time covered by trace file entries as delineated by timestamps in the file (parameter
tim , see Chapter 24) and the time accounted for by CPU consumption and waiting is usually
less than ten percent. In other words, the quality of instrumentation is high and measurement
intrusion is low, such that it is possible to use the data for reliable performance diagnoses.
I use the term application instrumentation to differentiate instrumentation of the ORACLE
DBMS from instrumentation within an application or database client. Basically, instrumenta-
tion of the DBMS is the core functionality, which may be leveraged to a greater degree when the
application is also instrumented. This is the main theme I'm trying to bring across in this chapter.
The DBMS has application interfaces that allow setting module, action, and client identifier. In
this context, a module identifies a longer code path, which may correspond to a batch job, a
business task, or a functional component within a larger application. For example, human
resources and manufacturing might be modules of an enterprise resource planning application.
Modules consist of one or more actions, i.e., actions are at a more granular level than modules.
A client identifier serves to uniquely identify an end user. In a connection pooling environ-
ment, dedicated server processes are spawned once and reused again and again for running
DML on behalf of different end users. A DBA has no way of knowing which dedicated server
process services which end user, unless the application is instrumented and sets the client
identifier. Since all the sessions in a connection pool are opened by the same application server
and connect to a single database user, the columns USERNAME , OSUSER , and MACHINE in V$SESSION
are useless. With application instrumentation in place, a DBA can identify the database session
which services an end user. The DBA will also see which module and action are executed. Of
course, you may wish to add a lot more instrumentation code than simply setting module,
action, and client identifier. Code that has nothing to do with tracing or statistics collection in
the DBMS, but instead focuses on your application's functionality or measures response time
independently of the DBMS. Especially if your application includes modules that do not interact
with a DBMS instance, you will need to include some kind of accounting for the time spent in
those modules. After all, the DBMS can only account for the time spent in database calls and
the time spent waiting to receive another database call, i.e., waiting for the event SQL*Net
message from client .
In addition to module, action, and client identifier, the instance service name (see “Instance
Service Name vs. Net Service Name” in this topic's Introduction) used by an application may
also be used to facilitate monitoring and tracing. Separate applications (or even connection
pools) running against an instance should use different service names. This opens up the
possibility of tracing and monitoring by service name.
Case Study
I have chosen Java, JDBC, and the new JDBC end-to-end metrics introduced with Oracle10 g for
the code sample in this case study. In Oracle11 g , the JDBC end-to-end metrics interface has
been removed from documentation in favor of Dynamic Monitoring Service. However, the
interface is still present in the JDBC driver release 11.1.0.6.0 and works exactly as in Oracle10 g .
Instrumentation with PL/SQL has been available for a number of years and works as expected.
JDBC end-to-end metrics is a different issue in that the code sample provided by Oracle in the
 
Search WWH ::




Custom Search