Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Bad performance in Interactive Report
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
Answers
-
Can we show the query?
-
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.
-
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
-
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.
-
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.
-
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.
-
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
