10 Replies Latest reply: Sep 5, 2014 8:35 AM by TexasApexDeveloper RSS

    Apex bogged down and sloooowwwww

    Alexandra Robin

      Application Express 4.2.2.00.11

       

      While developing stuff in Apex, things have been getting so bogged down that IE times out and the "Recover Website" option comes up at the bottom of my screen.  I'm the report developer, not the network administrator.  My boss (also not the network administrator, but knows as much if not more than he does) found this as a possibility where the problem is.  Your help, as always, hugely appreciated in advance.

       

      “C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\sqlnet.ora”

      This file apparently is used to set parameters for Oracle. The “live” version on our
      server seems to be almost empty and does not contain a variable like the one
      shown below.

       

      From the example file one level further down in the folder
      hierarchy:

       

      ########################

      #sqlnet.expire_time = 10

      ########################

      #

      #Possible values: 0-any valid positive integer! (in minutes)

      #Default: 0 minutes

      #Recommended value: 10 minutes

          #Purpose: Indicates the time interval to send a probe to
      verify the  client session is alive (this is used to reclaim watseful

         resources on a dead client)

      #Supported since: v2.1

        • 1. Re: Apex bogged down and sloooowwwww
          TexasApexDeveloper

          What you need is someone to come into your environment and setup your database and APEX with the proper configuration (a DBA type person if you will..).

           

          Also you MIGHT want to look at the queries being run to be sure they are properly tuned, so the server has less work to do..

           

          (I know these are pie in the sky ideas, but just throwing them out there, so that you can pass them UP your command chain, so maybe someone will try HELPING you solve the issue...)

           

          Thank you,

           

          Tony Miller
          LuvMuffin Software
          Ruckersville, VA

          • 2. Re: Apex bogged down and sloooowwwww
            Earl Lewis

            Is it all pages? Or just some pages? You're the report developer so is it just reports that you're developing?

             

            It might be helpful to see an example query and the "explain plan" for it. You can get the explain plan from the bottom of the SQL command window. If you don't know how to read the explain plan you could post it here and we could help you read it.

             

            Earl

            • 3. Re: Apex bogged down and sloooowwwww
              fac586

              Alexandra Robin wrote:

               

              Application Express 4.2.2.00.11

              And what else? The database and webserver details are actually more important in this instance.

              While developing stuff in Apex, things have been getting so bogged down

              What does this mean? Are you referring to the performance/response time you are getting as a developer when working in the Application Builder? The performance/response times seen by users of your applications? Or both?

              I'm the report developer, not the network administrator.  My boss (also not the network administrator, but knows as much if not more than he does) found this as a possibility where the problem is.  Your help, as always, hugely appreciated in advance.

               

              “C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\sqlnet.ora”

              This file apparently is used to set parameters for Oracle. The “live” version on our
              server seems to be almost empty and does not contain a variable like the one
              shown below.

               

              From the example file one level further down in the folder
              hierarchy:

               

              ########################

              #sqlnet.expire_time = 10

              ########################

              #

              #Possible values: 0-any valid positive integer! (in minutes)

              #Default: 0 minutes

              #Recommended value: 10 minutes

                  #Purpose: Indicates the time interval to send a probe to
              verify the  client session is alive (this is used to reclaim watseful

                resources on a dead client)

              #Supported since: v2.1

              On what basis is this being considered as a possibility? It's much less likely than many others, and impossible to evaluate without full details of the database and app/web server configuration, and network topology. We need facts, not random hypotheses. If you have evidence that this is the most likely reason for the problem, what is it?

              • 4. Re: Apex bogged down and sloooowwwww
                Alexandra Robin

                Thanks, Everyone.  I was off on Friday.  Let me get myself back in the swing of this and I'll try to give more detail.  Yep, Tony, it would be great to have things properly tuned and my database set up properly.

                 

                Earl, it's the recent "module" I've been adding on.  I'll see if I can make sense of the "explain plan."  And thanks for the suggestion to post query examples.  I shall once I get rolling again this morning; unless everything has miraculously cleared up in my absence.

                 

                And Fac586: it was during my development of pages.  While creating reports and forms, testing them to run, they spun out.

                 

                None of the above is any help I know.  I'll return to this project sometime this week and give you an update, hopefully with information you can use.

                Thanks and sorry-

                Alex

                • 5. Re: Apex bogged down and sloooowwwww
                  fac586

                  Alexandra Robin wrote:

                   

                  And Fac586: it was during my development of pages.  While creating reports and forms, testing them to run, they spun out.

                  So the problem is in one of your applications at runtime, not in app builder?

                  • 6. Re: Apex bogged down and sloooowwwww
                    TexasApexDeveloper

                    1st suggestion would be, take the query out of page and run withing sql developer, see the sql explain plan like others have suggested.  Make sure you are using bind variables for filtering. Also make sure your joins are all correct, have seen times where a join was done improperly and the query was doing a Cartesian product...

                     

                    Thank you,

                     

                    Tony Miller
                    LuvMuffin Software
                    Ruckersville, VA

                    • 7. Re: Apex bogged down and sloooowwwww
                      Alexandra Robin

                      Thanks, you guys.  I haven't revisited this in awhile.  Will do when I've got the time.

                      All the best -

                      A

                      • 8. Re: Apex bogged down and sloooowwwww
                        joelkallman-Oracle

                        Alexandra,

                         

                        I think the questions everyone has asked and you have answered have most assuredly identified the likely culprit.  If your usage of the Application Builder, itself, is normally fast, and it's only the report pages that you've authored seem slow, then most likely:

                         

                        1. Your SQLNET.ORA file is just fine.
                        2. Your Web server and database settings are acceptable, for the time being.
                        3. APEX is doing exactly what you tell it to do, slow-performing SQL and all.  If you run one of your pages in Debug mode and look at the timings, you'll most likely see in explicit detail the execution timing of your page.  And most likely, the culprit will be the execution of the SQL you've authored.

                         

                        Thus, it's most likely not "Apex bogged down and sloooowwwww".  Rather, it's the SQL you've told APEX to run that is "bogged down and sloooowwwww".  It could involve rewriting the query, adding an index, etc.

                         

                        I hope this helps.

                         

                        Joel

                        • 9. Re: Apex bogged down and sloooowwwww
                          Alexandra Robin

                          Thanks, Joel - very well put.  Makes sense as I'm always running on the skin of my sql teeth knowing I've not got the knowledge to tune my queries for speed.

                          I'll work on it.

                          All the best, and thanks again to all for your help.

                          • 10. Re: Apex bogged down and sloooowwwww
                            TexasApexDeveloper

                            Alex,

                              Saw this blog entry and thought of your situation: How to identify SQL performing poorly on an APEX application? | Carlos Sierra's Tools and Tips

                             

                            Thank you,

                             

                            Tony Miller
                            LuvMuffin Software
                            Ruckersville, VA