Home » RDBMS Server » Enterprise Manager » Interpreting 'sql service response time' and 'User Calls Per Sec'
Interpreting 'sql service response time' and 'User Calls Per Sec' [message #633734] Tue, 24 February 2015 07:24 Go to next message
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

Re: Interpreting 'sql service response time' and 'User Calls Per Sec' [message #633774 is a reply to message #633734] Tue, 24 February 2015 10:21 Go to previous messageGo to next message
VIP2013
Messages: 91
Registered: June 2013
Member
Can somebody, please help me understanding same?
Re: Interpreting 'sql service response time' and 'User Calls Per Sec' [message #633776 is a reply to message #633774] Tue, 24 February 2015 10:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You'd have a better chance to get an answer if you posted it in the correct forum which I move it now to.
I hope next time you will take care where you post and then won't lose many hours, you have the chance I saw it by accident.

Re: Interpreting 'sql service response time' and 'User Calls Per Sec' [message #633778 is a reply to message #633776] Tue, 24 February 2015 11:59 Go to previous messageGo to next message
VIP2013
Messages: 91
Registered: June 2013
Member
oh my bad, i was supposed to post this in Performance tuning forum. Perhaps put it in different forum.
Thanks for moving it.

[Updated on: Tue, 24 February 2015 12:04]

Report message to a moderator

Re: Interpreting 'sql service response time' and 'User Calls Per Sec' [message #633827 is a reply to message #633778] Wed, 25 February 2015 10:34 Go to previous message
VIP2013
Messages: 91
Registered: June 2013
Member
So ideally, throughput VS reponcetime relation should hold good, So, is it that the AVG data points which is captured in the DBA_HIST_SYSMETRIC_SUMMARY is not accurate enough to be rely upon? Or is there some other metric which should be followed in this scenario?
Previous Topic: Oracle 12C Cloud control instalation issue (unable to deployee agent software)
Next Topic: Difference between OEM 11g and OEM cloud computing
Goto Forum:
  


Current Time: Thu Mar 28 09:06:49 CDT 2024