Statspack report- Top wait events
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Statspack report- Top wait events

  1. #1
    Join Date
    Jan 2003
    Location
    Bahrain
    Posts
    109

    Statspack report- Top wait events

    Hi,

    My statspack is running every one hour.

    In my report for the wait events, I am getting as follows.

    Top 5 Wait Events
    ~~~~~~~~~~~~~~~~~ Wait % Total
    Event Waits Time (cs) Wt Time
    -------------------------------------------- ------------ ------------ -------
    queue messages 1,800
    1,842,945 82.62
    PL/SQL lock timer 360
    368,574 16.52
    db file sequential read 106,165
    15,344 .69
    db file scattered read 13,802
    1,097 .05
    log file parallel write 4,293
    923 .04

    Here as for as I know waits time is in 100th of a second

    So for a queue messages

    1,842,945 =18429.45 Seconds
    18429.45/60 =307.1575 M
    305.1575/60=5.085 Hours

    How come it can for 5 Hours time in a one hour statspack report?
    How is it possible? My performance is very slow.

    Please clarify my doubt

    Seelan

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    if you had 1000 users waiting for 1 hour each it would show up as 1000 hours waited.

    Also your snap period is too long - use 10 - 15 miuntes, then drill down on the waits, find your top sql and find whre you are going slow.

    Noone here will be able to tell you what your problem is ans tehre isnt enough info - you dont even mention what version you use

  3. #3
    Join Date
    Jan 2003
    Location
    Bahrain
    Posts
    109
    Hi,

    From MetaLink
    *************

    Application hangs completely a few times per day.

    Diagnosing waits of the session, it is found that the session is waiting for wait event "queue messages".

    When a session tries to dequeue from a queue without dequeue_options.wait (ie. using default option), and, if there is no message ready for dequeuing from that queue, then the session waits for the message with wait event "queue messages". This is expected behavior.

    If your application does not need to wait for message in the queue, then use
    no_wait option for dequeue.

    The above document I got it from Metalink not. So how can I check whether I am default option or not.

    Then how to change to no_wait option.

    My oracle version is

    Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
    PL/SQL Release 8.1.7.4.0 - Production
    CORE 8.1.7.0.0 Production
    TNS for Linux: Version 8.1.7.4.0 - Production
    NLSRTL Version 3.4.1.0.0 - Production

    Thanks,
    Seelan

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    When "it hangs" try to look for the locks causing the hanging. But it could be anything: CPU resources unavailable, lots of FTSs, etc...
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  5. #5
    Join Date
    Jan 2003
    Location
    Bahrain
    Posts
    109
    Hi,

    In all my statspack report Queue messages is howing pretty high wait event.

    That is why I want to change the dequeue option.

    But I don't know how to do that

    Can you please guide me?

    Seelan

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by seelan
    Hi,

    In all my statspack report Queue messages is howing pretty high wait event.

    That is why I want to change the dequeue option.

    But I don't know how to do that

    Can you please guide me?

    Seelan
    Are you using dequeue_options.wait := dbms_aq.forever
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  7. #7
    Join Date
    Jan 2003
    Location
    Bahrain
    Posts
    109
    Hi Julian,

    Thanks for your prompt reply.

    I don't know whether I am using it or not. Can you guide me how to check that first?

    And also if it is in default mode, how to change it?

    Thanks
    Seelan

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    It is not straightforward. Check Note:121565.1 and see if it helps.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

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