Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Interactive Report performance problems due to max_row_count

Peter de VaalApr 21 2016 — edited Jul 13 2016

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)?

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 10 2016
Added on Apr 21 2016
6 comments
1,367 views