APEX

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.

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

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.

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!

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

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.