Results 1 to 9 of 9

Thread: High wait times

  1. #1
    Join Date
    Apr 2009
    Posts
    3

    Red face Unanswered: High wait times

    Hi All
    I am not an Oracle DBA but I hope some could possibly help me, I am a Sap Basis Admin and we are having a problem with high wait times. I am applying a support pack and it took three day's in our QA env. In Dev it took 1.5 hrs in PRD it took 45 minutes. Some issues that stood out in QA are

    Event Wait time(ms) Waits
    log file switch (archiving needed) 466.439.871 556.751
    db file sequential read 225.121.974 640.873.033
    RMAN backup & recovery I/O 114.114.965 789.572




    Event wait time (ms) Waits
    rdbms ipc message 4.817.515.759 / 2.216.887
    rdbms ipc message 4.817.515.759 / 2.216.887
    SQL*Net message from client 4.129.424.028 / 72.484.516
    SQL*Net message from client 4.129.424.028 / 72.484.516
    pmon timer 606.126.167 / 197.909

    We are on Oracle version 10.2.0.4 any help would be greatly appreciated.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I am a Sap Basis Admin and we are having a problem with high wait times.
    Why did you conclude Wait Times are the problem & relative to what are they considered "high"?

    > I am applying a support pack and it took three day's in our QA env. In Dev it took 1.5 hrs in PRD it took 45 minutes.
    Why such a big difference between the environments?
    What does "applying a support pack" mean?

    I suggest that SQL_TRACE=TRUE to see where time is really being spent.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2009
    Posts
    3

    Wait times

    I was just going into DBACockpit looking at some things that stood out. for the high wait times are they high? Or you cant tell. The support packs are modifications to Sap ie code changes security changes enhancements. Thats what I was questioning why the big difference between systems can you give me some things to look at so maybe I can trace it down. I think it could be Oracle perhaps. Thanks for helping

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    When it comes to tuning Oracle 2 general approaches exist; 1) top down or 2) bottom up.
    Top down involves changing initSID.ora parameters.
    Bottom up involves identifying SQL statements which take a long time & tune the SQL.

    >The support packs are modifications to Sap ie code changes security changes enhancements
    These would be either DML or DDL & not anything which could be changed.

    I am sorry, but I don't have any advice for you.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    As Anacedent said, It's pretty tough to troubleshoot without digging into the waits and their cause ...

    the SQLNet message and the ipc message waits you really don't care too much about.

    the only waits that you have shown worth looking into are:
    log file switch
    db file sequential read

    Other things ... I would assume the PROD machine is a pretty "beefy" machine with a large enough SGA ( enough ram and fast drives) and
    has probably been tuned and objects analyzed.

    My guess would be that the DEV machine have a smaller sga but still
    not too bad. Statistics may be a little older and less tuning.
    The QA machine is probably not tuned and a less than adaquate sga,
    possibly all database files (data, control, redos, etc) on the same drive.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    In any report of "wait times" some items will ALWAYS be at the top of this list.
    One of the wait times will be larger than the rest of them.
    Because there is an order list of wait times, does not mean that there is anything wrong.

    Another way of assessing the situation is to do a mental exercise.
    Pretend you have a magic wand that could be waved over the DB server which 100% eliminates all the wait times completely.
    How much absolute wait time has been eliminated & what is this as percentage of total elapsed time is this?
    In other words if total wait time is only 5% of total elapsed time, then eliminating wait time won't produce much benefit.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Very true !!

    Just because there are Waits ... does that mean something is wrong???
    NO ...

    for all we know the QA box mentioned may be a Pentium II with 512 ram
    and 5700rpm disks ... It's not waiting ... it's chugging along

  8. #8
    Join Date
    Apr 2009
    Posts
    3
    Hi Guys thanks for your help and tips I really appreciate the quick responce.
    The problem was resolved by running the update statistics in Sap.

    Thanks again

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Thanks for the feedback & follow up.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •