This discussion is archived
4 Replies Latest reply: Sep 4, 2013 1:11 PM by taohiko RSS

What is measure of "Waits" value in AWR Report?

taohiko Newbie
Currently Being Moderated
EventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn
SQL*Net more data from dblink406,747 4661184.13
db file scattered read28,659 3301212.97
SQL*Net message from dblink30,115 254813.63
log file parallel write21,021 17789.52

 

Hi All

 

    As below example, I have question What is measure of "Waits" value in "Wait Events" section in AWR report?

    Because "Waits" value is high but "Total Wait Time (s)" is low. example "SQL*Net message from dblink" has 30,115 "Waits" but "Total Wait Time (s)" less than "db file scattered read" has 28,659 "Waits".

 

    Could you please explain more about "Waits" value?

 

Thank you

Hiko

  • 1. Re: What is measure of "Waits" value in AWR Report?
    sb92075 Guru
    Currently Being Moderated

    when all else fails Read The Fine Manual

     

    Oracle Wait Events

     

     

    the "Waits" column shows the count of the number of Wait Events  which occurred?

     

    >Because "Waits" value is high

    Why do you consider the value to be high?

    What value do you deem to be acceptable?

  • 2. Re: What is measure of "Waits" value in AWR Report?
    Aman.... Oracle ACE
    Currently Being Moderated

    taohiko wrote:

     

    EventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn
    SQL*Net more data from dblink 406,747 466 1 184.13
    db file scattered read 28,659 330 12 12.97
    SQL*Net message from dblink 30,115 254 8 13.63
    log file parallel write 21,021 177 8 9.52

     

    Hi All

     

        As below example, I have question What is measure of "Waits" value in "Wait Events" section in AWR report?

        Because "Waits" value is high but "Total Wait Time (s)" is low. example "SQL*Net message from dblink" has 30,115 "Waits" but "Total Wait Time (s)" less than "db file scattered read" has 28,659 "Waits".

     

        Could you please explain more about "Waits" value?

     

    Thank you

    Hiko

    The meaning of "Waits" being high but the "Total Wait Time(s)" being less is that though there were significant number of waits, the wait didn't last for long. As for the answer, the unit of Waits is their count so in your example, it would be 46747 total waits that did occur.

     

    Aman....

  • 3. Re: What is measure of "Waits" value in AWR Report?
    EdStevens Guru
    Currently Being Moderated

    taohiko wrote:

     

    EventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn
    SQL*Net more data from dblink 406,747 466 1 184.13
    db file scattered read 28,659 330 12 12.97
    SQL*Net message from dblink 30,115 254 8 13.63
    log file parallel write 21,021 177 8 9.52

     

    Hi All

     

        As below example, I have question What is measure of "Waits" value in "Wait Events" section in AWR report?

     

    It's simply a count ... of the number of times the system had to "WAIT" on the specified event.

     

     

        Because "Waits" value is high but "Total Wait Time (s)" is low. example "SQL*Net message from dblink" has 30,115 "Waits" but "Total Wait Time (s)" less than "db file scattered read" has 28,659 "Waits".

    Look at "AVG wait time".    In the one case, there were fewer occurrences of the event, but the average wait time per event was greater.

     

    If I buy 3 hamburgers and pay $1000 each

    or I buy 500 hamburgers and pay $1.00 each,

    why do 3 hamburgers cost more than 500 hamburgers?

     

     

     

        Could you please explain more about "Waits" value?

    "Waits" is the number of times the system had to wait on the named event, during the sampling period.

    "Total Wait Time" is the total time (measured in seconds) spent waiting on all occurrences of the specified event, during the sampling period.

    "Avg" wait" is the average time (measured in milliseconds) spent on each occurrence of the specified event, during the sampling period.

     

    It seems pretty self-evident.  You count how many times something happens (waits), you measure the time spent doing that something (total wait time), and you divide the total time spent (total wait time) by the number of times it happened (waits) to find out the average time spent on each occurrence of that something.   

    Thank you

    Hiko

  • 4. Re: What is measure of "Waits" value in AWR Report?
    taohiko Newbie
    Currently Being Moderated

    Thank you everyone for your information.

     

    Hiko

Legend

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