This content has been marked as final. Show 5 replies
you should definitely have a look at the SQL statement of the interactive report. Have you already created an explain plan of the SQL statement? Or used TKPROF to get additional information about the execution of your statement? See the p_trace parameter at http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21674/debug_sql_trace.htm#HTMDB10004
My Blog: http://www.inside-oracle-apex.com
APEX Plug-Ins: http://apex.oracle.com/plugins
Sorry, I cannot share the query. I do know that the query is a problem and needs tuning, but our dba has run a diagnostic report and thinks it has to do with the db fetchsize. The other thing I forgot to mention was that I have run the same report in apex.oraclecorp with much better performance, although the same process step of "Rows loop: 100 row(s)" still remains the bottleneck.
Thanks for the help.
Along the same lines of checking the trace of the SQL statement, make sure that the database has accurate and up-to-date statistics on the schema so that the appropriate plan is chosen by the CBO. Also you might want to check which optimizer mode you are running in (ALL_ROWS vs FIRST_ROWS). The CBO plan might look OK but once new stats are gathered a better plan may emerge. There is of course the simplest idea - are your tables properly indexed? Sometimes indexes are created in development but not migrated to in production. Sillier things have happened.
I had the same problem as you using APEX 4.1...
A page was loading slow and it appeared through the debugger that the query was running fast but the page rendering (looping through to create the rows of the table) was the slowdown. However, it turned out to be the query itself.
I executed the query in SQL developer where it still took a "long" period of time to run. This eliminated APEX from the equation. I then commented out some of the functions the query called and determined that one function was causing the slow down (when commented out, the query went from 60 seconds to < 2 seconds).
I can't insert screen shots of the debug window, but here is what i was seeing before fine-tuning that function where it looked like the looping took 65 seconds:
elapsed execution message
0.28349 0.00507 ...Execute Statement: select * from ( SELECT lreq.p......... 0 ) order by 7 desc nulls first 4
0.28856 0.00760 print column headings 4
0.29617 65.47619 rows loop: 1000 row(s)
and now with the function optimized it looks like:
0.27137 0.00775 ...Execute Statement: select * from ( SELECT lreq.p......... 0 ) order by 7 desc nulls first 4
0.27918 0.00284 print column headings 4
0.28196 1.72328 rows loop: 1000 row(s)
my advice would be the problem is probably somewhere in your query itself and APEX has a bug (?) that appears to make it look as though the query is more efficient than it is by not clearly explaining the debug window and the different parts of processing a report.
best of luck!