Thursday, December 22, 2011

Oracle Database Tuning Approach


I have been co-opted to do this tuning exercise in my "spare time".  I was chosen since this was the database for the project I had worked on during my first assignment with this client.  This was a mere five months ago, so I suppose my knowledge of the application, data model and environment would give me an "edge". 

The assumption was that yours truly would be quickly able to narrow down the options and focus on the exact problem.  The client was desperate for a quick solution, and therefore was urging me in that direction.


This "silver bullet methodology", as I'll call it, works real well in Werewolf Horror Movies - with database tuning, not so much!


There are several very expert blogs out there to cover exactly HOW to tune a database.  I will refer to my colleagues such as Kerry Osborne http://kerryosborne.oracle-guy.com/ and Cary Milsap http://carymillsap.blogspot.com/ for the exact technical process to follow.  Follow their lead, and you can't go wrong.  But where do you start, especially when assumptions and analysis by key players have already been made, and you are expected to act on these?




This leads me to my (unofficial) pragmatic, political database tuning approach (with apologies to  the experts):

 1)  Assume Nothing.

 This covers the thought that I could somehow walk right in and fix something immediately because I'd worked on it before.  I must admit that vanity comes into play here.  A good, professional DBA can easily make the same mistake.  Don't assume problems which occurred before, on this system or any other, are the same ones you're seeing today.  

This also covers cases where a new, faster machine or an improved set of disks simply MUST make the database run faster.

 2)  Believe No One.

 This is also covered by #1 above.  It is a strange assumption to believe that just because someone fixed something once, they already have the solution.  It may mean they are competent, or even, an expert.  In this particular case, I've already been lead to some assumed causes and solutions (from the recent past) which are already proving to be dead ends!  

 3)  Objectively measure.

 Don't believe it when people say performance is "bad".  Bad is a relative, subjective term.  It is not a technical term.  Before declaring something bad and worth addressing, quantify it in clear terms.

 4)  Use standard tools.

 This covers the developer or DBA who comes running at you with a slow SQL statement he ran yesterday at 3pm that the explain plan says costs a gazillion dollars.  Using Grid Control or an AWR report is not only more precise, objective and standard, these are supported tools that you've already paid a lot of money to have, and will be useful if you must report the problem to the vendor.

 5)  Review and confirm configuration.

 This could also be covered under item #1, but is a step that should be taken before assuming a cluster is really clustering, or a memory management setting is actually implemented as intended.  I've already determined that something is not right with the SGA, and the DBAs flatly denied the configuration was ever meant to be the way it is.  Now how could that have happened?  I would never have found out if I accepted what I was told (see #2).

 6)  Define measurable goals.

 So as not to insult the client, I did not list this first.  However, before "fixing" anything, someone needs to define what "fixed" is.  You can't always make a database run fast enough to satisfy everyone, so define a reasonable goal.  Not doing this may result in you no longer being invited to future tuning parties, but doing it eloquently is important as well. 

 7)  Document methodology and results.

I am going to skip the tuning specifics.  Once you've done all of the above, these will be self-evident.  Present your results in a professional, easy to read manner.  Sometimes, the fixes do not involve the database itself at all.  This can become a political issue if the recommendations cross organizational boundaries (the disks or networks are not configured properly, or the application code really blows!!!).