Response-Time Analysis
940748Apr 22 2013 — edited Apr 23 2013Hi,
I have been reading an article at http://www.oracle.com/technetwork/articles/schumacher-analysis-099313.html.
In this article a nice query has been provided by the author. That is
select CASE METRIC_NAME
WHEN 'SQL Service Response Time' then 'SQL Service Response Time (secs)'
WHEN 'Response Time Per Txn' then 'Response Time Per Txn (secs)'
ELSE METRIC_NAME
END METRIC_NAME,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' then ROUND((MINVAL / 100),2)
WHEN 'Response Time Per Txn' then ROUND((MINVAL / 100),2)
ELSE MINVAL
END MININUM,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' then ROUND((MAXVAL / 100),2)
WHEN 'Response Time Per Txn' then ROUND((MAXVAL / 100),2)
ELSE MAXVAL
END MAXIMUM,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' then ROUND((AVERAGE / 100),2)
WHEN 'Response Time Per Txn' then ROUND((AVERAGE / 100),2)
ELSE AVERAGE
END AVERAGE
from SYS.V_$SYSMETRIC_SUMMARY
where METRIC_NAME in ('CPU Usage Per Sec',
'CPU Usage Per Txn',
'Database CPU Time Ratio',
'Database Wait Time Ratio',
'Executions Per Sec',
'Executions Per Txn',
'Response Time Per Txn',
'SQL Service Response Time',
'User Transaction Per Sec')
ORDER BY 1
My question is while 'User Transaction Per Sec' is almost same
1. If I observe that 'Response Time Per Txn' is increasing day after day then is it correct that performance of the database is degrading?
2. What can be said if there is constant increment in 'Database CPU Time Ratio' or in 'Database Wait Time Ratio'?
3. What to interpret from 'SQL Service Response Time' if every output regarding this metric is 0?
Please help to clarify these doubt.
--Gourab