I have several interactive reports that used to perform well (between 0.2 and 2 seconds, fetching timeseries rows for 1 day or 1 month), but at some point in time (while only minor changes have been made to the report) for some of these the performance drops alarmingly, and may well go to over 30 seconds.
Although I have found many similar issues have been reported by several people, no article gives the solution, most articles end with the conclusion that the query is causing the problem.
I have had this issue about half a year ago, and could not pinpoint the problem. It was solved by deleting the report region and redefining it.
Some facts:
- The queries all use bind variables (item values on the page). When I take the query and execute it in SQLDeveloper then it performs as expected (0.05-1.5 sec).
- When I run the page with debug, then the debug shows that 99% of the time goes into the APXWS_MAX_ROW_CNT call, which is being called 3 times during the page refresh.
- If I make a classic report with exactly the same query the performance is ok.
Conclusion: The issue is definitely caused by the way Interactive Reports try to determine the row count.
Does anybody has a solutions for this (other than rebuilding the report region)?