Oracle Performance Tuning - Part 1
September 14, 2005
Performance tuning is a
broad and somewhat complex topic area when it comes to Oracle databases. Two of
the biggest questions faced by your average DBA concern where to start and what
to do. All you may know is that someone (a user) reports a problem about a slow
or poor performing application or query. Where do you even begin to start when
faced with this situation?
Oracle's Approach to Tuning
For anyone who has taken the
Performance Tuning exam for Oracle8i certification, one of the testable areas
dealt with Oracle's Tuning Methodology. Oracle's emphasis on this particular methodology
changed when Oracle9i was released. The approach has gone from top-down in 8i
to that of following principles in 9i/10g. Neither methodology is absolute as
each has its advantages and disadvantages. In Oracle8i, the steps consisted of
Tuning the Business Rules
Tuning the Data Design
Tuning the Application Design
Tuning the Logical Structure of
Tuning Database Operations
Tuning the Access Paths
Tuning Memory Allocation
Tuning I/O and Physical Structure
Tuning Resource Contention
10. Tuning the Underlying Platform(s)
principle-based approach, the principles, in order of priority, are:
Reference: OCP: Oracle9i
Performance Tuning Study Guide, SYBEX, Inc.
Define the problem clearly
and then formulate a tuning goal.
Examine the host system
and gather Oracle statistics.
Compare the identified
problem to the common performance problems identified by Oracle in the
Oracle9i Database Performance Methods (Release 1)/Database Performance
Planning (Release 2)
Use the statistics
gathered in the second step to get a conceptual picture of what might be
happening on the system.
Identify the changes to be
made and then implement those changes.
Determine whether the
objectives identified in step one have been met. If they have, stop tuning.
If not, repeat steps five and six until the tuning goal is met.
Interestingly, the emphasis
on identifying which step an action falls under went away with Oracle9i, and
recitation of the principles is not a testable item. The title of documentation
even changed between releases one and two, and that should send a clear signal
that the art of performance tuning (or, performance and tuning) is still just
that an art. When it comes to instance tuning, the steps are even further
reduced in Oracle10g.
The performance tuning guide
for Oracle10g (Release 2) identifies the overall process as The Oracle
Performance Improvement Method. The steps have been expanded, but overall,
remain the same.
1. Perform the following initial standard checks:
a. Get candid feedback from users. Determine the
performance project's scope and subsequent performance goals, as well as
performance goals for the future. This process is key in future capacity
b. Get a full set of operating system, database, and
application statistics from the system when the performance is both good and
bad. If these are not available, then get whatever is available. Missing
statistics are analogous to missing evidence at a crime scene: They make
detectives work harder and it is more time-consuming.
c. Sanity-check the operating systems of all machines
involved with user performance. By sanity-checking the operating system, you
look for hardware or operating system resources that are fully utilized. List
any over-used resources as symptoms for analysis later. In addition, check
that all hardware shows no errors or diagnostics.
2. Check for the top ten most common mistakes with
Oracle, and determine if any of these are likely to be the problem. List
these as symptoms for later analysis. These are included because they
represent the most likely problems. ADDM automatically detects and reports
nine of these top ten issues. See Chapter
6, "Automatic Performance Diagnostics" and "Top
Ten Mistakes Found in Oracle Systems".
3. Build a conceptual model of what is happening on
the system using the symptoms as clues to understand what caused the
performance problems. See "A
Sample Decision Process for Performance Conceptual Modeling".
4. Propose a series of remedy actions and the
anticipated behavior to the system, then apply them in the order that can
benefit the application the most. ADDM produces recommendations each with an
expected benefit. A golden rule in performance work is that you only change
one thing at a time and then measure the differences. Unfortunately, system
downtime requirements might prohibit such a rigorous investigation method. If
multiple changes are applied at the same time, then try to ensure that they
are isolated so that the effects of each change can be independently
5. Validate that the changes made have had the desired
effect, and see if the user's perception of performance has improved.
Otherwise, look for more bottlenecks, and continue refining the conceptual
model until your understanding of the application becomes more accurate.
Repeat the last three steps until
performance goals are met or become impossible due to other constraints.
performance tuning guide for Oracle10g (Release 2)
The Change is Part of the Problem
The change from a top-down
structured approach to a principle-based "make it stop hurting" one is part of
the problem. Gathering statistics is obviously important because how else do
you know if you have improved (or worsened) the problem? Still, to some degree
with either approach, you are left with the original two questions: what do I
look for, and how do I make it better? If the structured approach left you
scratching your head, the principled approach only adds to the confusion.
What would help the novice
tuner (disclaimer: I am far from being an expert) is a list of items or areas
to evaluate (configure, diagnose, and tune) in each of the following areas:
Tuning the Buffer Cache
Tuning the Redo Log Buffer
Tuning the Shared Pool Memory
Tuning the Program Global Area
Optimizing Data Storage
Tuning Undo Segments
Detecting Lock Contention
These areas pretty much
cover the Oracle RDBMS and instance from top to bottom. The remainder of this
article will focus on tuning SQL, or more precisely, preventing slow SQL
execution. Aren't these the same thing? Mostly yes, but a common approach in
development is making a statement perform well enough or fast enough. Each and
every statement does not have to be optimal, but some thought has to go into
coding them. You do not have the time to optimize hundreds or even thousands of
SQL statements, but at the same time, there are guidelines you can follow to
avoid common mistakes and bad coding.
17 Tips for Avoiding Problematic Queries
The source of these 17 tips
is from Oracle9i Performance Tuning: Optimizing Database Productivity by
Hassan Afyouni (Thompson Course Technology, 2004). These tips provide a solid
foundation for two outcomes: making a SQL statement perform better, and
determining that nothing else can be done in this regard (i.e., you have done
all you can with the SQL statement, time to move on to another area).
The 17 tips are listed
Avoid Cartesian products
Avoid full table scans on large
Use SQL standards and conventions
to reduce parsing
Lack of indexes on columns
contained in the WHERE clause
Avoid joining too many tables
Monitor V$SESSION_LONGOPS to
detect long running operations
Use hints as appropriate
Use the SHARED_CURSOR parameter
Use the Rule-based optimizer if I
is better than the Cost-based optimizer
10. Avoid unnecessary sorting
11. Monitor index browning (due to deletions; rebuild as
12. Use compound indexes with care (Do not repeat
13. Monitor query statistics
14. Use different tablespaces for tables and indexes (as
a general rule; this is old-school somewhat, but the main point is reduce I/O
15. Use table partitioning (and local indexes) when
appropriate (partitioning is an extra cost feature)
16. Use literals in the WHERE clause (use bind variables)
17. Keep statistics up to date
That is quite a list and
overall is thorough and accurate. Step 9, referring to the use of the
Rule-based optimizer, may cause a reliance or dependency on a feature Oracle
has identified as a future item to be deprecated. You are eventually going to
have to solve the problem using the CBO, so you may as well start now and
forget about the RBO. Step 14 should be changed to something along the
lines of "reduce I/O contention" instead of its currently stated
"separate index and table tablespaces" guidance.
In the next article of this
series, we will look at some specific steps of these tips. For example, advice
given on many Web sites about how to improve a SQL statement's performance
typically includes "use bind variables." Well, I am sure many people have this
question: "How, exactly, do I do that?" It is actually pretty simple, as are
many of the details of how to use many of these tips.
See All Articles by Columnist Steve Callan