A few weeks ago we upgraded to the latest Oracle JDBC driver (ojdbc14). This caused some side effects which we have just understood. In particular:
<sql:query var="data"> select enddate,startdate,submitdate,cpusecondsused from processinstance PI where PI.process = ? <sql:param value="${row.PROCESS}"/> </sql:query> <aida:tuple var="tuple" query="${data}" /> <aida:tupleProjection var="wallPlot" tuple="${tuple}" xprojection="(ENDDATE-STARTDATE)/60"/> <aida:tupleProjection var="waitPlot" tuple="${tuple}" xprojection="(STARTDATE-SUBMITDATE)/60"/>
setenv JAVA_OPTS "-server -Doracle.jdbc.J2EE13Compliant=true"
Often we have tables containing many (time related) data points, but we would like to plot only every nth point, or time average over n points. Both of these can be achieved using oracle's rownum built-in.
It is possible to get oracle to average arbitrary sets of consecutive points using this following query. We used this in the Pipeline II usage plots, but it may be useful in other trending related queries.
<sql:query var="data"> select min(entered) entered,avg(ready) ready,avg(submitted) submitted ,avg(running) running from ( select sum(ready) ready, sum(running) running, sum(submitted) submitted,entered from processingstatistics where entered>=? and entered<=? <sql:dateParam value="${startRange}"/> <sql:dateParam value="${endRange}"/> group by entered order by entered ) group by floor(rownum/?) <sql:param value="${groupby}"/> </sql:query>