Monday, April 13, 2009

Apex performance week over week

I've looking into tuning the public apex.oracle.com. I could tell from the system standpoint that what we've done has been working. Now the real test is how is the end user's response times. I put a quick query together to attempt to measure that. Here's what I used which shows the trailing 24 hours pitted against the same time period 14 days ago.



select t.dt "Hour" ,t.cnt "Today Views" ,round(t.av,2) "Avg time",
l.cnt "2 Week Ago Views",round(l.av,2) "2 Week Ago Avg" ,
case when (t.cnt-l.cnt) > 0 then '<span style="color:green;">'||round((l.cnt/t.cnt)*100) ||'% ('||(t.cnt-l.cnt)||')</span>' else '<span style="color:red;">-'||round((l.cnt/t.cnt)*100) ||'% ('||(t.cnt-l.cnt)||')</span>' end "Count Diff" ,
case when (t.av - l.av) > 0 then '<span style="color:red;">-'||round((l.av/t.av)*100) ||'% ('||(round((t.av - l.av),2))||')</span>' else '<span style="color:green;">'||round((l.av/t.av)*100)|| '% ('||(round((t.av - l.av),2))||')</span>' end "Avg Diff"
from (
select to_char(time_stamp,'HH24') dt,count(1) cnt,avg(elap) av
from apex_030200.wwv_flow_activity_log
where time_stamp > sysdate - 1
group by to_char(time_stamp,'HH24')
) t,
( select to_char(time_stamp,'HH24') dt,count(1) cnt ,avg(elap) av
from apex_030200.wwv_flow_activity_log
where time_stamp between sysdate -14 -1 and sysdate -14
group by to_char(time_stamp,'HH24')
) l
where t.dt = l.dt
order by 1


Here's the results of the query when run for apex.oracle.com this morning. From these measure points, it appears what we're doing is working.