| Interpreting 'sql service response time' and 'User Calls Per Sec' [message #633734] |
Tue, 24 February 2015 07:24  |
 |
VIP2013
Messages: 91
Registered: June 2013
|
Member |
|
|
Hi, i am using Release 11.2.0.3.0 of Oracle.
I was trying to plot two DB metrics to know the DB performance, was plotting Average value of 'no of users calls'(Number of parse+fetch+execute calls) trend and accordingly how the metric 'sql service response time' behaves with variation 'no of user calls', so that we can have a pattern and we will use that for DB performance forecasting, and can estimate when will be the break point arise, also will be able to set one value(lowest value below which users experience slowness) of 'sql service response time' , through which we may have some alert scheduled. My thought is that, with increase in throughput(no of user calls), the responce time might be constant for sometime but then after it should increase, as because queue time will be increase.
i am using below sql to derive historical data of last 3 months for both the metrics, but surprisingly i dont see, a proportional trend in both the metric, both seems different altogether. Need expert suggestion, if i am interpreting the metrics in wrong way, or is there some other way of doing same?
SELECT end_time,instance_number,
metric_name || '(' || metric_unit || ')' metric_and_unit,
average, minval,maxval
FROM ( SELECT end_time,
instance_number,
metric_name,
metric_unit,
average,
maxval,
minval
FROM dba_hist_sysmetric_summary
WHERE (dbid, GROUP_ID, metric_id) IN
(SELECT dbid, GROUP_ID, metric_id
FROM dba_hist_metric_name
WHERE metric_name ='User Calls Per Sec' -- 'SQL Service Response Time'
and dbid= 1234567
)
AND end_time >= SYSDATE -90 and dbid= 1234567
AND instance_number = 2
)
ORDER BY end_time
[Updated on: Tue, 24 February 2015 07:30] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
