Question:Is there any rule/formula evaluating sysstat: Enqueues Statistics
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Question:Is there any rule/formula evaluating sysstat: Enqueues Statistics

  1. #1
    Join Date
    Jul 2004
    Posts
    21

    Lightbulb Question:Is there any rule/formula evaluating sysstat: Enqueues Statistics

    Hi,

    v$Sysstat statistics are helpful in giving system wide "Health Check"
    indicator how the database is performing.

    Question: Is there any rule / formula for
    evaluating and grading (Good/poor/error)
    the v$sysstat values relating to CLASS - Enqueues statistics

    which are:
    25 - enqueue requests
    22 - enqueue timeouts
    23 - enqueue waits
    24 - enqueue deadlocks

    For example -

    1) if 22 + 23 + 24 is over zero then ...

    or

    2) if 22 + 23 + 24 is a certain percentage of 25


    For example for Class Buffer -
    there is a system Buffer Cache Hit Ratio formula:
    1-('physical reads'/('db block gets')+('consistent gets') )
    where for example one can grade the results:
    Between 90 - 100 OK
    Between 80 - 90 Warning
    Between 0 - 80 Error

    Is there a rule/formula for Enqueue like my example for Buffer
    (I was not able to find in Metalink) ?

    Thanks in advance,
    CMT

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447

    Re: Question:Is there any rule/formula evaluating sysstat: Enqueues Statistics

    Originally posted by CMT

    For example for Class Buffer -
    there is a system Buffer Cache Hit Ratio formula:
    1-('physical reads'/('db block gets')+('consistent gets') )
    where for example one can grade the results:
    Between 90 - 100 OK
    Between 80 - 90 Warning
    Between 0 - 80 Error
    that is pretty much useless nowdays

  3. #3
    Join Date
    Jul 2004
    Posts
    21
    To pando -

    You wrote: "that is pretty much useless nowdays"

    Why ; explain?

    Even statspack report calculates and reports it
    as part of the spreport?

    BTW - You did not address my question that referred to enqueues

    CMT

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by CMT
    To pando -

    You wrote: "that is pretty much useless nowdays"

    Why ; explain?

    Even statspack report calculates and reports it
    as part of the spreport?

    Search this site and the web why this is useless.



    You did not address my question that referred to enqueues

    Ratios are approximations. In some situations they may fit, some they may not. Show me a ratio, and I'll show you how to both prove and disprove your theory. Here you will get invalid information if you overuse 'FOR UPDATE'.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Jul 2004
    Posts
    21
    marist89,

    How do you monitor your databases ?
    Do you use statspack ?

    BTW -
    Which site were you referring to: "Search this site"
    the url is missing

  6. #6
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Originally posted by CMT
    marist89,

    BTW -
    Which site were you referring to: "Search this site"
    the url is missing
    http://www.google.com
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by CMT
    marist89,

    How do you monitor your databases ?

    I use a combination of things; custom database scripts, custom OS scripts, statspack, etc.


    Do you use statspack ?

    Yes. I use statspack to compare my workload over time. I also use it to diagnose instance wide problems where appropriate.


    Which site were you referring to: "Search this site"
    the url is missing

    http://www.dbasupport.com/forums/search.php
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    =========
    25 - enqueue requests
    22 - enqueue timeouts
    23 - enqueue waits
    24 - enqueue deadlocks

    ==========
    If you think enqueue latch contention is high in your system, then you need to look into application code /design for tuning.

    There is no fixed formula or fixed solution to tune latch contention.

    Tamil

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