FAQ
All,

Can anyone point me to a good outline for a 'database health-check'. I.e.
is it best to base it on statspack/bstat/estat, some form of response time
breakdown (I'm reading Cary's book at the moment) or something else
entirely.

Regards
--
David Lord
Senior DBA
Iron Mountain (UK) Ltd
This email and its attachments are confidential under applicable law and are intended for use of the sender's addressee only, unless the sender expressly agrees otherwise, or unless a separate written agreement exists between Iron Mountain and a recipient company governing communications between the parties and any data that may be so transmitted. Transmission of email over the Internet is not a secure communications medium. If you are requesting or have requested the transmittal of personal data, as defined in applicable privacy laws, by means of email or in an attachment to email, you may wish to select a more secure alternate means of transmittal that better supports your obligations to protect such personal data.

If the recipient of this message is not the recipient named above, and/or you have received this email in error, you must take no action based on the information in this email. You are hereby notified that any dissemination, misuse or copying or disclosure of this communication by a recipient who has received this message in error is strictly prohibited. If this message is received in error, please return this email to the sender and immediately highlight any error in transmittal. Thank you.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Search Discussions

  • SJHussain_at_alfransi.com.sa at Aug 25, 2004 at 7:32 am
    David,

    You are on right track. Go ahed with it.

    Best Regards,

    Syed Jaffar Hussain

    Lord David
    To
    Sent by: "'oracle-l_at_freelists.org'"
    oracle-l-bounce_at_f
    reelists.org cc

    Subject
    08/25/2004 03:32 d/b health check
    PM

    Please respond to
    oracle-l_at_freelist
    s.org

    All,

    Can anyone point me to a good outline for a 'database health-check'. I.e.
    is it best to base it on statspack/bstat/estat, some form of response time
    breakdown (I'm reading Cary's book at the moment) or something else
    entirely.

    Regards
    --
    David Lord
    Senior DBA
    Iron Mountain (UK) Ltd
    This email and its attachments are confidential under applicable law and
    are intended for use of the sender's addressee only, unless the sender
    expressly agrees otherwise, or unless a separate written agreement exists
    between Iron Mountain and a recipient company governing communications
    between the parties and any data that may be so transmitted. Transmission
    of email over the Internet is not a secure communications medium. If you
    are requesting or have requested the transmittal of personal data, as
    defined in applicable privacy laws, by means of email or in an attachment
    to email, you may wish to select a more secure alternate means of
    transmittal that better supports your obligations to protect such personal
    data.

    If the recipient of this message is not the recipient named above, and/or
    you have received this email in error, you must take no action based on the
    information in this email. You are hereby notified that any dissemination,
    misuse or copying or disclosure of this communication by a recipient who
    has received this message in error is strictly prohibited. If this message
    is received in error, please return this email to the sender and
    immediately highlight any error in transmittal. Thank you.

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

    Visit us on www.alfransi.com.sa
    ====== Banque Saudi Fransi - Privacy Notice ======
    This message is intended only for the person or entity to which it is
    addressed and may contain confidential and/or privileged material. Any
    use of this information by persons or entities other than the intended
    recipient is prohibited. If you have received this in error, please
    contact the sender and delete the material from your computer. Any
    opinions and other information contained in this message that do not
    relate to the official business of Banque Saudi Fransi shall be
    understood as neither given or endorsed by it. Although precautions
    have been taken to ensure no viruses are present in this email, BSF
    cannot accept responsibility for any loss or damage arising from the
    use of this email or attachments.
    ====== Banque Saudi Fransi - Privacy Notice ======

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  • Edgar Chupit at Aug 25, 2004 at 7:40 am
    Dear Lord,

    LD> is it best to base it on statspack/bstat/estat, some form of response time
    LD> breakdown (I'm reading Cary's book at the moment) or something else
    LD> entirely.

    Read deeply into it, and You will understand that main point is that
    You must concentrate on user actions (complains) and not on anything
    else like values of db "metrics".

    The "healthy" database is database that is not doing anything.

    --
    Edgar

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  • Stephane Faroult at Aug 25, 2004 at 7:47 am
    David,

    This is a pretty subjective topic which is opened to a wide array of
    interpretations. I would tend to think that a database is healthy when
    internally there isn't too much chaining, that there is no HWM problem, that
    basically design and structures match what we want to do, and that init.ora
    parameters are not outrageously shocking.

    One however can have dreadful performance on an otherwise 'healthy'
    database, as everybody knows. And you can even have crazy requirements with
    which, whatever you do, you cannot win (the DSS type of query againts an
    OLTPdatabase, for instance) unless you rethink the system.

    IMHO, it all starts with identifying what is 'typical' of your system -
    perhaps identifying a handful of key queries - and checking how things
    (response time breakdown and all, ie number of logons, number of executions)
    evolve over time. If it deteriorates under an identical load, then it is a
    database problem. Otherwise it can be anything.

    Regards,

    Stephane Faroult

    RoughSea Ltd
    http://www.roughsea.com

    On Wed, 25 Aug 2004 13:32, Lord David sent:

    All,

    Can anyone point me to a good outline for a 'database health-check'. I.e.
    is it best to base it on statspack/bstat/estat, some form of response time
    breakdown (I'm reading Cary's book at the moment) or something else
    entirely.

    Regards
    --
    David Lord
    Senior DBA
    Iron Mountain (UK) Ltd

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  • Daniel W. Fink at Aug 25, 2004 at 8:03 am
    There's more to database health than what you can find in statspack
    (though it is part of the overall toolkit). Health also includes
    security, recoverability, supportability, etc.

    Is the database and related files being properly backed up? Does the
    backup set include *everything* you need to recover the database and
    supporting jobs (cron, standard reports, etc)? Has the backup been
    tested recently (way too many war stories about unusable backups)?

    What about security? Do users have access to the database as schema
    owners or dba-level accounts? If they have access, is it proper and
    reasonable?

    Do you have a proper QA/Staging environment? Nothing kills database
    health faster than the release from hell.

    Consider that a poorly performing database is usually more acceptable
    than a down database.

    Regards,
    Daniel Fink

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
  • Freeman, Donald at Aug 25, 2004 at 7:54 am
    I may be wrong but the first thing I got out of Carey and Jeff's book is =
    to ask the question, "Is anybody complaining?" I have long thought that =
    should be the primary indicator that something needs checked. When I =

    look long enough, and hard enough, I will undoubtably find something =
    that needs to be messed with, often to my detriment.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Lord David
    Sent: Wednesday, August 25, 2004 8:33 AM
    To: 'oracle-l_at_freelists.org'
    Subject: d/b health check

    All,

    Can anyone point me to a good outline for a 'database health-check'. =
    I.e.
    is it best to base it on statspack/bstat/estat, some form of response =
    time
    breakdown (I'm reading Cary's book at the moment) or something else
    entirely.

    Regards
    --
    David Lord
    Senior DBA
    Iron Mountain (UK) Ltd
    This email and its attachments are confidential under applicable law and =
    are intended for use of the sender's addressee only, unless the sender =
    expressly agrees otherwise, or unless a separate written agreement =
    exists between Iron Mountain and a recipient company governing =
    communications between the parties and any data that may be so =
    transmitted. Transmission of email over the Internet is not a secure =
    communications medium. If you are requesting or have requested the =
    transmittal of personal data, as defined in applicable privacy laws, by =
    means of email or in an attachment to email, you may wish to select a =
    more secure alternate means of transmittal that better supports your =
    obligations to protect such personal data.

    If the recipient of this message is not the recipient named above, =
    and/or you have received this email in error, you must take no action =
    based on the information in this email. You are hereby notified that =
    any dissemination, misuse or copying or disclosure of this communication =
    by a recipient who has received this message in error is strictly =
    prohibited. If this message is received in error, please return this =
    email to the sender and immediately highlight any error in transmittal. =
    Thank you.

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/

    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org

    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  • Daniel W. Fink at Aug 25, 2004 at 8:28 am
    Just because no-one is complaining (that you know about) does not mean
    that nothing is wrong. Within most organizations that I have been a part
    of, the gap between the users and IT usually limited the communication
    about performance problems. Users may become conditioned to accept
    things the way they are and just complain amongst themselves and never
    to management or IT. Management and IT don't seem all that eager to go
    looking for problems, when they usually have enough to deal with already.

    If we use the analogy of a health check in the medical fashion, we need
    to consider the difference between reactive and preventative actions.
    When we have pain, illness or something just does not feel right, we are
    being reactive. We can describe the symptoms and have an idea of the
    resolution (stop the pain, feel better, etc.). This is the strength of
    Method-R (IMHO). It enables you to really drill down to a root cause of
    the problem. What about times where something is wrong, but you either
    ignore the problem (and accept it as a part of life) or don't have any
    symptoms that you are aware of? Not to be morbid, but this illustrates
    the point. Cancer of the stomach is one of the deadliest because
    symptoms do not usually arise until it is too late. Aneurisms(sp?) in
    the brain are very similar. There are tests to determine if there are
    these types of problems, but I don't think they are all that common in
    practice.

    Just food for thought,
    Daniel Fink

    Freeman, Donald wrote:
    I may be wrong but the first thing I got out of Carey and Jeff's book is =
    to ask the question, "Is anybody complaining?" I have long thought that =
    should be the primary indicator that something needs checked. When I =
    look long enough, and hard enough, I will undoubtably find something =
    that needs to be messed with, often to my detriment.
    -----------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Peter Robson at Aug 25, 2004 at 8:37 am
    Dan -

    Your medical analogy...


    DWF> If we use the analogy of a health check in the medical fashion, we need

    DWF> Not to be morbid, but this illustrates

    ... is distinctly morbid,

    DWF> Just food for thought,

    ... and what's more - its putting me RIGHT OFF my food!

    peter
    edinburgh
    ...............

    --
    mailto:pgro_at_bgs.ac.uk

    *********************************************************************
    This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. However, the information contained in this e-mail may subsequently be subject to public disclosure under the Freedom of Information Act 2000 and, unless the information is legally exempt from disclosure, the confidentially of this e-mail and your reply cannot be guaranteed. If this message was not intended for you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. http://www.bgs.ac.uk
    *********************************************************************

    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Paul Drake at Aug 25, 2004 at 11:11 am

    "Daniel W. Fink" wrote:

    Just because no-one is complaining (that you know
    about) does not mean
    that nothing is wrong. snip
    Users may become
    conditioned to accept
    things the way they are and just complain amongst
    themselves and never
    to management or IT.
    large snip to avoid bouncing
    Just food for thought,
    Daniel Fink
    Dan,

    I totally agree. Users may be conditioned to beating
    their collective heads against the wall.

    "that report took too long, so we don't use it
    anymore".

    there may also be latent time bombs such that when you
    upgrade from say 8.1.7 to 9.2 are comperable to an
    aneurism bursting.

    one site had posting routines running for hours,
    frequently hanging - not because they took a long time
    to actually execute, but due to the app code causing a
    series of serializations which were manifested as
    blocked sessions. (users hitting ctrl-alt-del left
    around zombies, which on win32 - PMON is notoriously
    lazy in actually terminating the thread and cleaning
    up the wreckage). I knew nothing about it until I was
    onsite - and a user was testing routines after an
    8.1.7.4 to 9.2.0.4 migration (on a saturday) - during
    actual acceptance testing. "Oh - these hang all the
    time. we just kill the app and restart it."
    Well, their zombied session (and others) would still
    be sitting there, holding locks, until PMON would get
    motivated. (yes, they should not have been running on
    win32).

    the routine ran in minutes in single user mode.

    One has to assume that the users will not complain
    directly about app performance.

    You as the dba might not find out about it, until you
    hear that the client has moved to a competitive
    product, or when you are up in front of a group of
    users at a conference and caught offguard.

    "they're out there".

    Pd

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Cary Millsap at Aug 25, 2004 at 11:33 am

    I knew nothing about it until I was
    onsite - and a user was testing routines after an
    8.1.7.4 to 9.2.0.4 migration (on a saturday) - during
    actual acceptance testing. "Oh - these hang all the
    time. we just kill the app and restart it."=20
    Well, their zombied session (and others) would still
    be sitting there, holding locks, until PMON would get
    motivated. (yes, they should not have been running on
    win32)."
    Exactly. Sometimes you have to be there and see it to know what's going =
    on.
    But you'll never, ever find out if you're not willing to even listen. =
    And
    that, I think, is Problem #1 with traditional methods. They don't say it
    explicitly, but implicitly, step one is this: "Make the user go away so =
    that
    you can get busy looking at statistics." :)=20

    Cary Millsap
    Hotsos Enterprises, Ltd.
    http://www.hotsos.com
    * Nullius in verba *

    Upcoming events:
    - Performance Diagnosis 101: 9/14 San Francisco, 10/5 Charlotte, 10/26
    Toronto
    - SQL Optimization 101: 8/16 Minneapolis, 9/20 Hartford, 10/18 New =
    Orleans
    - Hotsos Symposium 2005: March 6-10 Dallas
    - Visit www.hotsos.com for schedule details...

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org =

    On Behalf Of Paul Drake
    Sent: Wednesday, August 25, 2004 11:06 AM
    To: oracle-l_at_freelists.org
    Subject: Re: d/b health check

    "Daniel W. Fink" wrote:
    Just because no-one is complaining (that you know
    about) does not mean=20
    that nothing is wrong.=20 snip
    Users may become
    conditioned to accept=20
    things the way they are and just complain amongst
    themselves and never=20
    to management or IT.=20
    large snip to avoid bouncing
    Just food for thought,
    Daniel Fink
    Dan,

    I totally agree. Users may be conditioned to beating
    their collective heads against the wall.

    "that report took too long, so we don't use it
    anymore".

    there may also be latent time bombs such that when you
    upgrade from say 8.1.7 to 9.2 are comperable to an
    aneurism bursting.

    one site had posting routines running for hours,
    frequently hanging - not because they took a long time
    to actually execute, but due to the app code causing a
    series of serializations which were manifested as
    blocked sessions. (users hitting ctrl-alt-del left
    around zombies, which on win32 - PMON is notoriously
    lazy in actually terminating the thread and cleaning
    up the wreckage). I knew nothing about it until I was
    onsite - and a user was testing routines after an
    8.1.7.4 to 9.2.0.4 migration (on a saturday) - during
    actual acceptance testing. "Oh - these hang all the
    time. we just kill the app and restart it."=20
    Well, their zombied session (and others) would still
    be sitting there, holding locks, until PMON would get
    motivated. (yes, they should not have been running on
    win32).

    the routine ran in minutes in single user mode.

    One has to assume that the users will not complain
    directly about app performance.

    You as the dba might not find out about it, until you
    hear that the client has moved to a competitive
    product, or when you are up in front of a group of
    users at a conference and caught offguard.

    "they're out there".

    Pd

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------

    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Jared.Still_at_radisys.com at Aug 25, 2004 at 11:42 am

    --- "Daniel W. Fink" wrote:

    one site had posting routines running for hours,
    frequently hanging - not because they took a long time
    to actually execute, but due to the app code causing a
    series of serializations which were manifested as
    blocked sessions. (users hitting ctrl-alt-del left
    around zombies, which on win32 - PMON is notoriously
    lazy in actually terminating the thread and cleaning
    up the wreckage). I knew nothing about it until I was
    onsite - and a user was testing routines after an
    8.1.7.4 to 9.2.0.4 migration (on a saturday) - during
    actual acceptance testing. "Oh - these hang all the
    time. we just kill the app and restart it."
    Well, their zombied session (and others) would still
    be sitting there, holding locks, until PMON would get
    motivated. (yes, they should not have been running on
    win32).
    At one of my previous employers there may still be running
    a daemon that does a 'kill -9' on those pesky hung sessions.

    PMON sometimes lets them go for days.

    Jared

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Cary Millsap at Aug 25, 2004 at 10:15 am
    Despite the title, the focus of the book is really "diagnosing Oracle
    performance problems." From this, you can see why the focus of the book =
    is
    on user complaints. One of the prime motives for writing the book is the
    repeated scenario 1) user complains, 2) technician springs into action
    "fixing" things, 3) technician announces success, 4) user doesn't =
    perceive
    any improvement.

    But responding to complaints is of course only part of the picture. A
    responsible performance MANAGER (a person who works with applications =
    even
    when they're healthy, as opposed to a guy like me who tends to see them =
    only
    when they're sick) needs to execute the same profiling steps in an
    environment where nobody's complaining. Even in a healthy, no-complaints
    environment, targeting of specific user actions is still /extremely/
    important.

    In a no-complaints environment, the targeting is not driven by =
    complaints
    (there aren't any), but it /is/ still driven by business priority. For
    example, if your company sells widgets, then the part of the application
    that takes orders, ships orders, and bills for orders had #$%^ well be
    efficient. You don't even have to know that much about the business to
    figure these things out: for example, if some program runs 10,000 times =
    a
    day, then you /know/ that improving it--even just a little bit--can mean =
    a
    big reduction in overall workload.

    How can you tell is a system is efficient? ...By ensuring that your
    business's Top=A0N most important user actions are efficient. So, step 1 =
    is to
    target your Top=A0N most important user actions. Without this step =
    (Chapter 2,
    especially p40), you can't go on. Of course, the bigger you can make the
    value of N, the more effective you are. For many shops, focusing =
    correctly
    on N=3D5 would result in spectacular progress.

    Once you've done your targeting step, then how can you tell if a user =
    action
    is efficient? Profile it: collect carefully scoped timing data, and
    summarize it into a form with which you can tell by looking whether =
    there's
    anything unnecessary going on--that is, whether there are any =
    unnecessary
    steps contributing to response time.

    Here's an example. Just this week, my team and visited an airline where =
    we
    looked at a user action that nobody was really complaining about (yet).
    Collecting the right trace data was a challenge because of the =
    multi-tier
    application architecture. But the guys stuck with it and got the right =
    data
    (the stuff shown in figure 3-4 on p50 of the book). The reward was =
    pretty
    cool: we got a 5x throughput improvement by making a trivial =
    modification to
    the application. Once we had the right data, seeing the opportunity and
    getting the 5x took less than half an hour of analysis time. Because the
    response time problem showed up as an excessive number of calls to the
    'SQL*Net message from client' timed event, we would have never found =
    this
    opportunity with statspack or the "let's find bad SQL" approach.

    Back to the original point, the reason we were looking at this =
    particular
    user action is not that someone was complaining about it. It was because =
    the
    business regarded this user action as important enough to warrant an
    efficiency evaluation. When you take this approach of looking at =
    individual
    user actions, prioritized by business importance, you'll always get one =
    of
    two results: either (1) you'll find an inefficiency and deliver a nice
    (maybe even spectacular) result to a place in the business that really =
    needs
    it; or (2) you'll prove that the user action is as fast as it's going to =
    get
    on the current technology architecture (and you'll know exactly what =
    part of
    that architecture needs upgrading to make it better). As a bonus, you'll
    frequently eliminate significant percentages of overall system workload,
    which kind of accidentally makes life better for every user on the =
    system.

    You just can't get these results by looking at system-wide metrics.

    Cary Millsap
    Hotsos Enterprises, Ltd.
    http://www.hotsos.com
    * Nullius in verba *

    Upcoming events:
    - Performance Diagnosis 101: 9/14 San Francisco, 10/5 Charlotte, 10/26
    Toronto
    - SQL Optimization 101: 8/16 Minneapolis, 9/20 Hartford, 10/18 New =
    Orleans
    - Hotsos Symposium 2005: March 6-10 Dallas
    - Visit www.hotsos.com for schedule details...

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org =

    On Behalf Of Freeman, Donald
    Sent: Wednesday, August 25, 2004 7:59 AM
    To: oracle-l_at_freelists.org
    Subject: RE: d/b health check

    I may be wrong but the first thing I got out of Carey and Jeff's book is =
    =3D
    to ask the question, "Is anybody complaining?" I have long thought that =
    =3D
    should be the primary indicator that something needs checked. When I =
    =3D
    look long enough, and hard enough, I will undoubtably find something =3D
    that needs to be messed with, often to my detriment.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Lord David
    Sent: Wednesday, August 25, 2004 8:33 AM
    To: 'oracle-l_at_freelists.org'
    Subject: d/b health check

    All,

    Can anyone point me to a good outline for a 'database health-check'. =
    =3D
    I.e.
    is it best to base it on statspack/bstat/estat, some form of response =
    =3D
    time
    breakdown (I'm reading Cary's book at the moment) or something else
    entirely.

    Regards

    --
    David Lord
    Senior DBA
    Iron Mountain (UK) Ltd
    This email and its attachments are confidential under applicable law and =
    =3D
    are intended for use of the sender's addressee only, unless the sender =
    =3D
    expressly agrees otherwise, or unless a separate written agreement =3D
    exists between Iron Mountain and a recipient company governing =3D
    communications between the parties and any data that may be so =3D
    transmitted. Transmission of email over the Internet is not a secure =
    =3D
    communications medium. If you are requesting or have requested the =3D
    transmittal of personal data, as defined in applicable privacy laws, by =
    =3D
    means of email or in an attachment to email, you may wish to select a =
    =3D
    more secure alternate means of transmittal that better supports your =3D
    obligations to protect such personal data.

    If the recipient of this message is not the recipient named above, =3D
    and/or you have received this email in error, you must take no action =
    =3D
    based on the information in this email. You are hereby notified that =
    =3D
    any dissemination, misuse or copying or disclosure of this communication =
    =3D
    by a recipient who has received this message in error is strictly =3D
    prohibited. If this message is received in error, please return this =3D
    email to the sender and immediately highlight any error in transmittal. =
    =3D
    Thank you.

    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------

    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Peter Robson at Aug 25, 2004 at 7:58 am
    LD> Can anyone point me to a good outline for a 'database health-check'. I.e.
    LD> is it best to base it on statspack/bstat/estat, some form of response time
    LD> breakdown (I'm reading Cary's book at the moment) or something else
    LD> entirely.

    Ah, this is a great question!

    I answer, because I have built an automated system for returning a
    'database health quotient' via email every day. Management love it ...

    This is how it works...

    There are something like 150 exception alerts generated every night by
    batch jobs, checking all manner of parameters on Oracle, both data and
    metadata. Each test provide a FAIL numeric, as against a POPULATION
    numeric, eg the total number of distinct conditions checked for
    compliance.

    At the end of all the batch runs, a total FAIL count is computed as
    a percentage of the total population from which the fails are obtained
    - QED a percentage - your 'database health check'!

    Of course, it is utterly meaningless in isolation of a clear and
    objective understanding of the parameters being measured. So any such
    quotient will be highly specific. If not, it may mask something that
    is critically important!

    But is ain't half cool to say that the database health quotient
    improved from 97.35% to 98.21% last night!!

    Bottom line - 'caveat emptor'.

    peter
    edinburgh
    ..............

    --

    mailto:pgro_at_bgs.ac.uk

    This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. However, the information contained in this e-mail may subsequently be subject to public disclosure under the Freedom of Information Act 2000 and, unless the information is legally exempt from disclosure, the confidentially of this e-mail and your reply cannot be guaranteed. If this message was not intended for you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. http://www.bgs.ac.uk

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  • Stephane Faroult at Aug 25, 2004 at 8:10 am


    Nice point!
    Regards,

    Stephane Faroult

    RoughSea Ltd
    http://www.roughsea.com

    On Wed, 25 Aug 2004 07:07, 'Daniel W. Fink' sent:

    There's more to database health than what you can find in statspack
    (though it is part of the overall toolkit). Health also includes
    security, recoverability, supportability, etc.

    [snip]

    Consider that a poorly performing database is usually more acceptable
    than a down database.

    Regards,
    Daniel Fink

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
  • Freeman, Donald at Aug 25, 2004 at 9:35 am
    I agree with your point that users many not know when something isn't =
    working right or not understand the significance of something they =
    observe. I have visited users on site and watched as they clicked =
    through warnings, exceptions and errors without comment to get to the =
    problem that is really bugging them. So yes, people do ignore obvious =
    problems if the pain is tolerable. But my approach would be to engage =
    my users rather than try instrument the database in such a way that =
    would reduce my dependence on prompt disclosure of issues from my users. =
    You haven't said anything I disagree with but, personally, I have more =
    confidence in my ability to extract the details of my users experience =
    through communication and observation than setting up a health check. =
    I'm in a position to do that with my users though and I'm sure many =
    DBA's don't have that kind of contact.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Daniel W. Fink
    Sent: Wednesday, August 25, 2004 9:32 AM
    To: oracle-l_at_freelists.org
    Subject: Re: d/b health check

    Just because no-one is complaining (that you know about) does not mean=20
    that nothing is wrong. Within most organizations that I have been a part =

    of, the gap between the users and IT usually limited the communication=20
    about performance problems. Users may become conditioned to accept=20
    things the way they are and just complain amongst themselves and never=20
    to management or IT. Management and IT don't seem all that eager to go=20
    looking for problems, when they usually have enough to deal with =
    already.

    If we use the analogy of a health check in the medical fashion, we need =

    to consider the difference between reactive and preventative actions.=20
    When we have pain, illness or something just does not feel right, we are =

    being reactive. We can describe the symptoms and have an idea of the=20
    resolution (stop the pain, feel better, etc.). This is the strength of=20
    Method-R (IMHO). It enables you to really drill down to a root cause of=20

    the problem. What about times where something is wrong, but you either=20
    ignore the problem (and accept it as a part of life) or don't have any=20
    symptoms that you are aware of? Not to be morbid, but this illustrates=20

    the point. Cancer of the stomach is one of the deadliest because=20
    symptoms do not usually arise until it is too late. Aneurisms(sp?) in=20
    the brain are very similar. There are tests to determine if there are=20
    these types of problems, but I don't think they are all that common in=20
    practice.

    Just food for thought,
    Daniel Fink

    Freeman, Donald wrote:
    I may be wrong but the first thing I got out of Carey and Jeff's book = is =3D
    to ask the question, "Is anybody complaining?" I have long thought = that =3D
    should be the primary indicator that something needs checked. When = I =3D
    look long enough, and hard enough, I will undoubtably find something = =3D
    that needs to be messed with, often to my detriment.
    -----------------
    =20
    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Lord David at Aug 25, 2004 at 9:59 am
    Bah, and I was hoping for a nice easy checklist to steal;-)

    --
    David Lord
    >
    This email and its attachments are confidential under applicable law and are intended for use of the sender's addressee only, unless the sender expressly agrees otherwise, or unless a separate written agreement exists between Iron Mountain and a recipient company governing communications between the parties and any data that may be so transmitted. Transmission of email over the Internet is not a secure communications medium. If you are requesting or have requested the transmittal of personal data, as defined in applicable privacy laws, by means of email or in an attachment to email, you may wish to select a more secure alternate means of transmittal that better supports your obligations to protect such personal data.

    If the recipient of this message is not the recipient named above, and/or you have received this email in error, you must take no action based on the information in this email. You are hereby notified that any dissemination, misuse or copying or disclosure of this communication by a recipient who has received this message in error is strictly prohibited. If this message is received in error, please return this email to the sender and immediately highlight any error in transmittal. Thank you.

    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Darrell_at_landrum.com at Aug 25, 2004 at 10:01 am
    Right! Communication with users is paramount. Pain is
    always worse when you don't know if anyone is working
    on it as apposed to when you know someone is working on
    it. Then, circumstances are even better if you know
    WHO is working on it.
    It takes extra time to contact folks with feedback,
    etc. but it can also buy you time which is often
    needed. :)

    On Wed, 25 Aug 2004 10:38:28 -0400, "Freeman, Donald"
    wrote:
    I agree with your point that users many not know when
    something isn't =
    working right or not understand the significance of
    something they =
    observe. I have visited users on site and watched as
    they clicked =
    through warnings, exceptions and errors without comment
    to get to the =
    problem that is really bugging them. So yes, people do
    ignore obvious =
    problems if the pain is tolerable. But my approach
    would be to engage =
    my users rather than try instrument the database in
    such a way that =
    would reduce my dependence on prompt disclosure of
    issues from my users. =
    You haven't said anything I disagree with but,
    personally, I have more =
    confidence in my ability to extract the details of my
    users experience =
    through communication and observation than setting up a
    health check. =
    I'm in a position to do that with my users though and
    I'm sure many =
    DBA's don't have that kind of contact.
    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Nelson, Allan at Aug 25, 2004 at 12:28 pm
    I think a set of representative queries with based line response time is
    good if you can afford the extra load and the deck of queries is
    reasonably stable. Otherwise cultivate relationships with your most
    verbal users. They'll call once they know you care.

    Allan

    -----Original Message-----
    =46rom: oracle-l-bounce_at_freelists.org
    On Behalf Of Edgar Chupit
    Sent: Wednesday, August 25, 2004 7:45 AM
    To: Lord David
    Subject: Re: d/b health check

    Dear Lord,

    LD> is it best to base it on statspack/bstat/estat, some form of=20
    LD> response time breakdown (I'm reading Cary's book at the moment) or=20
    LD> something else entirely.

    Read deeply into it, and You will understand that main point is that You
    must concentrate on user actions (complains) and not on anything else
    like values of db "metrics".

    The "healthy" database is database that is not doing anything.

    --=20
    Edgar

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org put
    'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    =46AQ is at http://www.freelists.org/help/fom-serve/cache/1.html

    ___________________________________________________________________________=

    ___
    This email is intended solely for the person or entity to which it is =
    addressed and may contain confidential and/or privileged information. =
    Copying, forwarding or distributing this message by persons or entities =
    other than the addressee is prohibited. If you have received this email in =
    error, please contact the sender immediately and delete the material from =
    any computer. This email may have been monitored for policy compliance. =
    [021216]

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 25, '04 at 7:28a
activeAug 25, '04 at 12:28p
posts18
users12
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase