Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Connection pool and session identifier

Re: Connection pool and session identifier

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 20 Feb 2007 16:38:38 -0800
Message-ID: <1172018318.558679.157910@k78g2000cwa.googlegroups.com>


On Feb 20, 6:10 pm, melogr..._at_volcanomail.com wrote:
> I'm currently involved in a project requring the customization of an
> existing application heavily accessing an Oracle database (version
> 9i). The applications connects to Oracle via a connection pool. I
> would like to monitor the behavior of the application from the point
> of view of usage of Oracle resource (physical read operations, logical
> blocks used, cpu used ...) via the session information available in
> dynamic performance view such as v$mystat, v_$sesstat, v_$session, but
> while I know that with Oracle connection pool the connection used by
> an application changes at every database access, I do not know if the
> session identifier remains the same. If also the session idenitfier
> changes each time, it does not have any meaning to collect information
> via the above view will be meanigless.
>
> Thanks
>
> Enrico

If the application uses a connection pool to connect to Oracle then the connections likely retain their Oracle sessions when the end-user request completes. As such you will likely have several Oracle sessions to monitor. Some connection managers will increase and decrease the number of connections while others pretty much work with a set number. How hard it will be for you to monitor the connections will depend on exactly what you are trying to accomplish. More than likely sampling will provide all the information you really need.

If the database is mostly dedicated to supporting this one application then statspack reports might also prove of benefit.

You might find using SQL trace for the pools sessions during specific points in time of use. You can turn trace on and off for a session from another session.

The usuable options really depend on the end goal. Monitoring 100% of activity is probably not necessary.

HTH -- Mark D Powell -- Received on Tue Feb 20 2007 - 18:38:38 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US