Wait events
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Wait events

  1. #1
    Join Date
    Sep 2005
    Posts
    278

    Wait events

    Good Evening,

    Below are the list of wait events with 17.7 days the top wait event "SQL*Net message from client".
    How can I reduce it?

    Code:
    EVENT                          TOTAL_WAITS TIME_WAITED
    ------------------------------ ----------- -----------
    SQL*Net message from client        4298474    63977240
    rdbms ipc message                    87695     9830470
    PX Deq: Execution Msg               310670     8080779
    pmon timer                            7135     2035552
    virtual circuit status                 647     2031403
    dispatcher timer                       323     2021496
    PX Idle Wait                        279360     1998518
    smon timer                              68     1971965
    wakeup time manager                    611     1957159
    enqueue                             275521      558988
    SQL*Net more data from client         3949       44848
    PX Deq: Join ACK                    270437       32571
    db file sequential read              86321       27344
    PX Deq: Table Q Normal               59714       11390
    PX Deq: Execute Reply                 1217        6311
    db file scattered read               34101        5654
    PX Deq Credit: send blkd             28541        5213
    latch free                            1328        3208
    direct path read                      8744        2229
    PX Deq Credit: need buffer           17004        1913
    log file sync                        16344        1579
    direct path write                     5268        1406
    log file parallel write              19410        1148

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    its an idle wait event, from a database point of view you can ignore it

    but it means your database is waiting on the client program to send data to it

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Is your application Oracle Forms based?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Sep 2005
    Posts
    278
    Its Business Objects.

    Is there anyway to set some parameters in oracle Network related files such as sqlnet.ora?

  5. #5
    Join Date
    Sep 2005
    Posts
    278
    I run the statspack report even and that is also very much fine, I dont find any problem except with
    SQL*Net message from client and PX Deq: Execution Msg events.

    Every other things are seems to be fine.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    As Davey stated, from the database point of view this is an idle event however, this is affecting query performance.

    You are experiencing too many round-trips between the client and the server.

    This is likely not easy to troubleshoot.

    You can start by tracing the most used queries and look for "SQL*Net message from client" wait. As a rule of thumbs take into consideration just queries where this event is greater than the sum of all other wait events.

    Once you have identified a offending query... tune it up

    Good luck!
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Sep 2005
    Posts
    278
    Thanks PAVB, you are right, I will try to do such

  8. #8
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    I think setting profiles, resource limits and sqlnet expire time would reduce the wait events from client. check!

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by dbasan
    I think setting profiles, resource limits and sqlnet expire time would reduce the wait events from client. check!
    Not sure how profiles and resource limits would help when the root cause of the issue is likely to be the application forcing too many round-trips in between the client and the server.

    Would you mind to elaborate?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  10. #10
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Server is waiting for the client to feed the data. It may be the user is out off his place or waiting for some thing else to influence, his decision for input,....are the odd things we need to eliminate to fool statspack.

    Under these circumstances when you take a stats report it would certainly show "SQL*Net message from client" as idle wait times consuming more resources, which actually not and can be ignored.

    On the other hand it can also be a bottleneck from the network or the resource. Need to find them where is the exact problem and then trouble shoot.

    Resource limits and profiles indirectly assist you in eliminating unwanted waits, and be sure in your analysis towards resource or network bottleneck.

Posting Permissions

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



Click Here to Expand Forum to Full Width