Oracle tuning best practices
Oracle Database Tips by Donald BurlesonFebruary 17, 2015
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
- 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
- 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
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
You can buy it direct from the publisher for 30%-off and get instant
access to the code depot of Oracle tuning scripts.