Bad performance in Interactive Report

cdiscla

Hello,

i have performance problems with a query that is fast when run in SqlDeveloper / SqlPlus but takes 6-7 seconds in an interactive report.

If i enable debug window, i see that 7.4 seconds are taken by IR binding: "APXWS_MAX_ROW_CNT" value="100000", if i clear Max Row Count value , the 7 seconds are taken by "IR binding: P67_FIELD_NAME1"; if i remove that field from the query it is taken by another field used as condition in the query.

How can it be solved?

Query returns 300-400 records and runs fine outside Apex.

Thanks in advance and greetings

Cris

Pierre Yotti

Can we show the query?

fac586

cdiscla wrote:i have performance problems with a query that is fast when run in SqlDeveloper / SqlPlus but takes 6-7 seconds in an interactive report.If i enable debug window, i see that 7.4 seconds are taken by IR binding: "APXWS_MAX_ROW_CNT" value="100000", if i clear Max Row Count value , the 7 seconds are taken by "IR binding: P67_FIELD_NAME1"; if i remove that field from the query it is taken by another field used as condition in the query.How can it be solved?Query returns 300-400 records and runs fine outside Apex.

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 statistics on the data sources do not exist or are out of date). 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?

Check that the statistics on the objects used by the query are up to date.

cdiscla

Hi, thanks for your reply; if i copy the code generated by IR in the debug window and paste in SqlDeveloper the query is fast (less than before, naturally, but takes 0.3 seconds ).

The 7 seconds are used, don't know why, in another way

Mint-Innit

Are you definltely returning all the rows when the query runs fast in SQL Developer, or just the first 50?

I've had several problems with crippling performance of IRs on that binding action. The reports though were all based on calls to pipelined table functions, and I never was able to find out how to speed them up. In my case moving the query out of a package and back in to the application helped a lot. Fac586's reply is interesting. If I get another performance issue I'll definitely explore in more depth what is actually being run and what the optimiser is doing with it.

Cheers.

PS: pipeline table functions usually work fine in APEX reports - it was just one or two that ground to a shuddering halt when re-used in APEX.

fac586

Mint-Innit? wrote:Are you definltely returning all the rows when the query runs fast in SQL Developer, or just the first 50?I've had several problems with crippling performance of IRs on that binding action. The reports though were all based on calls to pipelined table functions, and I never was able to find out how to speed them up. In my case moving the query out of a package and back in to the application helped a lot. Fac586's reply is interesting. If I get another performance issue I'll definitely explore in more depth what is actually being run and what the optimiser is doing with it.PS: pipeline table functions usually work fine in APEX reports - it was just one or two that ground to a shuddering halt when re-used in APEX.

Queries that use pipelined functions may require hinting as there are no optimizer statistics available on the function:

Query performance problems in APEX can be investigated by enabling SQL tracing and then following standard database performance tuning practice.

cdiscla

I will try to enable trace as suggested, but the strange thing is that if i create a new page with a new interactive report with the same query, the query is run in 0.4 seconds.

If i apply filters, save public report in IR, after a little use of the new IR, performances go down and take 5-6 seconds to complete.

ToolTimeTabor

FAC586,

Your comment about "Queries that use pipelined functions may require hinting as there are no optimizer statistics available on the function" is very instructive.  We have been troubleshooting a pipeline based IR performance issue for months.  In that process, we saw horrible Explain Plans, and even tried hints.  They helped, sort of, but not conclusively in our case.  Sometimes the hint was applied, other times not.  We were finally able to optimize its performance through a variety of techniques combined.

The point being, if folks are using pipeline functions and their Explain Plan(s) are not logical, then they should definitely look at hints.

Rob