This discussion is archived
12 Replies Latest reply: May 17, 2013 5:55 AM by Phelit RSS

Application slows down randomly.

Phelit Newbie
Currently Being Moderated
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.
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Hi,

    How about the AWR/ADDM report. You can see some issue there :)
  • 3. Re: Application slows down randomly.
    Phelit Newbie
    Currently Being Moderated
    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.
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.
    Justin Cave Oracle ACE
    Currently Being Moderated
    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.
    User477708-OC Journeyer
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    @Phelit it is difficult for us to help you without atleast an AWR in front of us.
  • 11. Re: Application slows down randomly.
    User477708-OC Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points