Re: Q: how to time a transaction

On Apr 5, 7:03 am, "Niall Litchfield" <niall.litchfi..._at_gmail.com> wrote:
> On Apr 4, 3:40 pm, "eAddict" <eAdd..._at_gmail.com> wrote:
>
> > I have been requested to monitor EVERY transaction for how long it
> > takes to run/complete. I know you can trace individaul transactions
> > but is there a way to get each one? How would I time how long it
> > takes from request to reply?
>
> > Thanks!
> > Vince
>
> What is the definition of a transaction in this regard? You
> technically *can* grab every database transaction (until you run out
> of space which will happen in a very short period of time indeed) by
> setting timed_statistics to true and sql_trace to true in the init.ora/
> spfile for all the instances in your system. You'd also need to set
> the max dump size to unlimited.
>
> I sincerely doubt however that that is what the requestor wants, or
> that you could analyse it and produce the report in your lifetime.
> They probably think that they want to monitor every business
> transaction (which maybe, probably is, several Oracle transactions) -
> the way to do this is to ensure that the application code is correctly
> instrumented - since you have to instrument at the layer that defines
> the transaction. This is possible (since Oracle do it as documented),
> but rarely done.
>
> Even if your application is properly instrumented, I find it extremely
> hard to understand why a request to monitor and time every transaction
> would be made, if you didn't have the time requirement I'd guess
> auditing was being asked for, the art of monitoring and recording is
> to focus on the things that matter - sometimes this can be hard to
> define (maybe it's whatever the chief exec has a bee in their bonnet
> about today, maybe its the transactions of highest value) but it
> should *be* defined before setting up monitoring.
>
> So in summary I'd go back and determine what the rationale for the
> request is and then look at monitoring the most important transactions
> at the level at which they are defined.
>
> cheers
> Niall

I think Niall has raised some very valid and important points. The definition of a business transaction varies from the database definition. While many business transactions will correspond to a database transaction some will encompass multiple database transactions. The process of paying a warranty claim could be considered one business transaction but it will be several database transactions: initial insertion of claim, update by company, updates by customer, and eventual payment and closing of claim.

Understanding what really needs to be monitored, tracked, and timed is basic. EAddict's post is not clear on this. If the request is for every single Oracle transaction then the request is not really very practical or beneficial like Daniel posted. In every system there are key activities that should be audited.

There is a little company in Dallas that puts on the Hot SOS presentations and they will tell you all code should be instrumented at design time. This allows turning on and off timing information as needed.

IMHO -- Mark D Powell -- Received on Fri Apr 06 2007 - 08:09:18 CDT