1 2 Previous Next 27 Replies Latest reply: Feb 7, 2014 11:59 AM by jgarry RSS

    Performance Slow Again.

      Hi All,

       

      We encountered performance slow again, and our boss email all of us tech support that >JAN-30-2014: There are many transaction rejects again... Please check where the problem is.

       

      I tried to run this script:

       

      select 'OS Busy Time' series, to_char(snaptime, 'yyyy-mm-dd hh24 hh24:mi') snap_time,

      round(busydelta / (busydelta + idledelta) * 100, 2) "CPU Use (%)"

      from

      (select s.begin_interval_time snaptime,

      os1.value - lag(os1.value) over (order by s.snap_id) busydelta,

      os2.value - lag(os2.value) over (order by s.snap_id) idledelta

      from dba_hist_snapshot s, dba_hist_osstat os1, dba_hist_osstat os2

      where

      s.snap_id = os1.snap_id and s.snap_id = os2.snap_id

      and s.instance_number = os1.instance_number and s.instance_number = os2.instance_number

      and s.dbid = os1.dbid and s.dbid = os2.dbid

      and s.instance_number = (select instance_number from v$instance)

      and s.dbid = (select dbid from v$database)

      and os1.stat_name = 'BUSY_TIME'

      and os2.stat_name = 'IDLE_TIME')

       

      And I got this output:

       

      TimeCPU Use (%)
      26-Jan27-Jan28-Jan29-Jan30-Jan31-Jan1-Feb2-Feb3-Feb
      013.4216.849.9214.929.868.848.408.19
      110.868.0616.6911.188.4412.1114.0631.179.45
      224.537.019.428.0814.6618.795.154.514.84
      35.736.419.046.249.2811.364.524.514.53
      45.445.987.9710.179.224.894.584.354.72
      56.156.075.455.599.514.775.295.314.47
      610.126.135.565.9810.535.1512.979.484.67
      75.726.716.916.7012.935.675.054.165.57
      85.879.7015.2413.8215.249.996.365.097.46
      96.3916.5710.3512.3919.639.806.915.9714.55
      107.5317.7413.8220.2623.5510.579.157.0616.13
      116.6916.3214.9016.3624.4010.029.085.8015.30
      126.5712.0714.6315.3923.1310.578.235.4814.81
      137.0416.0317.0217.2127.5310.328.665.9117.43
      147.6415.7022.8720.2427.3511.169.816.3218.87
      157.5316.4719.2122.2028.9412.999.486.3718.74
      167.8517.7819.6220.9133.1812.848.376.3321.27
      179.5917.1518.2320.9629.0013.797.936.42
      188.8613.3420.7816.1521.0712.398.207.68
      198.4213.4714.0914.2616.4812.227.156.62
      208.4311.1513.8813.3015.0213.056.646.85
      218.6511.0514.0314.3615.7314.246.176.89
      229.3521.6520.4225.5421.1129.947.487.91
      238.6011.4711.5410.1911.4412.436.286.19
      Average8.4212.3914.1014.0618.4311.627.777.2811.24

       

       

      I found out that Jan-30 has the highest average CPU usage. Specially 1pm-5pm with maximum of 27-33% .Does this mean that this is the ceiling %CPU when transaction gets rejected?

      Is this CPU% a database CPU usage or Aix OS CPU usage? Do I need to capture the TOP SQL at  Jan 30, 1-5pm?

       

      Thanks,

      pK

        • 1. Re: Performance Slow Again.
          Hoek

          Instead of focusing on CPU, imho you'd start focusing on the database and what's goin on there.

          See:

          Performance Tuning Overview

          • 2. Re: Performance Slow Again.
            sb92075

            > There are many transaction rejects again...

            APPLICATION BUG.


            Oracle DB  does not know how or why to reject transaction & accept others.

            • 3. Re: Performance Slow Again.
              jgarry

              Do you have an application server?  Does it get error messages from the database?  What errors?

              • 4. Re: Performance Slow Again.

                The app dev group said there is no error.

                 

                I am just taking chance that I can see something in AWR which  I need to adjust some parameters in the database? like memory?

                Since the behavior is like every pay-day 15th/30th of the month the CPU is high. And it is at this time that most transactions are rejected which connotes that the database server may not able to handle lots of connections or transactions?

                • 5. Re: Performance Slow Again.
                  rp0428
                  I am just taking chance that I can see something in AWR

                  I'm not familiar with that style of troubleshooting.

                   

                  Can you provide more info about it? For example, do you write possible causes on a piece of paper, put them in a hat and then draw one out at random?

                   

                  Why don't you explain what 'most transactions are rejected' means?

                   

                  What does 'We encountered performance slow again' mean. How do you know performance is now 'slow'? How did you know before that performance was 'not slow'?

                   

                  You need to start by gathering the facts.

                  • 6. Re: Performance Slow Again.

                    Thanks Rp,

                     

                    Actually as dba we only knew that there is performance issue when the user starts complaining that the application webpage very slow to launch or is hanging.

                    So if it hanging then chances are the pending processed transactions get aborted and were not process successfully.

                     

                    Since this happens most of the time when lots of users are connecting every payday of the month. So my guess is some existing parameter  was not tuned for this high volume of transactions?

                    It is as simple logic as  1+1=2

                    • 7. Re: Performance Slow Again.
                      rp0428

                      It could be as simple as your middle-tier connection pool only allows 3 connections and you have 8 million users trying to connect!

                       

                      Your users should take their complaint to the 'application webpage' people and have them determine why their application is 'very slow to launch or is hanging'. Don't just assume that it is a database problem.

                       

                      If the application locks an entire table no one else will be able to update it until the lock is released; that could cause EVERY session to 'hang'.

                       

                       


                      • 8. Re: Performance Slow Again.
                        jgarry

                        You might investigate whether a slow system anywhere in the chain is causing the app to retry a login, making it all slower, storming the battlements with more login attempts... 

                        • 9. Re: Performance Slow Again.

                          Thanks Rp & Jg & Sb & All,

                           

                          Our 3rd party app was turned over to us without much detailed training on our part in tuning it.

                          I asked the sysadmin what webserver do we used and he said we used websphere. I ask them to check if there are connection errors being logged there and he said no one has been trained to tune the websphere.

                           

                          Can you help me where to check errors in websphere and how to adjust connection pooling?

                           

                          Is websphere and weblogic the same? I heard they were bought by Oracle  so it is now an Oracle product.

                           

                           

                          Thanks

                          • 10. Re: Performance Slow Again.
                            EdStevens

                            f55237a7-2c38-4db3-a7a3-1d77256f0730 wrote:

                             

                            Thanks Rp & Jg & Sb & All,

                             

                            Our 3rd party app was turned over to us without much detailed training on our part in tuning it.

                            I asked the sysadmin what webserver do we used and he said we used websphere. I ask them to check if there are connection errors being logged there and he said no one has been trained to tune the websphere.

                             

                            So your company bought a commercial app that is based on infrastructure for which they have not hired the requisite expertise?  And worse, the people they do have are incapable of independent research and study? 

                            • 11. Re: Performance Slow Again.
                              Nicolas.Gasparotto

                              In the end, Petra is all but not a troll. She desperately wants us to do her job.

                               

                              Nicolas.

                              • 12. Re: Performance Slow Again.
                                sb92075

                                Nicolas.Gasparotto wrote:

                                 

                                She desperately wants us to do her job.

                                 

                                Nicolas.

                                +1

                                • 13. Re: Performance Slow Again.
                                  jgarry

                                  No websphere is IBM and weblogic is Oracle.

                                   

                                  Yes, there is such a thing as a stupid question.  One definition is asking something you should have already known or been able to easily figure out yourself.

                                  • 14. Re: Performance Slow Again.
                                    sb92075

                                    OP is unwilling or incapable to use GOOGLE

                                    1 2 Previous Next