Re: Tracing Session / Connection Pooling / Oracle 9i

From: Giovanni Cuccu <giovanni.cuccu_at_gmail.com>
Date: Thu, 30 Mar 2006 11:20:42 +0200
Message-ID: <23e0d1170603300120i721c92c9i8496c27a21fcca3@mail.gmail.com>

Hi Larry,

   I implemented your requirements while developing an application some years ago.
Here are some implmentation details:
1)I wrapped the connectionpool class and the calls for getting/releasing the connections were made trough the wrapper, this means that in the wrapper I can enable tracing while leasing the connection and disable while releasing it. 2)the ability to turn off/on the tracing was dictated by two factors: the trace level of the class wrapper itself (very easy to do if you use log4J or similar that gives automatically the trace level for the class) and an optional parameter passed to wrapper while leasing the connection.
Here is some java pseudocode
//inside a servlet o something else
Connection conn=pool.getConnection(clientid,enableTrace);

//client id serves the purpose of identifing the beginning
//and the end of this program in the trace file
//enableTrace is a boolean parameter indicating whether or not activate trace

try {
...
//use the connection
...
} finally {

   pool.releaseConnection(conn);//disable trace }

inside the pool class there is something similar to: public Connection getConnection(String clientId,boolean enableTrace) {

    if (logger.isDebugEnabled() || enableTrace) {     //execute sql to enable trace and set the clientId through a SQL o PL/SQL call

    }
    //eventually fills a map with connId,clientId ...
}

public Connection releaseConnection(Connection conn) {

    //get form the map the clientId
    //execute sql or pl/sql to mark the end of the session trace
    //stop the trace only if logger.isDebugEnabled() is false

    }
...
}

Hope it helps,

    Giovanni
On 3/29/06, Larry Elkins <elkinsl_at_flash.net> wrote:
> Listers,
>
> A bit of a theoretical question here -- I would like to test this myself but
> don't have access to the needed components and am not quite clear how things
> would work. Java app using connection pooling. Want to trace a user action.
> The ability to turn 10046 tracing off/on would be included in the code, as
> would calls to DBMS_APPLICATION_INFO to identify the session. Would the code
> need to initiate tracing (and DBMS_APPLICATION_INFO) each time one of the
> connections is grabbed? So let's say it drifts across 5 different
> connections I would expect a trace file out of each of those shared
> connections. Now what about turning off tracing? I'm just real unclear on
> how/what would happen, don't have products to test this, and am curious what
> others do.
>
> I've read a lot the past couple of days but haven't come across
> implementation details. Sure, I've seen things like redirect them to a
> dedicated connection, use something like a logon trigger to trap things and
> turn on tracing, etc. But let's say we want the tracing to be user initiated
> (much like Oracle Apps provides).
>
> Sorry for such a broad question -- I wish had access to the various
> components to test and see for myself.
>
> Regards,
>
> Larry G. Elkins
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
--------------------------------------------------------------------
Another free oracle resource profiler
http://sourceforge.net/projects/oraresprof/
Now version 0.9
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Mar 30 2006 - 03:20:42 CST