RE: Average response time

!! 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: sfaroult_at_oriole.com 
  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: sfaroult_at_oriole.com

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: ListGuru_at_fatcity.com (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).