1 2 Previous Next 18 Replies Latest reply: Mar 13, 2013 1:03 PM by 349522 RSS

    how to troubleshooting page slowness

    349522
      I have a report page, include one SQL report, which run 7 second in TOAD, but run 514 seconds in APEX, I would appreciate it very much if you could let me know how to troubleshooting/tune a page slowness
      My DB version 11.2.0.2.0
      My APEX version: 4.1.1.00.23
      Run debug don't show any obvious bad SQL.
        • 1. Re: how to troubleshooting page slowness
          jariola
          Hi,

          This might help
          http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35125/debug.htm#sthref2448


          Regards,
          Jari
          -----
          My Blog: http://dbswh.webhop.net/htmldb/f?p=BLOG:HOME:0
          Twitter: http://www.twitter.com/jariolai
          • 2. Re: how to troubleshooting page slowness
            349522
            Jari
            Thanks for the tip. Unfortunately, it is not appliable in my case. I am looking for a way to get timer usage infomation for a page to see which APEX internal component used all the extra time. 7 sec. vs. 500 sec leave a lot of room to tune. Any other suggestions?
            • 3. Re: how to troubleshooting page slowness
              TexasApexDeveloper
              Standard report? Interactive report??

              You are NOT going to really get an idea of what internal APEX component is taking so long, since that information is not really available.. What has been suggested is a good starting point.. What type of pagination are you using on the page in question? Can you post an example of the page with the offending query and sample data to the Oracle hosted apex site with developer login information? Maybe someone else will see something that your missing?

              Thank you,

              Tony Miller
              Ruckersville, VA
              • 4. Re: how to troubleshooting page slowness
                joelkallman-Oracle
                Hi Ching,

                Can you do an explain plan of this query in SQL Commands, and compare it to the explain plan results from TOAD? Ensure you use the same bind variables and bind values with both tests.

                Joel
                • 5. Re: how to troubleshooting page slowness
                  349522
                  I have compared explain plans in TOAD and APEX SQL Command, they are identical.
                  • 6. Re: how to troubleshooting page slowness
                    TexasApexDeveloper
                    What type of pagination are you using on the report?? In toad it is returning all the rows normally, but in APEX if you have the page set to use pagination, it is only returning a set # of rows at a time..

                    Thank you,

                    Tony Miller
                    Ruckersville, VA
                    • 7. Re: how to troubleshooting page slowness
                      349522
                      it is already set to minumum option, X to Y with previous and next links
                      • 8. Re: how to troubleshooting page slowness
                        joelkallman-Oracle
                        Hi Ching,

                        Have you run the page in debug? Do the Debug results show the execution of the SQL statement taking 500 seconds? Or are there other elements on the page that are impacting this?

                        When you run the query in SQL Commands, does it also take as long?

                        Joel
                        • 9. Re: how to troubleshooting page slowness
                          349522
                          run the page in debug? 2 seconds
                          Do the Debug results show the execution of the SQL statement taking 500 seconds? no, the sql show execusion 0.0045 second
                          are there other elements on the page that are impacting this? nothing show 500 seconds, the longest execusion is 0.017 second

                          When you run the query in SQL Commands, does it also take as long? 0.12

                          Here is debug result
                          Elapsed Execution Message Level Graph
                          0.00468 0.01937 S H O W: application="124" page="31" workspace="" request="" session="956891539040101" 4
                          0.02403 0.00107 Language derived from: FLOW_PRIMARY_LANGUAGE, current browser language: en-us 4
                          0.02510 0.00062 alter session set nls_language="AMERICAN" 4
                          0.02571 0.00396 alter session set nls_territory="AMERICA" 4
                          0.02967 0.00077 NLS: CSV charset=WE8MSWIN1252 4
                          0.03044 0.00064 ...NLS: Set Decimal separator="." 4
                          0.03108 0.00086 ...NLS: Set NLS Group separator="," 4
                          0.03196 0.00099 ...NLS: Set g_nls_date_format="DD-MON-RR" 4
                          0.03293 0.00075 ...NLS: Set g_nls_timestamp_format="DD-MON-RR HH.MI.SSXFF AM" 4
                          0.03367 0.00128 ...NLS: Set g_nls_timestamp_tz_format="DD-MON-RR HH.MI.SSXFF AM TZR" 4
                          0.03506 0.00138 NLS: Language=en-us 4
                          0.03633 0.00204 Application 124, Authentication: PLUGIN, Page Template: 11210918822644233 4
                          0.03837 0.00090 ...fetch session state from database 4
                          0.03927 0.00090 fetch items 4
                          0.04017 0.00098 ...fetched 14 session state items 4
                          0.04115 0.00693 Authentication check: Application Express (NATIVE_APEX_ACCOUNTS) 4
                          0.04807 0.00073 ... sentry+verification success 4
                          0.04880 0.00065 ...Session ID 956891539040101 can be used 4
                          0.04945 0.00150 ...Application session: 956891539040101, user=nobody 4
                          0.05096 0.00086 ...Setting session time_zone to -05:00 4
                          0.05182 0.00168 Session: Fetch session header information 4
                          0.05350 0.00103 Branch point: Before Header 4
                          0.05452 0.00255 Fetch application meta data 4
                          0.05707 0.00083 ...metadata, fetch computations 4
                          0.05790 0.00089 ...metadata, fetch buttons 4
                          0.05879 0.00063 ...http header processing 4
                          0.05941 0.00068 Computation point: Before Header 4
                          0.06009 0.00070 Processes - point: BEFORE_HEADER 4
                          0.06079 0.00070 ...Process "define default date" - Type: PLSQL 4
                          0.06149 0.00083 ......Skip because identified to run once per session and has already been run. 4
                          0.06243 0.00073 ...compatibility mode - do not set mime type 4
                          0.06304 0.01054 ...compatibility mode - do not set additional http headers 4
                          0.07358 0.00073 ...close http header 4
                          0.07431 0.00095 ...metadata, fetch item type settings 4
                          0.07526 0.00402 ...metadata, fetch items 4
                          0.07927 0.00139 Show page template header 4
                          0.08066 0.00170 Rendering form open tag and internal values 4
                          0.08236 0.00067 Computation point: After Header 4
                          0.08303 0.00214 Processes - point: AFTER_HEADER 4
                          0.08516 0.00258 Region: TTE Daily Outlier without Gargadge Collector 4
                          0.08774 0.00059 Computation point: Before Box Body 4
                          0.08833 0.00084 Processes - point: BEFORE_BOX_BODY 4
                          0.08918 0.00125 Region: Search 4
                          0.09043 0.00229 Item: P31_TIME NATIVE_DATE_PICKER_CLASSIC 4
                          0.09271 0.00166 Item: P31_SESS NATIVE_SELECT_LIST 4
                          0.09437 0.01788 ...Execute Statement: select unique sess d, sess r from tteol where time >= TO_DATE (:P31_TIME, 'MM/DD/YYYY') AND time < TO_DATE (:P31_TIME, 'MM/DD/YYYY') + 1 order by 1 4
                          0.11227 0.00130 Item: P31_TIME1 NATIVE_TEXT_FIELD 4
                          0.11356 0.00124 Item: P31_TIME2 NATIVE_TEXT_FIELD 4
                          0.11480 0.00122 Item: P31_BCRANGE NATIVE_TEXT_FIELD 4
                          0.11601 0.00143 Item: P31_CASRANGE NATIVE_TEXT_FIELD 4
                          0.11752 0.00129 Item: P31_REPORT_SEARCH NATIVE_TEXT_FIELD 4
                          0.11873 0.00326 Item: P31_EXCLUDE NATIVE_TEXT_FIELD 4
                          0.12200 0.00145 Item: P31_ROWS NATIVE_SELECT_LIST 4
                          0.12345 0.00256 ...Execute Statement: select /*+ cardinality(t 10) */ disp, val from table(wwv_flow_utilities.get_temp_lov_data(1)) t order by insert_order, disp 4
                          0.12601 0.00161 Item: P31_GO BUTTON 4
                          0.12762 0.00068 Region: Daily Outliers Without Garbage Collector in &P31_TIME. 4
                          0.12830 0.00234 Region: Daily Outliers Without Garbage Collector 2 in &P31_TIME. 4
                          0.13063 0.00295 show report 4
                          0.13359 0.00074 determine column headings 4
                          0.13433 0.00947 activate sort 4
                          0.14380 0.00081 parse query as: OPSADMIN 4
                          0.14461 0.00455 ...Execute Statement: SELECT TO_CHAR(time, 'MM/DD/YYYY') "TIME", "TID", 1000*rt rt, "USERID", gbct, "CLASS", CASE WHEN TYPE = 'Order' THEN "TYPE" || '(' || orig || ')' ELSE TYPE END TYPE, "SESS", "CAS", "CASIN", 1000*casd "CASD", "BC", ts, "BCD", 1000* casnetd casfe, dirf, midfrom, dirt, midto, 1000*tsdelta tsdelta, 1000*pllwait cllwait, 1000*pllhold cllhold, pllholdcnt, 1000*ohsd ohsd FROM "TTEOL" a WHERE time >= TO_DATE(:P31_TIME, 'MM/DD/YYYY') AND time < TO_DATE(:P31_TIME, 'MM/DD/YYYY') + 1 AND TO_CHAR(TO_TIMESTAMP(CASIN, 'HH24:MI:SS.FF'), 'HH24:MI:SS.FF3') BETWEEN :P31_TIME1 AND :P31_TIME2 AND bcd >= SUBSTR(:P31_BCRANGE, 1, INSTR(:P31_BCRANGE, '-') - 1) AND bcd <= SUBSTR(:P31_BCRANGE, INSTR(:P31_BCRANGE, '-') + 1) AND casd >= SUBSTR(:P31_CASRANGE, 1, INSTR(:P31_CASRANGE, '-') - 1) AND casd <= SUBSTR(:P31_CASRANGE, INSTR(:P31_CASRANGE, '-') + 1) AND gbc = 0 AND sess = :P31_SESS AND NOT EXISTS (SELECT NULL FROM "TTEOL" b WHERE time >= TO_DATE(TO_CHAR(A.time, 'MM/DD/YYYY'), 'MM/DD/YYYY') AND time < 1 + TO_DATE(:P31_TIME, 'MM/DD/YYYY') AND gbc = A.gbc AND sess = :P31_SESS AND a.tid = b.tid AND (0 < INSTR(UPPER("TID"), UPPER(NVL(:P31_EXCLUDE, "TID"))) OR 0 < INSTR(UPPER("USERID"), UPPER(NVL(:P31_EXCLUDE, "USERID"))) OR 0 < INSTR(UPPER("CLASS"), UPPER(NVL(:P31_EXCLUDE, "CLASS"))) OR 0 < INSTR(UPPER("TYPE"), UPPER(NVL(:P31_EXCLUDE, "TYPE"))) OR 0 < INSTR(UPPER("CAS"), UPPER(NVL(:P31_EXCLUDE, "CAS"))) OR 0 < INSTR(UPPER("CASIN"), UPPER(NVL(:P31_EXCLUDE, "CASIN"))) OR 0 < INSTR(UPPER("FE"), UPPER(NVL(:P31_EXCLUDE, "FE"))) OR 0 < INSTR(UPPER("BC"), UPPER(NVL(:P31_EXCLUDE, "BC"))) OR 0 < INSTR(UPPER("GC"), UPPER(NVL(:P31_EXCLUDE, "GC"))))) AND (0 < INSTR(UPPER("TID"), UPPER(NVL(:P31_REPORT_SEARCH, "TID"))) OR 0 < INSTR(UPPER("USERID"), UPPER(NVL(:P31_REPORT_SEARCH, "USERID"))) OR 0 < INSTR(UPPER("CLASS"), UPPER(NVL(:P31_REPORT_SEARCH, "CLASS"))) OR 0 < INSTR(UPPER("TYPE"), UPPER(NVL(:P31_REPORT_SEARCH, "TYPE"))) OR 0 < INSTR(UPPER("CAS"), UPPER(NVL(:P31_REPORT_SEARCH, "CAS"))) OR 0 < INSTR(UPPER("CASIN"), UPPER(NVL(:P31_REPORT_SEARCH, "CASIN"))) OR 0 < INSTR(UPPER("FE"), UPPER(NVL(:P31_REPORT_SEARCH, "FE"))) OR 0 < INSTR(UPPER("BC"), UPPER(NVL(:P31_REPORT_SEARCH, "BC"))) OR 0 < INSTR(UPPER("GC"), UPPER(NVL(:P31_REPORT_SEARCH, "GC")))) order by 10 nulls last,3 desc nulls last 4
                          0.14916 0.00139 print column headings 4
                          0.15055 - rows loop: 15 row(s) 4
                          • 10. Re: how to troubleshooting page slowness
                            joelkallman-Oracle
                            Hi Ching,

                            1) How did you determine that the page takes 500 seconds?

                            2) When the page is taking 500 seconds to complete, what do you see in your browser?

                            3) What is between the APEX engine and your browser?

                            4) How many rows are returned in your report?

                            Joel
                            • 11. Re: how to troubleshooting page slowness
                              349522
                              1) How did you determine that the page takes 500 seconds?
                              #TIMING# at footnote showed the time
                              2) When the page is taking 500 seconds to complete, what do you see in your browser?
                              first 15 rows of results
                              3) What is between the APEX engine and your browser?
                              ??? don't understand the question
                              4) How many rows are returned in your report?
                              total is 43,000 if that is what are you asking
                              • 12. Re: how to troubleshooting page slowness
                                joelkallman-Oracle
                                Hi Ching,

                                Does that page take 500 seconds to complete? That is - when you run the page with the report and you look at your watch/clock and start counting, does it truly take 8 minutes and 20 seconds to complete?

                                Joel
                                • 13. Re: how to troubleshooting page slowness
                                  349522
                                  yes, if we are lucky, we get report in 500 seconds, we also run into time out error someday, when the records is more than 90,000, where we have set timeout 600 seconds
                                  • 14. Re: how to troubleshooting page slowness
                                    joelkallman-Oracle
                                    It's time to look at what is between your browser and the APEX engine in the database. There's a network, there may be SSL, there may be the APEX Listener, there may be a database listener, etc. You should look at all of these components, because clearly the APEX engine is reporting it's complete with the page view after 0.15 seconds.

                                    If you're using Oracle HTTP Server, then you may want to also confirm in the Apache access log how long Apache thinks it took to deliver your request.

                                    I hope this helps.

                                    Joel
                                    1 2 Previous Next