Re: Q: how to time a transaction

On Apr 4, 9:40 am, "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

It would appear that the easiest way around this would be to set a 10046 event on login for every session and process the resulting trace files through tkprof. This, of course, requires disk resources and it may consume more storage than you can spare. Another option would be to enable timing for every session at login, and spool the results of every session to a file -- this, of course, presumes no one else is spooling any output in these transactions. And, again, this requires storage which you may not be able to spare. Using the timing option would report the total time from request to result, but as I just stated you can't guarantee someone won't try to spool output elsewhere. Also, I doubt very seriously that end-users would appreciate seeing timing information for every query/transaction they run. You are also wanting to time queries as well as insert/update/ delete transactions, I expect, so a trigger to populate a 'transaction time' table is not a viable solution as you cannot trigger on the SELECT operation.

The purpose of this request needs to be known before any sort of 'solution' can be formulated or presented. This appears to be micromanagement to me, and unless each and every query and transaction is considered problematic this also appears to be a waste of time. Statspack and AWR generate reports which can narrow the field, so to speak, of monitorable transactions so you can concentrate on those which can most benefit from the attention. To monitor each and every statement which is submitted to the database is nothing more than busy work without a valid reason for doing so.

David Fitzjarrell Received on Wed Apr 04 2007 - 10:02:18 CDT