5 Replies Latest reply: May 16, 2012 11:19 AM by 646237 RSS

    APEX Debug Log identifies "Rows loop: 100 row(s)" as bottleneck

    884162
      Hi,

      I am encountering a severe performance problem with loading an Interactive Report. When I run the page in debug mode it appears to spend the vast majority of the time(99.9%) on the process "Rows loop: 100 row(s)". I've looked into multiple possibilities but have not found a way to improve performance.

      My initial thought was trying to tune my SQL, but according to the debug log, my SQL statement was executed in less than .005 seconds. I have not completely dismissed this as the cause, as this may still be affecting load times indirectly.

      The settings that seemed to be in direct cause of the issue were the maximum number of rows to display and the maximum number of rows to query. I tried to configure each of these, but neither setting seemed to improve the loading times.

      Any help/suggestions would be appreciated.

      If you haven't encountered a similair problem, it would be particularly helpful if you could provide whether I should be looking into tuning SQL, tuning the database, configuring the APEX application, or configuring the APEX Listener.

      Thanks,
      Brian
        • 1. Re: APEX Debug Log identifies "Rows loop: 100 row(s)" as bottleneck
          Patrick Wolf-Oracle
          Hi Brian,

          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

          Regards
          Patrick
          -----------
          My Blog: http://www.inside-oracle-apex.com
          APEX Plug-Ins: http://apex.oracle.com/plugins
          Twitter: http://www.twitter.com/patrickwolf
          • 2. Re: APEX Debug Log identifies "Rows loop: 100 row(s)" as bottleneck
            scott.wesley
            How many rows does your report return? Is the query something you can offer as a post?

            Interesting things can happen with bind variables :-)
            http://www.grassroots-oracle.com/2012/01/apex-performance-issues-with-v.html
            • 3. Re: APEX Debug Log identifies "Rows loop: 100 row(s)" as bottleneck
              884162
              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.
              • 4. Re: APEX Debug Log identifies "Rows loop: 100 row(s)" as bottleneck
                tc*441059*in
                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.

                Good luck.

                Tony
                • 5. Re: APEX Debug Log identifies "Rows loop: 100 row(s)" as bottleneck
                  646237
                  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!