!! Please do not post Off Topic to this List !!
> Hi List,
>
> I am trying to calculate the average database response time for a data
> center audit currently underway. Without "expensive" monitoring tools, is
> it possible to determine this from database statistics. So far, I'm using
> (Service Time + Wait Time) / calls where this translates into
>
> Service Time = 'CPU used by this session' from v$sysstat
> Wait Time = sum(time_waited) from v$system_event (excluding idle
> events)
> User calls = 'user calls' from v$sysstat
>
> Am I way off the mark here?
>
> Interestingly, it seems as if Craig Shallahamer (www.orapub.com) is
> preparing a paper which addresses this very issue - determing response time
> from database statistics - but it is only due out later this year.
>
> Anybody with any ideas or reasons why the above is not feasible?
>
> TIA
> Paul
>
Paul,
Firstly a number of statistics are meaningless in V$SYSSTAT and only
make sense in V$SESSTAT (and vice-versa) and I believe that 'CPU used by
this session' belongs to this category. Usually most resource
consumption is traceable to a very tiny fraction of SQL statements, and
I doubt that an average will lead you anywhere. My point is that I think
that you should try to apply your ideas to relatively small slices of
time (polling every minute or so) hoping to catch the real problem
queries on the fly, using global statistics to get an idea about what
you have missed, and try to do the best out of it. Another idea would be
to concentrate on V$SQLAREA and the number of executions and of buffer
reads (there is a script named peep.sql in the DBA tool kit of the
Oriole site if you need one). I think that associating some average
elapsed time to access, say, 1,000 buffers, should not be extremely
difficult to do, based on a few suitable examples. By computing the
average number of buffer accesses per execution of a query, you could
then get something looking reasonably like an average execution time for
the query. Of course, a query is not a transaction, and the user's
vision of response times may be different and include other elements.
But it may be an interesting approach to complement other metrics.
--
Regards,
Stephane Faroult
email: [EMAIL PROTECTED]
Oriole Corporation
Voice: +44 (0) 7050-696-269
Fax: +44 (0) 7050-696-449
Performance Tools & Free Scripts
--------------------------------------------------------------
http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs
--------------------------------------------------------------
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
