I as note in my book "Oracle Tuning: The Definitive Reference", Oracle tuning is one of the most challenging areas of Oracle best practices, and there are many opinions on the best way to deploy and measure Oracle tuning efforts.

Here are a few of the best practices for Oracle tuning that I've gathered in my 25 years as a database administrator:

Oracle tuning best practices

Most successful Oracle shops understand holistic, broad-brush tuning techniques and employ a top-down approach, tuning the entire database first and then drilling-down into details, only after the whole systems has been tuned for it's specific workload.  Here are the mail areas of Oracle tuning best practices:

• Be proactive - The proactive tuning approach allows you to develop signatures for all salient Oracle tuning events (disk reads, internal contention). A reactive approach neglects the dynamic nature of Oracle tuning, and a best practice is to proactively monitor Oracle tuning, seeking opportunities by setting rolling, adaptive tuning thresholds.
   
• Use a top-down tuning approach - xxx   Don't be taken-in by any tuning expert who advocates a single method for tuning Oracle.  Oracle tuning is not that simple, and the savvy Oracle tuning expert will employ many tuning methods and tools.
   
• Tune to the workload first - One of the most common mistakes in Oracle tuning is diving-in to a problematic transaction before optimizing the whole workload.  Later, then the DBA changes important tuning parameters or adjusts the CBO statistics, these tuning efforts are un-done.  A successful shop will employ a tuning methodology that optimizes the instance as a whole first!
   
• Be vigilant - Always monitor your Oracle database, seeking exceptions that may indicate a transient tuning problem.  Most shops deploy sophisticated, customized Oracle tuning monitors, checking dozens of important tuning metrics in real-time.
   
• Use the right Oracle tuning tools - Following Oracle tuning best practices does not mean that you have to acquire expensive tools such as Oracle Performance Pack.  Oracle has a wealth of free tools that are as good, or better, in Oracle tuning, tools such as STATSPACK where you can closely monitor your tuning efforts over time.  There are also free third-party tuning analysis tools such as my statspackanalyzer.
   
• Don't listen to fake experts - One of the biggest problems in Oracle tuning best practices is the bad advice that is proffered by many foreigners, self-proclaimed Oracle experts, who deceive the public with false credentials and exaggerated accomplishments.  As a general rule, avoid any Oracle expert who does not advertise their training and job experience.  A real Oracle expert is proud of their Oracle tuning experience, they don't hide it.  Avoid the advice of any expert who does not make it easy to find their resume.  There is usually a very good reason that they hide their qualifications!

Also see my notes on Oracle Worst Practices.  For additional support on your Oracle tuning, feel free to contact Burleson Consulting.  We are real Oracle experts, seasoned veterans with years of hand-on tuning experience.  Our Oracle health check is a great way to ensure that you are following Oracle tuning best practices:

Notes on Oracle tuning best practices

See my notes here for related articles on Oracle tuning best practices:

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.