Oracle Scratchpad

June 4, 2009

Health Check

Filed under: Oracle — Jonathan Lewis @ 6:35 pm BST Jun 4,2009

Someone recently posted a question on the OTN Database General forum with the title like “Health Check on Oracle database” but gave no really solid context to indicate the type, purpose, or frequency of activity that they had in mind.

I can think of three or four possible interpretations for what he was saying – and this made me wonder what possible interpretations other people might have.

I won’t link to the specific thread just yet – someone has already asked “What do you mean by an Oracle health check” and if the OP comes up with a response I wouldn’t want to give too much of a lead to other people before they come up with their own interpretations.

So the question is this: in the complete absence of any sensible context, if you overheard a stranger talking about an Orace health check, what sort of activity do you think they might have in mind ?


  1. If i would get existing DB under my responsibility I would start with:
    – check undo/temp/flashback configurations
    – review non-default init parameters
    – review shared memory setup
    – check backup and recovery status/strategy
    – check available space in tablespaces
    – check if table/index statistics are currect
    – check if tables might benefit from shrinking
    – review security – unlocked user/ weak passwords

    and more application specific:
    – check if there are indexes that are redundant / not ever used
    – check if there are no missing indexes (ex. for fk)

    and many more…


    Comment by Paweł Barut — June 4, 2009 @ 8:32 pm BST Jun 4,2009 | Reply

  2. This really is a loaded question. Some very basic guesses:

    The presence of ORA-600/7445 errors in the alert log?
    Are any tablespaces nearly full (without autoextend)?
    A test of I/O throughput against some benchmark?
    A test of SQL response time against some benchmark (ala OEM)?

    Comment by Tony — June 4, 2009 @ 8:37 pm BST Jun 4,2009 | Reply

  3. “Health Check on Oracle database” out of context, to me, is asking “is my application running slow?”, which by itself is also not a very useful question. But I would suggest something to alert someone if a blocking lock exists for more than n seconds, if any query takes more than n seconds to return (this n certainly need not be the same as the previous n). I would also like to know if the database is about to run out of space, from the filesystem level, on up. Bottom line though, isn’t the performance pack/enterprise manager supposed to be the health check?

    Comment by Joseph Charpak — June 4, 2009 @ 8:52 pm BST Jun 4,2009 | Reply

  4. Good question. I suspect there will be many interpretations, but as a first cut:

    (Potentially some stuff which is version dependant which I’ll avoid…)

    – ask customer/users about any known issues
    – Licencing Audit
    – Review of patch or even version status
    – Check logs for oddities
    – check for invalid objects
    – review of performance, look at whole tuning arena
    – Security
    – suitability of backup strategy


    Comment by Richard Shuttlewood — June 4, 2009 @ 9:10 pm BST Jun 4,2009 | Reply

  5. “what sort of activity do you think they might have in mind ?”

    My guess is you have one of two things happening here. First option, you have a management team that wants something they can point to and say “See, it is running fine. The Health Check says so.” The other option is that you have someone new to the role of DBA (or at least new to Oracle) and they want something (or set of things) they can check periodically to let them know things are OK. Either way there is some more requirements gathering to do before you can give them an answer that will help them accomplish their goal.

    Comment by Kirk — June 4, 2009 @ 9:28 pm BST Jun 4,2009 | Reply

  6. I’m missing the goal/target in the question about a “Health Check on Oracle database”. Who is the target group? What’s the purpose? When should the checks be done (Regularly ;) ) etc.
    After defining the “playing grounds” specific actions for checks can be defined.

    Comment by TanteKaethe — June 5, 2009 @ 5:27 am BST Jun 5,2009 | Reply

  7. The other meaning I have seen attached to this is that something is not working as expected (Tune that slow database!), and management are asking is the database configured correctly because obviously our application is perfect and its having perfomance problems because of the “slow” database.

    in these cases its usually a matter of checking the install and any patches have been performed correctly, reviewing the configuration to make sure it makes sense and there are no obviously badly set parameters (e.g. a 639MB SGA on a machine with 640MB of ram). Then proceeding to actually perfrom some usefull diagnostics of why the appplication is performing badly rather than tweak random parameters untill it sort of works.

    Comment by Chris_c — June 8, 2009 @ 6:56 am BST Jun 8,2009 | Reply

  8. I’ve actually done lots of these things (albeit a bit more specific – “Performance Health Checks”). What I usually did was leave some sar -q, sar -u, and vmstat scripts running on the box (or use Performance Monitor in the single case where Windows was involved), install StatsPack (if not present) and schedule hourly snapshots, and then start interviewing the resident DBA about known problems I should specifically address. While waiting for the first couple of snapshots to be ready, I briefly look at the things Paweł and Tony listed (parameters, statistics, alert log, etc.)
    Assuming the Health Check takes place during normal operation of the database (not, say, on a weekend) the first StatsPack report gives me an idea where the fruits hang. Then I grab the lowest-hanging one and start discussing optimization strategies with the DBA and/or any developers that may be present (on rare occasions, sadly).
    The next day I look at what happened during the night on the box and in the database and address any (potential) problems found there.
    In the end, I go home and write a report of what I’ve found and any recommendations I have.

    Each Performance Health Check I’ve made turned out differently, but the above is the basic plan I have in mind when arriving at the customer site.
    Oracle 10g made the diagnostics easier (dbms_xplan and all the other goodness), and at the sites that had licensed the Diagnostics Pack I could do some longer-term trend analysis (if you can call looking at Excel charts in search of unusual peaks “trend analysis”).

    Comment by Flado — June 9, 2009 @ 2:52 pm BST Jun 9,2009 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by