12 Replies Latest reply: May 17, 2013 7:55 AM by Phelit RSS

    Application slows down randomly.

    Phelit
      Hi all,

      I have an application that works on connecting and making queries on an oracle DB. Every now and then, for no apparent reason, every query it does becomes extremely slow. I don't know if anyone ever had a similar problem.

      To make a few tests I created a simple procedure that connects to the DB and does the same select (select * from dual) in a loop for 100K times. I still get the same problem, most of the queries go instantly, but sometimes, in groups of 3 or more, the queries take up to 10 seconds!! I don't know what to do!

      I have Windows server 2003 64bit R2 and Oracle 10.2.0.4.

      I have verified that it is not a network problem (I get the issue also locally) or a Windows problem (I have plenty of RAM and free disk space)

      Any hints? Let me know if you need more info.

      P.s. I can't tell you anything about the application because I don't know how it is written and I am not allowed to know....
        • 1. Re: Application slows down randomly.
          JustinCave
          Are you licensed to use the AWR? Is Statspack installed? Do you have an AWR/ Statspack report for the system when performance is slow? Can you query the ASH data for the session(s) that are slow?

          Justin
          • 2. Re: Application slows down randomly.
            DK2010
            Hi,

            How about the AWR/ADDM report. You can see some issue there :)
            • 3. Re: Application slows down randomly.
              Phelit
              Done both AWR and ADDM a while ago and gave no results at all! For what I saw. I still have the logs, but they are too long to attach. I don't know if any of you want to see them and how to upload them (if possible)
              • 4. Re: Application slows down randomly.
                JustinCave
                What does "no results" mean, exactly? Do you mean that there was no report generated? That you weren't able to gather any information from the report? Something else?

                I assume this means that you are licensed to use the AWR?

                Justin
                • 5. Re: Application slows down randomly.
                  Phelit
                  I mean that I lunched the AWRRPT.SQL and ADDMRPT.SQL and the logs I got gave no interesting result. The DB appears at peace, no delays and hardly and suggestion from the ADDM.
                  • 6. Re: Application slows down randomly.
                    JustinCave
                    What are the wait events on the AWR report for a time period when then queries are slow? Is your snapshot interval short enough that the slowdown is apparent?

                    Can you identify the sessions in the ASH tables where the queries are slow? What are the queries waiting on?

                    Justin
                    • 7. Re: Application slows down randomly.
                      oralicious
                      redo the test. during the test, capture the wait events, either through the awr, stats OR live using this query. when you get the delay, look at the wait events, post them back here

                      If you want to automate it Id suggest putting the SQL in a loop, spool, when you get a delay you should have wait event of a few seconds if its the database thats causing it.
                      SQL> 
                        1  select w.sid sid,
                        2         p.spid PID,
                        3         w.event event,
                        4         substr(s.username,1,10) username,
                        5         substr(s.osuser, 1,10) osuser,
                        6         w.state state,
                        7         w.wait_time wait_time,
                        8         w.seconds_in_wait wis,
                        9         substr(w.p1text||' '||to_char(w.P1)||'-'||
                       10                w.p2text||' '||to_char(w.P2)||'-'||
                       11                w.p3text||' '||to_char(w.P3), 1, 45) P1_P2_P3_TEXT
                       12  from v$session_wait w, v$session s, v$process p
                       13  where s.sid=w.sid
                       14    and p.addr  = s.paddr
                       15    and w.event not in ('SQL*Net message from client', 'pipe get')
                       16*   and s.username is not null
                      15:14:44 vSQL>
                      
                      
                      
                                                                                                                       Sec. Sec.
                          SID PID                      Event                          USERNAME   OSUSER     STATE       Wtd Wtng P1_P2_P3_TEXT
                      ------- ------------------------ ------------------------------ ---------- ---------- ---------- ---- ---- ----------------------------------------
                         2273 22100                    Streams AQ: waiting for messag DBSNMP     oracle     WAITING       0    2 queue id 12603-process# 18678595376-wait
                                                                                                                                  time
                      check the wtng column..
                      • 8. Re: Application slows down randomly.
                        Jonathan Lewis
                        Phelit wrote:

                        To make a few tests I created a simple procedure that connects to the DB and does the same select (select * from dual) in a loop for 100K times. I still get the same problem, most of the queries go instantly, but sometimes, in groups of 3 or more, the queries take up to 10 seconds!! I don't know what to do!
                        a) repeatable test case
                        b) sometimes / groups of 3
                        c) 100K times in a loop

                        Sampling (ASH) or averaging (AWR) methods will probably not help.
                        Run the test with sql_trace enabled to show wait states, the do a detailed analysis of the trace file to see if you can find any waits that appear when your queries are slow. If there are no such waits, then you'll have to check timestamps through the file to see if you can find gaps that correspond to your slow queries. (The Hotsos profiler (licensed) or orasrp (free) will help with the analysis, but ultimately you may need to do some very detailed checks.)

                        Regards
                        Jonathan Lewis
                        • 9. Re: Application slows down randomly.
                          Phelit
                          I lauched the query you gave me when the problem was on...and this is the output:

                          SID PID
                          ---------- ------------
                          EVENT USERNAME
                          ---------------------------------------------------------------- ----------
                          OSUSER STATE WAIT_TIME WIS
                          ---------- ------------------- ---------- ----------
                          P1_P2_P3_TEXT
                          ---------------------------------------------
                          27 4296
                          SQL*Net message to client SYS
                          DCS\Andrea WAITED SHORT TIME -1 0
                          driver id 1413697536-#bytes 1- 0


                          SID PID
                          ---------- ------------
                          EVENT USERNAME
                          ---------------------------------------------------------------- ----------
                          OSUSER STATE WAIT_TIME WIS
                          ---------- ------------------- ---------- ----------
                          P1_P2_P3_TEXT
                          ---------------------------------------------
                          32 5604
                          Streams AQ: waiting for messages in the queue DBSNMP
                          SYSTEM WAITING 0 1
                          queue id 8877-process# 2533389952-wait time 5



                          ... doesn't look very meaningful.....

                          Anything else? :)

                          Thanks a lot everyone!
                          • 10. Re: Application slows down randomly.
                            Kunwar
                            @Phelit it is difficult for us to help you without atleast an AWR in front of us.
                            • 11. Re: Application slows down randomly.
                              oralicious
                              Phelit wrote:
                              I lauched the query you gave me when the problem was on...and this is the output:

                              SID PID
                              ---------- ------------
                              EVENT USERNAME
                              ---------------------------------------------------------------- ----------
                              OSUSER STATE WAIT_TIME WIS
                              ---------- ------------------- ---------- ----------
                              P1_P2_P3_TEXT
                              ---------------------------------------------
                              27 4296
                              SQL*Net message to client SYS
                              DCS\Andrea WAITED SHORT TIME -1 0
                              driver id 1413697536-#bytes 1- 0


                              SID PID
                              ---------- ------------
                              EVENT USERNAME
                              ---------------------------------------------------------------- ----------
                              OSUSER STATE WAIT_TIME WIS
                              ---------- ------------------- ---------- ----------
                              P1_P2_P3_TEXT
                              ---------------------------------------------
                              32 5604
                              Streams AQ: waiting for messages in the queue DBSNMP
                              SYSTEM WAITING 0 1
                              queue id 8877-process# 2533389952-wait time 5



                              ... doesn't look very meaningful.....

                              Anything else? :)

                              Thanks a lot everyone!
                              that indicates the sql net message to client wait event with no waits which means oracle has completed its processing and is sending the data back to the client but theres no wait there (zero time waiting). Can you run the same query from the server and see do you get the delay? When you got the delay did you query the waits at that exact time? how do you know there was a delay, can you set tim on timi on and spool the call to your query which will show you exact time of delay? Might be network you know.

                              In my experience the AWR might not show you something here, if you have run a query 1000 times and if 1 time the query took a long time, the average will lengthen out but if its short time to run for all other times you might not see it as a problem due to average runs.
                              • 12. Re: Application slows down randomly.
                                Phelit
                                It appears that the problem was the Server. We moved the DB to another server and the issue disappeared. Sadly we didn't understand why this issue appeared on the server. At least we have a working application now.

                                Thanks everyone for the help!