Re: Average response time
!! Please do not post Off Topic to this List !!
Paul,
Try this:
select ( s.service_seconds + w.wait_seconds) / user_calls service_time
from (
select
(sum(value)/100) / ( 3600 * 24 ) service_seconds
from v$sysstat
where upper(name) like '%CPU%'
and class in (1,64) -- User and SQL
) s,
(
select
sum(time_waited/100) wait_seconds
from v$system_event
where event not like '%timer'
and event not like '%from client'
) w,
(
select
sum(value) user_calls
from v$sysstat
where name like 'user%'
) u
/
While you may find this a useful number as a DBA, I'll bet your users won't
buy it.
On my SAP system it shows a 0.025 second reponse time. While that may be
accurate on a per call database, I don't think many queries are returned
that quickly. :)
Could be that I'm not getting the right numbers, but I don't believe that
determining
an average response time is quite that simple.
Jared
Paul.Parker@bm
wna.com To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
Sent by: cc:
[EMAIL PROTECTED] Subject: Average response time
om
09/14/01 09:35
AM
Please respond
to ORACLE-L
!! 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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
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: [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).
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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
