Re: Application, session and performance.

From: <gxchristian_at_yahoo.co.uk>
Date: 24 Mar 2007 16:08:10 -0700
Message-ID: <1174777690.589880.180650@l75g2000hse.googlegroups.com>

Hello,

     Many thanks for the nice response. I will try to explain a little more what I need.

     From a pure functional standpoint, the application consists of several queries between commits that forms a business step. For instance:

Start

     Query1
     Query2
     Query3

Commit
Start

     Query4
     Query1

Commit

     What I really need is to identify time each session (or, lets say the 5 longest running executing sessions each 1 minute) spend in each one of the business process. Of course, the best option is to instrument the application, but I may consider the imposibility of changing the application itself.

     So basically,what I need to capture can be based on queries or time between commits for each one of the users. This would lead me, with some tweaking on the data, to the result I need.

     Regarding the 10046 trace... How expensive is it from the performance perspective?. It sounds like a good idea...

     Iván.

On 24 mar, 22:46, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> On Mar 24, 2:10 pm, gxchrist..._at_yahoo.co.uk wrote:
>
>
>
>
>
> > Hello,
>
> > I need to monitor the performance of an application that executes
> > queries against an Oracle database. Given the application -Access- has
> > almost no way to trace activity inside it, I need to control its
> > performance from Oracle.
>
> > Given the requirements I have from the application monitoring, I am
> > lead to measure:
>
> > - Time it takes to execute specific transactions for specific users.
> > - Time between two commits (application/transaction) for specific
> > users.
> > - Long-running queries (with user data about who launched them).
>
> > I have read some stuff here and there, but still feel I need some
> > guidance. I have also read about specific functionality in Oracle 10g
> > around performance.... would it cover my needs?. Any tip or way to
> > face this?.
>
> > Many thanks,
> > George.
>
> While the performance monitoring capabilities have been enhanced with
> each release, many of the capabilities that you need have existed for
> more than a decade. The 10g capabilities that you may be thinking of
> are additional cost add-ons that can be added to the Enterprise
> Edition of Oracle.
>
> A good overview of performance monitoring:http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/i...
>
> Time between two commits is a bit more difficult to determine. At the
> system level, you can track the number of COMMITs executed like this:
> SELECT
> NAME,
> VALUE
> FROM
> V$SYSSTAT
> WHERE
> NAME='user commits';
>
> A similar query to track at the session level:
> SELECT
> SS.SID,
> SN.NAME,
> SS.VALUE
> FROM
> V$SESSTAT SS,
> V$STATNAME SN
> WHERE
> SN.NAME='user commits'
> AND SN.STATISTIC#=SS.STATISTIC#;
>
> The above does not actually indicate the time between COMMITs. To
> obtain that information, you likely will need to turn to a 10046
> trace. After the trace file is generated, look for lines that contain
> "XCTEND". If the line contains RLBK=0, then the session issued a
> COMMIT, if it contains RLBK=1, then the session issued a rollback.
> Use the tim= values in the trace file to determine the time delta
> between each XCTEND line.
>
> The 10046 trace can also be used to determine long running operations,
> but those trace files likely will not be the starting point for
> investigation. The various session level performance views provide a
> great deal of information to determine which session has used the
> greatest amount of the server resources. V$SQL also provides a lot of
> information needed to track down long running queries.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.- Ocultar texto de la cita -
>
> - Mostrar texto de la cita -
Received on Sat Mar 24 2007 - 18:08:10 CDT