Oracle performance best practices
Oracle Database Tips by Donald BurlesonFebruary 17, 2015
Managing Oracle performance is one of the most
complex areas of Oracle database management, and there are many conflicting
opinions on the "best practices" for managing Oracle performance. As a
working Oracle DBA, I offer these Oracle performance best practices, based upon
my first-hand observations of real-world Oracle shops.
Oracle performance monitoring best practices
Most successful Oracle shops have a firm performance methodology in-place to
measure end-user performance and ensure that the majority of their transactions
are completed within well-defined thresholds. Here are some of the most
common Oracle performance monitoring best practices:
- Develop a performance SLA - In order to successful monitor Oracle
performance, a comprehensive end-to-end response time SLA must be codified.
These performance SLA's may read like Ninety five percent of all
transactions must complete within one-second elapsed time". BC has
tools such as
FormsPack for Oracle which measures end-user response time for SQL*Forms
applications, and we also develop end-to-end response time performance
monitors for Apex, J2EE and other application server databases.
- Monitor performance at the system-level - Many shops fail to
monitor Oracle performance properly, and some fall into the "myopic" trap
that advocated a drill-down into the performance of critical tasks.
Shops that follow Oracle performance best practices understand that a
holistic approach is best, and they tune by adjusting system-level
techniques (adjusting Oracle parameters, CBO statistics, new indexes,
materialized views, &c). They also monitor Oracle performance using
proactive techniques such as STATSPACK, AWR and avoid real-time monitors
that cannot provide long-term performance opportunities.
- Employ Oracle performance features - Optimal features such as
partitioning can reduce query response time by an order of magnitude,
and TPC benchmark results show that optimal performance is related to
techniques such as function-based indexes, deploying multiple block buffers
and other advanced performance techniques.
- Use workload-based performance testing - The top Oracle
performance best practice is to conduct system wide performance benchmarking
using Oracle tools such as SQL Tuning Sets and the 11g SQL Performance
Analyzer. Inappropriate methods such as contrived test cases and
"proofs" cannot predict future performance. It is only by certifying a
databases optimal configuration with a real-world workload that ensures
Many Oracle shops keep fours environments, DEV
for development, TEST for unit testing, QA for pre-production
testing, and PROD for production. Take shortcuts on your
Oracle performance testing
and you risk unintended side effects.
- Optimize for high updates - Oracle performance has many aspects,
and performance optimization for
high-DML workloads is critical.
- Verify your expert advice - The Internet is clogged with
self-proclaimed Oracle experts, poser who proffer incorrect and dangerous
advice. Even some Oracle book authors have very little full-time job
experience, and you must be very careful to verify the expertise of any
Oracle expert, and reply on one that you know and trust. In general,
you should avoid anyone who claims to be an expert, but who hides their
resume and job experience.
In sum, there are many best practice techniques to monitor and ensure optimal
Oracle performance and it is not necessary to purchase expensive third party
tools to comply with Oracle performance best practices.
If you need additional advice on best
practices, feel free to contact us at 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.
Related Oracle performance best practices notes
See my related articles for more on Oracle performance best practices.
For a comprehensive treatment of Oracle performance best practices, see my book
Tuning: The Definitive Reference":
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.