Re: Application, session and performance.

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 24 Mar 2007 14:46:39 -0700
Message-ID: <1174772799.685290.77790@l77g2000hsb.googlegroups.com>

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/instance_tune.htm

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. Received on Sat Mar 24 2007 - 16:46:39 CDT