5 Replies Latest reply: Aug 13, 2013 5:16 AM by Christian Erlinger RSS

    Forms 10g App. is slow: What is the official procedure to follow?

    user12240205

      Hello Forms gurus. This question has been bugging me for some time. Here is the scenario.

       

      1. We have a Telco application developed using Forms 10g.
      2. A user complains that his form is slow.
      3. We have access to the live DB (assume it is a 10g DB).
      4. However, we cannot run the form in the live DB. We have no access to it. We only have access to the live DB.

       

      • What is the "procedure" to follow in a situation like this???
      • Is there an Oracle Recommended procedure to follow?

       

      You inputs will be greatly appreciated.

        • 1. Re: Forms 10g App. is slow: What is the official procedure to follow?
          andrewmy

          This is going to be a challenge, like trying to diagnose a patient when you are only allowed to examine him from the waist down only.  In a typical Forms 10g application the data passes through 3 tiers: client - app server - database server so the slowdown may be in any of them. Additionally the slowdown could also be caused by network issues.

           

          If you can only examine the database - you can only check if the database is the bottleneck. If you know the approximate date/time when the slowdown occurred you may want to take a look at the performance statistics during that window of time. In 10g you can either do this with Automatic Performance Statistics (AWR) (if you are on Enterprise Edition and have the Diagnostic and Tuning Pack licenses) or install Statspack and wait for the next slowdown. In either case what you are looking for are bottlenecks where the database is spending the majority of time. There may actually be no real bottlenecks found in which case the problem may be elsewhere. Even if you do find the culprit e.g. a horribly slow-performing SQL that is being sent by the form that should be re-written, if you aren't allowed to tune the SQL in the form, there may be little you can do.

          • 2. Re: Forms 10g App. is slow: What is the official procedure to follow?
            O.Developer

            YOur  situvation is  same for many companies  buying  application from consultant without  access to coding.

             

            One way of addressing this issue is...take  dump of  live database and  import in backup  system.

             

            Ask  user to simulate the same in backup system with latest backup....there  are chances you can identify.

             

            It is all trial and error  method only u can fix  as u have limited  access

            • 3. Re: Forms 10g App. is slow: What is the official procedure to follow?
              Michael Ferrante-Oracle

              Don't assume you have a db issue just because you hear the word "slow".

               

              Let me take you a few steps backward before you try going any further forward.  After 13 years working in Oracle Support and over 30 total years doing technical troubleshooting, the one thing I learned is that in order to successfully fix any problem, you must first understand it.

               

              In this case, step one (1) will be to determine exactly which product versions are being used and on exactly which platforms. You also need to understand the conditions.  So, consider this:

               

              • What is the client platform and version (e.g. Windows 7 Professional)?
              • How much memory (RAM) is installed on the poor performing client machine?
              • Which browser and version is used (e.g. Internet Explorer 8)?
              • Which Java Plugin and version is used (e.g. Java SE 6U45)?
              • What is the complete Oracle Forms version (e.g. 10.1.2.3)?  "10g" is not a version.  This is portion of a product name.  In the future, please use complete product versions.
              • On which platform and version is Oracle Forms installed (e.g. OEL4)?
              • What is the complete database version to which Forms is connecting?  "10g" is not a version.  This is portion of a product name.
              • What is the approximate number of concurrent users when the poor performance occurs?
              • Did this ever work correctly (better)?  If yes, what has changed?
              • And so on....

               

              Next, you (or they) said:

              "...user complains that his form is slow..."

               

              This statement can mean a countless number of things and be caused by an equally countless number of root causes.  So, you need to first, deterimine what "slow" means.

               

              • Is the applicaton slow to start?
              • Is the application slow navigating from field to field or form to form?
              • Is the application slow to insert, update, delete, or query?  Which specifically?
              • And so on....

               

              Once you know the answer to these, you then need to determine when the performance issue is at its worst.  In other words, does the same performance issue reproduce when only a few people are accessing the system?  Then, the most important piece of information after the above will be to understand to what they are comparing this "slow" performance.  In other words, if a query is taking 15 seconds to complete, how can you call this slow if you have nothing to compare it with?  Was the application previously based on an older Forms version where it ran faster?  If so, how much faster and for exactly which task?

               

              Notice how in all the above questioning, nowhere did I suggest how to fix the problem.  If you try to "fix" this problem before understanding all of the above first, there is a good chance you will either make a bigger mess or take forever to solve the problem.

              • 4. Re: Forms 10g App. is slow: What is the official procedure to follow?
                user12240205

                Thanks guys for the answers, especially Michael. I must say that the 2nd method of taking a dump of the tables and importing a schema in development server is not possible (at least here) since this is not allowed in most cases due to security issues and there is logistical problems, like if it takes about 250 GB how to tranfer?? from one site another??

                 

                AWR is also not there is any of our our customer sites. Statspack seem a great idea, but that seems to be turned on by the DBA and seems very complicated.

                 

                I have to say this. Here in my country, DBAs basically run the show.  They have final say on a system. I know it's not fair, but that's the situation here. Oracle Forms developers are not that much regarded here.

                 

                So, according to the them, if a form is "slow", 99.99% it is caused by a slow query.  So, they expect us developers to know tuning stuff. If I ask all the questions that Michael said (which is the "correct" way), believe me, I will not get any answers or maybe a good yelling from my boss.  They expect us to go to the DB straight and find the problem.

                 

                Assuming this scenario (i.e. slow query), what is the best way to find the slow performing query (without using statspack)???

                 

                There are 2 scenarios:

                (1.) We got e-mail from user saying form is slow. But he is not using the form now.

                         -- Is there a way to find out slow queries that happened say few days ago? Are they stored somewhere?

                (2.) User is currently logged on to the system and using the form.

                        -- Can you find out an slow query happening right now??

                • 5. Re: Forms 10g App. is slow: What is the official procedure to follow?
                  Christian Erlinger

                  Are you sure the database is the cause of your performance problems? Can you identify slow queries? The first thing you should investigate is *where* your problem relies. Ask your DBAs if they can tell you the bad performing queries or help you analyze if there are any. If the database behaves well then most certainly it isn't the culprit of your performance problem. Of course there are some other no-gos which might affect application performance (like slow-by-slow processing, or serializing, or what ever else). So you need to identify where the time is spent first.

                  So, according to the them, if a form is "slow", 99.99% it is caused by a slow query.  So, they expect us developers to know tuning stuff.

                  Erm...and who else should know it? Developers design the application, developers know how it should work, and therefore are the ones who should know how to tune it. And I'm not talking about looking into why the Hard disk responds slow kind of stuff but simple identifying sub-optimal code, or SQL tuning by looking at the execution plans, interpreting them and solving them by...oh, I don't know, rewriting the query, minimizing recursive SQL, and what ever else. DBAs on the other hand know next to nothing about your application. How should they know if a query plan is bad or not (except the query responds slow)? How should they know how to rewrite your query properly if they don't know what it's supposed to do? Don't write a query whatever way you like and expect them to understand it and tune it which might even imply changes on your underlying data model.

                   

                  There are 2 scenarios:

                  (1.) We got e-mail from user saying form is slow. But he is not using the form now.

                           -- Is there a way to find out slow queries that happened say few days ago? Are they stored somewhere?

                  (2.) User is currently logged on to the system and using the form.

                          -- Can you find out an slow query happening right now??

                  1.) This would be AWR. As they have the final saying about the database then ask them to provide you a AWR report of a Time period where you had performance problems. If you don't have AWR then it'll be somehow difficult to analyze performance problems of the past. But on the other hand I wouldn't worry too much if you can constantly reproduce your problems as this would take us straight foreward to:

                  2.) That would be SQL Trace. Simply trace the session experiencing performance problems, and run tkprof on the tracefile. If you don't know how to do that and don't know how to interpret a query plan then you might ask your DBAs if they can assist you. If they insist that you must know that on your own maybe ask them to teach you. It is one thing to ask someone to do your job, but an entirely other thing to ask someone for help to do your job better and share some knowledge.

                   

                  In the end it'll be easier to work *with* your DBAs to identify and solve performance problems and not working *against* them. You have knowledge about your application, about what data is stored where and why but might lack some basic knowledge about SQL Tuning. Your DBA on the other hand has no knowledge about your application but might have a better understanding of basic SQL tuning. IMHO the both of you would make a good couple to solve SQL performance problems as your knowledge combined should make it easy to resolve your problem.

                   

                  cheers