Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

A healthy way to do an Oracle database health-check

with 12 comments

Q: How do I do an Oracle database health-check?

A: It all depends. (hint: we can use this answer for most Oracle related questions in general and Performance related in particular, but don’t try it at home).

This seems like a quite broad question and yes it is. And of course there are many ways to proceed with a database health-check. So at this post I ‘d rather talk about: what I think is a healthy way to approach an Oracle database health-check.

  1. Start with the basics: Listen to the users of this database. If nobody complains then most probably you would have to define the scope by yourself. In any case, go on.
  2. Gather environment information. This includes the understanding of the architecture used, the databases on such architecture and the applications on those databases. Also learn who is who: Users, DBAs and Developers.
  3. Gather metrics. I am referring to OS metrics (CPU, IO and Memory), and also database metrics (AWR) together with alert logs. When gathering these metrics focus on time periods where the pain has been reported, and slice the time as small as possible (i.e. AWR reports for each time slice captured, avoiding the 6-24 hours time frame common mistake).
  4. Let the combination of complains (concerns) and findings on metrics to guide you to the next step. This is where most get lost. So don’t panic and dive in into what you see as contention on your metrics. Keep in mind that the most important metric of all is “user response time”, so anything affecting it must be in your priority list.
  5. There are many more things to check, but they are more in the configuration and sound practices arena. For example: redundancy on control files, archive mode, backups, non-default parameters, PX setup, memory setup, etc. For these, creating a check list would help.
  6. At some point you will have many leads and you will start to lose focus. Do some yoga or go for a walk, then make an A, B, C list with what is really important, what is kind-of and what is mere style.
  7. You are not an expert on every aspect of the database (nobody is). So, do not pretend you can do everything yourself. Rely on your peers and/or contacts. Reach out for help in those areas where you feel insecure (feeling insecure is a good thing, much better than feeling secure without any solid foundation).
  8. Once you think you have it nailed, go to your peers, colleagues, boss(es), friends, partner, or strangers if necessarily, and solicit a review of your findings and recommendations. Accept feedback. This is key. Maybe what you thought was sound it makes absolutely no sense to someone with more experience or simply with a different view.
  9. Reconsider everything. Avoid the pitfall of assuming that what you have learn in your two-digits years of experience can be applied to every case. For example, if you have done mostly SQL Tuning, don’t expect every issue to be SQL Tuning. Health-checks are like fortune cookies, you never know what you will get.
  10. Last but not least: Learn from your new experience, practice listening to others, use your common sense, exercise your knowledge, and work as a team member. Add the word “collaboration” to your daily work and maybe one day you will learn you are not alone.

Cheers — Carlos


Written by Carlos Sierra

November 1, 2013 at 7:27 am

12 Responses

Subscribe to comments with RSS.

  1. Excellent advice. It reminds me of: “Don’t just do something; Sit there!” It is so important to just listen before you do anything. Walking away from the problem (do some yoga) for a little while, helps me quite a bit, too.

    Christoph Ruepprich

    November 1, 2013 at 9:47 am

  2. Thanks for this review Carlos. I have enjoyed working with you this week and I appreciate your patience with our developers/DBAs. You have quickly assessed our situation and I’m anxious for us to implement your recommendations. Looking forward to another engaging week.

    Ken Phillips

    November 1, 2013 at 5:49 pm

  3. Well said Carlos ๐Ÿ™‚

    Raheel Syed

    November 3, 2013 at 6:39 am

  4. Great writing! I like the part “Do some yoga or go for a walk” for new ideas.

    Weidong Zhou

    November 5, 2013 at 7:48 am

  5. When I was an indie (before the days of AWR), the par was, I would get called in for a specific performance problem, then find the actual problems were often just accepted by the users. Your #4 is the key that most newbies don’t get when they ask about health checks, thinking that some report will give all answers. Even some very experienced people say to only focus on what the user/business complains about, that just makes some wrong assumptions. But at least most experienced will acknowledge it when pressed.

    I had to squawk when someone tweeted something like “if it’s not on the top-N list, I won’t look at your sql.”


    November 5, 2013 at 2:23 pm

    • i think there is a great diversity of styles when it comes to perform health-checks, and of course the same is true with regard to results… ๐Ÿ˜‰

      Carlos Sierra

      November 7, 2013 at 5:49 am

  6. Wow what a great post, its a very in depth Q&A, thank you so much for sharing an amazing article with us.

    Arthur Lawrence provides Oracle Consulting and Oracle Services in USA.

    John Willson

    November 7, 2013 at 6:04 am

Leave a Reply to Carlos Sierra Cancel reply

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

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

Facebook photo

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

Connecting to %s

%d bloggers like this: