Forum Stats

  • 3,770,456 Users
  • 2,253,116 Discussions
  • 7,875,463 Comments

Discussions

Interactive Report performance problems due to max_row_count

Peter de Vaal
Peter de Vaal Member Posts: 174 Bronze Badge
edited Jul 13, 2016 7:16AM in APEX Discussions

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

Tagged:
Peter de Vaal

Answers

  • Anderson Ferreira
    Anderson Ferreira Member Posts: 69 Blue Ribbon
    edited Jul 10, 2016 4:52PM

    Why don't you set the "Maximum Row Count" in the attributes of the interactive report to a lower number like 10,000 ?

    Querying fewer rows can improve performance and querying thousands of rows can degrade performance.

  • Peter de Vaal
    Peter de Vaal Member Posts: 174 Bronze Badge
    edited Jul 11, 2016 7:23AM

    Thanks Anderson,

    Unfortunately setting maximum row count does not help at all.

    The query returns a limited number of rows (just the number of hours in a few days, so 24 or 48). So I can set the max rowcount to a low number.

    The problem is not that the query has to fetch many rows, or is performing badly at all. From SQL Developer the query always execites in less than a second.

    However the query operates on a pipelined function, and the counting that is being done by APEX cannot cope very well with such queries (it cannot make use of table statistics unless it fetches all rows I think).

    I think it might be best if Oracle changed the behaviour of such Interactive Reports by providing the option to indicate that you know the query will not fetch too many rows (as in my case I know that not more than 96, i.e. all 15 minute intervals within a single day) are being fetched, so APEX does not need to do the counting (which apparently takes 99% of the time, it is doing it 3 times for some reason).

  • fac586
    fac586 Senior Technical Architect Member Posts: 20,140 Red Diamond
    edited Jul 11, 2016 8:50AM
    Peter de Vaal wrote:
    
    Thanks Anderson,
    
    Unfortunately setting maximum row count does not help at all.
    The query returns a limited number of rows (just the number of hours in a few days, so 24 or 48). So I can set the max rowcount to a low number.
    The problem is not that the query has to fetch many rows, or is performing badly at all. From SQL Developer the query always execites in less than a second.
    However the query operates on a pipelined function, and the counting that is being done by APEX cannot cope very well with such queries (it cannot make use of table statistics unless it fetches all rows I think).
    I think it might be best if Oracle changed the behaviour of such Interactive Reports by providing the option to indicate that you know the query will not fetch too many rows (as in my case I know that not more than 96, i.e. all 15 minute intervals within a single day) are being fetched, so APEX does not need to do the counting (which apparently takes 99% of the time, it is doing it 3 times for some reason).
    

    Post a complete debug trace of the page show processing, indicating what activity you consider to be "counting".

    Why is a pipelined function being used?

    APEX wraps interactive report source queries inside other queries to support the various IR features. This may affect the execution plan used for the query (particularly when one of the data sources is a pipelined function for which no table statistics exist). When running the query in SQL Developer are you comparing like with like using the query generated for the IR (shown in the debug trace or retrieved through the APEX_IR API), or only executing the source query from the report definition?

    If there is a known upper bound on the number of rows that may be returned by the pipelined function, consider using that value in a cardinality hint to provide this information to the optimizer.

    APEX embeds an analytical function [count(*) over () as apxws_row_cnt] as a column in the outer IR projection to determine the total number of rows returned by the query, so no additional queries are required in order to do this.

    Peter de Vaal
  • Peter de Vaal
    Peter de Vaal Member Posts: 174 Bronze Badge
    edited Jul 11, 2016 11:42AM

    Hi,

    The pipelined function is being used as where it is meant for: transforming, combining and filtering data from different sources into one view.

    I have been experimenting a bit with the cardinality hint, and this seems to have effect. Although I have to do more testing it feels promising.

    Thanks for the tip!

  • Mike Kutz
    Mike Kutz Member Posts: 5,826 Silver Crown
    edited Jul 11, 2016 12:35PM

    change in cardinality changes performance --> it sounds like your plans are changing.

    If you know that your Pipeline Function will ALWAYS produce X number of rows or less, you can tell Oracle that.

    setting cardinality for pipelined and table functions

    MK

    Peter de Vaal
  • Peter de Vaal
    Peter de Vaal Member Posts: 174 Bronze Badge
    edited Jul 13, 2016 7:16AM

    Thanks for the tip Mike.

    I have tried the dynamic sampling hint. This seems to do the job for some of the queries.

    I will investigate the extensible optimizer method later, probably that will make sense in many other cases.

    The dynamic in-list as discussed in the example is what we are using as well in some situations.

This discussion has been closed.