Re: Measuring Transaction/Query Times

From: Mark D Powell <mark.powell_at_eds.com>
Date: 27 Mar 2002 06:45:56 -0800
Message-ID: <178d2795.0203270645.74040435@posting.google.com>

orainfouk_at_yahoo.co.uk (Bob) wrote in message news:<fae6910c.0203270154.21a0604b_at_posting.google.com>...
> Hi all
>
> Is there anyway I can measure and audit the duration times of
> transactions and/or queries in Oracle 8i?
>
> I know that I can view current transactions and when they started by
> looking in the v$transaction table, but I can't put a trigger on that
> to audit the transaction duration. I also know that I can view recent
> SQL statements in the v$sql_area as well as various other stats, but I
> can't find out where Oracle stores duration (if indeed it does).
>
> Does anyone have any ideas or perhaps knows of some good Oracle
> monitoring/measuring products out there?
>
> Any help would be greatly appreciated.
>
> Thanks

Bob, I think your best bet is to make use of the statspack routines, which is the replacement for estat/bstat. The report shows the count and average time per transaction for the measured period.

See the following scripts:
$ORACLE_HOME/rdbms/admin/spdrop <== drop statspack objects, run first

                                        to clean out views on x$ if
present
                         spcreate   <== create statspack objects
                         spauto     <== example scheduling via dbmsjob
                         spreport   <== generate the report
                         sppurge    <== clean out old data

HTH -- Mark D Powell -- Received on Wed Mar 27 2002 - 08:45:56 CST